Pandas: If three columns have same entries then merge the 4th column(which is integer) and show count in 5th column

Asked Sep 22 '22 at 09:31

Active Sep 22 '22 at 12:26

Viewed 24 times

I have a datafame which will look like this: where col4 has unique ID. There are posts on concatenating strings, but I have to concatenate integer which is throwing error if I am using str(int) which is not the usual case

col1	col2	col3	col4	col5
1999	ABC	ggg	1	kogyk
1999	ABC	ggg	2	hfu
1989	CAT	ppp	3	gl
1999	ABC	uyt	4	klyif
1989	CAT	ppp	5	gil

I want to merge the contents of col4 if col1,col2,col3 values match and add a count of it. output must look like this:

col1	col2	col3	col4	count
1999	ABC	ggg	1,2	2
1989	CAT	ppp	3,5	2
1999	ABC	uyt	4	1

I got the necessary output with: df.groupby(['col1', 'col2', 'col3']).agg(col4=('col4', lambda x: ','.join([str(x) for x in list(x))), count=('col4', 'size')).reset_index() works as expected

edited Sep 22 '22 at 12:26

asked Sep 22 '22 at 09:31

gow_r

The last row of sample output should be: |1999|ABC|uyt|ID4|1| – gow_r Sep 22 '22 at 09:33
1

try: ```df.groupby(['col1', 'col2', 'col3']).agg(col4=('col4', list), count=('col4', 'size')).reset_index()``` – khaled koubaa Sep 22 '22 at 09:38
'col1' - is the error message @khaled koubaa – gow_r Sep 22 '22 at 10:02
copy paste the full error here – khaled koubaa Sep 22 '22 at 10:12
It worked! I had misplaced brackets. Thank you. is it is possible to have it as a comma seperated ID rather than list? @khaled koubaa – gow_r Sep 22 '22 at 10:39
yes, I will send you how, meanwhile, please upvote my comment since the solution works – khaled koubaa Sep 22 '22 at 10:46
```df.groupby(['col1', 'col2', 'col3']).agg(col4=('col4', lambda x: ','.join(list(x))), count=('col4', 'size')).reset_index()``` – khaled koubaa Sep 22 '22 at 10:47
sequence item 0: expected str instance, int found - is the error message – gow_r Sep 22 '22 at 11:36
df.groupby(['col1', 'col2', 'col3']).agg(col4=('col4', lambda x: ','.join([str(x) for x in list(x))), count=('col4', 'size')).reset_index() works as expected – gow_r Sep 22 '22 at 12:25
ok good, if you need more assistance, let me know – khaled koubaa Sep 22 '22 at 12:26

Pandas: If three columns have same entries then merge the 4th column(which is integer) and show count in 5th column

0 Answers0