CODEWITHSUNDEEP
pythonpandasdataframemerge
bobglu
bobglu

Reputation: 95

Combining Two Pandas Dataframe with Same Columns into one String Columns

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!

Upvotes: 2

Views: 78

Answers (2)

Brendan
Brendan

Reputation: 4011

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))

Upvotes: 2

not_speshal
not_speshal

Reputation: 23146

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

Upvotes: 1

Related Questions