Group by dataframe in python and concat strings on multiple columns

Question

I have dataframe like below

A,B,C,D
91102,1,john,
91102,2,john,
91102,3,john,
91102,1,,mary
91102,2,,mary
91102,3,,mary
91103,1,sarah,
91103,2,sarah,
91103,3,sarah,
91103,1,,khan
91103,2,,khan
91103,3,,khan

and I want groupby column A and column B and want get desired output like below

A,B,C,D
91102,1,john,mary
91102,2,john,mary
91102,3,john,mary
91103,1,sarah,khan
91103,2,sarah,khan
91103,3,sarah,khan

I tried below but not giving desired output

df=df.groupby(['A', 'B'], as_index=False).agg('' .join)

Can you please do df.to_dict() and post the result here to give us an easier time reconstructing your dataframe? — bitflip
– bitflip, Commented Sep 21, 2022 at 23:42
Can you please edit your question and put the sample input and expected output as text? (Not as a picture?) — Andrej Kesely
– Andrej Kesely, Commented Sep 21, 2022 at 23:42

sitting_duck · Accepted Answer · 2022-09-22 00:05:47Z

2

In the groupby you could back-fill and then take the first row of the group.

df.groupby(['A','B'], as_index=False).apply(lambda x: x.bfill().iloc[0])

Result

       A  B      C     D
0  91102  1   john  mary
1  91102  2   john  mary
2  91102  3   john  mary
3  91103  1  sarah  khan
4  91103  2  sarah  khan
5  91103  3  sarah  khan

answered Sep 22, 2022 at 0:05

sitting_duck

3,7901 gold badge17 silver badges20 bronze badges

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

Comments

Andrej Kesely · Accepted Answer · 2022-09-22 00:14:31Z

1

Try:

x = df.set_index(["A", "B"]).stack().unstack().reset_index()
print(x)

Prints:

       A  B      C     D
0  91102  1   john  mary
1  91102  2   john  mary
2  91102  3   john  mary
3  91103  1  sarah  khan
4  91103  2  sarah  khan
5  91103  3  sarah  khan

answered Sep 22, 2022 at 0:14

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Collectives™ on Stack Overflow

Group by dataframe in python and concat strings on multiple 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