CODEWITHSUNDEEP
pythonpandasdataframe
el323
el323

Reputation: 2920

Pandas Dataframe groupby two columns and sum up a column

I have pandas dataframe in the following format:

d = {'buyer_code': ['A', 'B', 'C', 'A', 'A', 'B', 'B', 'A', 'C'], 'dollar_amount': ['2240.000', '160.000', '300.000', '10920.000', '10920.000', '235.749', '275.000', '10920.000', '300.000']}
df = pd.DataFrame(data=d)
df

This is how my dataframe looks like:

    buyer_code  dollar_amount
0   A           2240.000
1   B           160.000
2   C           300.000
3   A           10920.000
4   A           10920.000
5   B           235.749
6   B           275.000
7   A           10920.000
8   C           300.000

I have used groupby to list each buyer and there corresponding dollar amounts.

df.groupby(['buyer_code', 'dollar_amount']).size()

This is the result:

buyer_code  dollar_amount
A           10920.000        3
            2240.000         1
B           160.000          1
            235.749          1
            275.000          1
C           300.000          2
dtype: int64

Now I want dollarAmount multiplied by its count and then sum of all the amounts for each buyer.

Lets say for example buyer_code "A" should have (10920.000 * 3) + (2240.000 * 1)

The result should be something like this:

buyer_code  dollar_amount
A           35000
B           670.749
C           600.000

How can I get this output?

Upvotes: 2

Views: 969

Answers (2)

jezrael
jezrael

Reputation: 862641

Use groupby + aggregate sum:

df['dollar_amount'] = df['dollar_amount'].astype(float)
a = df.groupby('buyer_code', as_index=False).sum()
print (a)
  buyer_code  dollar_amount
0          A      35000.000
1          B        670.749
2          C        600.000

Upvotes: 3

cs95
cs95

Reputation: 402483

unstack your result, and then perform a matrix multiplication between the result and its columns with dot - 

i = df.groupby(['buyer_code', 'dollar_amount']).size().unstack()
i.fillna(0).dot(i.columns.astype(float))

buyer_code
A    35000.000
B      670.749
C      600.000
dtype: float64

Or,

i.fillna(0).dot(i.columns.astype(float))\
         .reset_index(name='dollar_amount')

  buyer_code  dollar_amount
0          A      35000.000
1          B        670.749
2          C        600.000

This is alright if you're doing something else with the intermediate groupby result, necessitating the need for its computation. If not, a groupby + sum makes more sense here.

Upvotes: 2

Related Questions