<jupyter_start><jupyter_text>2개년도 변화에 따른 포인트 부여 부분 코드<jupyter_code>def give_point(df1, df2): 
    df_n = pd.merge(df1, df2, on = "기업명", how = "inner").dropna()
    df_n["Point"] = df_n["평가등급_x"] - df_n["평가등급_y"]
    df_n["Point"][df_n["Point"].values != 0] =1
    df_n = pd.merge(df_n, df_credit_2014, on = "기업명", how="inner")
    return df_n[["기업명","Point"]]
df_2014 = give_point(df_credit_2013,df_credit_2014)
df_2015 = give_point(df_credit_2014,df_credit_2015)
df_2016 = give_point(df_credit_2015,df_credit_2016)
df_2017 = give_point(df_credit_2016,df_credit_2017)
df_2018 = give_point(df_credit_2017,df_credit_2018)
df_2019 = give_point(df_credit_2018,df_credit_2019)
df_2020 = give_point(df_credit_2019,df_credit_2020)
df_credit_2014 = df_2014
df_credit_2015 = df_2015
df_credit_2016 = df_2016
df_credit_2017 = df_2017
df_credit_2018 = df_2018
df_credit_2019 = df_2019
df_credit_2020 = df_2020
df_esg = pd.read_csv("2011-2019-ESG.tsv", sep='\t')
df_esg.head()<jupyter_output><empty_output><jupyter_text>기업코드 6자리로 앞자리 "0"부여하고 ESG등급 숫자 치환<jupyter_code>df_esg = df_esg.drop(['NO', '비고'], axis=1).dropna()
df_esg["기업코드"] = df_esg["기업코드"].astype(str)
df_esg.기업코드 = df_esg.기업코드.apply(lambda x: x.zfill(6))
df_esg.기업코드
#esg 데이터를 정수화 합니다

di = {"S":70.0,"A+":60.0,"A":50.0,"B+":40.0,"B":30.0,"B이하":30.0,"C":20.0,"C이하":20.0,"D":10.0}
esg = map(di.get, df_esg.ESG등급)
e = map(di.get, df_esg.지배구조)
s = map(di.get, df_esg.ESG등급)
g = map(di.get, df_esg.ESG등급)
df_esg.ESG등급 = list(esg)
df_esg.지배구조 = list(e)
df_esg.사회 = list(s)
df_esg.환경 = list(g)
df_esg.head()
import seaborn as sns
plt.figure(figsize=(100,100))
sns.catplot(x="기업명", y="ESG등급",
                hue="평가년도",
                data=df_esg.tail(50), kind="bar")
#esg 평가데이터를 평가년도에 맞게 각 데이터 프레임으로 분활해 줍니다
for i in range(2011,2020):
    setattr(mod, 'df_esg_{}'.format(i),df_esg[df_esg["평가년도"]==i])
#기업들의 년도별 각 계정과목들을 변수로 만들어줍니다
df_f = ["당기순이익.xlsx","자산총계.xlsx","매출액.xlsx","재고자산.xlsx","유보액_총자산.xlsx","비유동부채.xlsx","유동부채.xlsx","유동자산.xlsx"]

def change_columns(df):
    columns = ["종목코드","기업명","결산월","단위","2019","2018","2017","2016","2015","2014","2013","2012","2011","2010","2009","2008"]
    df.columns = columns
    df = df.rename(columns = {"종목코드":"기업코드"})
    return df

for i in range(1,9):
    setattr(mod, 'df_balance_{}'.format(i), change_columns(pd.read_excel(df_f[i-1])[9:]))
    
plt.figure(figsize=(50,70))
sns.catplot(x="2018", y="2019",
                hue="기업명",
                data=df_balance_1.head(10), kind="bar")
list_credit = [df_credit_2013,df_credit_2014,df_credit_2015,df_credit_2016,df_credit_2017,
             df_credit_2018,df_credit_2019]

list_esg = [df_esg_2011,df_esg_2012,df_esg_2013,df_esg_2014,df_esg_2015,df_esg_2016,
            df_esg_2017,df_esg_2018,df_esg_2019]

list_balance = [df_balance_1,df_balance_2,df_balance_3,df_balance_4,df_balance_5,
               df_balance_6,df_balance_7,df_balance_8,]
#기업명만 있는 신용평가 데이터를 ESG데이터에 기업명이 같은 데이터로만 병합를 시킵니다
def merge_code(dfa,dfb):
    dfb = dfb[["기업코드","기업명"]][dfb["기업명"].isin(dfa["기업명"])]
    dfa = pd.merge(dfa,dfb, on="기업명",how="inner")
    return dfa

for i in range(2013,2020):
    setattr(mod, 'df_rating_{}'.format(i), merge_code(list_credit[i-2013],list_esg[i-2013]))

# 그 후 각 년도별 계정 데이터프레임에 있는 기업들 중 신용평가 점수와 esg점수가 있는 기업별로 묶어줍니다

for k in range(2011, 2020):
    for i in range(1,9):
        setattr(mod, 'df_{}_{}'.format(k,i), pd.DataFrame(list_balance[i-1].iloc[:,[0,2023-k]]))
        

df_columns = ["기업코드","당기순이익","자산총계","매출액","재고자산","유보액_총자산","비유동부채","유동부채","유동자산"]<jupyter_output><empty_output><jupyter_text>망작<jupyter_code># class merger:
    
#       # 생성자 함수 : __init__
#     def __init__(self, num):
#         self.num1 = num
#         data_frames_2011 = [df_2011_1,df_2011_2,df_2011_3,df_2011_4,df_2011_5,df_2011_6,df_2011_7,df_2011_8,]
#         data_frames_2012 = [df_2012_1,df_2012_2,df_2012_3,df_2012_4,df_2012_5,df_2012_6,df_2012_7,df_2012_8,]
#         data_frames_2013 = [df_2013_1,df_2013_2,df_2013_3,df_2013_4,df_2013_5,df_2013_6,df_2013_7,df_2013_8,]
#         data_frames_2014 = [df_2014_1,df_2014_2,df_2014_3,df_2014_4,df_2014_5,df_2014_6,df_2014_7,df_2014_8,]
#         data_frames_2015 = [df_2015_1,df_2015_2,df_2015_3,df_2015_4,df_2015_5,df_2015_6,df_2015_7,df_2015_8,]
#         data_frames_2016 = [df_2016_1,df_2016_2,df_2016_3,df_2016_4,df_2016_5,df_2016_6,df_2016_7,df_2016_8,]
#         data_frames_2017 = [df_2017_1,df_2017_2,df_2017_3,df_2017_4,df_2017_5,df_2017_6,df_2017_7,df_2017_8,]
#         data_frames_2018 = [df_2018_1,df_2018_2,df_2018_3,df_2018_4,df_2018_5,df_2018_6,df_2018_7,df_2018_8,]
#         data_frames_2019 = [df_2019_1,df_2019_2,df_2019_3,df_2019_4,df_2019_5,df_2019_6,df_2019_7,df_2019_8,]
       
#     def merge(num):    
#         setattr(mod, 'df_merged_{}'.format(num),reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frame_list[num-2011]).dropna())
  


data_frames_2011 = [df_2011_1,df_2011_2,df_2011_3,df_2011_4,df_2011_5,df_2011_6,df_2011_7,df_2011_8,]#df_2011_9,df_2011_10,df_2011_11,]
df_merged_2011 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_2011).dropna()
df_merged_2011.columns = df_columns


data_frames_2012 = [df_2012_1,df_2012_2,df_2012_3,df_2012_4,df_2012_5,df_2012_6,df_2012_7,df_2012_8,]#df_2012_9,df_2012_10,df_2012_11,]
df_merged_2012 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_2012).dropna()
df_merged_2012.columns = df_columns

