Here how the departure table should look like and the headers:
| Report_Month | Aircraft_Departures(Domestic) | Aircraft_Departures(International) | MOM Change(domestic) | MOM Change(international) | change_SMLY(Domestic) | change_SMLY(International) |
|---|
Here is the code which I have written to get this table from SQL as pivot table.
import pandas as pd
import pymssql
import numpy as np
conn = pymssql.connect('database-1.cmtpadv1tggf.ap-south-1.rds.amazonaws.com','OPERATIONSDBOWNER','OPERATIONSDBOWNER@4321','ForOperationsData')
s ='''SELECT * FROM [ForOperationsData].[dbo].[IN_Monthly_DGCA_Airline_Traffic]'''
df = pd.read_sql(s, conn)
df1 = df.pivot_table(index='Report_Month', columns='Airline_Service', values='Aircraft_Departures', aggfunc=np.sum)
df1.sort_values(by = 'Report_Month', ascending=False, inplace=True)
df1.rename(columns = {'Report_Month':'Months','Domestic':'Aircraft_Departures(Domestic)','International':'Aircraft_Departures(International)'}, inplace = True)
df1['index'] = df1.index
first_column = df1.pop('index')
df1.insert(0, 'index', first_column)
cols = ['Aircraft_Departures(Domestic)','Aircraft_Departures(International)']
df1['index'] = pd.to_datetime(df1['index'], dayfirst=True)
df3 = df1.set_index('index')[cols]
print(df1)
d = {'Aircraft_Departures(Domestic)':'(Domestic)','Aircraft_Departures(International)':'(International)'}
df1 = df3.shift(1, freq='MS')
df2 = df3.shift(12, freq='MS')
df4 = df3.shift(36, freq='MS')#pre covid for year 2022
df11 = df3.sub(df1).div(df1).rename(columns=d).add_prefix('MOM Change')
df22 = df3.sub(df2).div(df2).rename(columns=d).add_prefix('change_SMLY')
df44 = df3.sub(df4).div(df4).rename(columns=d).add_prefix('change Pre-Covid')
df3 = pd.concat([df3, df11.reindex(df3.index), df22.reindex(df3.index), df44.reindex(df3.index)], axis=1)
df3.to_excel("Social media script", sheet_name='Ratio')
formula for m-o-m and y-o-y will be calculated by
MOM Change = (Departure this month - Departure previous month)/Departure previous month
change_SMLY = (Departure current month - Departure same month last year)/Departure same month last year change_Pre-covid = (Departure current month - Departure same month in 2019)/Departure same month in 2019
I have calculated 'change_pre-covid ' for the year 2022 as an example.
Help me in getting those values for the years 2021 and 2020 as well
Create DatetimeIndex first, then use DataFrame.shift by one and 12 months, subtract and divide and last join all DataFrames together:
cols = ['Aircraft_Departures(Domestic)','Aircraft_Departures(International)']
df1['Report_Month'] = pd.to_datetime(df1['Report_Month'], dayfirst=True)
df = df1.set_index('Report_Month')[cols]
d = {'Aircraft_Departures(Domestic)':'(International)',
'Aircraft_Departures(International)':'(domestic)'}
df1 = df.shift(1, freq='MS')
df2 = df.shift(12, freq='MS')
df11 = df.sub(df1).div(df1).rename(columns=d).add_prefix('MOM Change')
df22 = df.sub(df2).div(df2).rename(columns=d).add_prefix('change_SMLY')
df = pd.concat([df, df11.reindex(df.index), df22.reindex(df.index)], axis=1)
print (df)
Aircraft_Departures(Domestic) \
Report_Month
2022-06-01 39482
2022-05-01 87224
2022-04-01 82701
2022-03-01 81971
2022-02-01 58153
2022-01-01 64437
2021-12-01 88270
2021-11-01 82614
2021-10-01 74137
2021-09-01 66580
2021-08-01 63108
2021-07-01 51402
2021-06-01 34318
Aircraft_Departures(International) MOM Change(International) \
Report_Month
2022-06-01 7896 -0.547349
2022-05-01 12220 0.054691
2022-04-01 10641 0.008906
2022-03-01 10636 0.409575
2022-02-01 8396 -0.097522
2022-01-01 9826 -0.270001
2021-12-01 9599 0.068463
2021-11-01 8526 0.114342
2021-10-01 8475 0.113503
2021-09-01 7281 0.055017
2021-08-01 5756 0.227734
2021-07-01 4252 0.497815
2021-06-01 3311 NaN
MOM Change(domestic) change_SMLY(International) \
Report_Month
2022-06-01 -0.353846 0.150475
2022-05-01 0.148388 NaN
2022-04-01 0.000470 NaN
2022-03-01 0.266794 NaN
2022-02-01 -0.145532 NaN
2022-01-01 0.023648 NaN
2021-12-01 0.125850 NaN
2021-11-01 0.006018 NaN
2021-10-01 0.163988 NaN
2021-09-01 0.264941 NaN
2021-08-01 0.353716 NaN
2021-07-01 0.284204 NaN
2021-06-01 NaN NaN
change_SMLY(domestic)
Report_Month
2022-06-01 1.384778
2022-05-01 NaN
2022-04-01 NaN
2022-03-01 NaN
2022-02-01 NaN
2022-01-01 NaN
2021-12-01 NaN
2021-11-01 NaN
2021-10-01 NaN
2021-09-01 NaN
2021-08-01 NaN
2021-07-01 NaN
2021-06-01 NaN