Python处理SQL航班数据：环比同比及Pre-Covid对比计算扩展求助

目标表格结构 #

现有Python代码 #

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')

计算公式 #

• MOM Change = (本月起降量 - 上月起降量)/上月起降量
• change_SMLY = (本月起降量 - 去年同期起降量)/去年同期起降量
• change Pre-Covid = (本月起降量 - 2019年同期起降量)/2019年同期起降量

当前需求 #

现有代码仅实现了2022年的change Pre-Covid计算，需要扩展该计算至2020年和2021年。

原代码用固定shift(36)仅能匹配2022年（2022-2019=36个月），但2020年需匹配2019年同期（差12个月）、2021年差24个月，固定shift无法覆盖所有年份。更通用的方法是通过月份匹配获取2019年同期数据：

修改后完整代码 #

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={'Domestic':'Aircraft_Departures(Domestic)','International':'Aircraft_Departures(International)'}, inplace=True)
df1.index = pd.to_datetime(df1.index, dayfirst=True)
df3 = df1.copy()
cols = ['Aircraft_Departures(Domestic)','Aircraft_Departures(International)']

# 定义列名映射
d = {'Aircraft_Departures(Domestic)':'(Domestic)','Aircraft_Departures(International)':'(International)'}

# 计算MOM Change
df_mom = df3.sub(df3.shift(1, freq='MS')).div(df3.shift(1, freq='MS')).rename(columns=d).add_prefix('MOM Change')

# 计算change_SMLY
df_smly = df3.sub(df3.shift(12, freq='MS')).div(df3.shift(12, freq='MS')).rename(columns=d).add_prefix('change_SMLY')

# 计算change Pre-Covid（适配所有年份）
# 提取2019年数据并添加月份字段
df_2019 = df3[df3.index.year == 2019].copy()
df_2019['month'] = df_2019.index.month
df_2019 = df_2019.reset_index(drop=True)

# 为原数据添加月份字段用于匹配
df3['month'] = df3.index.month

# 合并原数据与2019年同期数据
df_merged = pd.merge(df3, df_2019, on='month', suffixes=('', '_2019'))

# 计算Pre-Covid变化率并整理列名
df_pre_covid = df_merged[cols].sub(df_merged[[col+'_2019' for col in cols]]).div(df_merged[[col+'_2019' for col in cols]])
df_pre_covid.columns = [f'change Pre-Covid{d[col]}' for col in cols]
df_pre_covid.index = df3.index

# 合并所有计算结果并导出
df_final = pd.concat([df3[cols], df_mom, df_smly, df_pre_covid], axis=1)
df_final.to_excel("Social media script", sheet_name='Ratio')

说明 #

• 通过月份匹配替代固定shift，无论当前年份是2020/2021/2022还是之后的年份，都能自动匹配到2019年同期数据
• 保留原代码的MOM、SMLY计算逻辑，仅修改Pre-Covid部分的实现
• 自动处理不同年份与2019年的同期匹配，无需手动调整shift参数

内容的提问来源于stack exchange，提问作者Nilay Suman