<jupyter_start><jupyter_text>calculate non-residential-sqft and job-space growth from buildings output<jupyter_code># simplify building types and sources

blg_type_dict = {'HO':  'other',
                 'OF':  'office',
                 'PA2': 'other',
                 'RS':  'retail',
                 'HM':  'other',
                 'HS':  'other',
                 'IH':  'industrial/warehouse',
                 'IL':  'industrial/warehouse',
                 'IW':  'industrial/warehouse',
                 'MR':  'mixed-use',
                 'SC':  'other',
                 'RB':  'retail',
                 'HT':  'other',
                 'ME':  'mixed-use',
                 'GQ':  'other',
                 'VP':  'other',
                 'PG':  'other',
                 'VA':  'other',
                 'OT':  'other'}
pg = pd.read_csv(r'C:\Users\ywang\Box\Modeling and Surveys\Urban Modeling\Bay Area UrbanSim\PBA50\Current PBA50 Large General Input Data\2021_02_25_parcels_geography.csv',
                 usecols = ['PARCEL_ID', 'eir_tra_id', 'juris'])
juris = pd.read_csv(r'C:\Users\ywang\Documents\GitHub\petrale\zones\jurisdictions\juris_county_id.csv',
                    usecols = ['juris_name_full', 'county_name'])


# calculate non-residential-sqft and job-spaces growth
b50 = pd.read_csv('run375_building_data_2050.csv',
                  usecols = ['building_id', 'parcel_id', 'non_residential_sqft', 'job_spaces', 'building_type', 'source'])
b50['blg_type'] = b50['building_type'].map(blg_type_dict)
b50 = b50.merge(pg, left_on='parcel_id', right_on='PARCEL_ID', how='left')
b50 = b50.merge(juris, left_on = 'juris', right_on = 'juris_name_full', how='left')

# check NA in columns
for i in list(b50):
    if b50[i].isnull().values.any():
        print('column with NA: {}'.format(i))

# fill na in tra_id
b50['eir_tra_id'] = b50['eir_tra_id'].fillna('NA')

b50_sum = b50.groupby(['county_name', 'juris', 'eir_tra_id', 'blg_type', 'source'])['non_residential_sqft', 'job_spaces'].sum().reset_index()
b50_sum.rename(columns = {'non_residential_sqft': 'non_residential_sqft_50',
                          'job_spaces': 'job_spaces_50'}, inplace=True)


print(b50[['non_residential_sqft','job_spaces']].sum())
print(b50_sum[['non_residential_sqft_50','job_spaces_50']].sum())
# display(b50_sum)


b15 = pd.read_csv('run375_building_data_2015.csv',
                  usecols = ['building_id', 'parcel_id', 'non_residential_sqft', 'job_spaces', 'building_type', 'source'])
b15['blg_type'] = b15['building_type'].map(blg_type_dict)
b15 = b15.merge(pg, left_on='parcel_id', right_on='PARCEL_ID', how='left')
b15 = b15.merge(juris, left_on = 'juris', right_on = 'juris_name_full', how='left')

# check NA in columns
for i in list(b15):
    if b15[i].isnull().values.any():
        print('column with NA: {}'.format(i))
    
# fill na in tra_id
b15['eir_tra_id'] = b15['eir_tra_id'].fillna('NA')

b15_sum = b15.groupby(['county_name', 'juris', 'eir_tra_id', 'blg_type', 'source'])['non_residential_sqft', 'job_spaces'].sum().reset_index()
b15_sum.rename(columns = {'non_residential_sqft': 'non_residential_sqft_15',
                          'job_spaces': 'job_spaces_15'}, inplace=True)

print(b15[['non_residential_sqft','job_spaces']].sum())
print(b15_sum[['non_residential_sqft_15','job_spaces_15']].sum())
# display(b15_sum)
b_chg = b50_sum.merge(b15_sum, on=['county_name','juris','eir_tra_id','blg_type','source'], how='outer')

for i in ['non_residential_sqft_50', 'non_residential_sqft_15', 'job_spaces_50', 'job_spaces_15']:
    b_chg[i] = b_chg[i].fillna(0)
    
b_chg['non_residential_sqft_growth'] = b_chg['non_residential_sqft_50'] - b_chg['non_residential_sqft_15']
b_chg['job_spaces_growth'] = b_chg['job_spaces_50'] - b_chg['job_spaces_15']

b_chg.to_csv('growth_by_building_Alt1.csv', index=False)
b_chg_type = b_chg[['blg_type',
                    'source',
                    'non_residential_sqft_growth',
                    'job_spaces_growth']].groupby(['blg_type',
                                                   'source'])['non_residential_sqft_growth',
                                                              'job_spaces_growth'].sum().reset_index().sort_values(['blg_type', 'source'])
