Pandas: how to append rows with for loop from existing value

Question

So i have this code:

monthly['differences'] = monthly.GMV.diff()
monthly['percentage'] = monthly.differences / (monthly.GMV - monthly.differences) *100
monthly

which produce this dataframe:

    GMV         differences percentage
date            
1   69793.30    NaN         NaN
2   65159.60    -4633.70    -6.639176
3   70397.10    5237.50     8.037956
4   68736.80    -1660.30    -2.358478
5   71402.75    2665.95     3.878490
6   68230.20    -3172.55    -4.443176
7   72557.90    4327.70     6.342792
8   68278.25    -4279.65    -5.898255
9   64180.05    -4098.20    -6.002204
10  64027.60    -152.45     -0.237535
11  70395.35    6367.75     9.945320
12  64701.15    -5694.20    -8.088887

And i want to add new rows from the existing data with loop, i wanted the 13th row to have these value:

GMV13 = GMV12 + (GMV12 * percent12)
diff13= GMV13 - GMV12
percent13= diff13 / (GMV13 * diff13) * 100

And for the 14th row and on as well until the data will kinda look like this: (ignore the value, this is just an example)

    GMV         differences percentage
date            
1   69793.30    NaN         NaN
2   65159.60    -4633.70    -6.639176
3   70397.10    5237.50     8.037956
4   68736.80    -1660.30    -2.358478
5   71402.75    2665.95     3.878490
6   68230.20    -3172.55    -4.443176
7   72557.90    4327.70     6.342792
8   68278.25    -4279.65    -5.898255
9   64180.05    -4098.20    -6.002204
10  64027.60    -152.45     -0.237535
11  70395.35    6367.75     9.945320
12  64701.15    -5694.20    -8.088887
13  70397.10    5237.50     8.037956
14  68736.80    -1660.30    -2.358478
15  71402.75    2665.95     3.878490
16  68230.20    -3172.55    -4.443176
17  72557.90    4327.70     6.342792
18  68278.25    -4279.65    -5.898255
19  64180.05    -4098.20    -6.002204

Your formula for percent13 doesn't make much sense to me... What's the idea behind it? — Tranbi
– Tranbi, Commented Oct 26, 2022 at 12:19
its the percentage of the difference from the previous month it should be percent13 = diff13 / GMV12 — Huesca Rashad
– Huesca Rashad, Commented Oct 26, 2022 at 12:42
ok that makes more sense. check my answer! edit: with this formula, you're just keeping the same percent from one row to the next, so you could as well do percent13 = percent12 — Tranbi
– Tranbi, Commented Oct 26, 2022 at 12:45

Tranbi · Accepted Answer · 2022-10-26 12:45:18Z

1

You can loop in range 13..19 to calculate new rows from the previous ones:

def calc_new_val(old_row):
    new_row = pd.Series()
    new_row['GMV'] = old_row['GMV'] * (1 + old_row['percentage'] / 100)
    new_row['differences'] = new_row['GMV'] - old_row['GMV']
    new_row['percentage'] = new_row['differences'] / old_row['GMV'] * 100
    return new_row

for i in range(13, 20):
    monthly.loc[i, :] = calc_new_val(monthly.loc[i-1, :])

Output:

             GMV  differences  percentage
1   69793.300000          NaN         NaN
2   65159.600000 -4633.700000   -6.639176
3   70397.100000  5237.500000    8.037956
4   68736.800000 -1660.300000   -2.358478
5   71402.750000  2665.950000    3.878490
6   68230.200000 -3172.550000   -4.443176
7   72557.900000  4327.700000    6.342792
8   68278.250000 -4279.650000   -5.898255
9   64180.050000 -4098.200000   -6.002204
10  64027.600000  -152.450000   -0.237535
11  70395.350000  6367.750000    9.945320
12  64701.150000 -5694.200000   -8.088887
13  59467.547089 -5233.602911   -8.088887
14  54657.284403 -4810.262686   -8.088887
15  50236.118430 -4421.165973   -8.088887
16  46172.575577 -4063.542853   -8.088887
17  42437.728114 -3734.847463   -8.088887
18  39004.988241 -3432.739873   -8.088887
19  35849.918818 -3155.069423   -8.088887

answered Oct 26, 2022 at 12:45

Tranbi

12.8k6 gold badges19 silver badges39 bronze badges

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

3 Comments

Huesca Rashad Over a year ago

thank you, but can you explain a little bit to me about the last line of code so i can make my own in the future? sorry in advance

Tranbi Over a year ago

calc_new_val is a function that takes a pandas Series as input (old_row) and returns another pandas Series (new_row) that is then assigned to the i-th row of the df via .loc.

Huesca Rashad Over a year ago

ahhh, okay i see, thank you for clearing that up

Collectives™ on Stack Overflow

Pandas: how to append rows with for loop from existing value

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