def funds_by_investment_type(workbook): #Creates a table that shows a pivot of funds by investment type over vintage year # in decades title = 'Table 2: Breakdown of Funds by Investment Type' #Basic Cleaning of Fund Data df = pd.read_csv(os.path.join(OPEN_PATH, 'fund_view.csv'), header = 0) df = df[['VINTAGE_YEAR', 'FUND_TYPE']] df = df.dropna() df = df[df.VINTAGE_YEAR >= 1980] df = df[df.VINTAGE_YEAR <= 2014] df = pd.crosstab(df.FUND_TYPE, [df.VINTAGE_YEAR], rownames = ['FUND_TYPE'], colnames = ['VINTAGE_YEAR']) df = transform_to_decades(df) df['Total'] = df.sum(axis = 1) df = convert_columns_to_perc(df) df = convert_index_to_col(df, 'Fund Type') #Converts Fund Type to more readable format df['Fund Type'].replace(global_vars.fund_type_replace, inplace = True) basic_write_out(df, title, workbook)
def gp_loc_perc(workbook):
#Creates a table that shows a pivot of GP by location over time (decades)
title = 'Table 1: General Partners by Location of Company Headquarters'
#Basic cleaning of GP data
df = pd.read_csv(os.path.join(OPEN_PATH, 'gp_view.csv'), header = 0)
df = df[['YEAR_FOUNDED', 'REGION_ID', 'COUNTRY_ID']]
df = df.dropna()
df = df[df.YEAR_FOUNDED >= 1980]
df = df[df.REGION_ID != 'ANTARCTICA']
#Separates UNITED STATES from North America
df.ix[df.COUNTRY_ID == 'UNITED STATES', 'REGION_ID'] = 'UNITED STATES'
df = pd.crosstab(df.REGION_ID, [df.YEAR_FOUNDED], rownames = ['REGION_ID'],
colnames = ['YEAR_FOUNDED'])
df = transform_to_decades(df)
df['Total'] = df.sum(axis = 1)
df = convert_columns_to_perc(df)
df = convert_index_to_col(df, 'REGIONS')
df['REGIONS'].replace(global_vars.region_replace, inplace = True)
width = {'index' = 17, 'other' = 17}
basic_write_out(df, title, workbook, width = width)
def companies_by_region(workbook): #Creates a table that shows a pivot of portfolio company by region over year founded title = 'Table 4: Companies by Region, Split by Year Founded' #Basic Cleaning of Company Data df = df.read_csv(os.path.join(OPEN_PATH, 'company_view.csv'), header = 0) df = df[['YEAR_FOUNDED', 'COUNTRY_ID', 'REGION_ID']] df.dropna() df = df[df.YEAR_FOUNDED >= 1980] df = df[df.REGION_ID != 'ANTARCTICA'] #Separates United States from North America df.ix[df.COUNTRY_ID == 'UNITED STATES', 'REGION_ID'] = 'UNITED STATES' df = pd.crosstab(df.REGION_ID, [df.YEAR_FOUNDED], rownames = ['REGION_ID'], colnames = ['YEAR_FOUNDED']) df = transform_to_decades(df) df = convert_columns_to_perc(df) df = convert_index_to_col(df, 'Regions') #Converts Regions to more readable format df['Regions'].replace(global_vars.region_replace, inplace = True) basic_write_out(df, title, workbook)
def fund_type(workbook):
#Createa table that shows the distribution of fund_type
title = 'Distribution of Fund Type'
df = pd.read_csv(os.path.join(OPEN_PATH, 'fund_view.csv'), header = 0)
df['Count'] = 1
df = df.groupby(["FUND_TYPE"])
df = df.agg({'Count':'sum'})
df = convert_index_to_col(df, "Fund Types")
width = {'index': 20, 'other': 15}
basic_write_out(df, title, workbook, width = width, cell_style = comma_style)
def exit_type_id(workbook):
#Creates a table that shows the distribution of exit_type_id
title = 'Distribution of Exit Type ID'
#Cleans the data
df = pd.read_csv(os.path.join(OPEN_PATH, 'exit_view.csv'), header = 0)
df['Count'] = 1
df = df.groupby(['EXIT_TYPE_ID'])
df = df.agg({'Count':'sum'})
df = convert_index_to_col.create(df, "Exit Types")
width = {'index': 25, 'other': 15}
basic_write_out(df, title, workbook, width = width, cell_style = comma_style)
def fund_type(workbook):
#Createa table that shows the distribution of fund_type
title = 'Distribution of Fund Type'
df = pd.read_csv(os.path.join(OPEN_PATH, 'fund_view.csv'), header=0)
df['Count'] = 1
df = df.groupby(["FUND_TYPE"])
df = df.agg({'Count': 'sum'})
df = convert_index_to_col(df, "Fund Types")
width = {'index': 20, 'other': 15}
basic_write_out(df, title, workbook, width=width, cell_style=comma_style)
def exit_type_id(workbook):
#Creates a table that shows the distribution of exit_type_id
title = 'Distribution of Exit Type ID'
#Cleans the data
df = pd.read_csv(os.path.join(OPEN_PATH, 'exit_view.csv'), header=0)
df['Count'] = 1
df = df.groupby(['EXIT_TYPE_ID'])
df = df.agg({'Count': 'sum'})
df = convert_index_to_col.create(df, "Exit Types")
width = {'index': 25, 'other': 15}
basic_write_out(df, title, workbook, width=width, cell_style=comma_style)
def investment_type(workbook):
#Creates a table that shows the distribution of investment category
title = 'Distribution of Investment Type'
#Cleans the data
df = pd.read_csv(os.path.join(OPEN_PATH, 'investment_view.csv'), header = 0)
df['Count'] = 1
df = df.groupby(['INVESTMENT_TYPE'])
df = df.agg({'Count':'sum'})
df = convert_index_to_col(df, "Investment Types")
width = {'index': 30, 'other': 15}
basic_write_out(df, title, workbook, width = width, cell_style = comma_style)
def investment_type(workbook):
#Creates a table that shows the distribution of investment category
title = 'Distribution of Investment Type'
#Cleans the data
df = pd.read_csv(os.path.join(OPEN_PATH, 'investment_view.csv'), header=0)
df['Count'] = 1
df = df.groupby(['INVESTMENT_TYPE'])
df = df.agg({'Count': 'sum'})
df = convert_index_to_col(df, "Investment Types")
width = {'index': 30, 'other': 15}
basic_write_out(df, title, workbook, width=width, cell_style=comma_style)
def gps_by_type(workbook): #Creates a table that show ta pivot of GP by gp type over year founded title = 'Table 5: PRivate Capital Firms by Type' #Basic cleaning of GP data df = pd.read_csv(os.path.join(OPEN_PATH, 'gp_view.csv'), header = 0) df = df[['YEAR_FOUNDED', 'GP_TYPE']] df.dropna() df = df[df.YEAR_FOUNDED >= 1980] df = pd.crosstab(df.GP_TYPE, [df.YEAR_FOUNDED], rownames = ['GP_TYPE'], colnames = ['YEAR_FOUNDED']) df = transform_to_decades(df) df['Total'] = df.sum(axis = 1) df = convert_columns_to_perc(df) df = convert_index_to_col(df, 'GP Type') df['GP Type'].replace(global_vars.fund_type_replace, inplace = True) basic_write_out(df, title, workbook)
def companies_by_nvca(workbook):
#Creates a table that shows a pivot of portfolio company by NVCA over year founded
title = 'Table 3: Companies by Industry, Split by Year Founded'
#Basic cleaning of Company data
df = pd.read_csv(os.path.join(OPEN_PATH, 'company_view.csv'), header = 0)
df = df[['YEAR_FOUNDED', 'NVCA']]
df = df.dropna()
df = df[df.YEAR_FOUNDED >= 1980]
df = df[df.NVCA != 'OTHER']
df = pd.crosstab(df.NVCA, [df.YEAR_FOUNDED], rownames = ['NVCA'],
colnames = ['YEAR_FOUNDED'])
df = transform_to_decades(df)
df['Total'] = df.sum(axis = 1)
df = convert_columns_to_perc(df)
df = convert_index_to_col(df, 'NVCA')
width = {'index': 20, 'other': 15}
basic_write_out(df, title, workbook, width = width)