How to merge rows in dataframe with different columns?

Question

I want to merge rows of dataframe with one common column value and then merge rest of the column values separated by comma for string values and convert to array/list for int values.

A   B     C    D
1  one   100  value
4  four  400  value
5  five  500  value
2  two   200  value

Expecting result like:

   A                B                 C            D
[1,4,5,2]  one,four,five,two  [100,400,500,200]  value

I can use groupby for column D but how can I use apply for columns A,C as apply(np.array) and apply(','.join) for column B in df all at once?

jezrael · Accepted Answer · 2019-06-25 05:45:31Z

3

Dynamic solution - strings columns are joined and numeric are converted to lists with GroupBy.agg:

f = lambda x: x.tolist() if np.issubdtype(x.dtype, np.number) else ','.join(x)
#similar for test strings - https://stackoverflow.com/a/37727662
#f = lambda x: ','.join(x) if np.issubdtype(x.dtype, np.flexible) else x.tolist()
df1 = df.groupby('D').agg(f).reset_index().reindex(columns=df.columns)
print (df1)
              A                  B                     C      D
0  [1, 4, 5, 2]  one,four,five,two  [100, 400, 500, 200]  value

Another solution is specify each functions separately for each column:

df2 = (df.groupby('D')
        .agg({'A': lambda x: x.tolist(), 'B': ','.join, 'C':lambda x: x.tolist()})
        .reset_index()
        .reindex(columns=df.columns))

print (df2)

              A                  B                     C      D
0  [1, 4, 5, 2]  one,four,five,two  [100, 400, 500, 200]  value

edited Jun 25, 2019 at 5:45

answered Jun 25, 2019 at 5:29

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

iamklaus · Accepted Answer · 2019-06-25 05:36:41Z

2

df = df.groupby('D').apply(lambda x: pd.Series([list(x.A),','.join(x.B),list(x.C)])).reset_index().rename({0:'A',1:'B',2:'C'}, axis=1)

df = df[['A','B','C','D']]

Output

              A                  B                     C      D
0  [1, 4, 5, 2]  one,four,five,two  [100, 400, 500, 200]  value

answered Jun 25, 2019 at 5:36

iamklaus

3,7682 gold badges14 silver badges21 bronze badges

Comments

U13-Forward · Accepted Answer · 2019-06-25 05:38:41Z

0

Why not a one-liner agg:

>>> df.groupby('D', as_index=False).agg(lambda x: x.tolist() if x.dtype != object else ','.join(x))[df.columns]
              A                  B                     C      D
0  [1, 4, 5, 2]  one,four,five,two  [100, 400, 500, 200]  value
>>>

answered Jun 25, 2019 at 5:38

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

How to merge rows in dataframe with different columns?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related