[FIXED] How to calculate correlation for one column vs rest of the dataframe columns grouped by index

Issue

I have a data frame like this:

df:
         Col-1   Col-2   Col-3 ..................Col-100
index   
  A        23      33     1                        5                       
  A        33      22     4                        29
  A        1       5      3                        24
  B        65      12     4                        42
  B        2       24     4                        24
  B        3       12     24                       5

I want to calculate the correlation for Col-1 against all the other columns and group them by Index.

So my output looks like this:

       Col-1  Col-2  Col-3 ............Col-100
  A    1      0.24    0.2               0.45
  B    1      0.30    0.25              0.18

Can anyone help me with this?

Solution

You can use groupby.apply:

df.groupby(level=0).apply(lambda x: x.corrwith(x.iloc[:,0]))

Output:

       Col-1     Col-2     Col-3   Col-100
index                                     
A        1.0  0.757782  0.119968 -0.014472
B        1.0 -0.511951 -0.487953  0.850923

Answered By - Quang Hoang

Answer Checked By - David Goodson (PHPFixing Volunteer)