python - 当重复的行具有不同的值时，如何将它们合并为 DataFrame 上的一个

Question

我有一个像下面这样的 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  

Answer 1

你可以设置你的ID和NAMEcolumns为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