I have two pandas dataframes that look about the same but with different information stored in them. My question will be about how to compare the two dataframes to ensure column and row match before performing some analysis and to obtain a third dataframe of the correlation between the two.

df1 (50x14492):

Name1 .0945 .0845
Name2 .9074 Nan
Name3 1     0

and df2 (50x14492):

Name1 .9045 .895
Name2 .074  1
Name3 .5    .045

Hoped for result df3 that is as yet unobtained (50x14492):

TYPE  GENRE1                 GENRE2
Name1 spearsonr(.9045,.9045) spearsonr(.0845,.895)
Name2 spearsonr(.9074,.074)  spearsonr(Nan,1)
Name3 spearsonr(1,.5)        spearsonr(0,.045)

I'd like to compare each row of each column (df1.GENRE1.Name1 to df2.GENRE1.Name1, df1.GENRE1.Name2 to df2.GENRE1.Name2, etc.) but am getting lost in the implementation. In order to try to do this I have the following code:

for key1, value1 in df1.iteritems():
    for key2, value2 in df2.iteritems():
        if key1 == key2:
           # this gets me to df1.GENRE1 == df2.GENRE1
           for newkey1, newval1 in key1.iterrows():
               for newkey2, newval2 in key2.iterrows():
                   if newkey1 == newkey2:
                      # this does not seem to get me to df1.GENRE1.Name1 == df2.GENRE1.Name1
                      scipy.stats.spearmanr(newval1, newval2)

This is allowing me to get as far as to compare the coulmns (df1.GENRE1 and df2.GENRE1 match) but I am not sure how to get to the next logical step of also ensuring that the rows match (df1.GENRE1.Name1 == df2.GENRE1.Name1). Another way to put it, I am unsure of how to ensure the rows match now that I have the columns.

NOTE: I have tried to use spearmanr on the full two dataframes as such:

corr, p_val = scipy.stats.spearmanr(df1, df2, axis=0, nan_policy='omit')

but rather than getting a new dataframe of the same size (50x14492, telling me that each value in the dataframe was evaluated with spearmanr) I am getting a table back that's 100x100.

Similarly if I use:

corr, p_val = scipy.stats.spearmanr(df1['GENRE1'], df2['GENRE1'], axis=0, nan_policy='omit')

I get the correlation of the two columns as a whole (1x1, rather than each row of that column, which would be of size 1X14492)

  • $\begingroup$ reading your decribtion it seems that you want to compute "corelation" between cells of two tables, right? I think that correlation is not defined in between just two numbers... you can compute correlation for rows or columns but not for cells... what would be your correlation between 1 and 0.5? $\endgroup$ – Jirka B. Apr 23 '19 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.