如果这是重复的,我很抱歉-我似乎找不到合适的关键字来获得我想要的点击量。
假设我有一个类似的DataFrame
:
name desc class group subgroup
0 abc _d class1 group1 sg1
1 def _g class1 group1 sg1
2 ghi _j class1 group1 sg2
3 jkl _m class1 group1 sg2
4 mno _p class2 group2 sg1
5 pqr _s class2 group2 sg1
6 stu _v class2 group2 sg2
7 vwx _y class2 group2 sg2
最终,我想将框架重塑为如下所示:
class group subgroup name1 desc1 name2 desc2
0 class1 group1 sg1 abc _d def _g
1 class1 group1 sg2 ghi _j jkl _m
2 class2 group2 sg1 mno _p pqr _s
3 class2 group2 sg2 stu _v vwx _y
不太理想,即使是列表格式(如果需要,我可以稍后拆分(:
class group subgroup name desc
0 class1 group1 sg1 [abc, def] [_d, _g]
1 class1 group1 sg2 [ghi, jkl] [_j, _m]
2 class2 group2 sg1 [mno, pqr] [_p, _s]
3 class2 group2 sg2 [stu, vwx] [_v, _y]
我尝试使用df.pivot(index=['class','group','subgroup'],columns=['name','desc'])
,但很明显这不起作用,因为它不能扩展列。它只是给了我一个ValueError
:
ValueError: Length mismatch: Expected 8 rows, received array of length 3
我可以使用df.groupby(['class', 'group', 'subgroup']).sum()
,但它会损坏name
/desc
:
name desc
class group subgroup
class1 group1 sg1 abcdef _d_g
sg2 ghijkl _j_m
class2 group2 sg1 mnopqr _p_s
sg2 stuvwx _v_y
我可能可以通过几个步骤来做到这一点,但我想知道是否有一个明显的解决方案是我完全错过的?
以下是使用groupby.cumcount
创建最终1、2、。。列名称中。然后是CCD_ 8和CCD_。如果你不想要一个多索引列,那么把它展平:
df_f = df.assign(cc=df.groupby(['class', 'group', 'subgroup']).cumcount()+1)
.set_index(['class', 'group', 'subgroup', 'cc']).unstack()
# if you don't want the multiindex columns
df_f.columns = [f'{col[0]}{col[1]}' for col in df_f.columns]
print (df_f)
name1 name2 desc1 desc2
class group subgroup
class1 group1 sg1 abc def _d _g
sg2 ghi jkl _j _m
class2 group2 sg1 mno pqr _p _s
sg2 stu vwx _v _y
这比我预期的要棘手。其基本思想是对分组后的列进行.apply(list)
。但是,我无法在groupby
之后同时选择多个列。出于这个原因,我使用了列表理解,并将生成的两个系列与pd.concat(axis=1)
连接起来。这也可能是一行,但我认为如果在一行中完成,它缺乏可读性
selects = ['desc','name']
list_of_series = [so.groupby(['class', 'group', 'subgroup'])[val].apply(list) for val in selects]
so_new = pd.concat(list_of_series,axis=1)
输出
desc name
class group subgroup
class1 group1 sg1 [_d, _g] [abc, def]
sg2 [_j, _m] [ghi, jkl]
class2 group2 sg1 [_p, _s] [mno, pqr]
sg2 [_v, _y] [stu, vwx]
未建议的单行
so_new2 = pd.concat([so.groupby(['class', 'group', 'subgroup'])[val].apply(list) for val in ['desc','name']],axis=1)
更新的答案
selects = ['desc', 'name']
df = pd.concat([pd.DataFrame(lst.values.tolist(),columns=[selects[ii]+"1",selects[ii]+"2"],index=lst.index)
for ii,lst in enumerate([so.groupby(['class', 'group', 'subgroup'])[val].apply(list)
for val in selects])],axis=1)
输出
desc1 desc2 name1 name2
class group subgroup
class1 group1 sg1 _d _g abc def
sg2 _j _m ghi jkl
class2 group2 sg1 _p _s mno pqr
sg2 _v _y stu vwx
正如我的问题中所提到的,我能够通过几个步骤实现这一点:
df = df.pivot_table(index=['class','group','subgroup'], values=['name','desc'], aggfunc=list)
# desc name
# class group subgroup
# class1 group1 sg1 [_d, _g] [abc, def]
# sg2 [_j, _m] [ghi, jkl]
# class2 group2 sg1 [_p, _s] [mno, pqr]
# sg2 [_v, _y] [stu, vwx]
df = df.reset_index()
# Create new columns and join it back
df = df.join(pd.DataFrame(df.desc.to_list(), columns=['desc0','desc1']))
df = df.join(pd.DataFrame(df.name.to_list(), columns=['name0','name1']))
# Remove the superfluous listed columns
df.drop(['desc','name'], axis=1, inplace=True)
结果:
class group subgroup desc0 desc1 name0 name1
0 class1 group1 sg1 _d _g abc def
1 class1 group1 sg2 _j _m ghi jkl
2 class2 group2 sg1 _p _s mno pqr
3 class2 group2 sg2 _v _y stu vwx
理想情况下,我仍然希望步骤更少,并想知道是否存在更好的方法。