import pandas as pd
from sqlalchemy import create_engine
conn=("mysql+pymysql://root:123456@localhost:3306/test")


file=pd.read_sql("select * from tbl_course",conn)

df1=pd.DataFrame(file)
mask=(df1["open_time"]<"2019-12")
df1=df1[mask]
df1=df1.drop("dependency",axis=1).join(df1["dependency"].str.split("[/-]",expand=True).stack().reset_index(level=1,drop=True).rename("new_dependency"))
df2=df1.reset_index().groupby("new_dependency",as_index=False)['hours_per_week'].sum()
df3=df1.reset_index().groupby("new_dependency",as_index=False)['lecturer_id'].nunique()
df3=pd.concat([df2,df3],axis=1)

df3.columns=["dependency" "total_hours" "uniqs"]
df3=df3.sort_values(by="total_hours",ascending=False)
df3.to_csv(r"C:\Users\PC\Desktop\heyanli\2A_he.csv")