import pandas as pd

def sunlight(CHARLS_data):
    years = [2011, 2013,2015, 2018, 2020]
    for year in years:
        #读取日照数据
        sunlight_data_last = pd.read_excel(f"气象数据/2000-2020年按年逐年日照/【{year-1}年】逐年日照.xlsx")
        sunlight_data_last = sunlight_data_last[["城市", "累积日照"]]
        sunlight_data_last = sunlight_data_last.rename(columns={"累积日照":"last_sunlight"})
        sunlight_data_before_last = pd.read_excel(f"气象数据/2000-2020年按年逐年日照/【{year-2}年】逐年日照.xlsx")
        sunlight_data_before_last = sunlight_data_before_last[["城市", "累积日照"]]
        sunlight_data_before_last = sunlight_data_before_last.rename(columns={"累积日照":"before_sunlight"})
        #开始筛选出year的数据
        CHARLS_data_year = CHARLS_data[CHARLS_data['wave']==year]
        #合并日照
        table_merge = pd.merge(CHARLS_data_year, sunlight_data_last, left_on="city", right_on="城市", how='left')
        table_merge = pd.merge(table_merge, sunlight_data_before_last, left_on="city", right_on="城市", how='left')
        #更新CHARLS表
        CHARLS_data.loc[CHARLS_data['wave']==year, 'last_year_sunlight'] = table_merge['last_sunlight'].values
        CHARLS_data.loc[CHARLS_data['wave']==year, 'before_last_sunlight'] = table_merge['before_sunlight'].values
        CHARLS_data.to_csv("CHARLS_data_pollutants_mete.csv",index=False)
        print(year)

def wind(CHARLS_data):
    years = [2011, 2013,2015, 2018, 2020]
    for year in years:
        #读取日照数据
        wind_data_last = pd.read_excel(f"气象数据/2000-2020年按年逐年风速/【{year-1}年】逐年风速.xlsx")
        wind_data_last = wind_data_last[["城市", "平均风速"]]
        wind_data_last = wind_data_last.rename(columns={"平均风速":"last_wind"})
        wind_data_before_last = pd.read_excel(f"气象数据/2000-2020年按年逐年风速/【{year-2}年】逐年风速.xlsx")
        wind_data_before_last = wind_data_before_last[["城市", "平均风速"]]
        wind_data_before_last = wind_data_before_last.rename(columns={"平均风速":"before_wind"})
        #开始筛选出year的数据
        CHARLS_data_year = CHARLS_data[CHARLS_data['wave']==year]
        #合并日照
        table_merge = pd.merge(CHARLS_data_year, wind_data_last, left_on="city", right_on="城市", how='left')
        table_merge = pd.merge(table_merge, wind_data_before_last, left_on="city", right_on="城市", how='left')
        #更新CHARLS表
        CHARLS_data.loc[CHARLS_data['wave']==year, 'last_year_wind'] = table_merge['last_wind'].values
        CHARLS_data.loc[CHARLS_data['wave']==year, 'before_last_wind'] = table_merge['before_wind'].values
        CHARLS_data.to_csv("CHARLS_data_pollutants_mete.csv",index=False)
        print(year)

def rain(CHARLS_data):
    years = [2011, 2013,2015, 2018, 2020]
    for year in years:
        #读取日照数据
        rain_data_last = pd.read_excel(f"气象数据/2000-2020年按年逐年降水/【{year-1}年】逐年降水.xlsx")
        rain_data_last = rain_data_last[["城市", "累积降水"]]
        rain_data_last = rain_data_last.rename(columns={"累积降水":"last_rain"})
        rain_data_before_last = pd.read_excel(f"气象数据/2000-2020年按年逐年降水/【{year-2}年】逐年降水.xlsx")
        rain_data_before_last = rain_data_before_last[["城市", "累积降水"]]
        rain_data_before_last = rain_data_before_last.rename(columns={"累积降水":"before_rain"})
        #开始筛选出year的数据
        CHARLS_data_year = CHARLS_data[CHARLS_data['wave']==year]
        #合并日照
        table_merge = pd.merge(CHARLS_data_year, rain_data_last, left_on="city", right_on="城市", how='left')
        table_merge = pd.merge(table_merge, rain_data_before_last, left_on="city", right_on="城市", how='left')
        #更新CHARLS表
        CHARLS_data.loc[CHARLS_data['wave']==year, 'last_year_rain'] = table_merge['last_rain'].values
        CHARLS_data.loc[CHARLS_data['wave']==year, 'before_last_rain'] = table_merge['before_rain'].values
        CHARLS_data.to_csv("CHARLS_data_pollutants_mete.csv",index=False)
        print(year)