data_frames_2013 = [df_2013_1,df_2013_2,df_2013_3,df_2013_4,df_2013_5,df_2013_6,df_2013_7,df_2013_8,]#df_2013_9,df_2013_10,df_2013_11,]
df_merged_2013 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_2013).dropna()
df_merged_2013.columns = df_columns

data_frames_2014 = [df_2014_1,df_2014_2,df_2014_3,df_2014_4,df_2014_5,df_2014_6,df_2014_7,df_2014_8,]#df_2014_9,df_2014_10,df_2014_11,]
df_merged_2014 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_2014).dropna()
df_merged_2014.columns = df_columns

data_frames_2015 = [df_2015_1,df_2015_2,df_2015_3,df_2015_4,df_2015_5,df_2015_6,df_2015_7,df_2015_8,]#df_2015_9,df_2015_10,df_2015_11,]
df_merged_2015 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_2015).dropna()
df_merged_2015.columns = df_columns

data_frames_2016 = [df_2016_1,df_2016_2,df_2016_3,df_2016_4,df_2016_5,df_2016_6,df_2016_7,df_2016_8,]#df_2016_9,df_2016_10,df_2016_11,]
df_merged_2016 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_2016).dropna()
df_merged_2016.columns = df_columns

data_frames_2017 = [df_2017_1,df_2017_2,df_2017_3,df_2017_4,df_2017_5,df_2017_6,df_2017_7,df_2017_8,]#df_2017_9,df_2017_10,df_2017_11,]
df_merged_2017 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_2017).dropna()
df_merged_2017.columns = df_columns

data_frames_2018 = [df_2018_1,df_2018_2,df_2018_3,df_2018_4,df_2018_5,df_2018_6,df_2018_7,df_2018_8,]#df_2018_9,df_2018_10,df_2018_11,]
df_merged_2018 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_2018).dropna()
df_merged_2018.columns = df_columns

data_frames_2019 = [df_2019_1,df_2019_2,df_2019_3,df_2019_4,df_2019_5,df_2019_6,df_2019_7,df_2019_8,]#df_2019_9,df_2019_10,df_2019_11,]
df_merged_2019 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_2019).dropna()
df_merged_2019.columns = df_columns
df_merged_2019.tail()
df_merged_2019.describe()
df_m = pd.read_excel("매출채권회전율.xlsx").rename(columns={"name":"기업명"})
df_m["매출채권회전율"] = df_m["매출채권회전율"].apply(lambda x : np.reciprocal(x))
for i in range(2011,2020):
        setattr(mod, 'df_m_{}'.format(i), df_m[df_m["DATE"]==i].replace("(주)","").dropna())
df_m_2011.head()
m_list = [df_m_2011, df_m_2012, df_m_2013, df_m_2014, df_m_2015, df_m_2016, df_m_2017, df_m_2018, df_m_2019,]
for i in range(2011,2020):
        setattr(mod, 'df_m_{}'.format(i), pd.merge(m_list[i-2011], df_balance_2, on="기업명", how="inner").drop_duplicates(['기업명'],keep="last").reset_index(drop=True).iloc[:,3:5] )
merged_list = [df_merged_2011,df_merged_2012,df_merged_2013,df_merged_2014,df_merged_2015,df_merged_2016,df_merged_2017,df_merged_2018,df_merged_2019]
m_list = [df_m_2011, df_m_2012, df_m_2013, df_m_2014, df_m_2015, df_m_2016, df_m_2017, df_m_2018, df_m_2019,]
for i in range(2011,2020):
        setattr(mod, 'df_merged_{}'.format(i), pd.merge(m_list[i-2011], merged_list[i-2011], on='기업코드', how='inner'))
list(df_merged_2011.columns)
def test(df) :
    df["부채총계"] = df["유동부채"] + df["비유동부채"]
    df["운전자본"] = df_merged_2011["유동자산"] - df["유동부채"]
    df["당기순이익_총자산"] = df["당기순이익"] / df["자산총계"]
    df["부채총계_총자산"] = df["부채총계"] / df["자산총계"]
    df["운전자본_총자산"] = df["운전자본"] / df["자산총계"]
    df["유동자산_총자산"] = df["유동자산"] / df["자산총계"]
    df["매출액_총자산"] = df["매출액"] / df["자산총계"]
    df["재고자산_총자산"] = df["재고자산"] / df["자산총계"]
    df["매출채권_총자산"] = (df["매출채권회전율"] * df["매출액"])/ df["자산총계"]
    return df

def test2 ():
    for df in df_ls:
        test(df)
df_merged_2019.head()
data_frames_a_2011 = [df_merged_2011,df_esg_2011]
df_merged_b_2011 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_a_2011).dropna()

data_frames_a_2012 = [df_merged_2012,df_esg_2012]
df_merged_b_2012 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_a_2012).dropna()

data_frames_a_2013 = [df_merged_2013,df_esg_2013]
df_merged_b_2013 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_a_2013).dropna()

data_frames_a_2014 = [df_merged_2014,df_esg_2014]
df_merged_b_2014 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_a_2014).dropna()

data_frames_a_2015 = [df_merged_2015,df_esg_2015]
df_merged_b_2015 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_a_2015).dropna()

data_frames_a_2016 = [df_merged_2016,df_esg_2016]
df_merged_b_2016 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_a_2016).dropna()

data_frames_a_2017 = [df_merged_2017,df_esg_2017]
df_merged_b_2017 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_a_2017).dropna()

data_frames_a_2018 = [df_merged_2018,df_esg_2018]
df_merged_b_2018 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_a_2018).dropna()

data_frames_a_2019 = [df_merged_2019,df_esg_2019]
df_merged_b_2019 = reduce(lambda left,right: pd.merge(left,right,on=['기업코드'], how='inner'), data_frames_a_2019).dropna()
df_merged_b_2019.head()
# def change_columns(a):
#     a=a.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]


df_merged_b_2011 = df_merged_b_2011.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]#.rename(columns={"자산총계":"자산총계"})
df_merged_b_2012 = df_merged_b_2012.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]#.rename(columns={"자산총계":"자산총계"})
df_merged_b_2013 = df_merged_b_2013.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]#.rename(columns={"자산총계":"자산총계"})
df_merged_b_2014 = df_merged_b_2014.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]#.rename(columns={"자산총계":"자산총계"})
df_merged_b_2015 = df_merged_b_2015.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]#.rename(columns={"자산총계":"자산총계"})
df_merged_b_2016 = df_merged_b_2016.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]#.rename(columns={"자산총계":"자산총계"})
df_merged_b_2017 = df_merged_b_2017.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]#.rename(columns={"자산총계":"자산총계"})
df_merged_b_2018 = df_merged_b_2018.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]#.rename(columns={"자산총계":"자산총계"})
df_merged_b_2019 = df_merged_b_2019.iloc[:,[1,6,12,13,14,15,16,17,18,20,21,22,23]]#.rename(columns={"자산총계":"자산총계"})
change_columns(df_merged_b_2011)
change_columns(df_merged_b_2012)
change_columns(df_merged_b_2013)
change_columns(df_merged_b_2014)
change_columns(df_merged_b_2015)
change_columns(df_merged_b_2016)
change_columns(df_merged_b_2017)
change_columns(df_merged_b_2018)
change_columns(df_merged_b_2019)

