Pandas融化得到多个变量和值列

Question

我有熊猫数据帧

data = {
"SomeCol":[1,2],
"Group1":['x','y'],
"Group2":['a','b'],
"SomeAgg":[100,200]
}
df = pd.DataFrame(data)

   SomeCol Group1 Group2  SomeAgg
0        1      x      a      100
1        2      y      b      200

我怎样才能得到这个：

^{pr2}$

我试过pd.melt函数，它只给出一个变量和一个值列。在

任何帮助都将不胜感激

Answer 1

import pandas as pd

data = {
"SomeCol":[1,2],
"Group1":['x','y'],
"Group2":['a','b'],
"SomeAgg":[100,200]
}
df = pd.DataFrame(data)

s1 = df.melt(id_vars=['SomeCol','SomeAgg'], value_vars=['Group1'],var_name='Var1', value_name='Var1_value')
s2 = df.melt(id_vars=['SomeCol'], value_vars=['Group2'],var_name='Var2', value_name='Var2_value')
ls = ['SomeCol','Var1','Var1','Var1_value', 'Var2_value','SomeAgg']
result = s1.merge(s2,on='SomeCol')
print(result[ls])

Answer 2

也可以不使用pd.melt来获得解决方案：

data = {
"SomeCol":[1,2],
"Group1":['x','y'],
"Group2":['a','b'],
"SomeAgg":[100,200]
}
df = pd.DataFrame(data)
col_names = ['Group1', 'Group2']
df['Var1'], df['Var2'] = col_names[0], col_names[1]
df = df.rename(columns={col_names[0]: 'Var1_Value', col_names[1]: 'Var2_Value'})

print(df)

   SomeCol Var1_Value Var2_Value  SomeAgg    Var1    Var2
0        1          x          a      100  Group1  Group2
1        2          y          b      200  Group1  Group2

Answer 3

我想你是在尝试多索引。此代码直接来自文档：
https://pandas.pydata.org/pandas-docs/stable/user_guide/cookbook.html#cookbook-multi-index

   row  One_X  One_Y  Two_X  Two_Y
0    0    1.1    1.2   1.11   1.22
1    1    1.1    1.2   1.11   1.22
2    2    1.1    1.2   1.11   1.22

# As Labelled Index
In [76]: df = df.set_index('row')

In [77]: df
Out[77]: 
     One_X  One_Y  Two_X  Two_Y
row                            
0      1.1    1.2   1.11   1.22
1      1.1    1.2   1.11   1.22
2      1.1    1.2   1.11   1.22

# With Hierarchical Columns
In [78]: df.columns = pd.MultiIndex.from_tuples([tuple(c.split('_'))
   ....:                                         for c in df.columns])
   ....: 

In [79]: df
Out[79]: 
     One        Two      
       X    Y     X     Y
row                      
0    1.1  1.2  1.11  1.22
1    1.1  1.2  1.11  1.22
2    1.1  1.2  1.11  1.22