using pandas dataframe group by columns, multiply and add each current row & previous row based on month

s nandan 0
Viewed 108

am having one dataframe, dataframe has 16 columns cust_id, order_id, value, date, jan,feb,mar,apr -- dec columns

Using group by cust_id & order_id - I have to implement multiply & add a calculation for each current row and previous row of Values column & Jan feb mar apr may -- dec columns based on date column

If previous month values are not present, that particular month should be considered as 0 like for Ex: In date column first row was starting from 12th[Dec] month so previous month value November is not existed in dataframe for that calculation should be 0 below calculation shows if any of previous month values are not there

   groupby[1008,001] date[dec month] calculation = output = 1000*0.4 + 0 =400

Input Dataframe 
    import pandas as pd
    import numpy as np
    import datetime

    df = pd.DataFrame({'cust_id': ['1008'] * 4 + ['1009'] *4,
                    'order_id': ['51'] * 4 + ['192'] * 4,
                    'Date': ["2020-12-01",
                             "2021-01-01",
                            "2021-02-01",
                            "2021-03-01",
                            "2020-12-01",
                            "2021-01-01",
                            "2021-02-01",
                            "2021-03-01"],
                     'Value': [1000, 2000, 3000, 3000, 6000, 9000, 180, 400],
                       'Dec': [0.1]*2+ [0]*2 + [0.5]*2 + [0.5]*2,
                      'Jan': [0.1]*2+ [0.5]*2 + [0.3]*2 + [0]*2,
                      'Feb': [0.2]*2+ [0]*2 + [0.1]*2 + [0.5]*2,
                      'Mar': [0.8]*2+ [0.4]*2 + [0.1]*2 + [0.2]*2,
                      'Apr': [0.3]*2+ [0.5]*2 + [0.4]*2 + [0.6]*2})

Output Dataframe 
data
###
    cust_id  order_id    date    value   output                                                         
     1008      001    2020-12-01   1000    400    [1000*0.4 + 0]
     1008      001    2020-01-01   2000    1100   [2000*0.5 + 1000*0.1]
     1008      001    2020-02-01   3000    0      [3000*0+2000*0]
     
     2007     009     2021-02-01   3000     0
     2007     009     2021-03-01   6000    1500
     2007     009     2021-04-01   9000    2700


I tried the below code but not working
df['output'] = df.groupby['cust_id','order_id']['Date'].apply(lambda x:(x['values']*x['jan']+x['values']*x['dec']))

but in loop it should work with jan,feb,mar -- dec

Even i tried below one as well
df_1 = pd.DataFrame(
    (df.Value * df[4:][:, None]).reshape(-1, df.shape[1]),
    pd.MultiIndex.from_product([df.index, df.index]),
    df.columns
)
df_1
dataframe pandas numpy python-3.x python
1 Answers

I did my best to understand and to apply your example data. Not sure if it is correct, but I placed column order_id to dataframe as it was missing. The code below has explanations/comments for each step. Finally, if I have Not understood your formulas/calculations correctly, just adjust them to your needs. I am sure your result is possible to achieve with smaller amount of steps, - but I am not yet in that level pandaninja.

P.S. Be careful, the code below is for one calendar Year only, - if you want to apply it for longer period, - check the data sorting before applying calculations.

import pandas as pd
import numpy as np

df = pd.DataFrame({'legal_entity': ['1008'] * 4 + ['1009'] *4,
                'order_id': ['001'] * 4 + ['009'] * 4,
                'key_account': ['51'] * 4 + ['192'] * 4,
                'Date': ["2020-12-01",
                         "2021-01-01",
                         "2021-02-01",
                         "2021-03-01",
                         "2020-12-01",
                         "2021-01-01",
                         "2021-02-01",
                         "2021-03-01"],
                 'Value': [1000, 2000, 3000,  3000,  6000, 9000, 180, 400],
                   'Dec': [0.1]*2+ [0]*2 + [0.5]*2 + [0.5]*2,
                  'Jan': [0.1]*2+ [0.5]*2 + [0.3]*2 + [0]*2,
                  'Feb': [0.2]*2+ [0]*2 + [0.1]*2 + [0.5]*2,
                  'Mar': [0.8]*2+ [0.4]*2 + [0.1]*2 + [0.2]*2,
                  'Apr': [0.3]*2+ [0.5]*2 + [0.4]*2 + [0.6]*2})

