<jupyter_start><jupyter_text>Where is the University of California Getting it's Insurance? This notebook analyses UC insurance contracts obtained by a freedom of information act inquiry.<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
# Map child companies to parents
family = dict()

family['safety national'] = 'tokio marine'

family['philadelphia'] = 'tokio marine'
rawdata_filename = 'CPRA #20-3063 9.23.2020.xlsx'
# Load all sheets
rawdata_excel = pd.read_excel(rawdata_filename, sheet_name=None)
# Get sheet names
sheet_names = list(rawdata_excel.keys())
sheet_names
#Define output folders
png_folder = 'png_figures/'
pdf_folder = 'pdf_figures/'<jupyter_output><empty_output><jupyter_text>Let's look at the UC Regents Policies first<jupyter_code>#Clean up the header and label the renewal date column
regents_policies = pd.read_excel(rawdata_filename, sheet_name=sheet_names[0], header=2)

#There are trailing spaces in some of the column names in the raw datafile

#Rename the columns
regents_policies.rename(columns={'Policy ': 'Policy',
                                 'Carrier ': 'Carrier',
                                 'Premium ': 'Premium',
                                 'Unnamed: 3': "Renewal"}, inplace=True)

regents_policies
def prep_for_merge(df, column):
    
    #remove trailing spaces
    df[column] = df[column].str.strip()
    
    #lowercase
    df[column] = df[column].str.lower()
    
    return df
regents_policies = prep_for_merge(regents_policies, 'Carrier')
#Clean up data types
regents_policies = regents_policies.convert_dtypes()
regents_policies
#So which companies are we paying and how much?<jupyter_output><empty_output><jupyter_text>"Projects with a projected construction value of $25 million and over (total for all phases) are to be insured under the University Controlled Insurance Program, or “UCIP.”  The UCIP is a single insurance program that insures the University of California, Enrolled Contractors, Enrolled Subcontractors, and other designated parties (“Contractors”) for Work performed at the Project Site."https://www.ucop.edu/construction-services/programs-and-processes/university-controlled-insurance-program/ucip.html<jupyter_code>## What about the other sheets?
#Clean up the header and label the renewal date column
fiatlux_policies = pd.read_excel(rawdata_filename, sheet_name=sheet_names[1], header=2)

#Rename the columns
fiatlux_policies.rename(columns={'Policy ': 'Policy',
                                 'Carrier ': 'Carrier',
                                 'Premium ': 'Premium',
                                 'Unnamed: 3': "Renewal"}, inplace=True)

#Remove leading or trailing spaces and lowercase Carrier column
fiatlux_policies = prep_for_merge(fiatlux_policies, 'Carrier')

fiatlux_policies
# Can these companies be grouped more coursely? - yes once the total dataframe is generated. This gives the full list of companies
uc_construction = pd.read_excel(rawdata_filename, sheet_name=sheet_names[2], header=2)

#Rename the columns
uc_construction.rename(columns={'COVERAGE TYPE': 'Policy',
                                 'INSURANCE COMPANY': 'Carrier',
                                 'PREMIUM': 'Premium'}, inplace=True)

#Remove leading or trailing spaces and lowercase Carrier column
uc_construction = prep_for_merge(uc_construction, 'Carrier')

uc_construction
#How much are we paying Liberty Mutual in total?
combined = pd.concat([regents_policies, fiatlux_policies, uc_construction])

#Replace 'swissre' with 'swiss re'
combined.Carrier = combined.Carrier.replace('swissre', 'swiss re')

totals = combined.groupby(by='Carrier').sum().sort_values('Premium', ascending=False).reset_index()
totals
##Save the totals - this may alter exisiting graphs
#totals.to_pickle('C:/Users/<NAME>/Documents/PythonScripts_New/Climate Crisis/uc_insurers_2020/uc_insurance_totals.pkl')<jupyter_output><empty_output><jupyter_text>UC paid Liberty Mutual over 8,000,000 dollars in 2020."Liberty Mutual is a top global insurer of coal, oil, and gas. It also invests more than \\$8.9 billion in fossil fuel companies and utilities, including $1.5 billion in thermal coal. Yet, while fueling the climate crisis, Liberty Mutual is withdrawing coverage from and jacking up the costs of insurance for longtime customers in areas at risk of climate change impacts, like wildfire-affected counties in California."https://www.ran.org/press-releases/insure-our-future-campaign-targets-liberty-mutual/  How do these companies score?<jupyter_code>scoring_filename = 'C:/Users/<NAME>/Documents/PythonScripts_New/Climate Crisis/uc_insurers_2020/Unfriend Coal/Unfriend Coal_Insure Our Future_Scoring sheet - Scorecard 2019.xlsx'