b_chg_type['non_residential_sqft_growth'] = b_chg_type['non_residential_sqft_growth'].apply(lambda x: int(x))
display(b_chg_type)
b_chg.loc[b_chg.eir_tra_id.isnull(), 'eir_tra_id'] = 'non-tra'
b_chg_geo = b_chg[['juris',
                   'eir_tra_id',
                   'non_residential_sqft_growth',
                   'job_spaces_growth']].groupby(['eir_tra_id',
                                                  'juris'])['non_residential_sqft_growth',
                                                            'job_spaces_growth'].sum().reset_index().sort_values(['eir_tra_id', 'juris'])
b_chg_geo['non_residential_sqft_growth'] = b_chg_geo['non_residential_sqft_growth'].apply(lambda x: int(x))
display(b_chg_geo)
b_chg_geo_type = b_chg[['juris',
                        'blg_type',
                        'source',
                        'non_residential_sqft_growth',
                        'job_spaces_growth']].groupby(['juris',
                                                       'blg_type',
                                                       'source'])['non_residential_sqft_growth',
                                                                  'job_spaces_growth'].sum().reset_index().sort_values(['blg_type', 'source'])
b_chg_geo_type['non_residential_sqft_growth'] = b_chg_geo_type['non_residential_sqft_growth'].apply(lambda x: int(x))
display(b_chg_geo_type)<jupyter_output>C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\ipykernel_launcher.py:8: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.<jupyter_text>calculate job-space and emp growth from parcels output<jupyter_code>p50 = pd.read_csv('run375_parcel_data_2050.csv',
                  usecols = ['parcel_id', 'total_job_spaces', 'totemp'])
p50 = p50.merge(pg, left_on='parcel_id', right_on='PARCEL_ID', how='left')
p50 = p50.merge(juris, left_on = 'juris', right_on = 'juris_name_full', how='left')

# check NA in columns
for i in list(p50):
    if p50[i].isnull().values.any():
        print('column with NA: {}'.format(i))
    
# fill na in tra_id
p50['eir_tra_id'] = p50['eir_tra_id'].fillna('NA')
p50['totemp'] = p50['totemp'].fillna(0)

p50_sum = p50.groupby(['county_name', 'juris', 'eir_tra_id'])['total_job_spaces', 'totemp'].sum().reset_index()
p50_sum.rename(columns = {'total_job_spaces': 'total_job_spaces_50',
                          'totemp': 'totemp_50'}, inplace=True)

print(p50[['total_job_spaces', 'totemp']].sum())
print(p50_sum[['total_job_spaces_50', 'totemp_50']].sum())



p15 = pd.read_csv('run375_parcel_data_2015.csv',
                  usecols = ['parcel_id', 'total_job_spaces', 'totemp'])
p15 = p15.merge(pg, left_on='parcel_id', right_on='PARCEL_ID', how='left')
p15 = p15.merge(juris, left_on = 'juris', right_on = 'juris_name_full', how='left')

# check NA in columns
for i in list(p15):
    if p15[i].isnull().values.any():
        print('column with NA: {}'.format(i))

# fill na in tra_id
p15['eir_tra_id'] = p15['eir_tra_id'].fillna('NA')
p15['totemp'] = p15['totemp'].fillna(0)

p15_sum = p15.groupby(['county_name', 'juris', 'eir_tra_id'])['total_job_spaces', 'totemp'].sum().reset_index()
p15_sum.rename(columns = {'total_job_spaces': 'total_job_spaces_15',
                          'totemp': 'totemp_15'}, inplace=True)

print(p15[['total_job_spaces', 'totemp']].sum())
print(p15_sum[['total_job_spaces_15', 'totemp_15']].sum())
p_chg = p15_sum.merge(p50_sum, on=['county_name', 'juris', 'eir_tra_id'], how='outer')

for i in ['total_job_spaces_50', 'total_job_spaces_15', 'totemp_50', 'totemp_15']:
    p_chg[i] = p_chg[i].fillna(0)
    
p_chg['total_job_spaces_growth'] = p_chg['total_job_spaces_50'] - p_chg['total_job_spaces_15']
p_chg['totemp_growth'] = p_chg['totemp_50'] - p_chg['totemp_15']

p_chg.to_csv('growth_by_parcel_Alt1.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16401663405088063, 'fraction_numerical': 0.03094422700587084, 'mean_word_length': 3.328745367919534, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 8, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '549742', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3213, 'n_tokens_neox': 2996, 'n_words': 420, 'path': 'scripts/EIR_Alt1_emp_analysis.ipynb', 'repo_name': 'BayAreaMetro/petrale', 'size': '8176'}