从具有相同ID的行创建列 [英] Create columns from row with same ID

问题描述

我有这样的df:

Id      username  age

1       michael.     34
6.      Mike.          65
7.      Stephanie.  14
1.      Mikael.        34
6.      Mick.           65

如您所见，对于相同的ID，用户名的写法不同.我想将所有用户名重新分组到同一行:

As you can see, username are not writed the same for the same id. I would like to regroup all username to the same row like this:

Id      username      username_2    Age

1       michael.         mikael.           34
6.      Mike.             Mick.              65
7.      Stephanie.                           14

谢谢.

推荐答案

您可以通过 unstack ，最后通过 add_prefix 进行数据清理代码> 与 重置索引 :

You can create MultiIndex for count duplicated Id by cumcount and then is possible reshape by unstack, last some data cleaning by add_prefix with reset_index:

df1 = (df.set_index(['Id', df.groupby('Id').cumcount()])['username']
         .unstack(fill_value='')
         .add_prefix('username_')
         .reset_index())
print (df1)
    Id username_0 username_1
0  1.0    michael     Mikael
1  6.0       Mike       Mick
2  7.0  Stephanie           

从 1 开始的

或重命名列:

df1 = (df.set_index(['Id', df.groupby('Id').cumcount()])['username']
         .unstack(fill_value='')
         .rename(columns = lambda x: f'username_{x+1}')
         .reset_index())
print (df1)
    Id username_1 username_2
0  1.0    michael     Mikael
1  6.0       Mike       Mick
2  7.0  Stephanie        

这篇关于从具有相同ID的行创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！