In [1]:

    import pandas as pd
    import os
    import numpy as np
    from zipfile import BadZipfile

    os.chdir(r'../Data/Data_website/Scrapping')

    ##Word-Category mapping
    wordlistMap = pd.read_excel(r'../Glossaries/Dictionaries_final.xlsx', sheet_name='full lists')
    wordlistMap['id'] = wordlistMap.index+1
    wordlistMap = wordlistMap.melt(id_vars='id')
    wordlistMap = wordlistMap.rename(columns={'variable':'var_name','value':'word'})
    wordlistMap['var_name'] = wordlistMap['var_name'] + wordlistMap['id'].astype(str)
    wordlistMap = wordlistMap.drop(columns=['id'])
    wordlistMap = wordlistMap.dropna(axis=0, subset=['word']).reset_index(drop=True)

    ##ESG Category - Dictionary mapping
    dictmapESG = pd.read_excel(r'../Glossaries/Dictionaries_final.xlsx', sheet_name='esg dictionaries')
    dictmapESG = dictmapESG[['dict_esg1','dict_e1','dict_s1','dict_g1', 'dict_esg2','dict_e2','dict_s2','dict_g2']]
    dictmapESG = dictmapESG.melt()
    dictmapESG = dictmapESG.rename(columns={'variable':'dict','value':'word'})
    dictmapESG = pd.merge(dictmapESG, wordlistMap, how='inner', on='word')
    dictmapESG.drop(columns='word', inplace=True)
    dictmapESG = dictmapESG[~dictmapESG.var_name.str.contains('non_esg')]
    dictmapESG = dictmapESG.sort_values(by=['dict','var_name'])

    ##Non-ESG Category - Dictionary mapping
    dictmapNonESG = pd.read_excel(r'../Glossaries/Dictionaries_final.xlsx', sheet_name='individual lists')
    dictmapNonESG.drop(columns=['esg_lower','esg_upper'], inplace=True)
    dictmapNonESG = dictmapNonESG.melt()
    dictmapNonESG['variable'] = dictmapNonESG['variable'].str.removesuffix('_lower')
    dictmapNonESG['variable'] = dictmapNonESG['variable'].str.removesuffix('_upper')
    dictmapNonESG = dictmapNonESG.rename(columns={'variable':'dict','value':'word'})
    dictmapNonESG = dictmapNonESG.dropna(axis=0, subset=['word']).reset_index(drop=True)
    dictmapNonESG = pd.merge(dictmapNonESG, wordlistMap, how='inner', on='word')
    dictmapNonESG.drop(columns='word', inplace=True)
    dictmapNonESG = dictmapNonESG[dictmapNonESG.var_name.str.contains('non_esg')]
    dictmapNonESG = dictmapNonESG.sort_values(by=['dict','var_name'])

    ##Function for constructing the measures
    def measureConstruction(df):
        try:
            df = df.dropna(subset=['word_count']).reset_index(drop=True)
        except KeyError:
            return None
        splits = df['esg_lower'].str.split(',', expand=True)
        splits.columns = splits.columns+1
        df = pd.concat([df,splits.add_prefix('esg_lower')], axis=1)
        splits = df['esg_upper'].str.split(',', expand=True)
        splits.columns = splits.columns+1
        df = pd.concat([df,splits.add_prefix('esg_upper')], axis=1)
        splits = df['non_esg_lower'].str.split(',', expand=True)
        splits.columns = splits.columns+1
        df = pd.concat([df,splits.add_prefix('non_esg_lower')], axis=1)
        splits = df['non_esg_upper'].str.split(',', expand=True)
        splits.columns = splits.columns+1
        df = pd.concat([df,splits.add_prefix('non_esg_upper')], axis=1)
        df.drop(columns=['esg_lower','esg_upper','non_esg_lower','non_esg_upper'], inplace=True)

        for i in dictmapESG.dict.unique():
            df[i]=0
            for j in dictmapESG[dictmapESG.dict==i].var_name.unique():
                df[i] = df[i] + df[j].astype(int)

        for i in dictmapNonESG.dict.unique():
            df[i]=0
            for j in dictmapNonESG[dictmapNonESG.dict==i].var_name.unique():
                df[i] = df[i] + df[j].astype(int)

        df = df[['fm_id', 'url_id', 'url_datetime', 'word_count', 'dict_esg1','dict_e1','dict_s1','dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'complex', 'constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal']]
        return df

    ##Measure Construction
    #PE Firms
    for file in os.listdir('./ESGcounts/PE Firms'):
        print(file)
        try:
            df = pd.read_pickle('./ESGcounts/PE Firms/%s' %file, compression='zip')
        except BadZipfile:
            os.remove('./ESGcounts/PE Firms/%s' %file)
            continue
        df = measureConstruction(df)
        if df is None:
            os.remove('./ESGcounts/PE Firms/%s' %file)
            continue
        else:
            df_source = pd.read_pickle('./snapshotURLlevelList/PE Firms/%s' %file, compression='zip')
            df_source = pd.merge(df_source, df, how='outer', on=['fm_id','url_id','url_datetime'])
            df_source.rename(columns={'complex':'Complex', 'constrain':'Constrain'}, inplace=True)
            df_source.drop(columns=['urlkey', 'url'], inplace=True)

            df_source['word_count'] = pd.to_numeric(df_source['word_count'])
            df_source = df_source.groupby(['fm_id', 'snapshot_year', 'snapshot_date', 'snapshot_datetime'])[['length', 'word_count', 'dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal']].sum()
            df_source = df_source.apply(lambda x: x/df_source.word_count*10000 if x.name in ['dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal'] else x)
            for colName in ['dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2']:
                df_source['%s_ihs' %colName] = np.arcsinh(df_source[['%s' %colName]])
            df_source = df_source.apply(lambda x: np.log(1+x) if x.name in ['dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal'] else x)
            df_source['word_count'] = df_source['word_count']/10000
            df_source['logWordCount'] = np.log(1+df_source['word_count'])
            df_source['logLength'] = np.log(df_source['length']/1000000)
            df_source.to_pickle('./ESGmetrics/PE Firms/%s' %file, compression='zip')

    #HF Firms
    for file in os.listdir('./ESGcounts/HF Firms'):
        print(file)
        try:
            df = pd.read_pickle('./ESGcounts/HF Firms/%s' %file, compression='zip')
        except BadZipfile:
            os.remove('./ESGcounts/HF Firms/%s' %file)
            continue
        df = measureConstruction(df)
        if df is None:
            os.remove('./ESGcounts/HF Firms/%s' %file)
            continue
        else:
            df_source = pd.read_pickle('./snapshotURLlevelList/HF Firms/%s' %file, compression='zip')
            df_source = pd.merge(df_source, df, how='outer', on=['fm_id','url_id','url_datetime'])
            df_source.rename(columns={'complex':'Complex', 'constrain':'Constrain'}, inplace=True)
            df_source.drop(columns=['urlkey', 'url'], inplace=True)

            df_source['word_count'] = pd.to_numeric(df_source['word_count'])
            df_source = df_source.groupby(['fm_id', 'snapshot_year', 'snapshot_date', 'snapshot_datetime'])[['length', 'word_count', 'dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal']].sum()
            df_source = df_source.apply(lambda x: x/df_source.word_count*10000 if x.name in ['dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal'] else x)
            for colName in ['dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2']:
                df_source['%s_ihs' %colName] = np.arcsinh(df_source[['%s' %colName]])
            df_source = df_source.apply(lambda x: np.log(1+x) if x.name in ['dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal'] else x)
            df_source['word_count'] = df_source['word_count']/10000
            df_source['logWordCount'] = np.log(1+df_source['word_count'])
            df_source['logLength'] = np.log(df_source['length']/1000000)
            df_source.to_pickle('./ESGmetrics/HF Firms/%s' %file, compression='zip')

    111.pkl
    436.pkl
    H7.pkl

In [ ]: