How do I correlate two pandas dataframes, find a single r value for all values? I don't want to correlate columns or rows, but all scalar values. One dataframe is the x axis, and the other dataframe is the y axis.
I downloaded identically structured csv files here: https://www.gapminder.org/data/ The tables have years for columns, countries for rows, with numerical values for the indicator that each table reports.
For instance, I want to see how the Political Participation Indicator (gapminder calls it an index, but I don't want to confuse it with a dataframe index) correlates overall with the Government Functioning Indicator, by year and country.
pol_partix_idx_EIU_df = pd.read_csv('polpartix_eiu.csv',index_col=0)
govt_idx_EIU_df = pd.read_csv('gvtx_eiu.csv',index_col=0)
pol_partix_idx_EIU_df.head()
2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018
country
Afghanistan 0.222 0.222 0.222 0.250 0.278 0.278 0.278 0.278 0.389 0.389 0.278 0.278 0.444
Albania 0.444 0.444 0.444 0.444 0.444 0.500 0.500 0.500 0.500 0.556 0.556 0.556 0.556
Algeria 0.222 0.194 0.167 0.223 0.278 0.278 0.389 0.389 0.389 0.389 0.389 0.389 0.389
Angola 0.111 0.250 0.389 0.416 0.444 0.444 0.500 0.500 0.500 0.500 0.556 0.556 0.556
Argentina 0.556 0.556 0.556 0.556 0.556 0.556 0.556 0.556 0.556 0.611 0.611 0.611 0.611
You can correlate by column or row:
pol_partix_idx_EIU_df.corrwith(govt_idx_EIU_df, axis=0)
2006 0.738297
2007 0.745321
2008 0.731913
...
2018 0.718520
dtype: float64
pol_partix_idx_EIU_df.corrwith(govt_idx_EIU_df, axis=1)
country
Afghanistan 6.790123e-01
Albania -5.664265e-01
...
Zimbabwe 4.456537e-01
Length: 164, dtype: float64
But, I want a single r value that compares every field in one table with every corresponding field in the other table. Essentially, I want the r value of this scatterplot:
plt.scatter(pol_cultx_idx_EIU_df,govt_idx_EIU_df)
plt.xlabel('Political participation')
plt.ylabel('Government functioning')
(The example code won't color the plot like this, but plots the same points.)
The second part of the question would be how to do this with tables that aren't exactly identical in structure. Every table (dataframe) I want to compare has country records and year columns, but not all of them have the same countries or years. In the example above, they do. How do I get a single r value for only the shared rows and columns of the dataframes?
I've simulated a setup that I think mimics yours--three dataframes with countries across rows and years across columns. I then concatenate the different sets of data into a single dataframe. And show how to compute the correlation between them. Let me know if something about this example doesn't match your setup.
Notice that, like your setup, some countries/years are not present in different datasets.
We can turns these into multi-indexed series by stacking along
year
and then concatenate these across columns usingpd.concat
.And we can compute a 3x3 correlation matrix across the three different sets.