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):
TYPE GENRE1 GENRE2
Name1 .0945 .0845
Name2 .9074 Nan
Name3 1 0
and df2 (50x14492):
TYPE GENRE1 GENRE2
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)