<jupyter_start><jupyter_text>Load<jupyter_code>df = pd.read_json('data/business_license.json')
print(df.shape)
print(df.dtypes)
df.head()<jupyter_output>(392636, 34)
id                                    object
license_id                             int64
account_number                         int64
site_number                            int64
legal_name                            object
doing_business_as_name                object
address                               object
city                                  object
state                                 object
zip_code                              object
ward                                 float64
precinct                             float64
ward_precinct                         object
police_district                      float64
license_code                           int64
license_description                   object
business_activity_id                  object
business_activity                     object
license_number                         int64
application_type                      object
application_requirements_complete     object
payment_date                          obje[...]<jupyter_text>Clean Data<jupyter_code># Changes datatype to datetime faster
# Function originally by fixxxer from https://stackoverflow.com/questions/29882573/pandas-slow-date-conversion
def lookup(s):
    """
    This is an extremely fast approach to datetime parsing.
    For large data, the same dates are often repeated. Rather than
    re-parse these, we store all unique dates, parse them, and
    use a lookup to convert all dates.
    """
    dates = {date:pd.to_datetime(date) for date in s.unique()}
    return s.map(dates)
# Change to date-time
df['date_issued'] = lookup(df['date_issued'])
df['license_start_date'] = lookup(df['license_start_date'])
df['expiration_date'] = lookup(df['expiration_date'])
# Add Attributes
df['mo_yr_issued'] = df.date_issued.dt.to_period('M')
df['mo_yr_start'] = df['license_start_date'].dt.to_period('M')
df['mo_yr_exp'] = df['expiration_date'].dt.to_period('M')
df.dtypes<jupyter_output><empty_output><jupyter_text>Check & Explore<jupyter_code>sns.set(rc={'figure.figsize':(20, 5)})
ax = sns.countplot(x='mo_yr_issued', data=df)

plt.title('Number of Business Licenses per year')
plt.ylabel('Number of Business Licenses')
plt.xlabel('Month-Year')
plt.xticks(rotation=90)
plt.show(ax)
sns.set(rc={'figure.figsize':(20, 5)})
ax = sns.countplot(x='mo_yr_start', data=df)

plt.title('Number of Business Licenses per year')
plt.ylabel('Number of Business Licenses')
plt.xlabel('Month-Year')
plt.xticks(rotation=90)
plt.show(ax)
sns.set(rc={'figure.figsize':(20, 5)})
ax = sns.countplot(x='mo_yr_exp', data=df)

plt.title('Number of Business Licenses per year')
plt.ylabel('Number of Business Licenses')
plt.xlabel('Month-Year')
plt.xticks(rotation=90)
plt.show(ax)<jupyter_output><empty_output><jupyter_text>***Why is there a spike in business licenses on December 2012?***<jupyter_code># Number of non-issued license statuses (cancelled, revoked, etc.)
df[df['license_status'] != 'AAI'].groupby('mo_yr_issued').id.count().reset_index(name='count').plot(x='mo_yr_issued', y='count')
plt.show()
# Number of issued licenses
df[df['license_status'] == 'AAI'].groupby('mo_yr_issued').id.count().reset_index(name='count').plot(x='mo_yr_issued', y='count')
plt.show()<jupyter_output><empty_output><jupyter_text>***Unclear why there is a spike, but have something to do with high number of cancellations as well. Could be a data cleaning issue over those years or some policy thing. Will probably have to limit timeframe collected***  Filter to Geography & Time  Chicago Only<jupyter_code># Only include Chicago business licenses
df_chi = df[df.city == 'CHICAGO']
print(df_chi.shape)
df_chi.head()
sns.set(rc={'figure.figsize':(20, 5)})
ax = sns.countplot(x='mo_yr_issued', data=df_chi)

plt.title('Number of Business Licenses per year')
plt.ylabel('Number of Business Licenses')
plt.xlabel('Month-Year')
plt.xticks(rotation=90)
plt.show(ax)<jupyter_output><empty_output><jupyter_text>Active Busineses from 2014-2018 Only<jupyter_code># Create list of month-years we care about
month_years = pd.date_range('2014-01-01','2018-12-31', 
              freq='MS').strftime("%Y-%m").tolist()
len(month_years)
month_years[:10]
# Create new columns for each month-year, indicating if row represents active business for that period
df_use = df_chi.copy()
for mo_yr in month_years:
    df_use[mo_yr] = 0
    df_use.loc[(df_use.mo_yr_start <= mo_yr) & (df_use.mo_yr_exp >= mo_yr), mo_yr] = 1

