<jupyter_start><jupyter_text>This workbook generates output CSV tables for the Executive summary, memo & final workbook results. Starting point is the 'all_rows_post' pickled dataframe generated in the main analysis notebook.<jupyter_code>all_rows_post = pd.read_pickle('all_rows_post.pkl')
detailed_sales = pd.read_excel('Extrap_byCZ_HARDI_Adjusted_25JAN2022_IsaacCopy.xlsx' , sheet_name = 'DetailedSales', usecols='A:H')
reported_sales = pd.read_excel('Extrap_byCZ_HARDI_Adjusted_25JAN2022_IsaacCopy.xlsx' , sheet_name = 'memo - reported', usecols='N:R')
detailed_sales =detailed_sales[detailed_sales['Supplier'] != 0]
all_rows_post.to_csv("All Rows Post Extrapolations.csv")
extrapolated_quantities=all_rows_post.groupby(['yearSale','Technology'])['TOTAL'].sum().reset_index()
#extrapolated_quantities2=all_rows_post.groupby(['yearSale','Technology','Sector'])['TOTAL'].sum().reset_index()
reported_quantities=detailed_sales.groupby(['yearSale','sector','Technology'])['Reported Quantity'].sum().reset_index()
#extrapolated_quantities2.to_csv("EXtrapolated_Quantities with sector.csv")
extrapolated_quantities.to_csv("EXtrapolated_Quantities.csv")
reported_quantities.to_csv("Reported_Quantities.csv")
ductless_heat_pump=all_rows_post[all_rows_post['Technology'] == 'Heat Pump - Ductless']
ductless_heat_pump = ductless_heat_pump[ductless_heat_pump['Sector'] == 'Residential']
heating = ductless_heat_pump.groupby(['yearSale','HeatingEfficiency'])['TOTAL'].sum().reset_index()
cooling = ductless_heat_pump.groupby(['yearSale','CoolingEfficiency'])['TOTAL'].sum().reset_index()


years = [2016, 2017, 2018, 2019, 2020]
heating_eff_mix = pd.DataFrame()
cooling_eff_mix = pd.DataFrame()


for y in years:
    df1 = heating[heating['yearSale'] == y]
    df2 = cooling[cooling['yearSale'] == y]
    total1=df1['TOTAL'].sum()
    total2=df2['TOTAL'].sum()
    df1['pct_share'] = df1['TOTAL'] / total1
    df2['pct_share'] = df2['TOTAL'] / total2
    heating_eff_mix = pd.concat([heating_eff_mix, df1], axis=0)
    cooling_eff_mix = pd.concat([cooling_eff_mix, df2], axis=0)
    
heating_eff_mix.to_csv("Ductless Heat Pump Heating Eff Mix.csv")
cooling_eff_mix.to_csv("Ductless Heat Pump Cooling Eff Mix.csv")
cac = all_rows_post[all_rows_post['Technology'] == 'Central Air Conditioning - Condenser']
mix = cac.groupby(['yearSale','CoolingEfficiency'])['TOTAL'].sum().reset_index()


years = [2016, 2017, 2018, 2019, 2020]
cooling_mix = pd.DataFrame()

for y in years:
    df = mix[mix['yearSale'] == y]
    total=df['TOTAL'].sum()
    df['pct_share'] = df['TOTAL'] / total
    cooling_mix = pd.concat([cooling_mix, df], axis=0)
    
cooling_mix.to_csv("CAC- Condenser Cooling Eff Mix.csv")
gas_furnace = all_rows_post[all_rows_post['Technology'] == 'Gas Furnace']
gas = gas_furnace.groupby(['yearSale','HeatingEfficiency'])['TOTAL'].sum().reset_index()


years = [2016, 2017, 2018, 2019, 2020]
heating_mix = pd.DataFrame()

for y in years:
    df = gas[gas['yearSale'] == y]
    total=df['TOTAL'].sum()
    df['pct_share'] = df['TOTAL'] / total
    heating_mix = pd.concat([heating_mix, df], axis=0)
    
