Adding a moving formula to excel using python-CodePudding

I am writing multiple dfs to excel and I am trying to add a formula to cells. The problem is that my assigned formula is static for the whole row, for example:

# df

            2019            2020            2021        2022
A           40              40              51          58
B           5               40              54          97
C           0.3             0.5             0.5         0.8
D           2000            40              200         300
E           0.02            1               0.25        0.19

And then adding a formula:

df.loc['test'] = '=SUM(sheet_1!D5:sheet_1!D10)'

Does work but now the result looks like this:

# df

            2019            2020            2021        2022
A           40              40              51          58
B           5               40              54          97
C           0.3             0.5             0.5         0.8
D           2000            40              200         300
E           0.02            1               0.25        0.19
test        1058            1058            1058        1058

I am trying to make the rolling window of '=SUM(sheet_1!D5:sheet_1!D10)', so that each column would have a moving formula:

2019 - '=SUM(sheet_1!D5:sheet_1!D10)'
2020 - '=SUM(sheet_1!D6:sheet_1!D11)'
2021 - '=SUM(sheet_1!D7:sheet_1!D12)'
# and so on

How could I achieve such result?

CodePudding user response：

You have to depend it on the number of the column. In the most brutal way, you can use loop:

df.loc['test'] = [f'=SUM(sheet_1!D{c 1}:sheet_1!D{c 6})' for c in range(0, len(df.columns)]