def temperature(CHARLS_data):
    years = [2011, 2013,2015, 2018, 2020]
    for year in years:
        #读取日照数据
        temperature_data_last = pd.read_excel(f"气象数据/2000-2020年按年逐年气温/【{year-1}年】逐年气温.xlsx")
        temperature_data_last = temperature_data_last[["城市", "平均气温"]]
        temperature_data_last = temperature_data_last.rename(columns={"平均气温":"last_temperature"})
        temperature_data_before_last = pd.read_excel(f"气象数据/2000-2020年按年逐年气温/【{year-2}年】逐年气温.xlsx")
        temperature_data_before_last = temperature_data_before_last[["城市", "平均气温"]]
        temperature_data_before_last = temperature_data_before_last.rename(columns={"平均气温":"before_temperature"})
        #开始筛选出year的数据
        CHARLS_data_year = CHARLS_data[CHARLS_data['wave']==year]
        #合并日照
        table_merge = pd.merge(CHARLS_data_year, temperature_data_last, left_on="city", right_on="城市", how='left')
        table_merge = pd.merge(table_merge, temperature_data_before_last, left_on="city", right_on="城市", how='left')
        #更新CHARLS表
        CHARLS_data.loc[CHARLS_data['wave']==year, 'last_year_temperature'] = table_merge['last_temperature'].values
        CHARLS_data.loc[CHARLS_data['wave']==year, 'before_last_temperature'] = table_merge['before_temperature'].values
        CHARLS_data.to_csv("CHARLS_data_pollutants_mete.csv",index=False)
        print(year)

def humidity(CHARLS_data):
    years = [2011, 2013,2015, 2018, 2020]
    for year in years:
        #读取日照数据
        humidity_data_last = pd.read_excel(f"气象数据/2000-2020年按年逐年湿度/【{year-1}年】逐年湿度.xlsx")
        humidity_data_last = humidity_data_last[["城市", "平均湿度"]]
        humidity_data_last = humidity_data_last.rename(columns={"平均湿度":"last_humidity"})
        humidity_data_before_last = pd.read_excel(f"气象数据/2000-2020年按年逐年湿度/【{year-2}年】逐年湿度.xlsx")
        humidity_data_before_last = humidity_data_before_last[["城市", "平均湿度"]]
        humidity_data_before_last = humidity_data_before_last.rename(columns={"平均湿度":"before_humidity"})
        #开始筛选出year的数据
        CHARLS_data_year = CHARLS_data[CHARLS_data['wave']==year]
        #合并日照
        table_merge = pd.merge(CHARLS_data_year, humidity_data_last, left_on="city", right_on="城市", how='left')
        table_merge = pd.merge(table_merge, humidity_data_before_last, left_on="city", right_on="城市", how='left')
        #更新CHARLS表
        CHARLS_data.loc[CHARLS_data['wave']==year, 'last_year_humidity'] = table_merge['last_humidity'].values
        CHARLS_data.loc[CHARLS_data['wave']==year, 'before_last_humidity'] = table_merge['before_humidity'].values
        CHARLS_data.to_csv("CHARLS_data_pollutants_mete.csv",index=False)
        print(year)

if __name__ == "__main__":
    #读取CHARLS数据
    CHARLS_data = pd.read_csv("CHARLS_data_p_n.csv")
    CHARLS_data.to_csv("CHARLS_data_p_n_m.csv",index=False)
    CHARLS_data = pd.read_csv("CHARLS_data_p_n_m.csv")
    sunlight(CHARLS_data)
    CHARLS_data = pd.read_csv("CHARLS_data_p_n_m.csv")
    wind(CHARLS_data)
    CHARLS_data = pd.read_csv("CHARLS_data_p_n_m.csv")
    rain(CHARLS_data)
    CHARLS_data = pd.read_csv("CHARLS_data_p_n_m.csv")
    temperature(CHARLS_data)
    CHARLS_data = pd.read_csv("CHARLS_data_p_n_m.csv")
    humidity(CHARLS_data)