pandas : sum with lagged param

Question

in sql request, i have a chance to sum with lagged param on the fly, like:

    CASE
       WHEN dt_rep.fcr = 0::numeric THEN 0::numeric
                    ELSE (dt_rep.fcr - lag(dt_rep.fcr) OVER (PARTITION BY dt_rep.place_id ORDER BY dt_rep.bucket)) / dt_rep.fcr
    END AS cr

here, I order the table by bucket (it is timeframe) and sorted them by place_id (to let group by place_id for lagging). Then, i can simply filtered the table with values of cr is null what gives me required structure.

in pandas, I can shift and assign new column like:

data_fin['fcr_b'] = data_fin.groupby(['place_id'])['fcr'].shift(1)

then I need to combine columns 'fcr_b' and 'fcr' to new one 'cr' and filter dataframe where fcr_b is NaN.

Is there more accurate approache to get new column 'cr' something like:

data_fin[cr] = data_fin['fcr'].apply(lambda x: x + lag(x).groupby('place_id'))

<= of cause, it is not working example!

U13-Forward · Accepted Answer · 2024-06-11 13:41:34Z

1

Something like this?

df = pd.DataFrame({"A": [1, 1, 1, 2, 2, 2, 3, 3, 3], 
                   "B": ["a", "b", "c", "d", "e", "f", "g", "h", "i"], 
                   "C": ['text', 'text', 'text', 'text', 'text', 'text', 'text', 'text', 'text']})

df['D'] = df.groupby('A', as_index=False)['B'].shift(1).add(df['C'], fill_value=0)
df

edited Jun 11, 2024 at 13:41

answered Jun 11, 2024 at 13:31

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OcMaRUS Over a year ago

not exactly. sorry for not beeing clear with the question. In your example, if you set column [B] to numeric, column [C].shift(1) from [B] and to get column D as the sum of B+C. You don't need to fill NaN by anything. it is ok.

OcMaRUS Over a year ago

probably, i have to use iloc but I need an idea how to filter index by column (place_id)

OcMaRUS Over a year ago

@U12-Forward - yes, thank you!! to delete "fill_value=0" and it is exactly required structure!

Collectives™ on Stack Overflow

pandas : sum with lagged param

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related