Pandas merge column duplicate and sum value

Question

I have the following data in pandas df:

quantity    color

1           White
3           White
1           Black
2           Black

I need to group duplicates and get the sum of their quantities:

quantity    color

4           White
3           Black

I have tried the following code to first sort by quantity, then using groupby to combine duplicates and get the sum.

df1 = df.sort_values(by=['quantity','color'], ascending=False)
df2 = df1.groupby(['color']).sum()
print(df2)

I'm getting the following output:

White    13
Black    12

As you can see it's not giving me the sum of the quantity, it's just listing those values side by side in the next column (1,3) and (1,2) for each color.

Check the `dtype` of `quantity` column and change it to `int`, using `df['quantity'] = df['quantity'].astype(int)`. — Space Impact, Oct 07 '19 at 20:57
@SandeepKadapa: good observation. Its just joining string values :-) — jottbe, Oct 07 '19 at 20:58
Possible duplicate of [Pandas group-by and sum](https://stackoverflow.com/questions/39922986/pandas-group-by-and-sum) — Space Impact, Oct 07 '19 at 21:00
I'm getting "ValueError: invalid literal for int() with base 10" when using .astype(int) — Bronson77, Oct 07 '19 at 21:03
Use `df['quanity'] = pd.to_numeric(df['quantity'])` And then use your `groupby` method. That should work — Erfan, Oct 07 '19 at 21:24

Celius Stingher · Accepted Answer · 2019-10-07T21:25:56.467

1

I believe there must be empty spaces in the example you are giving and that's the reason for why astype(int) isn't working. Using pd.to_numeric and passing errors='coerce' will make sure we convert all string values to np.Nan which can be easily dealt with when using groupby

import pandas as pd
a = {'quantity':[1,3,1,2],'color':['White','White','Black','Black']}
df = pd.DataFrame(a)
df['quantity'] = pd.to_numeric(df['quantity'],errors='coerce')
print(df.groupby('color')['quantity'].sum())

Output:

color
Black    3
White    4

edited Oct 07 '19 at 21:25

answered Oct 07 '19 at 21:05

Celius Stingher

17,835
6
23
53

No, just two columns (quantity, color). – Bronson77 Oct 07 '19 at 21:09
I mean in the `quantity` column, are other values? Did you tried `.str.replace(' ', '').astype(int)`? if it's not working, what error are you getting? Could there values that have `commas` ? – Celius Stingher Oct 07 '19 at 21:10
There are no other values other than numbers in the quantity column. Yes, I tried the replace blank spaces code, but still getting "ValueError: invalid literal for int() with base 10: 'quantity'" – Bronson77 Oct 07 '19 at 21:11
How about we use `astype('float64')`? Edited in answer too. – Celius Stingher Oct 07 '19 at 21:15
I just looked again at the csv i'm reading. Only thing in the quantity column are numbers 1, 2, 3, 4. No weird characters or anything. – Bronson77 Oct 07 '19 at 21:15
It's trying to pass (float64) as a variable. Not defined. – Bronson77 Oct 07 '19 at 21:16
Sorry meant between apostrophes. astype('float64') – Celius Stingher Oct 07 '19 at 21:16
Is it possible that your csv contains empty values in the column `quantity`? – Celius Stingher Oct 07 '19 at 21:17
ValueError: could not convert string to float: 'quantity' – Bronson77 Oct 07 '19 at 21:18
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/200530/discussion-between-celius-stingher-and-bronson77). – Celius Stingher Oct 07 '19 at 21:18

Pandas merge column duplicate and sum value

1 Answers1