Input

Output

I have a standard excel template that I collect from different entities regarding their sales data by site and product as shown in the first table. I would like to write a python code to transform ‘Input’ to ‘Output’ so that it only collects Forecast data. I would also like to extend the table by 5 years from the current year. (i.e. For 2024, it should extend to 2029). If there is no data available, input 0. The entity name in the ‘Output’ is from cell E1 of the ‘Input’. Could someone kindly help with the code?

import pandas as pd
import datetime


df = pd.read_excel('APPLE.xlsx')  


df = df[df['Actual/Forecast'] == 'F']


df = df[['Site', 'Year', 'Month', 'Product', 'Total']]


current_year = datetime.datetime.now().year

years = list(range(current_year, current_year + 5))


pivot_df = df.pivot(index='Entity', columns=['Month', 'Year'], values=['Runoff', 'Reinvest', 'Stretch/Others', 'ALL CCY'])

pivot_df.columns = pivot_df.columns.map(lambda x: f"{x[1]} {x[0]}")
pivot_df.index.name = ''


for year in years:
    if year not in pivot_df.columns:
        pivot_df[year] = 0


pivot_df = pivot_df.sort_index(axis=1)

output_df = pivot_df.T


print(output_df)

import pandas as pd
import datetime

df = pd.read_excel(‘APPLE.xlsx’)

df = df[df[‘Actual/Forecast’] == ‘F’]

df = df[[‘Site’, ‘Year’, ‘Month’, ‘Product’, ‘Total’]]

current_year = datetime.datetime.now().year

years = list(range(current_year, current_year + 5))

pivot_df = df.pivot(index=’Entity’, columns=[‘Month’, ‘Year’], values=[‘Runoff’, ‘Reinvest’, ‘Stretch/Others’, ‘ALL CCY’])

pivot_df.columns = pivot_df.columns.map(lambda x: f”{x[1]} {x[0]}”)
pivot_df.index.name = ”

for year in years:
if year not in pivot_df.columns:
pivot_df[year] = 0

pivot_df = pivot_df.sort_index(axis=1)

output_df = pivot_df.T

print(output_df)