Sum up previous rows upto 3 and multiply with value from another column using pandas

user_12866651 0
Viewed 102

I have 2 dataframes, i want to get sum value of every row based on groupby of unique id each previous 3rows & each row value should be multiply by other dataframe value

   for example : dataframe A                     dataframe B
                    unique_id  value  out_value      num_values  
                  1    1        45                     0.15
                  2    1        33                     0.30  
                  3    1        18                     0.18
                 #4    1        26    20.7
                  5    2        66
                  6    2        44
                  7    2        22
                 #8    2        19.   28.3

           expected output_value column 
              4th row = 18 * 0.15 + 33*0.30 + 45*0.18 = 2.7+9.9+8.1 = 20.7          
              8th row = 22 * 0.15 + 44*0.30 + 66*0.18 = 3.3+ 13.2 + 11.88= 28.3

  based on Unique_id each value should calculate based previous 3values.
  for every row there will be previous 3 rows available
pandas dataframe python-3.x numpy python
1 Answers
import pandas as pd
import numpy as np

df_a = pd.DataFrame({
    'uni_id':[1, 1, 1, 1, 2, 2, 2, 2, 152, 152, 152, 152, 152],
    'value':[45,33,18,26,66,44,22,19,36,27,45,81,90]
}, index=range(1,14))
df_b = pd.DataFrame({
    'num_values':[0.15,0.30,0.18]
})

df_a
###
    uni_id  value
1        1     45
2        1     33
3        1     18
4        1     26
5        2     66
6        2     44
7        2     22
8        2     19
9      152     36
10     152     27
11     152     45
12     152     81
13     152     90


df_b
###
   num_values
0        0.15
1        0.30
2        0.18


# main calculation
arr = [df_a['value'].shift(x+1).values[::-1][:3] for x in range(len(df_a['value']))[::-1]]
arr_b = pd.Series(np.inner(arr, df_b['num_values']))

# filter and clean
mask = df_a.groupby('uni_id').cumcount()+1 > 3
output = arr_b * mask
output[output == 0] = np.nan

# concat result to df_a
df_a['out_value'] = output


df_a
###
    uni_id  value  out_value
1        1     45        NaN
2        1     33        NaN
3        1     18        NaN
4        1     26      20.70
5        2     66        NaN
6        2     44        NaN
7        2     22        NaN
8        2     19      28.38
9      152     36        NaN
10     152     27        NaN
11     152     45        NaN
12     152     81      21.33
13     152     90      30.51


If you want to keep non-null values through filtrate:

df_a.query('out_value.notnull()')
###
    uni_id  value  out_value
4        1     26      20.70
8        2     19      28.38
12     152     81      21.33
13     152     90      30.51



Group with metrics uni_id,Year_Month

Data preparation:

# create date range series with 7 days
import pandas as pd
import numpy as np
rng = np.random.default_rng(42)
rng.integers(10,100, 26)
date_range = pd.Series(pd.date_range(start='01.30.2020', periods=27, freq='5D')).dt.to_period('M')
df_a = pd.DataFrame({
    'uni_id':[1, 1, 1, 1,1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 152, 152, 152, 152, 152,152, 152, 152, 152, 152],
    'Year_Month':date_range,
    'value':rng.integers(10,100, 26)
}, index=range(1,27))
df_b = pd.DataFrame({
    'num_values':[0.15,0.30,0.18]
})

df_a
###
    uni_id Year_Month  value
1        1    2020-02     46
2        1    2020-02     84
3        1    2020-02     59
4        1    2020-02     49
5        1    2020-02     50
6        1    2020-02     30
7        1    2020-03     18
8        1    2020-03     59
9        2    2020-03     89
10       2    2020-03     15
11       2    2020-03     87
12       2    2020-03     84
13       2    2020-04     34
14       2    2020-04     66
15       2    2020-04     24
16       2    2020-04     78
17     152    2020-04     73
18     152    2020-04     41
19     152    2020-05     16
20     152    2020-05     97
21     152    2020-05     50
22     152    2020-05     90
23     152    2020-05     71
24     152    2020-05     80
25     152    2020-06     78
26     152    2020-06     27



Processing

arr = [df_a['value'].shift(x+1).values[::-1][:3] for x in range(len(df_a['value']))[::-1]]
arr_b = pd.Series(np.inner(arr, df_b['num_values']))

# filter and clean
mask = df_a.groupby(['uni_id','Year_Month']).cumcount()+1 > 3
output = arr_b * mask
output[output == 0] = np.nan

# concat result to df_a
df_a['out_value'] = output
df_a
###
    uni_id Year_Month  value out_value
1        1    2020-02     46       NaN
2        1    2020-02     84       NaN
3        1    2020-02     59       NaN
4        1    2020-02     49     40.17
5        1    2020-02     50     32.82
6        1    2020-02     30     28.32
7        1    2020-03     18       NaN
8        1    2020-03     59       NaN
9        2    2020-03     89       NaN
10       2    2020-03     15       NaN
11       2    2020-03     87       NaN
12       2    2020-03     84      41.4
13       2    2020-04     34       NaN
14       2    2020-04     66       NaN
15       2    2020-04     24       NaN
16       2    2020-04     78     30.78
17     152    2020-04     73       NaN
18     152    2020-04     41       NaN
19     152    2020-05     16       NaN
20     152    2020-05     97       NaN
21     152    2020-05     50       NaN
22     152    2020-05     90     45.96
23     152    2020-05     71     46.65
24     152    2020-05     80      49.5
25     152    2020-06     78       NaN
26     152    2020-06     27       NaN
user_145278860
Related