# Preview
print(df_use.shape)
df_use.head()
# Add column that indicates if row should be included
df_use['total_active_months'] = df_use[month_years].sum(axis=1)
df_use['include'] = df_use.total_active_months > 0
df_use
# Filter to only include time-relevant rows
df_use = df_use[df_use.include == True]
print(df_use.shape)
df_use.head()
sns.set(rc={'figure.figsize':(20, 5)})
ax = sns.countplot(x='mo_yr_issued', data=df_use)

plt.title('Number of Business Licenses per year')
plt.ylabel('Number of Business Licenses')
plt.xlabel('Month-Year')
plt.xticks(rotation=90)
plt.show(ax)<jupyter_output><empty_output><jupyter_text>Check Missing Data<jupyter_code># Check how many lat-longs are missing
print('Num of missing locations:', 
      df_use['longitude'].isnull().sum(), 
      df_use['latitude'].isnull().sum(), 
      df_use['location'].isnull().sum())
df_use[df_use.location.isnull()]
print('% missing:', round(df_use['location'].isnull().sum() / df_use.shape[0] * 100, 3))<jupyter_output>% missing: 1.557<jupyter_text>***Not a lot of missing locations, given the total number of licenses per year--only about 2% are missing locations****DROP THOSE WITH NULL LOCATIONS*<jupyter_code>df_use = df_use[df_use.location.notnull()]
print(df_use.shape)
df_use.head()<jupyter_output>(229172, 99)<jupyter_text>Load Block Groups<jupyter_code># BLOCK GROUPS
blocks = gpd.read_file('data/Boundaries - Census Blocks - 2010.geojson')

print(blocks.shape)
print(blocks.crs)
blocks
# NEIGHBORHOODS
areas = gpd.read_file("data/Boundaries - Neighborhoods.geojson")

print(areas.shape)
print(areas.crs)
areas
# Checks
print(blocks.blockce10.nunique())
print(blocks.geoid10.nunique())
print(areas.pri_neigh.nunique())
# Clean Block Groups
block_areas = blocks.copy()
block_areas["blockgroup"] = block_areas["geoid10"].str[0:12]
block_areas = block_areas[["geometry", "blockgroup"]]

# Merge with Neighborhoods
block_areas = gpd.sjoin(block_areas, areas, how="left", op='intersects')
block_areas.head()
# Dissolve areas by block group
block_areas = block_areas.dissolve(by='blockgroup', aggfunc='first')
block_areas.head()
block_areas = block_areas[["geometry", "pri_neigh"]].reset_index()
block_areas.head()<jupyter_output><empty_output><jupyter_text>Merge Business License Data with Block Group Data<jupyter_code># Convert df_chi into geopandas
chi_gdf = gpd.GeoDataFrame(df_use, crs ='epsg:4326', geometry=gpd.points_from_xy(df_use.longitude, df_use.latitude))

print(chi_gdf.shape)
print(chi_gdf.crs)
chi_gdf.head()
# Merge
merged_gdf = gpd.sjoin(chi_gdf, block_areas, how='left', op='intersects')
print(merged_gdf.shape)
merged_gdf.head()<jupyter_output>(229172, 103)<jupyter_text>Create Block by Month-Year Counts<jupyter_code>#Filter to only include issued licenses (not cancelled or revoked)
aai_only = merged_gdf[merged_gdf.license_status == 'AAI'].copy()
print(aai_only.shape[0], 'rows in aai_only')
# Select columns to keep
aai_only = aai_only[['blockgroup', 'pri_neigh', 'total_active_months',
                     'mo_yr_start', 'mo_yr_exp', 'mo_yr_issued',
                     'application_type', 'license_number', 'address',
                     'doing_business_as_name', 'account_number'] + month_years]
aai_only.head()
# CHECK: Total number of licenses active on 12/2018
print('These should be the same:')
print(aai_only[aai_only.blockgroup.notnull()]['2018-12'].sum())
print(aai_only.groupby('blockgroup')['2018-12'].sum().sum()) # non-null values for blockgroups
# Initiate new block-month-year dataframe
block_mo_yr = pd.DataFrame(columns=['blockgroup', 'month-year', 'active', 'new'])
block_mo_yr
# Generate relevant counts (unique business identified as unique address)
for mo_yr in month_years:
    # Active Businesses
    activ_bus = aai_only[aai_only[mo_yr] == 1].groupby('blockgroup').address.nunique().reset_index(name='active')
    
    # Newly Licensed Businesses
    new_bus = aai_only[(aai_only.application_type == 'ISSUE') &
                       (aai_only.mo_yr_issued == mo_yr)].groupby('blockgroup').address.nunique().reset_index(name='new')
    
    # Merge numbers and add to block_mo_yr dataframe
    merged = activ_bus.merge(new_bus, on='blockgroup', how='outer')
    merged['month-year'] = mo_yr
    block_mo_yr = block_mo_yr.append(merged, ignore_index=True)
