How can I add one row for each group in pandas dataframe?

Question

My current df looks like that

Region	Descript	Material	Input
UK	Bottle	#1	500
UK	Cap	#5	20
India	Bottle	#1	400
India	Cap	#5	2

would like to have a df2 to be like:

Region	Descript	Material	Input
UK	Bottle	#1	500
UK	Cap	#5	20
UK	other	#7	NA
India	Bottle	#1	400
India	Cap	#5	2
India	other	#7	NA

I am new to python. Maybe there is a simple solution or an already similar case but i couldn't find anything

mozway · Accepted Answer · 2023-01-16 14:52:39Z

2

You can use groupby.tail to get the last row of each group, and modify them with assign, then concat it to the original DataFrame and sort by index while resetting it:

out = pd.concat([df,
                 df.groupby('Region', sort=False, as_index=False).tail(1)
                   .assign(Descript='other', Material='#7', Input='NA')]
               ).sort_index(ignore_index=True)

Output:

  Region Descript Material Input
0     UK   Bottle       #1   500
1     UK      Cap       #5    20
2     UK    other       #7    NA
3  India   Bottle       #1   400
4  India      Cap       #5     2
5  India    other       #7    NA

edited Jan 16, 2023 at 14:52

answered Jan 16, 2023 at 14:14

mozway

267k13 gold badges56 silver badges106 bronze badges

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

2 Comments

John Luke Over a year ago

One little thing: ...Input='NA'))]..one parenthesis was missing

mozway Over a year ago

Thanks @John, fixed it (you can actually remove the parentheses) ;)

Collectives™ on Stack Overflow

How can I add one row for each group in pandas dataframe?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related