pandas :将值上的列分组并创建新的列标题 [英] Pandas: grouping a column on a value and creating new column headings

问题描述

我正在尝试重新排列熊猫数据框.

I am trying to re-arrange a pandas dataframe.

目前我有:

id  mins  param
1   10    0.15
1   11    0.16 
1   12    0.17
2   10    0.20
2   11    0.21
2   12    0.22

但是我想重新排列并按ID分组:

But I would like to re-arrange and group by id to have:

id param_10 param_11 param_12 
1  0.15     0.16     0.17  
2  0.20     0.21     0.22

在大熊猫中，有没有一种有效的方法?还是我应该手动慢慢研磨所有东西?

Is there an efficient way to do this in pandas? or should I just grind everything slowly manually?

谢谢

推荐答案

使用 pivot 或 和最后一个 add_prefix :

Use pivot or unstack with set_index and last add_prefix:

df = df.pivot(index='id', columns='mins', values='param').add_prefix('param_')
print (df)
mins  param_10  param_11  param_12
id                                
1         0.15      0.16      0.17
2         0.20      0.21      0.22

---

df = df.set_index(['id','mins'])['param'].unstack().add_prefix('param_')
print (df)
mins  param_10  param_11  param_12
id                                
1         0.15      0.16      0.17
2         0.20      0.21      0.22

如果出错:

ValueError:索引包含重复的条目，无法重塑

ValueError: Index contains duplicate entries, cannot reshape

表示重复，需要汇总.使用 pivot_table 或 unstack :

it means duplicates, need aggregation. Use pivot_table or groupby with function like mean ( sum, median, ...) and last unstack:

print (df)
   id  mins  param
0   1    10   0.15 <- id mins dupe
1   1    10   0.50 <- id mins dupe
2   1    11   0.16
3   1    12   0.17
4   2    10   0.20
5   2    11   0.21
6   2    12   0.22

df = df.pivot_table(index='id', columns='mins', values='param', aggfunc='mean')
       .add_prefix('param_')
print (df)
mins  param_10  param_11  param_12
id                                
1        0.325      0.16      0.17 <- (0.15+0.5)/2 = 0.325
2        0.200      0.21      0.22

---

df = df.groupby(['id','mins'])['param'].mean().unstack().add_prefix('param_')
print (df)
mins  param_10  param_11  param_12
id                                
1        0.325      0.16      0.17 <- (0.15+0.5)/2 = 0.325
2        0.200      0.21      0.22

通过"> c13> 用于id列:

For cleaning set columns name to None by rename_axis and use reset_index for column id:

df = df.rename_axis(None, axis=1).reset_index()
print (df)
   id  param_10  param_11  param_12
0   1     0.325      0.16      0.17
1   2     0.200      0.21      0.22

多列解决方案:

df['param1'] = df['param'] / 4
print (df)
   id  mins  param  param1
0   1    10   0.15  0.0375
1   1    10   0.50  0.1250
2   1    11   0.16  0.0400
3   1    12   0.17  0.0425
4   2    10   0.20  0.0500
5   2    11   0.21  0.0525
6   2    12   0.22  0.0550

df = df.pivot_table(index='id', columns='mins', values=['param', 'param1'], aggfunc='mean')
df.columns = ['_'.join((x[0], str(x[1]))) for x in df.columns]
print (df)
    param_10  param_11  param_12  param1_10  param1_11  param1_12
id                                                               
1      0.325      0.16      0.17    0.08125     0.0400     0.0425
2      0.200      0.21      0.22    0.05000     0.0525     0.0550

这篇关于 pandas :将值上的列分组并创建新的列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！