import pandas as pd

if __name__ == "__main__":
    years = [2011, 2013,2015, 2018, 2020]

    #读取CHARLS数据
    CHARLS_data = pd.read_csv("CHARLS_data_p_n_m.csv")
    CHARLS_data.to_csv("CHARLS_data_p_n_m_nd.csv",index=False)
    CHARLS_data = pd.read_csv("CHARLS_data_p_n_m_nd.csv")
    
    #读取NDVI数据
    ndvi_data = pd.read_excel(f"NDVI/【立方数据学社】地级市等级的逐年NDVI.xlsx")

    for year in years:
        #新增两列，分别为year的去年和前年的环境值
        # CHARLS_data[['last_year_pm2.5', "before_last_pm2.5"]]=''
        #开始筛选出year的数据
        CHARLS_data_year = CHARLS_data[CHARLS_data['wave']==year]
        #两个表合并
        table_merge = pd.merge(CHARLS_data_year, ndvi_data, left_on="city", right_on="CITY", how='left')
        # table_merge_last.to_csv("123.csv",index=False)
        #更新CHARLS表
        CHARLS_data.loc[CHARLS_data['wave']==year, 'last_year_ndvi'] = table_merge[str(year-1)].values
        CHARLS_data.loc[CHARLS_data['wave']==year, 'before_last_ndvi'] = table_merge[str(year-2)].values
        print(year)
    CHARLS_data.to_csv("CHARLS_data_p_n_m_nd.csv",index=False)