python - 如何在 pandas df 中沿行进行分组和关联？

Question

我有一个 pandas df，如下所示，其中可以有多个名称和变量 x1、x2、x3 等的组。

group name    x1        x2        x3
1      A      32        21        34
1      B      12        13        14
1      C      12        14        16
2      A      23        19        26
2      B      18        28        45
2      C      12        46        13

我想在每个单独组的每个名称之间形成一个相关矩阵，如下所示:

group 1
       A                           B                                              C
A      1        correlation between A and B in Group 1        correlation between A and C in Group 1
B      ``                          1                                              ``
C      ``                         ``                                              1

group 2
       A                           B                                              C
A      1        correlation between A and B in Group 2        correlation between A and C in Group 2
B      ``                          1                                              ``
C      ``                         ``                                              1

请问pandas有什么函数可以得到这样的相关矩阵？谢谢!

Answer 1

我对 DataFrame.melt 的处理方法和 DataFrame.pivot_table

corr_df = (df.melt(['group','name'])
             .pivot_table(index = ['group','variable'],
                          columns = 'name',
                          values = 'value')
             .groupby('group')
             .corr()
            #.apply(pd.DataFrame.corr) #if previous line doesn't work
          )
print(corr_df)
name               A         B         C
group name                              
1     A     1.000000  0.142857  0.142857
      B     0.142857  1.000000  1.000000
      C     0.142857  1.000000  1.000000
2     A     1.000000  0.556267 -0.892854
      B     0.556267  1.000000 -0.122427
      C    -0.892854 -0.122427  1.000000

详情

print(df.melt(['group','name'])
        .pivot_table(index = ['group','variable'],
                      columns = 'name',
                      values = 'value'))
name             A   B   C
group variable            
1     x1        32  12  12
      x2        21  13  14
      x3        34  14  16
2     x1        23  18  12
      x2        19  28  46
      x3        26  45  13