The Pandas Code looks like below.
cognos_cols = ‘DataType,Combi,Month,Year,Qty_Val’.split(‘,’)
df_cis = pd.read_csv(cis_file, sep=None, encoding = “UTF-16”,
header=None, engine=‘python’, names = cognos_cols, usecols=[0, 1, 3, 4, 5])
df_volume = pd.read_csv(volume_file, sep=None, encoding = “UTF-16”,
header=None, engine=‘python’, names = cognos_cols, usecols=[0, 1, 3, 4, 5])df_orders = pd.read_excel(fs_orders_file, header=0)
df_orders.PromDock_InvoiceDate = pd.to_datetime(df_orders.PromDock_InvoiceDate).dt.datedf_bom = pd.read_excel(fs_bom_file, header=0)
df_bom = df_bom.drop(axis=0, index=0) # drop the first row
df_bom = df_bom.drop(columns=‘IsBR’)df_actuals = pd.read_excel(actuals_file, ‘Actuals’, skiprows=4, header=0)
df_actuals.By = pd.to_datetime(df_actuals.By).dt.datedf_brazil = pd.read_excel(brazil_file, header=0)
df_brazil.By = pd.to_datetime(df_brazil.By).dt.datePivot to get sum of Plan, Month and Year Values
df1_plan = df_plan.pivot_table(index = [‘Combi’, ‘Month’, ‘Year’],
columns = ‘DataType’,
values = ‘Qty_Val’,
aggfunc = ‘sum’).reset_index().rename_axis(None).rename_axis(None, axis=1)Set up columns with correct names
df1_plan[‘DataType’] = ‘Plan’
df1_plan = df1_plan.rename(columns = {‘Budget’:‘Qty’, ‘Budget Value GBP’:‘GBPValue’ })Multiply the budget value with 1,000
df1_plan.GBPValue = df1_plan.GBPValue * 1000
Convert Months to MonthNum
df1_plan[‘MonthNum’] = df1_plan.Month.apply(lambda x: dict((v, k) for k, v in enumerate(calendar.month_abbr)))
Remove 2019 of Cognos from data from df1_plan, because this is coming from Jared + Darshil’s FDS file
df1_plan = df1_plan[df1_plan.Year == 2018].reset_index(drop=True)
Error I am getting like:-
Kindly share your suggestion.
i am sure cognos_cols is a list.
This is not full script.
Thanks a lot