heating_mix.to_csv("Gas Furnace Eff Mix.csv")
extrapolation_summary1=all_rows_post.groupby(['yearSale','Technology'])['TOTAL'].sum().reset_index()
extrapolation_summary2=all_rows_post.groupby(['yearSale','Technology'])['extrapped_qty'].sum().reset_index()
extrapolation_summary = extrapolation_summary1.merge(extrapolation_summary2, how='outer')
extrapolation_summary['pct_extrapolated'] = extrapolation_summary['extrapped_qty'] / extrapolation_summary['TOTAL']
extrapolation_summary.to_csv("Pct Extrapolated Summary.csv")
extrapolation_summary3=all_rows_post.groupby(['yearSale'])['TOTAL'].sum().reset_index()
extrapolation_summary4=all_rows_post.groupby(['yearSale'])['extrapped_qty'].sum().reset_index()
extrapolation_summary5 = extrapolation_summary3.merge(extrapolation_summary4, how='outer')
extrapolation_summary5['pct_extrapolated'] = extrapolation_summary5['extrapped_qty'] / extrapolation_summary5['TOTAL']
extrapolation_summary5.to_csv("Pct Extrapolated Summary by Year.csv")
all_rows_post2 = pd.read_pickle('all_rows_post.pkl')
all_rows_post2.loc[all_rows_post2['Technology'] == 'Heat Pump - Air Source, Variable Capacity', 'HeatingEfficiency'] = 'Heat Pump - Air Source, Variable Capacity'
all_rows_post2.loc[all_rows_post2['Technology'] == 'Heat Pump - Air Source, Variable Capacity', 'CoolingEfficiency'] = 'Heat Pump - Air Source, Variable Capacity'
all_rows_post2.loc[all_rows_post2['Technology'] == 'Heat Pump - Air Source, Variable Capacity', 'Technology'] = 'Heat Pump'
all_rows_post2.to_pickle('all_rows_post2.pkl')
all_rows_post2
all_rows_post2['TOTAL'].sum()
all_rows_post3 = all_rows_post2[all_rows_post2['Supplier'] != 0]
all_rows_post3['Reported Quantity'].sum()
res  = all_rows_post2[all_rows_post2['Sector'] == 'Residential']
res1 = res[(res['Technology'] == 'Heat Pump - Ductless') | (res['Technology'] == 'Heat Pump')|(res['Technology'] == 'Gas Furnace')]
res2 = res[(res['Technology'] == 'Heat Pump') | (res['Technology'] == 'Central Air Conditioning - Condenser')]
res1=res1[res1['HeatingEfficiency'] != 'UNDEFINED']
res_heating = res1.groupby(['yearSale','Technology','HeatingEfficiency'])['TOTAL'].sum().reset_index()
res_cooling = res2.groupby(['yearSale','Technology','CoolingEfficiency'])['TOTAL'].sum().reset_index()


years = [2016, 2017, 2018, 2019, 2020]
res_heat_eff_mix = pd.DataFrame()
res_cool_eff_mix = pd.DataFrame()


for y in years:
    df1 = res_heating[res_heating['yearSale'] == y]
    df2 = res_cooling[res_cooling['yearSale'] == y]
   
    ductless_total  = df1[df1['Technology'] == 'Heat Pump - Ductless']['TOTAL'].sum()
    hp_total        = df1[df1['Technology'] == 'Heat Pump']['TOTAL'].sum()
    furnace_total   = df1[df1['Technology'] == 'Gas Furnace']['TOTAL'].sum()
    cac_total       = df2[df2['Technology'] == 'Central Air Conditioning - Condenser']['TOTAL'].sum()

    df1.loc[df1['Technology'] == 'Heat Pump', 'pct_share']            = df1['TOTAL'] / hp_total
    df1.loc[df1['Technology'] == 'Heat Pump - Ductless', 'pct_share'] = df1['TOTAL'] / ductless_total
    df1.loc[df1['Technology'] == 'Gas Furnace', 'pct_share']          = df1['TOTAL'] / furnace_total
    
    df2.loc[df2['Technology'] == 'Heat Pump', 'pct_share'] = df2['TOTAL'] / hp_total
    df2.loc[df2['Technology'] == 'Central Air Conditioning - Condenser', 'pct_share'] = df2['TOTAL'] / cac_total
    
    res_heat_eff_mix = pd.concat([res_heat_eff_mix, df1], axis=0)
    res_cool_eff_mix = pd.concat([res_cool_eff_mix, df2], axis=0)
    
res_heat_eff_mix.to_csv("Residential Heating Eff Mix.csv")
res_cool_eff_mix.to_csv("Residential Cooling Eff Mix.csv")
ashp=all_rows_post2[(all_rows_post2['Technology'] == 'Heat Pump')&(all_rows_post2['HeatingEfficiency'] != 'Heat Pump - Air Source, Variable Capacity')&(all_rows_post2['Sector'] == 'Residential')]
heating = ashp.groupby(['yearSale','HeatingEfficiency'])['TOTAL'].sum().reset_index()
cooling = ashp.groupby(['yearSale','CoolingEfficiency'])['TOTAL'].sum().reset_index()