df_merged_b_2011.head()
df_merged_b_2011.describe()
df_merged_z_2013 = df_merged_b_2013[df_merged_b_2013["기업코드"].isin(list(set(df_merged_b_2011["기업코드"])&set(df_merged_b_2013["기업코드"])&set(df_merged_b_2012["기업코드"])))].reset_index(drop=True)
df_merged_z_2012 = df_merged_b_2012[df_merged_b_2012["기업코드"].isin(list(set(df_merged_b_2011["기업코드"])&set(df_merged_b_2013["기업코드"])&set(df_merged_b_2012["기업코드"])))].reset_index(drop=True)
df_merged_z_2011 = df_merged_b_2011[df_merged_b_2011["기업코드"].isin(list(set(df_merged_b_2011["기업코드"])&set(df_merged_b_2013["기업코드"])&set(df_merged_b_2012["기업코드"])))].reset_index(drop=True)
print(len(df_merged_z_2011))
print(len(df_merged_z_2013))
print(len(df_merged_z_2012))
# df_merged_c_2013 = pd.DataFrame(columns=df_merged_b_2014.columns)
# df_merged_c_2013["기업코드"] = df_merged_z_2013["기업코드"]
# df_merged_c_2013['자산총계'] = ((df_merged_z_2013['자산총계'] + df_merged_z_2012['자산총계'])/2 -(df_merged_z_2012['자산총계'] + df_merged_z_2011['자산총계'])/2)\
#                                     / np.abs((df_merged_z_2012['자산총계'] + df_merged_z_2011['자산총계'])/2)
# df_merged_c_2013['유보액_총자산'] = ((df_merged_z_2013['유보액_총자산'] + df_merged_z_2012['유보액_총자산'])/2 -(df_merged_z_2012['유보액_총자산'] + df_merged_z_2011['유보액_총자산'])/2)\
#                                     / np.abs((df_merged_z_2012['유보액_총자산'] + df_merged_z_2011['유보액_총자산'])/2)
# df_merged_c_2013['당기순이익_총자산'] = ((df_merged_z_2013['당기순이익_총자산'] + df_merged_z_2012['당기순이익_총자산'])/2 -(df_merged_z_2012['당기순이익_총자산'] + df_merged_z_2011['당기순이익_총자산'])/2)\
#                                     / np.abs((df_merged_z_2012['당기순이익_총자산'] + df_merged_z_2011['당기순이익_총자산'])/2)
# df_merged_c_2013['부채총계_총자산'] = ((df_merged_z_2013['부채총계_총자산'] + df_merged_z_2012['부채총계_총자산'])/2 -(df_merged_z_2012['부채총계_총자산'] + df_merged_z_2012['부채총계_총자산'])/2)\
#                                     / np.abs((df_merged_z_2012['부채총계_총자산'] + df_merged_z_2011['부채총계_총자산'])/2)
# df_merged_c_2013['운전자본_총자산'] = ((df_merged_z_2013['운전자본_총자산'] + df_merged_z_2012['운전자본_총자산'])/2 -(df_merged_z_2012['운전자본_총자산'] + df_merged_z_2011['운전자본_총자산'])/2)\
#                                     / np.abs((df_merged_z_2012['운전자본_총자산'] + df_merged_z_2011['운전자본_총자산'])/2)
# df_merged_c_2013['매출액_총자산'] = ((df_merged_z_2013['매출액_총자산'] + df_merged_z_2012['매출액_총자산'])/2 -(df_merged_z_2012['매출액_총자산'] + df_merged_z_2011['매출액_총자산'])/2)\
#                                     / np.abs((df_merged_z_2012['매출액_총자산'] + df_merged_z_2011['매출액_총자산'])/2)
# df_merged_c_2013['재고자산_총자산'] = ((df_merged_z_2013['재고자산_총자산'] + df_merged_z_2012['재고자산_총자산'])/2 -(df_merged_z_2012['재고자산_총자산'] + df_merged_z_2011['재고자산_총자산'])/2)\
#                                     / np.abs((df_merged_z_2012['재고자산_총자산'] + df_merged_z_2011['재고자산_총자산'])/2)
# df_merged_c_2013['유동자산_총자산'] = ((df_merged_z_2013['유동자산_총자산'] + df_merged_z_2012['유동자산_총자산'])/2 -(df_merged_z_2012['유동자산_총자산'] + df_merged_z_2011['유동자산_총자산'])/2)\
#                                     / np.abs((df_merged_z_2012['유동자산_총자산'] + df_merged_z_2011['유동자산_총자산'])/2)
# df_merged_c_2013['매출채권_총자산'] = ((df_merged_z_2013['매출채권_총자산'] + df_merged_z_2012['매출채권_총자산'])/2 -(df_merged_z_2012['매출채권_총자산'] + df_merged_z_2011['매출채권_총자산'])/2)\
#                                     / np.abs((df_merged_z_2012['매출채권_총자산'] + df_merged_z_2011['매출채권_총자산'])/2)
# df_merged_c_2013['ESG등급'] = (df_merged_z_2013['ESG등급'] + df_merged_z_2012['ESG등급'])/2 -  (df_merged_z_2012['ESG등급'] + df_merged_z_2011['ESG등급'])/2
# df_merged_c_2013['지배구조'] = (df_merged_z_2013['지배구조'] + df_merged_z_2012['지배구조'])/2 - (df_merged_z_2012['지배구조'] + df_merged_z_2011['지배구조'])/2
# df_merged_c_2013['사회'] = (df_merged_z_2013['사회'] + df_merged_z_2012['사회'])/2 - (df_merged_z_2012['사회'] + df_merged_z_2011['사회'])/2
# df_merged_c_2013['환경'] = (df_merged_z_2013['환경'] + df_merged_z_2012['환경'])/2 - (df_merged_z_2012['환경'] +df_merged_z_2011['환경'])/2
# df_total_2014 = pd.merge(df_rating_2014,df_merged_c_2013.dropna(), on="기업코드", how="inner")
# df_total_2014.describe()
df_merged_z_2014 = df_merged_b_2014[df_merged_b_2014["기업코드"].isin(list(set(df_merged_b_2014["기업코드"])&set(df_merged_b_2013["기업코드"])&set(df_merged_b_2012["기업코드"])))].reset_index(drop=True)
df_merged_z_2013 = df_merged_b_2013[df_merged_b_2013["기업코드"].isin(list(set(df_merged_b_2014["기업코드"])&set(df_merged_b_2013["기업코드"])&set(df_merged_b_2012["기업코드"])))].reset_index(drop=True)
df_merged_z_2012 = df_merged_b_2012[df_merged_b_2012["기업코드"].isin(list(set(df_merged_b_2014["기업코드"])&set(df_merged_b_2013["기업코드"])&set(df_merged_b_2012["기업코드"])))].reset_index(drop=True)
print(len(df_merged_z_2014))
print(len(df_merged_z_2013))
print(len(df_merged_z_2012))
df_merged_b_2014.columns
df_merged_c_2014 = pd.DataFrame(columns=df_merged_b_2014.columns)
df_merged_c_2014["기업코드"] = df_merged_z_2014["기업코드"]
# df_merged_c_2014['자산총계'] = ((df_merged_z_2014['자산총계'] + df_merged_z_2013['자산총계'])/2 -(df_merged_z_2013['자산총계'] + df_merged_z_2012['자산총계'])/2)\
#                                     / np.abs((df_merged_z_2013['자산총계'] + df_merged_z_2012['자산총계'])/2)
df_merged_c_2014['유보액_총자산'] = ((df_merged_z_2014['유보액_총자산'] + df_merged_z_2013['유보액_총자산'])/2 -(df_merged_z_2013['유보액_총자산'] + df_merged_z_2012['유보액_총자산'])/2)\
                                    / np.abs((df_merged_z_2013['유보액_총자산'] + df_merged_z_2012['유보액_총자산'])/2)
df_merged_c_2014['당기순이익_총자산'] = ((df_merged_z_2014['당기순이익_총자산'] + df_merged_z_2013['당기순이익_총자산'])/2 -(df_merged_z_2013['당기순이익_총자산'] + df_merged_z_2012['당기순이익_총자산'])/2)\
                                    / np.abs((df_merged_z_2013['당기순이익_총자산'] + df_merged_z_2012['당기순이익_총자산'])/2)
