我有一个如下的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
最佳答案
您可以将ID
和NAME
columns
设置为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