<jupyter_start><jupyter_text>Identifying CSR board committeesIn this notebook, I am preprocessing the committee data from Refinitiv Eikon and identify which companies have a CSR related committee and which do not. Refinitiv has two data sources for committee information. First, they have a separate data field which lists CSR related committees on the board level AND the executive level. Because I am only interested in board level committees, I cannot use this list. Second, the director data from Refinitiv Eikon also contains information on the type of board committees a company has and which director is a part of those committees (albeit this dataset is missing the time period of committee membership, which therefore still needs to be manually gathered from DEF 14As). However, this director data only contains CSR related committees if their sole purpose is CSR. In many cases, companies have nominating and/or governance committees which also oversee sustainability and CSR at companies. Therefore, I cannot use this list alone either because there are many relevant committees missing from this dataset.Therefore, in this notebook, I am using the information from the two sources as an indication for the manual review I will be doing of the DEF 14As for the missing biographies and the missing committee information. The final excel file which I write in this notebook contains all relevant companies and directors with the additional information on potentially relevant CSR board committees. This list was then completed by manually researching missing biographies and committee information from DEF 14A filings.<jupyter_code># connecting to Google Drive to access files
from google.colab import drive
drive.mount('/content/drive')

import numpy as np
import pandas as pd
from glob import glob
import os
import tqdm.notebook as tqdm
import re

# change settings to display full width of column content
#pd.set_option('display.max_colwidth', None)
#pd.set_option('display.max_columns', None)<jupyter_output><empty_output><jupyter_text>Read in data<jupyter_code># read in all committee data which was previously preprocessed
committee_df = pd.read_csv('/content/drive/My Drive/director-csr/all_committees.csv')
committee_df.drop(columns=['Unnamed: 0'], inplace=True)
committee_df.head()

# how many entries are in this dataframe
committee_df.shape

# read in the csr sustainability committee data field by Reuters
csr_df = pd.read_excel('/content/drive/My Drive/director-csr/committees/CSR_scores_committee.xlsx')
csr_df.head()<jupyter_output><empty_output><jupyter_text>Check whether these committees include csr committees or similiar<jupyter_code># remove brackets from committee column values and
# turn the strings in the committee column into lists
committee_df['committee'] = committee_df['committee'].apply(lambda x: x.strip('[]').replace('\'', '').replace(' ', '').split(','))
committee_df['committee']

# check what types of committees there are
unique_coms = []

for row in committee_df['committee']:
    for com_type in row:
        if com_type not in unique_coms:
            unique_coms.append(com_type)
        else:
            pass

unique_coms<jupyter_output><empty_output><jupyter_text>Out of these committees, the ones that are of interest to me are: environment, csr responsibility, social, and maybe human resources (although this is likely similar to the nominating committee). I will now review the csr sustainability committee data field by Reuters.  Preprocess the csr sustainability committee data<jupyter_code># check how many companies are included
print(csr_df.shape)
# check how many variables are included per company
print((csr_df.shape[1]-2)/csr_df.shape[0])

# create a list of all ISINs that will be inserted as a row in the csr_df
all_isins_list = [item for isin in csr_df['ISIN Code'] for item in [isin]*8]

# drop the isin column
csr_df.drop(columns=['ISIN Code'], inplace=True)
# remove all empty rows
csr_df.dropna(subset=['Name'], inplace=True)

# turn the Name column which contains the years into the index
csr_df['Name'] = csr_df['Name'].astype(int)
csr_df.set_index('Name', inplace=True)

# add all ISINs to the table
csr_df.loc[0] = all_isins_list
csr_df

# drop any unnecessary columns from csr_df
cols = [col for col in csr_df.columns if any(['sustainability' in col.lower()])]
# create the new dataframe
csr_committee_df = csr_df[cols]

# change the column headers
changed_cols = [x.lower().split('-')[0].strip() for x in csr_committee_df.columns]
csr_committee_df.rename(columns=dict(zip(csr_committee_df.columns, changed_cols)), inplace=True)

# drop the year 2016 from the dataframe
csr_committee_df.drop([2016], inplace=True)
csr_committee_df

# remove any excess whitespace from values
csr_committee_df = csr_committee_df.applymap(lambda x: x.strip() if not pd.isna(x) else x)
csr_committee_df

