pandas 部分转置 [英] Pandas partial transpose

问题描述

我想通过转置一些列并修复其他列来重新格式化数据框.

I want to reformat a dataframe by transeposing some columns with fixing other columns.

原始数据:

ID  subID  values_A  
--  -----  --------  
A    aaa     10      
B    baa     20      
A    abb     30      
A    acc     40      
C    caa     50      
B    bbb     60      

旋转一次:

 pivot_table( df, index = ["ID", "subID"] )

输出:

ID  subID  values_A 
--  -----  -------- 
A    aaa     10     
     abb     30     
     acc     40     

B    baa     20     
     bbb     60     

C    caa     50     

我想要做什么(修复 ['ID'] 列和部分转置):

What I want to do ( Fix ['ID'] columns and partial transpose ) :

ID  subID_1  value_1   subID_2  value_2  subID_3  value_3
--  -------  -------  --------  -------  -------  -------
A    aaa       10       abb       30       acc       40
B    baa       20       bbb       60       NaN       NaN 
C    caa       50       NaN       NaN      NaN       NaN 

据我所知，每个 ID 下的最大子 ID 计数值.

what I know max subIDs count value which are under each IDs.

旋转和转置数据帧时我不需要任何计算值.

I don't need any calculating value when pivot and transepose dataframe.

请帮忙

推荐答案

使用 cumcount 作为计数器，通过 set_index，由 unstack 并在列中对 MultiIndex 的第一级进行排序 来自 sort_index.最后通过 list comprehension 和 reset_index:

Use cumcount for counter, create MultiIndex by set_index, reshape by unstack and sort first level of MultiIndex in columns by sort_index. Last flatten it by list comprehension with reset_index:

g = df.groupby('ID').cumcount()

df = df.set_index(['ID', g]).unstack().sort_index(level=1, axis=1)
#python 3.6+
df.columns = [f'{a}_{b+1}' for a, b in df.columns]
#python bellow
#df.columns = ['{}_{}'.format(a, b+1) for a, b in df.columns]
df = df.reset_index()
print (df)
  ID subID_1  values_A_1 subID_2  values_A_2 subID_3  values_A_3
0  A     aaa        10.0     abb        30.0     acc        40.0
1  B     baa        20.0     bbb        60.0     NaN         NaN
2  C     caa        50.0     NaN         NaN     NaN         NaN

这篇关于 pandas 部分转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！