Combining Two Pandas Dataframe with Same Columns into one String Columns

Question

I have two Pandas dataframes ie:

+-------+-------------------+--+
| Name  |       Class       |  |
+-------+-------------------+--+
| Alice | Physics           |  |
| Bob   | "" (Empty string) |  |
+-------+-------------------+--+

Table 2:

+-------+-----------+
| Name  |   Class   |
+-------+-----------+
| Alice | Chemistry |
| Bob   | Math      |
+-------+-----------+

Is there a way to combine it easily on the column Class so the resulting table is like:

+-------+--------------------+
| Name  |       Class        |
+-------+--------------------+
| Alice | Physics, Chemistry |
| Bob   | Math               |
+-------+--------------------+

I also want to make sure there are no extra commas when adding columns. Thanks!

Just append the two, groupby name, then concatenate

ifly6
– ifly6

2021-07-20 20:05:54 +00:00
Commented Jul 20, 2021 at 20:05 — ifly6
– ifly6, Commented Jul 20, 2021 at 20:05

Brendan · Accepted Answer · 2021-07-20 20:14:30Z

2

df = pd.DataFrame({'Name':['Alice','Bob'],
                   'Class':['Physics',np.nan]})
df2 = pd.DataFrame({'Name':['Alice','Bob'],
                   'Class':['Chemistry','Math']})

df3 = df.append(df2).dropna(subset=['Class']).groupby('Name')['Class'].apply(list).reset_index()

# to remove list
df3['Class'] = df3['Class'].apply(lambda x: ', '.join(x))

answered Jul 20, 2021 at 20:14

Brendan

4,0111 gold badge17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

not_speshal · Accepted Answer · 2021-07-20 20:10:42Z

1

Try with concat and groupby:

>>> pd.concat([df1, df2]).groupby("Name").agg(lambda x: ", ".join(i for i in x.tolist() if len(i.strip())>0)).reset_index()
                    
Name                Class     
Alice  Physics, Chemistry
Bob                  Math

answered Jul 20, 2021 at 20:10

not_speshal

23.2k2 gold badges18 silver badges33 bronze badges

Collectives™ on Stack Overflow

Combining Two Pandas Dataframe with Same Columns into one String Columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related