# get the duplicate column names
duplicate_cols = list(set([x for x in list(csr_committee_df.columns) if list(csr_committee_df.columns).count(x) > 1]))
print(duplicate_cols)
# remove these duplicate columns from the dataframe
dupes_df = csr_committee_df[duplicate_cols]
csr_committee_df.drop(columns=duplicate_cols, inplace=True)

# split the dupes_df into two different dfs so that I can rename the cols
new_df = dupes_df.iloc[:, ::2]
remain_dupes_df = dupes_df.iloc[:, 1::2]
# rename the columns
new_col_names = [x+'_diff isin' for x in new_df.columns]
new_df.rename(columns=dict(zip(new_df.columns, new_col_names)), inplace=True)

# add those two dataframes back to the overall dataframe
csr_committee_df = csr_committee_df.join([new_df, remain_dupes_df])
csr_committee_df

# drop any companies that have N for all years
csr_committee_no_df = csr_committee_df.copy()

for col in csr_committee_no_df.columns:
    if csr_committee_no_df[col][:-1].eq('Y').any():
        csr_committee_no_df.drop(columns=[col], inplace=True)

csr_committee_yes_df = csr_committee_df.drop(columns=csr_committee_no_df.columns)
csr_committee_yes_df

# transpose the dataframe
csr_committee_yes_df = csr_committee_yes_df.T
# rename the isin column from 0 to isin
csr_committee_yes_df.rename(columns={0: 'isin'}, inplace=True)
# turn everything in dataframe into lower case
csr_committee_yes_df = csr_committee_yes_df.applymap(lambda x: x.lower() if not pd.isna(x) else x)

csr_committee_yes_df<jupyter_output><empty_output><jupyter_text>Get relevant persons from committee_df<jupyter_code># show the unique committees included in committee_df
print(unique_coms)
# select only the relevant ones
rel_comms = ['environment', 'csrresponsibility', 'social']
rel_comms
# get the relevant person who sits on a relevant committee
rel_person = []

for index, row in committee_df.iterrows():
    for com_type in row['committee']:
        if com_type in rel_comms:
            rel_person.append(index)
        else:
            pass

print(len(rel_person), 'people with relevant committee memberships')

# put all relevant people in a dataframe
all_rel_persons_df = committee_df.iloc[rel_person, :]
all_rel_persons_df

# write this dataframe to an excel file
all_rel_persons_df.to_excel('/content/drive/My Drive/director-csr/committees/people_rel_comms.xlsx',
                            sheet_name='rel_people')

# now I also need to add a column to the csr_committee_yes_df if there is definitely
# a board committee based on the info from all_rel_persons_df
comps_with_comm = all_rel_persons_df['isin'].unique()
csr_committee_yes_df['board_committee'] = csr_committee_yes_df['isin'].apply(lambda x: 'Yes' if x in comps_with_comm else 'No')
csr_committee_yes_df<jupyter_output><empty_output><jupyter_text>The categorizing whether a company has a board committee related to CSR is not perfect because it relies on director information, therefore, directors sitting on multiple boards will cause false positives. However, this will be dealt with during manual review.<jupyter_code># write this dataframe to an excel file
csr_committee_yes_df.to_excel('/content/drive/My Drive/director-csr/committees/comps_rel_comms.xlsx',
                                sheet_name='rel_comps')<jupyter_output><empty_output><jupyter_text>Add committee<jupyter_code># read in the biography director file
dir_bio_df = pd.read_excel('/content/drive/My Drive/director-csr/director_bios_all.xlsx')
dir_bio_df.drop(columns=['Unnamed: 0'], inplace=True)
# rename the isin_x column to just isin to prepare for merging
dir_bio_df.rename(columns={'isin_x': 'isin'}, inplace=True)
dir_bio_df.head()

# shape of the dataframe
dir_bio_df.shape

# merge both dataframes
dir_bio_comm_df = pd.merge(csr_committee_yes_df, dir_bio_df, how='right', on='isin')
dir_bio_comm_df.head()