# Load all sheets
scoring_excel = pd.read_excel(scoring_filename, sheet_name=None)

# Get sheet names
scoring_sheet_names = list(scoring_excel.keys())
scoring_sheet_names

# Get overall scores
overall = pd.read_excel(scoring_filename, sheet_name=scoring_sheet_names[2], header=None)

#For some reason the header is at the bottom.
overall.columns = list(overall.tail(1).reset_index(drop=True).loc[0,:])
overall.drop(overall.tail(1).index, inplace=True)

names = list(overall.columns)
names[0] = 'Carrier'
overall.columns = names

# Add Sum
overall['Sum'] = overall.convert_dtypes().sum(axis=1)

#Remove leading or trailing spaces and lowercase Carrier column
overall = prep_for_merge(overall, 'Carrier')

overall
uc_scores = pd.merge(totals, overall, on='Carrier', how='outer')

uc_scores
# Look for nested matchings
# Make a mapping dictionary 'key' = long name, 'value' = shorter name
nested = dict()

string_list = uc_scores.Carrier.sort_values()
substrings  = uc_scores.Carrier.sort_values()

for x in string_list:
    for y in substrings:
        if y != x:
            if y in x:
                nested.update({x:y})

nested
# Map longer names onto shorter ones.
# This makes assumptions about the relationship of abbreviated names.
tidy = uc_scores.copy()
tidy.Carrier = tidy.Carrier.replace(nested)

tidy = tidy.convert_dtypes()

# Aggregate but treat NAs and zeros differently.
#By setting min_count=1, numeric values will overwrite NaN values.
tidy = tidy.groupby('Carrier').sum(min_count=1).sort_values('Premium', ascending=False).reset_index()
tidy
tidy.Carrier = tidy.Carrier.replace(family)

# Aggregate but treat NAs and zeros differently.
#By setting min_count=1, numeric values will overwrite NaN values.
tidy = tidy.groupby('Carrier').sum(min_count=1).sort_values('Premium', ascending=False).reset_index()
tidy.head(10)
## Tidy names early
def tidy_names(df):
    
    #remove trailing spaces
    df.Carrier = df.Carrier.str.strip()
    
    #lowercase
    df.Carrier = df.Carrier.str.lower()
    
    df.Carrier = df.Carrier.replace(nested)
    df.Carrier = df.Carrier.replace(family)
    
    return df
tidy_regents_policies = tidy_names(regents_policies)

#Replace 'swissre' with 'swiss re'
tidy_regents_policies.Carrier = tidy_regents_policies.Carrier.replace('swissre', 'swiss re')

tidy_fiatlux_policies = tidy_names(fiatlux_policies)
tidy_uc_construction  = tidy_names(uc_construction)
tidy_totals           = tidy_names(totals)
def bar_plot(df, head, color, ax=None):
    
    # Restore Title case
    df.Carrier = df.Carrier.str.title()
    
    df = df.sort_values('Premium', ascending=False).head(head)

    df.plot.bar('Carrier', 'Premium', color=color, edgecolor='k', ax=ax, legend=None);
    
    if ax is None:
        ax=plt.gca()
        
    ax.set_xlabel(None)
    ax.set_ylabel('United States Dollars');

    #Give dollars with zeros and commas
    ax.get_yaxis().set_major_formatter(
        ticker.FuncFormatter(lambda y, p: format(int(y), ',')))
    
    #Turn off the top and right hand axis lines.
    ax.spines['right'].set_visible(False)
    ax.spines['top'].set_visible(False)

bar_plot(tidy_regents_policies, 5, color='#72CDF4')
plt.ylim(0, 5000000);
plt.title("UC Regents's top 5 premiums - 2020");

plt.savefig(pdf_folder+"UC Regents's top 5 premiums - 2020.pdf", bbox_inches='tight');
plt.savefig(png_folder+"UC Regents's top 5 premiums - 2020.png", bbox_inches='tight');
#What if you group the premiums?
df = tidy_regents_policies
df = df.groupby('Carrier').sum().reset_index()
bar_plot(df, 5, color='#005581')
plt.ylim(0, 8000000);
plt.title('Top 5 Insurers paid by Regents - 2020');

