Pandas: Merging rows into one

Arcyde 0
Viewed 26

I have the following table:

Name Age Data_1 Data_2
Tom 10 Test
Tom 10 Foo
Anne 20 Bar

How I can merge this rows to get this output:

Name Age Data_1 Data_2
Tom 10 Test Foo
Anne 20 Bar

I tried this code (and some other related (agg, groupby other fields, et cetera)):

import pandas as pd
data = [['tom', 10, 'Test', ''], ['tom', 10, 1, 'Foo'], ['Anne', 20, '', 'Bar']]

df = pd.DataFrame(data, columns=['Name', 'Age', 'Data_1', 'Data_2'])
df = df.groupby("Name").sum()

print(df)

But I only get something like this:

c2
Name
-------- --------------
Anne Foo
Tom Bar
pandas python-3.x
2 Answers

Use this if the empty cells are NaN :

(df.set_index(['Name', 'Age'])
   .stack()
   .groupby(level=[0, 1, 2])
   .apply(''.join)
   .unstack()
   .reset_index()
)

Otherwise, add this line df.replace('', np.nan, inplace=True) before the code above.

# Output
   Name  Age Data_1 Data_2
0  Anne   20    NaN    Bar
1   Tom   10   Test    Foo
abokey0

Just a groupby and a sum will do.

df.groupby(['Name','Age']).sum().reset_index()

   Name  Age Data_1 Data_2
0  Anne   20           Bar
1   tom   10   Test    Foo
Kevin Choon Liang Yew0
Related