years = [2016, 2017, 2018, 2019, 2020]
heating_eff_mix = pd.DataFrame()
cooling_eff_mix = pd.DataFrame()


for y in years:
    df1 = heating[heating['yearSale'] == y]
    df2 = cooling[cooling['yearSale'] == y]
    total1=df1['TOTAL'].sum()
    total2=df2['TOTAL'].sum()
    df1['pct_share'] = df1['TOTAL'] / total1
    df2['pct_share'] = df2['TOTAL'] / total2
    heating_eff_mix = pd.concat([heating_eff_mix, df1], axis=0)
    cooling_eff_mix = pd.concat([cooling_eff_mix, df2], axis=0)
    
heating_eff_mix.to_csv("ASHP Heating Eff Mix.csv")
cooling_eff_mix.to_csv("ASHP Cooling Eff Mix.csv")
res_techs   = all_rows_post2[all_rows_post2['Sector'] == 'Residential']
res_techs   = res_techs[(res_techs['Technology'] == 'Gas Furnace')|(res_techs['Technology'] == 'Heat Pump')|(res_techs['Technology'] == 'Heat Pump - Ductless')|(res_techs['Technology'] == 'Central Air Conditioning - Condenser')]
res_techs   = res_techs.groupby(['yearSale','Technology'])['TOTAL'].sum().reset_index()


years = [2016, 2017, 2018, 2019, 2020]
key_res_techs = pd.DataFrame()

for y in years:
    df = res_techs[res_techs['yearSale'] == y]
    total=df['TOTAL'].sum()
    df['pct_share'] = df['TOTAL'] / total
    key_res_techs = pd.concat([key_res_techs, df], axis=0)
    
key_res_techs.to_csv("Key Residential Techs.csv")
unitary = all_rows_post[all_rows_post['Technology'] == 'Unitary Large Equipment']
uni     = unitary.groupby(['yearSale'])['TOTAL'].sum().reset_index()
uni.to_csv('Unitary Large Equipment Yearly Trend.csv')
a = all_rows_post2[all_rows_post2['Technology'] == 'Heat Pump']
a = a.groupby(['yearSale'])['TOTAL'].sum().reset_index()
b = all_rows_post2[all_rows_post2['HeatingEfficiency'] == 'Heat Pump - Air Source, Variable Capacity']
b = b.groupby(['yearSale'])['TOTAL'].sum().reset_index()
vchp = a.merge(b, how='outer', on='yearSale')
vchp.columns = ['yearSale','all heat pump sales','VCHP sales']
vchp['pct_share'] = vchp['VCHP sales'] / vchp['all heat pump sales']
vchp.to_csv("VCHP Yearly Trends.csv")
ashp=all_rows_post[(all_rows_post['Technology'] == 'Heat Pump - Air Source, Variable Capacity')&(all_rows_post['Sector'] == 'Residential')]
heating = ashp.groupby(['yearSale','HeatingEfficiency'])['TOTAL'].sum().reset_index()
cooling = ashp.groupby(['yearSale','CoolingEfficiency'])['TOTAL'].sum().reset_index()


years = [2016, 2017, 2018, 2019, 2020]
heating_eff_mix = pd.DataFrame()
cooling_eff_mix = pd.DataFrame()


for y in years:
    df1 = heating[heating['yearSale'] == y]
    df2 = cooling[cooling['yearSale'] == y]
    total1=df1['TOTAL'].sum()
    total2=df2['TOTAL'].sum()
    df1['pct_share'] = df1['TOTAL'] / total1
    df2['pct_share'] = df2['TOTAL'] / total2
    heating_eff_mix = pd.concat([heating_eff_mix, df1], axis=0)
    cooling_eff_mix = pd.concat([cooling_eff_mix, df2], axis=0)
    
heating_eff_mix.to_csv("VCHP Heating Eff Mix.csv")
cooling_eff_mix.to_csv("VCHP Cooling Eff Mix.csv")
ashp<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17180792652490764, 'fraction_numerical': 0.028251971648198062, 'mean_word_length': 6.796108949416342, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '643860', 'license': 'MIT', 'n_tokens_mistral': 4108, 'n_tokens_neox': 3686, 'n_words': 578, 'path': 'Generate Outputs.ipynb', 'repo_name': 'ischultz-cadeo/TO_43_Sales_Data', 'size': '10017'}