plt.savefig(pdf_folder+"Top 5 Insurers paid by Regents - 2020.pdf", bbox_inches='tight');
plt.savefig(png_folder+"Top 5 Insurers paid by Regents - 2020.png", bbox_inches='tight');
bar_plot(tidy_fiatlux_policies, 5, color='#FFE552')
plt.ylim(0, 6000000);
plt.title("FiatLux's top 5 premiums - 2020");
# Who are '<NAME>', combined here with Brit, but nto sure if Brit Axis exist.

plt.savefig(pdf_folder+"FiatLux's top 5 premiums - 2020.pdf", bbox_inches='tight');
plt.savefig(png_folder+"FiatLux's top 5 premiums - 2020.png", bbox_inches='tight');
#What if you group the premiums?
df = tidy_fiatlux_policies
df = df.groupby('Carrier').sum().reset_index()
bar_plot(df, 5, color='#FFD200')
plt.ylim(0, 6000000);
plt.title('Top 5 Insurers paid by FiatLux - 2020');

plt.savefig(pdf_folder+"Top 5 Insurers paid by FiatLux - 2020.pdf", bbox_inches='tight');
plt.savefig(png_folder+"Top 5 Insurers paid by FiatLux - 2020.png", bbox_inches='tight');
bar_plot(tidy_uc_construction, 3, color='#BEB6AF')
plt.ylim(0, 4000000);
plt.title("Top 3 UC construction insurance contracts - 2020");

plt.savefig(pdf_folder+"Top 3 UC construction insurance contracts - 2020.pdf", bbox_inches='tight');
plt.savefig(png_folder+"Top 3 UC construction insurance contracts - 2020.png", bbox_inches='tight');
#What if you group the premiums?
df = tidy_uc_construction
df = df.groupby('Carrier').sum().reset_index()
bar_plot(df, 3, color='#8F8884')
plt.ylim(0, 5000000);
plt.title("UC's top 3 construction insurers - 2020");

plt.savefig(pdf_folder+"UCs top 3 construction insurers - 2020.pdf", bbox_inches='tight');
plt.savefig(png_folder+"UCs top 3 construction insurers - 2020.png", bbox_inches='tight');
df = tidy_totals
df = df.groupby('Carrier').sum().reset_index()
bar_plot(df, 5, color='#B4975A')
plt.ylim(0, 9000000);
plt.title('Top 5 UC insurers - 2020');

plt.savefig(pdf_folder+"Top 5 UC insurers - 2020.pdf", bbox_inches='tight');
plt.savefig(png_folder+"Top 5 UC insurers - 2020.png", bbox_inches='tight');
# This plot shows UC spending against climate ranking, considering the parent company Tokio Marine.
xs = tidy.Premium
ys = tidy.Sum

labels = tidy.Carrier.str.title()

#Recapitalize AIG
labels = [label.replace('Aig', 'AIG') for label in labels]

plt.scatter(xs, ys, s=50, c=ys, cmap='YlOrRd_r', edgecolors ='k'); ## Color by score, yellow to red.
plt.title('How do UC insurance companies score?');
plt.ylabel('Combined Score\n underwriting, investment and climate leadership');
plt.xlabel('Total Contracts with UC (Million $)');
plt.ylim(0,15);

#Turn off the top and right hand axis lines.
ax = plt.gca()
ax.spines['right'].set_visible(False)
ax.spines['top'].set_visible(False)

n=int(0)