# this function is taken from biography_matching.ipynb
def clean_names(df, bio=True):
    '''
    Function to clean up the director names so that they can be matched
    Args: df = dataframe; containing director names
          bio = bool; True if the biographies dataframe is added, False otherwise 
    Returns: df = dataframe
    '''

    # change the strings to lower case
    df.iloc[:,0] = df.iloc[:,0].apply(lambda x: x.lower())

    # check if the names contain anything in parentheses and if so remove them and their content
    df.iloc[:,0] = df.iloc[:,0].apply(lambda x: re.sub(r'\([^()]*\)', '', x))

    # check if the names contain a title like ms. and mr. and if so remove them
    df.iloc[:,0] = df.iloc[:,0].apply(lambda x: re.sub(r'^\w{2,3}\. ?', '', x))

    # do two different things with the commas for the different dataframes
    if bio:
        # move the last name in the front of the comma to the back of the string and remove the comma
        df.iloc[:,0] = df.iloc[:,0].apply(lambda x: ' '.join([x.split(',')[1], x.split(',')[0]]))
    else:
        # create a new column that contains all the words after a comma at the end
        df['qualification'] = df.iloc[:,0].apply(lambda x: x.split(',')[-1] if len(x.split(',')) > 1 else None)
        df.iloc[:,0] = df.iloc[:,0].apply(lambda x: x.split(',')[0])

    # remove any initials or titles because they might be distracting when matching names
    df.iloc[:,0] = df.iloc[:,0].apply(lambda x: ' '.join([name if '.' not in name else '' for name in x.split()]))

    # remove 'the' substring from names
    df.iloc[:,0] = df.iloc[:,0].apply(lambda x: re.sub(r'^the\s', '', x))

    # ensure that all white space is stripped
    df.iloc[:,0] = df.iloc[:,0].apply(lambda x: re.sub(' +', ' ', x).strip())

    return df
    
# clean the names in the all_rel_persons_df
all_rel_persons_df = clean_names(all_rel_persons_df, bio=False)
all_rel_persons_df.head()

# manual sanity check
for name in dir_bio_comm_df.sort_values(by='name', ascending=True)['name']:
    if 'fuller' in name:
        print(name)<jupyter_output><NAME>
<NAME>er<jupyter_text>During my manual review of DEF 14A reports for biographies and committee memberships, I noticed several inconsistencies in the name files and therefore, I will be doing some name replacements<jupyter_code># merge this cleaned dataframe with the dir_bio_df
dir_bio_comm_all = pd.merge(all_rel_persons_df[['name','committee']], dir_bio_comm_df, how='right', on='name')
dir_bio_comm_all.shape

# example of the merged dataframes
dir_bio_comm_df[dir_bio_comm_df['name'] == '<NAME>']

# sort this dataframe by company name and not by director name
dir_bio_comm_all.sort_values(by='comp_name_x', ascending=True, inplace=True)
# rearrange the order of the dataframe
cols = list(dir_bio_comm_all.columns)
new_cols = ['name', 'org_name_x', 'org_name_y', 'board_committee', 'committee', 'comp_name_x', 'isin', 'biographies']
remove_cols = [col for col in cols if col not in new_cols]
new_cols.extend(remove_cols)
dir_bio_comm_all = dir_bio_comm_all[new_cols]

dir_bio_comm_all.head()

# what is the size of this final dataframe
dir_bio_comm_all.shape

# write this dataframe to an excel file
dir_bio_comm_all.to_excel('/content/drive/My Drive/director-csr/dir_bio_comm_all.xlsx',
                                sheet_name='dir_bio_comm')<jupyter_output><empty_output><jupyter_text>Please note the followingI had already started the manual review of the `dir_bio_comm_all` excel file when I tried some additional techniques to match directors to biographies in the `biography_matching` notebook. This additional code has now resulted in a different review list that was generated at the end of this notebook and saved as `dir_bio_comm_all.xlsx`. The manual review has been completed with the previous version of this excel list. However, it is not possible for me under the current time constraints, to revert those changes in the biography matching notebook to again generate the original excel list. Therefore, we will all have to live with this slight discrepancy in the lists generated for manual review.**This will not change anything in relation to the overall results or calculations of this thesis.** My manually reviewed list will be compared to this newly generated excel file to ensure that no director was missed. So this can just be considered an FYI and a sorry for the inconsistency.<jupyter_code><jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08892272554244385, 'fraction_numerical': 0.004187285877426722, 'mean_word_length': 5.141187470780738, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '43019', 'license': 'FTL', 'n_tokens_mistral': 4176, 'n_tokens_neox': 3999, 'n_words': 1421, 'path': 'csr-committee/csr_committees.ipynb', 'repo_name': 'julianikulski/director-experience-sustainability', 'size': '13157'}