地铁刷卡数据分析第三关

# -*- coding: utf-8 -*- import pandas as pd def return_values(): # 读取数据表 df = pd.read_excel("星期、天气、节假日数据.xlsx") # 获取所有列名 cols = df.columns.tolist() # 智能匹配列名（关键词查找） date_col = next((c for c in cols if "日期" in c), cols[0]) week_col = next((c for c in cols if "星期" in c), cols[1]) # 节假日列：找包含"节"/"假"/"holiday"的列，找不到就用第3列兜底 holiday_col = next((c for c in cols if "节" in c or "假" in c or "holiday" in c.lower()), cols[2]) temp_col = next((c for c in cols if "温" in c or "度" in c), cols[3]) weather_col = next((c for c in cols if "天气" in c or "雨雪" in c), cols[4]) # 初始化结果数据框 data = pd.DataFrame() # A1_日期 data["A1_日期"] = df[date_col] # A2_星期值：星期一~星期四=0；星期五=0.5；周末=1 def week_val(x): x = str(x).strip() if x in ["星期一", "星期二", "星期三", "星期四"]: return 0 elif x == "星期五": return 0.5 else: # 星期六、星期日 return 1 data["A2_星期值"] = df[week_col].apply(week_val) # A3_节假日：中秋、国庆=1；其他节假日=0.5；非节假日=0 def holiday_val(x): x = str(x).strip() if x in ["中秋节", "国庆节"]: return 1 elif x == "非节假日" or x == "" or x == "nan": return 0 else: return 0.5 data["A3_节假日"] = df[holiday_col].apply(holiday_val) # A4_气温：>=10度为1，否则为2 data["A4_气温"] = df[temp_col].apply(lambda x: 1 if float(x) >= 10 else 2) # A5_雨雪：晴/阴/多云/转晴/转多云=1；小雨/阵雨/转小雨/转阵雨=2；其他=3 def weather_val(x): x = str(x).strip() if x in ["晴", "阴", "多云", "转晴", "转多云"]: return 1 elif x in ["小雨", "阵雨", "转小雨", "转阵雨"]: return 2 else: return 3 data["A5_雨雪"] = df[weather_col].apply(weather_val) return data