我有一个如下的DataFrame

ID  NAME    TEL_1   TEL_2   TEL_3
1   John    123456  754987  465317
1   John    465987          465987
1   John            546783
2   Robert  264687
2   Robert          462531
3   William 432645  765346  875137

我需要合并具有相同ID的行,保存电话值,如下所示:
ID  NAME    TEL_1   TEL_2   TEL_3   TEL_4   TEL_5   TEL_6
1   John    123456  754987  465317  465987  465987  546783
2   Robert  264687  462531
3   William 432645  765346  875137

最佳答案

您可以将IDNAMEcolumns设置为index,对它们使用groupby,然后水平地concat各自的rows以获得所需的输出:

persons = df.set_index(['ID', 'NAME']).groupby(level=['ID', 'NAME'])
new_df =pd.DataFrame()
for details, phones in persons:
    person_phones = pd.concat([row for i, row in phones.iterrows()]).to_frame()
    person_phones.index = ['TEL_{}'.format(i) for i in range(len(person_phones))]
    new_df = pd.concat([new_df, person_phones], axis=1)

new_df.transpose().reset_index().rename(columns={'level_0': 'ID', 'level_1': 'NAME'})

得到:
   ID     NAME   TEL_0   TEL_1   TEL_2   TEL_3   TEL_4   TEL_5  TEL_6   TEL_7  \
0   1     John  123456  754987  465317  465987     NaN  465987    NaN  546783
1   2   Robert  264687     NaN     NaN     NaN  462531     NaN    NaN     NaN
2   3  William  432645  765346  875137     NaN     NaN     NaN    NaN     NaN

   TEL_8
0    NaN
1    NaN
2    NaN

08-25 10:50
查看更多