CODEWITHSUNDEEP
pythonexcelpandas
Jonas Palačionis
Jonas Palačionis

Reputation: 4842

Add a moving formula to excel using python

I have a df that looks like this:

    2021-12-06  2021-12-13  2021-12-20  2021-12-27
A   10          20          30          40
B   20          50          40          90
C   30

To replicate :

df = pd.DataFrame(index = ['A','B','C'],
             columns = pd.to_datetime(['2021-12-06','2021-12-13','2021-12-20','2021-12-27']), 
             data = [[10, 20, 30, 40],
                     [20, 40, 50, 90],
                     [30, np.nan, np.nan, np.nan]])

From 2021-12-13 onwards I am calculating C index value:

enter image description here

Where C4 is a calculation that uses both B & C columns and different rows. In C5 onwards I added the calculation used in the cell above.

To achieved the pure numbers in pandas dataframe like so:

for column_name in df.loc[:,2021-12-13:].columns:
    df.loc['C',column_name] =  df.loc['B', column_name - timedelta(days = 7)] - \
                               df.loc['B',column_name] + \
                               df.loc['A',column_name]

Which does take the cell value calculated before in to account. The problem is that I want cell C4, D4, E4 ... to be formula instead of hard written value so that when I export the df to excel I can change values and the row 4 would change accordingly to values in the entire df making the excel dynamic instead of static.

I understand that for this to work I need to address the excel column name instead of pandas, but I am not sure how can I address it if the df is not yet written to the excel.

I tried:

df.loc['test','2021-12-13':] = [f'=B{c+4-1} - C{c+4-1} + C{c+4-2}' 
                               for c in range(0, len(df.columns)-1)]

But then I realised that I am only able to change the row number and not the name of excel column, ending up my formula always having the same column numbers but different rows.

Result df:

        2021-12-06          2021-12-13          2021-12-20          2021-12-27
A       10.00               20.00               30.00               40.00
B       20.00               40.00               50.00               90.00
C       30.00               0.00                20.00               0.00
test    nan                 =B3 - C3 + C2       =B4 - C4 + C3       =B5 - C5 + C4

How can I achieve my desired result where after the export of df the excel would hold moving formula?

Upvotes: 0

Views: 106

Answers (1)

Jonas Palačionis
Jonas Palačionis

Reputation: 4842

I ended up using openpyxl as Quang Hoang suggested.

wb = openpyxl.load_workbook(filename = 'file.xlsx')
ws = wb['sheet_name']

for cell in ws[1]:
        try:
            if '2021-12-31' <= pd.to_datetime(cell.value):
                prev_cell_letter = openpyxl.utils.get_column_letter(op.utils.cell.coordinate_to_tuple(cell.coordinate)[1]-1)
                ws[f'{cell.column_letter}21'].value = f"={prev_cell_letter}25 -
                                                         {cell.column_letter}13 + 
                                                         {cell.column_letter}17"
        except TypeError:
            print('passed')
            continue

Upvotes: 0

Related Questions