How to correlate each row pair to the previous column?

user_15214059 0
Viewed 126

I have the following dataframe:

mydata

(shape is 72,22)

I would like to correlate each country to each other country, every year. This would result in 21 dataframes of shape 72*72. I guess what's confusing me is that correlation is defined as the relationship of the change between variables, and I'm unsure of how to shift the dataframe to compare the current year to the previous year(hence 21 instead of 22).

I've tried

corr = {}
for x in dfpivot.columns:
    corr[x] = dfpivot[x].corr()

And

corr = {}
for x in dfpivot.T.index:
    corr[x] = dfpivot.T.loc[x].corr()

And I get TypeError: corr() missing 1 required positional argument: 'other'

So I did:

corr = {}
for x in dfpivot.columns:
    corr[x] = dfpivot[x].corr(dfpivot.loc[:,x])

But this correlates each row to itself (meaning that I get all values of 1).

So this last one seemed to me what should work, yet it doesn't. Why does this return one value per year?:

corr = {}
for x in dfpivot.columns:
    for y in dfpivot.columns[1:]:
        corr[x] = dfpivot[x].corr(dfpivot.loc[:,y])

result:

{'1999-01-01': -0.7847692673880999,
 '2000-01-01': 0.5179357977713173,
 '2001-01-01': -0.8006230706819144,
 '2002-01-01': -0.8608851552658657,
 '2003-01-01': -0.23298450629551196,
 '2004-01-01': -0.792648030305533,
 '2005-01-01': 0.6711413744370501,

Can anyone help?

Data:

['1999-01-01',
 '2000-01-01',
 '2001-01-01',
 '2002-01-01',
 '2003-01-01',
 '2004-01-01',
 '2005-01-01',
 '2006-01-01',
 '2007-01-01',
 '2008-01-01',
 '2009-01-01',
 '2010-01-01',
 '2011-01-01',
 '2012-01-01',
 '2013-01-01',
 '2014-01-01',
 '2015-01-01',
 '2016-01-01',
 '2017-01-01',
 '2018-01-01',
 '2019-01-01',
 '2020-01-01']

['Africa',
 'All Countries Total',
 'Argentina',
 

[ 2299., -1538.,    nan, -1851.,  1604., -1827., -2047.,  -216.,
          985.,  1338.,  4694.,    16., -2143.,  2830., -2395.,   140.,
         -406.,  5675.,  1110., -2973., -1380.,  1414.]

[ 61756., -12431.,  11624.,  26483.,  -6609.,  20039., -15386.,
        -21390., -17339., -31049.,  48324., -41960.,  17528.,  17136.,
        -12768.,   2743., -17969., -20280., -38804.,  90313., -98720.,
        -66081.]

[  914.,   137.,   151.,  -623.,  -693.,   634.,    nan,    nan,
           nan,   -71.,   427., -3659.,    nan,    nan,   452.,   443.,
         -495., -1097.,   557., -5454.,   910.,    nan]
pandas correlation dataframe for-loop python
1 Answers

I may be out of my league here but it sounds like you're not after corr(). The way I understand this, is you want to perform a corr() on a dataframe consisting of current year(2020) and 1 other year for every year. That's just 2 columns in every dataframe. Not enough? If you want to do it it'll only return 1s or -1s for every year, at least that's what I'm getting for every year:

lets transpose your dataframe:

df2 = df.transpose()

now it looks more like this:

          Africa    All C...Total   Argentina
1999-01-01  2299.0    61756.0   914.0
2000-01-01  -1538.0  -12431.0   137.0
2001-01-01  NaN      11624.0    151.0
2002-01-01  -1851.0  26483.0    -623.0
2003-01-01  1604.0   -6609.0    -693.0
2004-01-01  -1827.0  20039.0    634.0
2005-01-01  -2047.0 -15386.0    NaN
2006-01-01  -216.0  -21390.0    NaN
2007-01-01  985.0   -17339.0    NaN
...

apparently we need a list to store our dataframes (naming them in a loop is not pythonic - I've just learned that from this answer)

list_of_df = list()  # <-- here we're going to store the dataframes for each year
index_list = df2.index
current_year = index_list[-1]
for i in range(len(index_list)):
    df_corelated = df2.loc[[index_list[i] , current_year]].corr()
    list_of_df.append(df_corelated)

now everything is ready in a list, want the... say fourth year?:

list_of_df[3]

               Africa   All Countries Total Argentina
Africa                   1.0    -1.0    NaN
All Countries Total     -1.0    1.0     NaN
Argentina                NaN    NaN     NaN

just 1s and -1s

user_165194240
Related