# zip joins x and y coordinates in pairs
for x, y in zip(xs,ys):

    label = labels[n]

    # this method is called for each point
    plt.annotate(label, # this is the text
                 (x,y), # this is the point to label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 ha='center') # horizontal alignment can be left, right or center
    n+=1
## Define ymax
ymax = tidy.Premium.max()+500000
# Flip axes
# This plot shows UC spending against climate ranking, considering the parent company Tokio Marine.
df = tidy.head(10)

xs = df.Sum
ys = df.Premium

labels = tidy.Carrier.str.title()

#Recapitalize AIG
labels = [label.replace('Aig', 'AIG') for label in labels]

plt.scatter(xs, ys, s=50, c=xs, cmap='YlOrRd_r', edgecolors ='k'); ## Color by score, yellow to red.
plt.title('How do UC insurance companies score?');
plt.xlabel('Combined Score\n (Underwriting, Investment and Climate leadership)');
plt.ylabel('Total Contracts with UC (USD)');
plt.xlim(0,15);
plt.ylim(0, ymax);

#Turn off the top and right hand axis lines.
ax = plt.gca()
ax.spines['right'].set_visible(False)
ax.spines['top'].set_visible(False)

n=int(0)

# zip joins x and y coordinates in pairs
for x, y in zip(xs,ys):

    label = labels[n]

    # this method is called for each point
    plt.annotate(label, # this is the text
                 (x,y), # this is the point to label
                 textcoords="offset points", # how to position the text
                 xytext=(10,0), # distance from text to points (x,y)
                 ha='left') # horizontal alignment can be left, right or center
    n+=1
    
#Give dollars with zeros and commas
plt.gca().get_yaxis().set_major_formatter(
    ticker.FuncFormatter(lambda y, p: format(int(y), ',')))

plt.savefig(pdf_folder+"How do UC's biggest insurers score.pdf", dpi=300, bbox_inches='tight')
plt.savefig(png_folder+"How do UC's biggest insurers score.png", dpi=300, bbox_inches='tight')
## Arrange plots in grid
fig, axes = plt.subplots(1,3, sharey=True, gridspec_kw = {'width_ratios': [5,5,3]})

# Panel 1
df = tidy_fiatlux_policies
df = df.groupby('Carrier').sum().reset_index()
bar_plot(df, 5, color='#FFD200',ax=axes[0])
axes[0].set_title('FiatLux', loc='left', pad=15)

# Panel 2
df = tidy_regents_policies
df = df.groupby('Carrier').sum().reset_index()
bar_plot(df, 5, color='#005581',ax=axes[1])
axes[1].spines['left'].set_visible(False)
axes[1].set_title('Regents', loc='left', pad=15)

# Panel 3
df = tidy_uc_construction
df = df.groupby('Carrier').sum().reset_index()
bar_plot(df, 3, color='#8F8884',ax=axes[2])
axes[2].spines['left'].set_visible(False)
axes[2].set_title('Construction', loc='left', pad=15)

plt.ylim(0,ymax);

plt.savefig(pdf_folder+"Biggest spending by section.pdf", dpi=300, bbox_inches='tight')
plt.savefig(png_folder+"Biggest spending by section.png", dpi=300, bbox_inches='tight')
# Policy Comparison of Main players
def policies_to_df(filename, sheet_name):
    
    #The format is a multi index. Column 3 
    #We want one row per insurer
    df = pd.read_excel(filename, sheet_name, index_col=[0,1,2])

    #Subset by rows with all NaN values
    #find null values
    null_values = df.isnull()

    #find rows (axis=1)  that are all null
    null_row = list(null_values.all(axis=1))

    #subset the original dataframe
    section_questions = df.loc[null_row, :]

    #get the questions from the third row of
    question = list(section_questions.index.get_level_values(2))

    #also get the section index from the first row
    section = list(section_questions.index.get_level_values(0))

    data_row =[not row for row in null_row]

    #Now remove the null rows, and add the section questions as a index.
    #Might be easier the other way around.

    #No, just strip this data and keep it as a separate dictionary.
    df = df.loc[data_row, :]
    
    return df    
scoring_sheet_names
underwriting = policies_to_df(scoring_filename, 'Underwriting')
underwriting
investment = policies_to_df(scoring_filename, 'Investment')
investment
leadership = policies_to_df(scoring_filename, 'Other Climate leadership')
leadership
# List key players
players = list(tidy.head(10).Carrier.str.title())
def subset_policies(df, companies: list):
    
    players = []

    for player in companies:
        if player in list(df.columns):
            players.append(player)
    
    return pd.DataFrame(df.T.loc[players, :])
subset_policies(underwriting, players).T
subset_policies(investment, players).T
subset_policies(leadership, players).T
# Drop rows where none of the players have a score.
def drop_zero(df):
    df = df[(df.T != 0).any()]
    return df
drop_zero(subset_policies(leadership, players).T)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11913152926368785, 'fraction_numerical': 0.02183763373190686, 'mean_word_length': 4.546596858638743, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '600770', 'license': 'MIT', 'n_tokens_mistral': 5699, 'n_tokens_neox': 5214, 'n_words': 1444, 'path': 'uc_insurance_infographic.ipynb', 'repo_name': 'davidbrown2324/uc_insurers', 'size': '15903'}