Creating New Row with SUMIF in Pandas

user_12989327 0
Viewed 180

How do I create a new row with sum based on condition using pandas?

Initial table -

Product   Date  CAT    Value
Product A Apr   F31    100
Product A Apr   F32    200
Product A Apr   F45    300
Product A Apr   F46    400
Product A May   F31    200
Product A May   F32    300
Product A May   F45    400
Product A May   F46    500
Product B Apr   F31    200
Product B Apr   F32    300
Product B Apr   F45    400
Product B Apr   F46    500
Product B May   F31    600
Product B May   F32    700
Product B May   F45    800
Product B May   F46    900

I wanted to create this by combining F31 and F32 into F3 and it should give me the sum in value.

Product   Date  CAT    Value
Product A Apr   F3     300
Product A Apr   F45    300
Product A Apr   F46    400
Product A May   F3     500
Product A May   F45    400
Product A May   F46    500
Product B Apr   F3     500
Product B Apr   F45    400
Product B Apr   F46    500
Product B May   F3    1300
Product B May   F45    800
Product B May   F46    900

Can you please help me?

pandas python
2 Answers

For Creating the DataFrame as mentioned above, we need to apply two operations.

  1. String .replace

  2. .groupby with Aggregation i.e. .sum() and .reset_index()

  3. String Replace method is used to replace the characters in strings, by which we can convert the string i.e. F31, F32 to F3.

    .replace('F31','F3')

    .replace('F32','F3')

  4. .groupby method is used to separate the data, based on the Constraint Given. This Method Separates the Data into Blocks where operations can be performed. Here, we need to Group the Data based on multiple Columns.

Then we need to Aggregate the Sum of Value Column based on 'CAT' Column i.e. these are grouped first by 'Product', then by 'Date' and then by 'CAT'. So, for this we use .sum().

The result after this will be a grouped object, but as to convert it into DataFrame, we need to use .reset_index(), this method allows to fill the columns with corresponding values based on the input Conditioned Column.

Data.groupby(['Product','Date','CAT'])['Value'].sum().reset_index(name='Value')

The Code Snippet is show below:

Product = ['Product A','Product A','Product A','Product A','Product A','Product A','Product A','Product A','Product B','Product B','Product B','Product B','Product B','Product B','Product B','Product B']

Date = ['Apr','Apr','Apr','Apr','May','May','May','May','Apr','Apr','Apr','Apr','May','May','May','May']

CAT = ['F31','F32','F45','F46','F31','F32','F45','F46','F31','F32','F45','F46','F31','F32','F45','F46']

Value = [100, 200,300,400,200,300,400,500,200,300,400,500,600,700,800,900]

# Creating Data Frame

Data = pd.DataFrame({'Product':Product,'Date':Date,'CAT':CAT,'Value':Value})

# String Replace

Data['CAT'] = Data['CAT'].replace('F31','F3')

Data['CAT'] = Data['CAT'].replace('F32','F3')

# Group By Operation

DataG = pd.DataFrame(Data.groupby(['Product','Date','CAT'])['Value'].sum().reset_index(name='Value'))

Data Before applying above operations.

Data Before applying above operations

Data After applying above operations.

Data After applying above operations

user_120051910

First let's do a targeted regex: replace only replacing a number that comes after CAT A

So CAT A5 --> CAT A

df['CAT'] = df['CAT'].str.replace('(CAT A)(\d+)',r'\1')

then do your group by.

df.groupby(['Product','Date','CAT'])['Value'].sum()
Product    Date  CAT  
Product A  Apr   CAT A     300
                 CAT B     300
                 CAT C     400
           Jul   CAT C     500
           Jun   CAT B     400
           May   CAT A     500
Product B  Apr   CAT A     500
                 CAT B     400
                 CAT C     500
           May   CAT A    1300
                 CAT B     800
                 CAT C     900
Name: Value, dtype: int64

Add .reset_index() if you want a dataframe returned.

user_93751020
Related