# Preview
block_mo_yr.sort_values('active', ascending=False)
# Sanity Checks
print(block_mo_yr['month-year'].nunique()) #should be 60
print(block_mo_yr['blockgroup'].nunique()) #no more than 2176
print('max number of active businesses in a given month:', block_mo_yr.groupby('month-year').active.sum().max())
print('number of unique businesses (max above should not exceed this):', aai_only.address.nunique())

print(block_mo_yr.active.isna().sum()) # should not be many
print(block_mo_yr.new.isna().sum()) # ok if many
# Replace NAs in 'new' column with zeroes
block_mo_yr.new.fillna(0, inplace=True)
print(block_mo_yr.active.isna().sum())
block_mo_yr
# Check datatypes
block_mo_yr.dtypes
# Clean up datatypes
block_mo_yr['active'] = pd.to_numeric(block_mo_yr['active'])
block_mo_yr['month-year'] = pd.to_datetime(block_mo_yr['month-year'])
block_mo_yr.sort_values('new', ascending=False)
block_mo_yr.dtypes
block_mo_yr['month'] = block_mo_yr['month-year'].dt.month
block_mo_yr['year'] = block_mo_yr['month-year'].dt.year
block_mo_yr<jupyter_output><empty_output><jupyter_text>Add More Features  Previous Years<jupyter_code>years = [yr for yr in range(2014, 2019)]
years
to_merge_df = pd.DataFrame(columns=('blockgroup', 'month-year', 'prev_month-year', 'prev_yr_active', 'prev_yr_new'))

for yr in years[1:]:
    # Previous years
    prev_yr_df = block_mo_yr[block_mo_yr['year'] == yr - 1][['blockgroup', 'month-year', 'active', 'new']]
    prev_yr_df.rename(columns={'month-year': 'prev_month-year',
                               'active': 'prev_yr_active',
                               'new': 'prev_yr_new'}, inplace=True)
    prev_yr_df['month-year'] = prev_yr_df['prev_month-year'] + pd.offsets.DateOffset(years=1)
    
    # Append to working df to merge with main block_mo_yr df
    to_merge_df = to_merge_df.append(prev_yr_df, ignore_index=True)

    #Check
    print(yr - 1, ': ', prev_yr_df.shape[0], 'rows')

to_merge_df
# Sanity Check
print('These should match:')
print(block_mo_yr[block_mo_yr.year == 2018].shape[0] + to_merge_df.shape[0])
print(block_mo_yr.shape[0])

print(to_merge_df['month-year'].nunique(), 'months in previous years')
print(block_mo_yr['month-year'].nunique(), 'months in current years')
print(to_merge_df.blockgroup.nunique(), 'blockgroups in previous years')
print(block_mo_yr.blockgroup.nunique(), 'blockgroups in current years')
print(block_mo_yr.year.value_counts())
# Merge with main dataframe
block_mo_yr = block_mo_yr.merge(to_merge_df, on=['blockgroup', 'month-year'], how='left')
block_mo_yr
# Add % Change Attributes
block_mo_yr['%_change_active'] = ((block_mo_yr['active'] - block_mo_yr['prev_yr_active']) 
                                  / block_mo_yr['prev_yr_active']).fillna(0)
block_mo_yr
# Add % Change Attributes
block_mo_yr['%_change_new'] = ((block_mo_yr['new'] - block_mo_yr['prev_yr_new'])
                               / block_mo_yr['prev_yr_new']).fillna(0)
block_mo_yr
block_mo_yr.to_csv("data/business_licenses_by_blockgroup.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13635218012265815, 'fraction_numerical': 0.015458287826598336, 'mean_word_length': 4.554829678021465, 'pattern_counts': {'":': 0, '<': 46, '<?xml version=': 0, '>': 47, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '284107', 'license': 'Naumen', 'n_tokens_mistral': 4296, 'n_tokens_neox': 4050, 'n_words': 807, 'path': 'DataWrangling_BusinessLicenses.ipynb', 'repo_name': 'lokijoanderso/Chicago-Transit-ML-Project', 'size': '11903'}