CODEWITHSUNDEEP
pandas
Zaur Guliyev
Zaur Guliyev

Reputation: 59

analogy of SUMIFS in Excel function in Pandas

I have a difficulty with applying Excel SUMIFS type function in Pandas. I have a table similar to one on picture. I need to find Sum of each product sold each day. But I don't need it in Summary table. I need it to be written in column next to each one as shown in red column. In excel I'm using SUMIFS function. But in Pandas I can't find any analogy. Once again, I don't need just count/or sum as shown summary in another table. I need it be near each entry in new column. Is there any way I can do it? P.S VERY Important thing- Writing with group by where I will need to write each condition isn't a solution. Because I want it to show next to each cell. In my data there will be thousands of entries and I don't know each entry to write =="apple", =="orange" each time. I need the same logic as in Excel. enter image description here

Upvotes: 1

Views: 139

Answers (2)

creanion
creanion

Reputation: 2743

You can do this with groupby and its transform method.

Creating something that looks like your dataframe, but abbreviated:

import pandas as pd

df = pd.DataFrame({
    'date': ["22.10.2021", "22.10.2021", "22.10.2021", "22.10.2021", "23.10.2021"],
    'Product': ["apple", "apple", "orange", "orange", "cherry"],
    'sold_kg': [2, 3, 1, 4, 2]})

Then we group by and apply the sum as transformation to the sold_kg column and assign the result back as a new column:

df['Sold that day'] = df.groupby(['date', 'Product']).sold_kg.transform("sum")

Sold that day column created

In your words, we often use groupby to create "summaries" or aggregations. But transform is also useful to know since it allows us to splat the result back into the data frame it came from, just like in your example.

Upvotes: 2

Danail Petrov
Danail Petrov

Reputation: 1875

if we consider the image as dataframe df, simply do

>>> pd.merge( df.groupby(['Date','Product']).sum().reset_index(),df, on=['Date','Product'], how='left')

You will just need to rename some columns later-on, but that should do

Upvotes: 0

Related Questions