df_merged_c_2014['부채총계_총자산'] = ((df_merged_z_2014['부채총계_총자산'] + df_merged_z_2013['부채총계_총자산'])/2 -(df_merged_z_2013['부채총계_총자산'] + df_merged_z_2012['부채총계_총자산'])/2)\
                                    / np.abs((df_merged_z_2013['부채총계_총자산'] + df_merged_z_2012['부채총계_총자산'])/2)
df_merged_c_2014['운전자본_총자산'] = ((df_merged_z_2014['운전자본_총자산'] + df_merged_z_2013['운전자본_총자산'])/2 -(df_merged_z_2013['운전자본_총자산'] + df_merged_z_2012['운전자본_총자산'])/2)\
                                    / np.abs((df_merged_z_2013['운전자본_총자산'] + df_merged_z_2012['운전자본_총자산'])/2)
df_merged_c_2014['매출액_총자산'] = ((df_merged_z_2014['매출액_총자산'] + df_merged_z_2013['매출액_총자산'])/2 -(df_merged_z_2013['매출액_총자산'] + df_merged_z_2012['매출액_총자산'])/2)\
                                    / np.abs((df_merged_z_2013['매출액_총자산'] + df_merged_z_2012['매출액_총자산'])/2)
df_merged_c_2014['재고자산_총자산'] = ((df_merged_z_2014['재고자산_총자산'] + df_merged_z_2013['재고자산_총자산'])/2 -(df_merged_z_2013['재고자산_총자산'] + df_merged_z_2012['재고자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2013['재고자산_총자산'] + df_merged_z_2012['재고자산_총자산'])/2)
df_merged_c_2014['유동자산_총자산'] = ((df_merged_z_2014['유동자산_총자산'] + df_merged_z_2013['유동자산_총자산'])/2 -(df_merged_z_2013['유동자산_총자산'] + df_merged_z_2012['유동자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2013['유동자산_총자산'] + df_merged_z_2012['유동자산_총자산'])/2)
df_merged_c_2014['매출채권_총자산'] = ((df_merged_z_2014['매출채권_총자산'] + df_merged_z_2013['매출채권_총자산'])/2 -(df_merged_z_2013['매출채권_총자산'] + df_merged_z_2012['매출채권_총자산'])/2)\
                                    / np.abs((df_merged_z_2013['매출채권_총자산'] + df_merged_z_2012['매출채권_총자산'])/2)
df_merged_c_2014['ESG등급'] = (df_merged_z_2014['ESG등급'] + df_merged_z_2013['ESG등급'])/2 -  (df_merged_z_2013['ESG등급'] + df_merged_z_2012['ESG등급'])/2
df_merged_c_2014['지배구조'] = (df_merged_z_2014['지배구조'] + df_merged_z_2013['지배구조'])/2 - (df_merged_z_2013['지배구조'] + df_merged_z_2012['지배구조'])/2
df_merged_c_2014['사회'] = (df_merged_z_2014['사회'] + df_merged_z_2013['사회'])/2 - (df_merged_z_2013['사회'] + df_merged_z_2012['사회'])/2
df_merged_c_2014['환경'] = (df_merged_z_2014['환경'] + df_merged_z_2013['환경'])/2 - (df_merged_z_2013['환경'] +df_merged_z_2012['환경'])/2
df_total_2015 = pd.merge(df_rating_2015,df_merged_c_2014.dropna(), on="기업코드", how="inner")
df_total_2015.describe()
# df_kospi_2015 = df_total_2015[df_total_2015["기업코드"].isin(kospi_2015)]
import seaborn as sns
plt.figure(figsize=(20,5))
sns.boxplot(
    data=df_total_2015,
    x='평가등급',
    y='ESG등급',
    color='red')
df_merged_z_2015 = df_merged_b_2014[df_merged_b_2015["기업코드"].isin(list(set(df_merged_b_2014["기업코드"])&set(df_merged_b_2013["기업코드"])&set(df_merged_b_2015["기업코드"])))].reset_index(drop=True)
df_merged_z_2014 = df_merged_b_2013[df_merged_b_2014["기업코드"].isin(list(set(df_merged_b_2014["기업코드"])&set(df_merged_b_2013["기업코드"])&set(df_merged_b_2015["기업코드"])))].reset_index(drop=True)
df_merged_z_2013 = df_merged_b_2012[df_merged_b_2013["기업코드"].isin(list(set(df_merged_b_2014["기업코드"])&set(df_merged_b_2013["기업코드"])&set(df_merged_b_2015["기업코드"])))].reset_index(drop=True)
df_merged_c_2015 = pd.DataFrame(columns=df_merged_b_2014.columns)
df_merged_c_2015["기업코드"] = df_merged_z_2015["기업코드"]
# df_merged_c_2015['자산총계'] = ((df_merged_z_2015['자산총계'] + df_merged_z_2014['자산총계'])/2 -(df_merged_z_2014['자산총계'] + df_merged_z_2013['자산총계'])/2)\
#                                     / np.abs((df_merged_z_2014['자산총계'] + df_merged_z_2013['자산총계'])/2)
df_merged_c_2015['유보액_총자산'] = ((df_merged_z_2015['유보액_총자산'] + df_merged_z_2014['유보액_총자산'])/2 -(df_merged_z_2014['유보액_총자산'] + df_merged_z_2013['유보액_총자산'])/2)\
                                    / np.abs((df_merged_z_2014['유보액_총자산'] + df_merged_z_2013['유보액_총자산'])/2)
df_merged_c_2015['당기순이익_총자산'] = ((df_merged_z_2015['당기순이익_총자산'] + df_merged_z_2014['당기순이익_총자산'])/2 -(df_merged_z_2014['당기순이익_총자산'] + df_merged_z_2013['당기순이익_총자산'])/2)\
                                    / np.abs((df_merged_z_2014['당기순이익_총자산'] + df_merged_z_2013['당기순이익_총자산'])/2)
df_merged_c_2015['부채총계_총자산'] = ((df_merged_z_2015['부채총계_총자산'] + df_merged_z_2014['부채총계_총자산'])/2 -(df_merged_z_2014['부채총계_총자산'] + df_merged_z_2013['부채총계_총자산'])/2)\
                                    / np.abs((df_merged_z_2014['부채총계_총자산'] + df_merged_z_2013['부채총계_총자산'])/2)
df_merged_c_2015['운전자본_총자산'] = ((df_merged_z_2014['운전자본_총자산'] + df_merged_z_2013['운전자본_총자산'])/2 -(df_merged_z_2014['운전자본_총자산'] + df_merged_z_2013['운전자본_총자산'])/2)\
                                    / np.abs((df_merged_z_2014['운전자본_총자산'] + df_merged_z_2013['운전자본_총자산'])/2)
df_merged_c_2015['매출액_총자산'] = ((df_merged_z_2015['매출액_총자산'] + df_merged_z_2014['매출액_총자산'])/2 -(df_merged_z_2014['매출액_총자산'] + df_merged_z_2013['매출액_총자산'])/2)\
                                    / np.abs((df_merged_z_2014['매출액_총자산'] + df_merged_z_2013['매출액_총자산'])/2)
df_merged_c_2015['재고자산_총자산'] = ((df_merged_z_2015['재고자산_총자산'] + df_merged_z_2014['재고자산_총자산'])/2 -(df_merged_z_2014['재고자산_총자산'] + df_merged_z_2013['재고자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2014['재고자산_총자산'] + df_merged_z_2013['재고자산_총자산'])/2)
df_merged_c_2015['유동자산_총자산'] = ((df_merged_z_2015['유동자산_총자산'] + df_merged_z_2014['유동자산_총자산'])/2 -(df_merged_z_2014['유동자산_총자산'] + df_merged_z_2013['유동자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2014['유동자산_총자산'] + df_merged_z_2013['유동자산_총자산'])/2)
df_merged_c_2015['매출채권_총자산'] = ((df_merged_z_2015['매출채권_총자산'] + df_merged_z_2014['매출채권_총자산'])/2 -(df_merged_z_2014['매출채권_총자산'] + df_merged_z_2013['매출채권_총자산'])/2)\
                                    / np.abs((df_merged_z_2014['매출채권_총자산'] + df_merged_z_2013['매출채권_총자산'])/2)
df_merged_c_2015['ESG등급'] = (df_merged_z_2015['ESG등급'] + df_merged_z_2014['ESG등급'])/2 -  (df_merged_z_2014['ESG등급'] + df_merged_z_2013['ESG등급'])/2
df_merged_c_2015['지배구조'] = (df_merged_z_2015['지배구조'] + df_merged_z_2014['지배구조'])/2 - (df_merged_z_2014['지배구조'] + df_merged_z_2013['지배구조'])/2
df_merged_c_2015['사회'] = (df_merged_z_2015['사회'] + df_merged_z_2014['사회'])/2 - (df_merged_z_2014['사회'] + df_merged_z_2013['사회'])/2
df_merged_c_2015['환경'] = (df_merged_z_2015['환경'] + df_merged_z_2014['환경'])/2 - (df_merged_z_2014['환경'] +df_merged_z_2013['환경'])/2
df_total_2016 = pd.merge(df_rating_2016,df_merged_c_2015.dropna(), on="기업코드", how="inner")
df_total_2016.describe()
# df_kospi_2016 = df_total_2016[df_total_2016["기업코드"].isin(kospi_2016)]
import seaborn as sns
plt.figure(figsize=(20,5))
sns.boxplot(
    data=df_total_2016,
    x='평가등급',
    y='ESG등급',
    color='red')
df_merged_z_2016 = df_merged_b_2016[df_merged_b_2016["기업코드"].isin(list(set(df_merged_b_2014["기업코드"])&set(df_merged_b_2016["기업코드"])&set(df_merged_b_2015["기업코드"])))].reset_index(drop=True)
df_merged_z_2015 = df_merged_b_2015[df_merged_b_2015["기업코드"].isin(list(set(df_merged_b_2014["기업코드"])&set(df_merged_b_2016["기업코드"])&set(df_merged_b_2015["기업코드"])))].reset_index(drop=True)
df_merged_z_2014 = df_merged_b_2014[df_merged_b_2014["기업코드"].isin(list(set(df_merged_b_2014["기업코드"])&set(df_merged_b_2016["기업코드"])&set(df_merged_b_2015["기업코드"])))].reset_index(drop=True)
df_merged_c_2016 = pd.DataFrame(columns=df_merged_b_2014.columns)
df_merged_c_2016["기업코드"] = df_merged_z_2016["기업코드"]
# df_merged_c_2016['자산총계'] = ((df_merged_z_2016['자산총계'] + df_merged_z_2015['자산총계'])/2 -(df_merged_z_2015['자산총계'] + df_merged_z_2014['자산총계'])/2)\
#                                     / np.abs((df_merged_z_2015['자산총계'] + df_merged_z_2014['자산총계'])/2)
df_merged_c_2016['유보액_총자산'] = ((df_merged_z_2016['유보액_총자산'] + df_merged_z_2015['유보액_총자산'])/2 -(df_merged_z_2015['유보액_총자산'] + df_merged_z_2014['유보액_총자산'])/2)\
                                    / np.abs((df_merged_z_2015['유보액_총자산'] + df_merged_z_2014['유보액_총자산'])/2)
df_merged_c_2016['당기순이익_총자산'] = ((df_merged_z_2016['당기순이익_총자산'] + df_merged_z_2015['당기순이익_총자산'])/2 -(df_merged_z_2015['당기순이익_총자산'] + df_merged_z_2014['당기순이익_총자산'])/2)\
                                    / np.abs((df_merged_z_2015['당기순이익_총자산'] + df_merged_z_2014['당기순이익_총자산'])/2)
df_merged_c_2016['부채총계_총자산'] = ((df_merged_z_2016['부채총계_총자산'] + df_merged_z_2015['부채총계_총자산'])/2 -(df_merged_z_2015['부채총계_총자산'] + df_merged_z_2014['부채총계_총자산'])/2)\
                                    / np.abs((df_merged_z_2015['부채총계_총자산'] + df_merged_z_2014['부채총계_총자산'])/2)
df_merged_c_2016['운전자본_총자산'] = ((df_merged_z_2016['운전자본_총자산'] + df_merged_z_2015['운전자본_총자산'])/2 -(df_merged_z_2015['운전자본_총자산'] + df_merged_z_2014['운전자본_총자산'])/2)\
                                    / np.abs((df_merged_z_2015['운전자본_총자산'] + df_merged_z_2014['운전자본_총자산'])/2)
df_merged_c_2016['매출액_총자산'] = ((df_merged_z_2016['매출액_총자산'] + df_merged_z_2015['매출액_총자산'])/2 -(df_merged_z_2015['매출액_총자산'] + df_merged_z_2014['매출액_총자산'])/2)\
                                    / np.abs((df_merged_z_2015['매출액_총자산'] + df_merged_z_2014['매출액_총자산'])/2)
df_merged_c_2016['재고자산_총자산'] = ((df_merged_z_2016['재고자산_총자산'] + df_merged_z_2015['재고자산_총자산'])/2 -(df_merged_z_2015['재고자산_총자산'] + df_merged_z_2014['재고자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2015['재고자산_총자산'] + df_merged_z_2014['재고자산_총자산'])/2)
df_merged_c_2016['유동자산_총자산'] = ((df_merged_z_2016['유동자산_총자산'] + df_merged_z_2015['유동자산_총자산'])/2 -(df_merged_z_2015['유동자산_총자산'] + df_merged_z_2014['유동자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2015['유동자산_총자산'] + df_merged_z_2014['유동자산_총자산'])/2)
df_merged_c_2016['매출채권_총자산'] = ((df_merged_z_2016['매출채권_총자산'] + df_merged_z_2015['매출채권_총자산'])/2 -(df_merged_z_2015['매출채권_총자산'] + df_merged_z_2014['매출채권_총자산'])/2)\
                                    / np.abs((df_merged_z_2015['매출채권_총자산'] + df_merged_z_2014['매출채권_총자산'])/2)
df_merged_c_2016['ESG등급'] = (df_merged_z_2016['ESG등급'] + df_merged_z_2015['ESG등급'])/2 -  (df_merged_z_2015['ESG등급'] + df_merged_z_2014['ESG등급'])/2
df_merged_c_2016['지배구조'] = (df_merged_z_2016['지배구조'] + df_merged_z_2015['지배구조'])/2 - (df_merged_z_2015['지배구조'] + df_merged_z_2014['지배구조'])/2
df_merged_c_2016['사회'] = (df_merged_z_2016['사회'] + df_merged_z_2015['사회'])/2 - (df_merged_z_2015['사회'] + df_merged_z_2014['사회'])/2
df_merged_c_2016['환경'] = (df_merged_z_2016['환경'] + df_merged_z_2015['환경'])/2 - (df_merged_z_2015['환경'] +df_merged_z_2014['환경'])/2
df_total_2017 = pd.merge(df_rating_2017,df_merged_c_2016.dropna(), on="기업코드", how="inner")
df_total_2017.describe()
# df_kospi_2017 = df_total_2017[df_total_2017["기업코드"].isin(kospi_2017)]
df_merged_z_2017 = df_merged_b_2017[df_merged_b_2017["기업코드"].isin(list(set(df_merged_b_2017["기업코드"])&set(df_merged_b_2016["기업코드"])&set(df_merged_b_2015["기업코드"])))].reset_index(drop=True)
df_merged_z_2016 = df_merged_b_2016[df_merged_b_2016["기업코드"].isin(list(set(df_merged_b_2017["기업코드"])&set(df_merged_b_2016["기업코드"])&set(df_merged_b_2015["기업코드"])))].reset_index(drop=True)
df_merged_z_2015 = df_merged_b_2015[df_merged_b_2015["기업코드"].isin(list(set(df_merged_b_2017["기업코드"])&set(df_merged_b_2016["기업코드"])&set(df_merged_b_2015["기업코드"])))].reset_index(drop=True)
df_merged_c_2017 = pd.DataFrame(columns=df_merged_b_2014.columns)
df_merged_c_2017["기업코드"] = df_merged_z_2017["기업코드"]
# df_merged_c_2017['자산총계'] = ((df_merged_z_2017['자산총계'] + df_merged_z_2016['자산총계'])/2 -(df_merged_z_2016['자산총계'] + df_merged_z_2015['자산총계'])/2)\
#                                     / np.abs((df_merged_z_2016['자산총계'] + df_merged_z_2015['자산총계'])/2)
df_merged_c_2017['유보액_총자산'] = ((df_merged_z_2017['유보액_총자산'] + df_merged_z_2016['유보액_총자산'])/2 -(df_merged_z_2016['유보액_총자산'] + df_merged_z_2015['유보액_총자산'])/2)\
                                    / np.abs((df_merged_z_2016['유보액_총자산'] + df_merged_z_2015['유보액_총자산'])/2)
df_merged_c_2017['당기순이익_총자산'] = ((df_merged_z_2017['당기순이익_총자산'] + df_merged_z_2016['당기순이익_총자산'])/2 -(df_merged_z_2016['당기순이익_총자산'] + df_merged_z_2015['당기순이익_총자산'])/2)\
                                    / np.abs((df_merged_z_2016['당기순이익_총자산'] + df_merged_z_2015['당기순이익_총자산'])/2)
df_merged_c_2017['부채총계_총자산'] = ((df_merged_z_2017['부채총계_총자산'] + df_merged_z_2016['부채총계_총자산'])/2 -(df_merged_z_2016['부채총계_총자산'] + df_merged_z_2015['부채총계_총자산'])/2)\
                                    / np.abs((df_merged_z_2016['부채총계_총자산'] + df_merged_z_2015['부채총계_총자산'])/2)
df_merged_c_2017['운전자본_총자산'] = ((df_merged_z_2017['운전자본_총자산'] + df_merged_z_2016['운전자본_총자산'])/2 -(df_merged_z_2016['운전자본_총자산'] + df_merged_z_2015['운전자본_총자산'])/2)\
                                    / np.abs((df_merged_z_2016['운전자본_총자산'] + df_merged_z_2015['운전자본_총자산'])/2)
df_merged_c_2017['매출액_총자산'] = ((df_merged_z_2017['매출액_총자산'] + df_merged_z_2016['매출액_총자산'])/2 -(df_merged_z_2016['매출액_총자산'] + df_merged_z_2015['매출액_총자산'])/2)\
                                    / np.abs((df_merged_z_2016['매출액_총자산'] + df_merged_z_2015['매출액_총자산'])/2)
df_merged_c_2017['재고자산_총자산'] = ((df_merged_z_2017['재고자산_총자산'] + df_merged_z_2016['재고자산_총자산'])/2 -(df_merged_z_2016['재고자산_총자산'] + df_merged_z_2015['재고자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2016['재고자산_총자산'] + df_merged_z_2015['재고자산_총자산'])/2)
df_merged_c_2017['유동자산_총자산'] = ((df_merged_z_2017['유동자산_총자산'] + df_merged_z_2016['유동자산_총자산'])/2 -(df_merged_z_2016['유동자산_총자산'] + df_merged_z_2015['유동자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2016['유동자산_총자산'] + df_merged_z_2015['유동자산_총자산'])/2)
df_merged_c_2017['매출채권_총자산'] = ((df_merged_z_2017['매출채권_총자산'] + df_merged_z_2016['매출채권_총자산'])/2 -(df_merged_z_2016['매출채권_총자산'] + df_merged_z_2015['매출채권_총자산'])/2)\
                                    / np.abs((df_merged_z_2016['매출채권_총자산'] + df_merged_z_2015['매출채권_총자산'])/2)
df_merged_c_2017['ESG등급'] = (df_merged_z_2017['ESG등급'] + df_merged_z_2016['ESG등급'])/2 -  (df_merged_z_2016['ESG등급'] + df_merged_z_2015['ESG등급'])/2
df_merged_c_2017['지배구조'] = (df_merged_z_2017['지배구조'] + df_merged_z_2016['지배구조'])/2 - (df_merged_z_2016['지배구조'] + df_merged_z_2015['지배구조'])/2
df_merged_c_2017['사회'] = (df_merged_z_2017['사회'] + df_merged_z_2016['사회'])/2 - (df_merged_z_2016['사회'] + df_merged_z_2015['사회'])/2
df_merged_c_2017['환경'] = (df_merged_z_2017['환경'] + df_merged_z_2016['환경'])/2 - (df_merged_z_2016['환경'] +df_merged_z_2015['환경'])/2
df_total_2018 = pd.merge(df_rating_2018,df_merged_c_2017.dropna(), on="기업코드", how="inner")
df_total_2018.describe()
# df_kospi_2018 = df_total_2018[df_total_2018["기업코드"].isin(kospi_2018)]
df_merged_z_2018 = df_merged_b_2018[df_merged_b_2018["기업코드"].isin(list(set(df_merged_b_2017["기업코드"])&set(df_merged_b_2016["기업코드"])&set(df_merged_b_2018["기업코드"])))].reset_index(drop=True)
df_merged_z_2017 = df_merged_b_2017[df_merged_b_2017["기업코드"].isin(list(set(df_merged_b_2017["기업코드"])&set(df_merged_b_2016["기업코드"])&set(df_merged_b_2018["기업코드"])))].reset_index(drop=True)
df_merged_z_2016 = df_merged_b_2016[df_merged_b_2016["기업코드"].isin(list(set(df_merged_b_2017["기업코드"])&set(df_merged_b_2016["기업코드"])&set(df_merged_b_2018["기업코드"])))].reset_index(drop=True)
df_merged_c_2018 = pd.DataFrame(columns=df_merged_b_2014.columns)
df_merged_c_2018["기업코드"] = df_merged_z_2018["기업코드"]
# df_merged_c_2018['자산총계'] = ((df_merged_z_2018['자산총계'] + df_merged_z_2017['자산총계'])/2 -(df_merged_z_2017['자산총계'] + df_merged_z_2016['자산총계'])/2)\
#                                     / np.abs((df_merged_z_2017['자산총계'] + df_merged_z_2016['자산총계'])/2)
df_merged_c_2018['유보액_총자산'] = ((df_merged_z_2018['유보액_총자산'] + df_merged_z_2017['유보액_총자산'])/2 -(df_merged_z_2017['유보액_총자산'] + df_merged_z_2016['유보액_총자산'])/2)\
                                    / np.abs((df_merged_z_2017['유보액_총자산'] + df_merged_z_2016['유보액_총자산'])/2)
df_merged_c_2018['당기순이익_총자산'] = ((df_merged_z_2018['당기순이익_총자산'] + df_merged_z_2017['당기순이익_총자산'])/2 -(df_merged_z_2017['당기순이익_총자산'] + df_merged_z_2016['당기순이익_총자산'])/2)\
                                    / np.abs((df_merged_z_2017['당기순이익_총자산'] + df_merged_z_2016['당기순이익_총자산'])/2)
df_merged_c_2018['부채총계_총자산'] = ((df_merged_z_2018['부채총계_총자산'] + df_merged_z_2017['부채총계_총자산'])/2 -(df_merged_z_2017['부채총계_총자산'] + df_merged_z_2016['부채총계_총자산'])/2)\
                                    / np.abs((df_merged_z_2017['부채총계_총자산'] + df_merged_z_2016['부채총계_총자산'])/2)
df_merged_c_2018['운전자본_총자산'] = ((df_merged_z_2018['운전자본_총자산'] + df_merged_z_2017['운전자본_총자산'])/2 -(df_merged_z_2017['운전자본_총자산'] + df_merged_z_2016['운전자본_총자산'])/2)\
                                    / np.abs((df_merged_z_2017['운전자본_총자산'] + df_merged_z_2016['운전자본_총자산'])/2)
df_merged_c_2018['매출액_총자산'] = ((df_merged_z_2018['매출액_총자산'] + df_merged_z_2017['매출액_총자산'])/2 -(df_merged_z_2017['매출액_총자산'] + df_merged_z_2016['매출액_총자산'])/2)\
                                    / np.abs((df_merged_z_2017['매출액_총자산'] + df_merged_z_2016['매출액_총자산'])/2)
df_merged_c_2018['재고자산_총자산'] = ((df_merged_z_2018['재고자산_총자산'] + df_merged_z_2017['재고자산_총자산'])/2 -(df_merged_z_2017['재고자산_총자산'] + df_merged_z_2016['재고자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2017['재고자산_총자산'] + df_merged_z_2016['재고자산_총자산'])/2)
df_merged_c_2018['유동자산_총자산'] = ((df_merged_z_2018['유동자산_총자산'] + df_merged_z_2017['유동자산_총자산'])/2 -(df_merged_z_2017['유동자산_총자산'] + df_merged_z_2016['유동자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2017['유동자산_총자산'] + df_merged_z_2016['유동자산_총자산'])/2)
df_merged_c_2018['매출채권_총자산'] = ((df_merged_z_2018['매출채권_총자산'] + df_merged_z_2017['매출채권_총자산'])/2 -(df_merged_z_2017['매출채권_총자산'] + df_merged_z_2016['매출채권_총자산'])/2)\
                                    / np.abs((df_merged_z_2017['매출채권_총자산'] + df_merged_z_2016['매출채권_총자산'])/2)
df_merged_c_2018['ESG등급'] = (df_merged_z_2018['ESG등급'] + df_merged_z_2017['ESG등급'])/2 -  (df_merged_z_2017['ESG등급'] + df_merged_z_2016['ESG등급'])/2
df_merged_c_2018['지배구조'] = (df_merged_z_2018['지배구조'] + df_merged_z_2017['지배구조'])/2 - (df_merged_z_2017['지배구조'] + df_merged_z_2016['지배구조'])/2
df_merged_c_2018['사회'] = (df_merged_z_2018['사회'] + df_merged_z_2017['사회'])/2 - (df_merged_z_2017['사회'] + df_merged_z_2016['사회'])/2
df_merged_c_2018['환경'] = (df_merged_z_2018['환경'] + df_merged_z_2017['환경'])/2 - (df_merged_z_2017['환경'] +df_merged_z_2016['환경'])/2
df_total_2019 = pd.merge(df_rating_2019,df_merged_c_2018.dropna(), on="기업코드", how="inner")
# df_kospi_2019 = df_total_2019[df_total_2019["기업코드"].isin(kospi_2019)]
df_merged_z_2019 = df_merged_b_2019[df_merged_b_2019["기업코드"].isin(list(set(df_merged_b_2017["기업코드"])&set(df_merged_b_2019["기업코드"])&set(df_merged_b_2018["기업코드"])))].reset_index(drop=True)
df_merged_z_2018 = df_merged_b_2018[df_merged_b_2018["기업코드"].isin(list(set(df_merged_b_2017["기업코드"])&set(df_merged_b_2019["기업코드"])&set(df_merged_b_2018["기업코드"])))].reset_index(drop=True)
df_merged_z_2017 = df_merged_b_2017[df_merged_b_2017["기업코드"].isin(list(set(df_merged_b_2017["기업코드"])&set(df_merged_b_2019["기업코드"])&set(df_merged_b_2018["기업코드"])))].reset_index(drop=True)
df_merged_c_2019 = pd.DataFrame(columns=df_merged_b_2014.columns)
df_merged_c_2019["기업코드"] = df_merged_z_2019["기업코드"]
# df_merged_c_2019['자산총계'] = ((df_merged_z_2019['자산총계'] + df_merged_z_2018['자산총계'])/2 -(df_merged_z_2018['자산총계'] + df_merged_z_2017['자산총계'])/2)\
#                                     / np.abs((df_merged_z_2018['자산총계'] + df_merged_z_2017['자산총계'])/2)
df_merged_c_2019['유보액_총자산'] = ((df_merged_z_2019['유보액_총자산'] + df_merged_z_2018['유보액_총자산'])/2 -(df_merged_z_2018['유보액_총자산'] + df_merged_z_2017['유보액_총자산'])/2)\
                                    / np.abs((df_merged_z_2018['유보액_총자산'] + df_merged_z_2017['유보액_총자산'])/2)
df_merged_c_2019['당기순이익_총자산'] = ((df_merged_z_2019['당기순이익_총자산'] + df_merged_z_2018['당기순이익_총자산'])/2 -(df_merged_z_2018['당기순이익_총자산'] + df_merged_z_2017['당기순이익_총자산'])/2)\
                                    / np.abs((df_merged_z_2018['당기순이익_총자산'] + df_merged_z_2017['당기순이익_총자산'])/2)
df_merged_c_2019['부채총계_총자산'] = ((df_merged_z_2019['부채총계_총자산'] + df_merged_z_2018['부채총계_총자산'])/2 -(df_merged_z_2018['부채총계_총자산'] + df_merged_z_2017['부채총계_총자산'])/2)\
                                    / np.abs((df_merged_z_2018['부채총계_총자산'] + df_merged_z_2017['부채총계_총자산'])/2)
df_merged_c_2019['운전자본_총자산'] = ((df_merged_z_2019['운전자본_총자산'] + df_merged_z_2018['운전자본_총자산'])/2 -(df_merged_z_2018['운전자본_총자산'] + df_merged_z_2017['운전자본_총자산'])/2)\
                                    / np.abs((df_merged_z_2018['운전자본_총자산'] + df_merged_z_2017['운전자본_총자산'])/2)
df_merged_c_2019['매출액_총자산'] = ((df_merged_z_2019['매출액_총자산'] + df_merged_z_2018['매출액_총자산'])/2 -(df_merged_z_2018['매출액_총자산'] + df_merged_z_2017['매출액_총자산'])/2)\
                                    / np.abs((df_merged_z_2018['매출액_총자산'] + df_merged_z_2017['매출액_총자산'])/2)
df_merged_c_2019['재고자산_총자산'] = ((df_merged_z_2019['재고자산_총자산'] + df_merged_z_2018['재고자산_총자산'])/2 -(df_merged_z_2018['재고자산_총자산'] + df_merged_z_2017['재고자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2018['재고자산_총자산'] + df_merged_z_2017['재고자산_총자산'])/2)
df_merged_c_2019['유동자산_총자산'] = ((df_merged_z_2019['유동자산_총자산'] + df_merged_z_2018['유동자산_총자산'])/2 -(df_merged_z_2018['유동자산_총자산'] + df_merged_z_2017['유동자산_총자산'])/2)\
                                    / np.abs((df_merged_z_2018['유동자산_총자산'] + df_merged_z_2017['유동자산_총자산'])/2)
df_merged_c_2019['매출채권_총자산'] = ((df_merged_z_2019['매출채권_총자산'] + df_merged_z_2018['매출채권_총자산'])/2 -(df_merged_z_2018['매출채권_총자산'] + df_merged_z_2017['매출채권_총자산'])/2)\
                                    / np.abs((df_merged_z_2018['매출채권_총자산'] + df_merged_z_2017['매출채권_총자산'])/2)
df_merged_c_2019['ESG등급'] = (df_merged_z_2019['ESG등급'] + df_merged_z_2018['ESG등급'])/2 -  (df_merged_z_2018['ESG등급'] + df_merged_z_2017['ESG등급'])/2
df_merged_c_2019['지배구조'] = (df_merged_z_2019['지배구조'] + df_merged_z_2018['지배구조'])/2 - (df_merged_z_2018['지배구조'] + df_merged_z_2017['지배구조'])/2
df_merged_c_2019['사회'] = (df_merged_z_2019['사회'] + df_merged_z_2018['사회'])/2 - (df_merged_z_2018['사회'] + df_merged_z_2017['사회'])/2
df_merged_c_2019['환경'] = (df_merged_z_2019['환경'] + df_merged_z_2018['환경'])/2 - (df_merged_z_2018['환경'] +df_merged_z_2017['환경'])/2
df_rating_2020 = merge_code(df_credit_2020, df_esg_2019)
df_total_2020 = pd.merge(df_rating_2020,df_merged_c_2019.dropna(), on="기업코드", how="inner")
df_total_2020.describe()
# df_kospi_2020 = df_total_2020[df_total_2020["기업코드"].isin(kospi_2020)]
df_sum_2015_2020 = pd.concat([df_total_2015,df_total_2016,df_total_2017,df_total_2018,df_total_2019,df_total_2020]).replace(np.inf,np.nan).dropna()
df_sum_2015_2020 = df_sum_2015_2020.rename(columns={"유보액_총자산":"유보액_총자산"})[:]
plt.figure(figsize=(15,15))
sns.heatmap(data = df_sum_2015_2020.corr(), annot=True, 
fmt = '.2f', linewidths=.5, cmap='Blues')
# sns.pairplot(df_sum_2015_2020)
# plt.show()
df_x = df_sum_2015_2020.iloc[:,5:]
df_y = df_sum_2015_2020.iloc[:,1]
robust_scaler = RobustScaler()
rb = robust_scaler.fit(df_x)
output = robust_scaler.transform(df_x)
output = pd.DataFrame(output, columns=df_x.columns, index=list(df_x.index.values))
output["Y"] = df_y
from sklearn.linear_model import LinearRegression
model = sm.OLS.from_formula("Y ~유보액_총자산+매출채권_총자산+당기순이익_총자산+부채총계_총자산+운전자본_총자산+유동자산_총자산+매출액_총자산+재고자산_총자산+지배구조+사회+환경+0", data=output)
result = model.fit()
print(result.summary())
fig = plt.figure(figsize=(12,8))
fig = sm.graphics.plot_partregress_grid(result, fig=fig)
model = sm.Logit.from_formula("Y ~유보액_총자산+매출채권_총자산+당기순이익_총자산+부채총계_총자산+운전자본_총자산+유동자산_총자산+매출액_총자산+재고자산_총자산+지배구조+사회+환경+0", data=output)
result1 = model.fit()
print(result1.summary())
fig = plt.figure(figsize=(12,8))
fig = sm.graphics.plot_partregress_grid(result1, fig=fig)<jupyter_output><empty_output><jupyter_text>DecisionTreeClass   ESG통합 +E + S + G<jupyter_code>X_train, X_test, y_train, y_test = train_test_split(df_x, df_y,test_size=0.3)
rb = robust_scaler.fit(X_train)
X_train = robust_scaler.transform(X_train)
X_test = robust_scaler.transform(X_test)
from sklearn.feature_selection import SelectFromModel
from sklearn.ensemble import ExtraTreesClassifier
from sklearn.tree import DecisionTreeRegressor

model_sel = ExtraTreesClassifier(n_estimators=50).fit(X_train, y_train)
selector = SelectFromModel(model_sel, prefit=True, max_features=14330)
X_train_sel = selector.transform(X_train)
X_test_sel = selector.transform(X_test)




#random_state = np.random.seed(3)
#model = DecisionTreeClassifier(ccp_alpha=0.0, class_weight=None, criterion='entropy',
#                       max_depth=1000, max_features=None, max_leaf_nodes=100,
#                       min_impurity_decrease=0.0, min_impurity_split=None,
#                       min_samples_leaf=2, min_samples_split=2,
#                       min_weight_fraction_leaf=0.0, presort='deprecated',
#                       random_state=None , splitter='best')
model_sel.fit(X_train_sel, y_train)
# random_state = np.random.seed(3)
# model = DecisionTreeRegressor()
# model.fit(X_train, y_train)
y_pred = list(model.predict(X_test))
y_true = list(np.array(y_test))
print(model.predict(X_test).shape)
print(y_test.shape)
print(len(y_test[y_test==1].dropna()))
precision_score(y_true, y_pred, average='weighted')
confusion_matrix(y_true, y_pred, labels=[1,0])
from sklearn.metrics import classification_report, confusion_matrix

print(classification_report(y_true, y_pred))<jupyter_output><empty_output><jupyter_text>사회<jupyter_code>df_x2 = df_x.drop(columns =["ESG등급","지배구조","환경"])
X_train, X_test, y_train, y_test = train_test_split(df_x2, df_y,test_size=0.3)
model = DecisionTreeClassifier(ccp_alpha=0.0, class_weight=None, criterion='entropy',
                       max_depth=1000, max_features=None, max_leaf_nodes=None,
                       min_impurity_decrease=0.0, min_impurity_split=None,
                       min_samples_leaf=1, min_samples_split=2,
                       min_weight_fraction_leaf=0.0, presort='deprecated',
                       random_state=None , splitter='best')
model.fit(X_train, y_train)
print(len(y_test[y_test==1].dropna()))
y_pred = list(model.predict(X_test))
y_true = list(np.array(y_test))
precision_score(y_true, y_pred, average='weighted')
confusion_matrix(y_true, y_pred, labels=[1,0])
df_x2 = df_x.drop(columns =["ESG등급","지배구조","환경"])
X_train, X_test, y_train, y_test = train_test_split(df_x2, df_y,test_size=0.3)
model = DecisionTreeClassifier()
model.fit(X_train, y_train)
print(len(y_test[y_test==1].dropna()))
y_pred = list(model.predict(X_test))
y_true = list(np.array(y_test))
from sklearn.metrics import classification_report, confusion_matrix

print(classification_report(y_true, y_pred))<jupyter_output><empty_output><jupyter_text>ESG 전체 제거<jupyter_code>df_x5 = df_x.drop(columns =["ESG등급","지배구조","환경","사회"])
X_train, X_test, y_train, y_test = train_test_split(df_x5, df_y,test_size=0.3)
model = DecisionTreeClassifier(ccp_alpha=0.0, class_weight=None, criterion='entropy',
                       max_depth=1000, max_features=None, max_leaf_nodes=None,
                       min_impurity_decrease=0.0, min_impurity_split=None,
                       min_samples_leaf=1, min_samples_split=2,
                       min_weight_fraction_leaf=0.0, presort='deprecated',
                       random_state=None , splitter='best')
model.fit(X_train, y_train)
y_pred = list(model.predict(X_test))
y_true = list(np.array(y_test))
print(len(y_test[y_test==1].dropna()))
precision_score(y_true, y_pred, average='weighted')
confusion_matrix(y_true, y_pred, labels=[1,0])
from sklearn.metrics import classification_report, confusion_matrix

print(classification_report(y_true, y_pred))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1739963443370257, 'fraction_numerical': 0.12686912286110683, 'mean_word_length': 6.301817012381412, 'pattern_counts': {'":': 21, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 63, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '399281', 'license': 'MIT', 'n_tokens_mistral': 29843, 'n_tokens_neox': 27941, 'n_words': 1530, 'path': 'linear_regression_project/regression_project_0627.ipynb', 'repo_name': 'yeonghwanchoi/TIL', 'size': '45409'}