# creating dictionary of short Month names and coresponding numbers
d_months = dict(zip(pd.date_range('2021-01-01', freq='M', periods=12).strftime('%b'), range(1,13)))
print(d_months)
{'Jan': 1, 'Feb': 2, 'Mar': 3, 'Apr': 4, 'May': 5, 'Jun': 6, 'Jul': 7, 'Aug': 8, 'Sep': 9, 'Oct': 10, 'Nov': 11, 'Dec': 12}


# melting dataframe to get Monhs from headers into rows, -  placed in the column 'Val_Month'
df2 = df.melt(id_vars=['legal_entity','order_id', 'key_account', 'Value', 'Date'],var_name = 'Val_Month', value_name = 'Smal_Vals')
print(df2)
   legal_entity order_id key_account  Value        Date Val_Month  Smal_Vals
0          1008      001          51   1000  2020-12-01       Dec        0.1
1          1008      001          51   2000  2021-01-01       Dec        0.1
2          1008      001          51   3000  2021-02-01       Dec        0.0
3          1008      001          51   3000  2021-03-01       Dec        0.0
4          1009      009         192   6000  2020-12-01       Dec        0.5
5          1009      009         192   9000  2021-01-01       Dec        0.5
6          1009      009         192    180  2021-02-01       Dec        0.5
7          1009      009         192    400  2021-03-01       Dec        0.5
....
38         1009      009         192    180  2021-02-01       Apr        0.6
39         1009      009         192    400  2021-03-01       Apr        0.6

# in the col: 'Val_Month', - replacing short Month names by relevant monthly numbers (stored in the dictionary 'd_months')
df2['Val_Month'] = df2['Val_Month'].map(d_months)

# creating new column: 'Month_FromDate' with relevant month numbers based on column 'Date'
df2['Month_FromDate'] = pd.DatetimeIndex(df2['Date']).month

# check if previous month values is NaN (missing), - result: True/False
# shift(1) checks values from cell above or literally shifts the column by periods/cell numbers in the parameter field ().
check_prev_month_value = df2['Value'].shift(1).isnull()

# your calculations/formulas. val1 used if previous month is missing
val1 = df2['Value']*df2['Smal_Vals']

# val2, if previous month value is present. And again, shift(1) takes values from the cell above
val2 = (df2['Value']*df2['Smal_Vals']) + (df2['Value'].shift(1) * df2['Smal_Vals'].shift(1))

# applying formula val1 if previous month values is NaN (Bolean=True) and val2 if Not
df2['Output'] = np.where(check_prev_month_value, val1, val2)

print(df2[['legal_entity', 'order_id', 'Date', 'Value', 'Smal_Vals', 'Output']])

 legal_entity order_id        Date  Value  Smal_Vals  Output
0          1008      001  2020-12-01   1000        0.1   100.0
1          1008      001  2021-01-01   2000        0.1   300.0
2          1008      001  2021-02-01   3000        0.0   200.0
3          1008      001  2021-03-01   3000        0.0     0.0
4          1009      009  2020-12-01   6000        0.5  3000.0
5          1009      009  2021-01-01   9000        0.5  7500.0
6          1009      009  2021-02-01    180        0.5  4590.0
7          1009      009  2021-03-01    400        0.5   290.0
8          1008      001  2020-12-01   1000        0.1   300.0
9          1008      001  2021-01-01   2000        0.1   300.0
10         1008      001  2021-02-01   3000        0.5  1700.0
....
38         1009      009  2021-02-01    180        0.6  3708.0
39         1009      009  2021-03-01    400        0.6   348.0

As per comments, in the end, to summarise the values Monthly, use the line below. As you calculate one month's value per each 5 months vals, you get extra 5 columns (values) per each month.

df3 = df2.pivot(index=['legal_entity','order_id', 'key_account', 'Value', 'Date'], columns='Val_Month')

df3.to_excel("test.xlsx")
NoobVB0
Related