<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Data munghing for business pattern data
Step1:  1. Download the table from the website
zip code, business type, # of establishment by # of employees
2. Concatenate tables for corresponding zip codes
Step4:  3. Simplify the business code
Step5:  4. Fill the gaps for non-existing data
Step7:  5. Predictor
<ASSISTANT_TASK:>
Python Code:
import requests
import numpy as np
import pandas as pd
import matplotlib
import matplotlib.pyplot as plt
%matplotlib inline
import csv
import seaborn as sns
font = {'family' : 'Arial',
        'weight' : 'bold',
        'size'   : 25}
matplotlib.rc('font', **font)
from census import Census
from us import states
import csv
Explanation: Data munghing for business pattern data
End of explanation
# load zip codes
import pickle
with open('zipcode_final.txt', 'rb') as f:
    zip_codes = pickle.load(f)    
# concatenate tables with different years and zip codes
df = pd.DataFrame()
years = range(2011,2014)
for year in years:
    temp = pd.read_csv('zbp'+ str(year-2000) +'detail.txt',error_bad_lines=False)
    temp['year']=year
    temp = temp.loc[temp['zip'].isin(map(int,zip_codes))]
    df = pd.concat([df,temp],ignore_index=True)
df.head()    
Explanation: 1. Download the table from the website
zip code, business type, # of establishment by # of employees
2. Concatenate tables for corresponding zip codes
End of explanation
    11	Agriculture, Forestry, Fishing and Hunting
    21	Mining, Quarrying, and Oil and Gas Extraction
    22	Utilities
    #23	Construction
    31-33	Manufacturing
    #42	Wholesale Trade
    #44-45	Retail Trade
    48-49	Transportation and Warehousing
    #51	Information
    #52	Finance and Insurance
    #53	Real Estate and Rental and Leasing
    #54	Professional, Scientific, and Technical Services
    #55	Management of Companies and Enterprises
    56	Administrative and Support and Waste Management and Remediation Services
    61	Educational Services
    62	Health Care and Social Assistance
    71	Arts, Entertainment, and Recreation
    #72	Accommodation and Food Services
    81	Other Services (except Public Administration)
    92	Public Administration
    
    potential parameters: 23, 42, 45, 51, 52, 53, 54, 55, 72
def conver_business_type(old_code):
    
        Convert N digit nacis codes to 2 digits
        http://www.census.gov/cgi-bin/sssd/naics/naicsrch?chart=2012
    
    # get the first two digit
    new_code = old_code[:2]
    # simplify
#     31-33	Manufacturing
#     44-45	Retail Trade
#     48-49	Transportation and Warehousing
    if new_code != '--':
        new_int = int(new_code)
        if new_int >= 31 and new_int <= 33:
            new_code = '30'
        elif new_int >= 44 and new_int <= 45:
            new_code = '45'
        elif new_int >= 48 and new_int <= 49:
            new_code = '49'
    return new_code
# create new column for simplified business_code
df['business_code']=df['naics'].apply(lambda x: conver_business_type(x))
# rename for consistence
df=df.rename(columns = {'zip':'zip_code'})
df.head()
# Add column that show potentially good indicators
# 23	Construction
# 42	Wholesale Trade
# 45	Retail Trade
# 51	Information
# 52	Finance and Insurance
# 53	Real Estate and Rental and Leasing
# 54	Professional, Scientific, and Technical Services
# 55	Management of Companies and Enterprises
# 72	Accommodation and Food Services
# good_biz_code_list = [23,42,45,51,52,53,54,55,72]
# save
df.to_csv('biz_sum.csv',encoding='utf-8',index=False)
df = pd.read_csv('biz_sum.csv')
df.head()
Explanation: 3. Simplify the business code
End of explanation
# simplify the table
df2 = df[['year','zip_code','business_code','est']]
df2.head()
# sum and normalize
# 1. Sum: use pandas groupby
gb = df2.groupby(('zip_code','year','business_code'),as_index=False)
gb_sum = gb.agg(np.sum)
gb_sum.head()
# Compute growth rate: divide year_(N+1) by year_N (>1: growth)
gb2 = gb_sum.groupby(('zip_code','year'))
# Create empty cell if the business type does not exist.
business_codes = [11, 21, 22, 23, 30, 42, 45, 49, 51, 52, 53, 54, 55, 56, 61, 62, 71, 72, 81, 99]
# for every year and every zip code, create same list of business_codes
# if not exist, est value = 0
years = range(2011,2014)
df_biz = pd.DataFrame()
for year in years:
    for zip_code in zip_codes:
        try:
            a = gb2.get_group((int(zip_code),year))
            est_list = []
            temp = pd.DataFrame()
            for i in range(len(business_codes)):
                business_code = business_codes[i]
                if str(business_code) in a['business_code'].values:
                    est_list.append(int(a[a['business_code']==str(business_code)]['est']))
                else:
                    est_list.append(0)
            temp['business_code']=pd.Series(business_codes)
            temp['est']=pd.DataFrame(est_list)
            temp['zip_code']=zip_code
            temp['year']=year
            df_biz = pd.concat([df_biz,temp],ignore_index=True)
        except KeyError:
            continue                
# save
df_biz.to_csv('biz_sum_final.csv',encoding='utf-8',index=False)
Explanation: 4. Fill the gaps for non-existing data
End of explanation
df_biz.head()
gb_biz = df_biz.groupby(('year','zip_code','business_code'))
df_all
ref_years = [2011, 2012]
df_biz_growth = pd.DataFrame()
for ref_year in ref_years:
    for zip_code in zip_codes:
        for business_code in business_codes:
            try:
                prev_year = int(gb_biz.get_group((ref_year,zip_code,business_code))['est'].values)
                this_year = int(gb_biz.get_group((ref_year+1,zip_code,business_code))['est'].values)
                if prev_year == 0 & this_year > 0:
                    growth = 2
                elif prev_year == 0 & this_year == 0:
                    growth = 0
                else:
                    growth = (this_year - prev_year+.0)/prev_year
                data = [dict(year = ref_year,
                            zip_code = zip_code,
                            business_code = business_code,
                            growth = growth)]        
                temp = pd.DataFrame(data)
                df_biz_growth = pd.concat([df_biz_growth,temp],ignore_index=True)
            except KeyError:
                continue                
print df_biz_growth.head()
len(df_biz_growth)
df_biz_growth.to_csv('biz_growth.csv',encoding='utf-8',index=False)
gb3 = df_biz_growth.groupby(('year','zip_code'))
gb3.get_group((2012,'21201'))
# original zip code
len(zip_codes)
# new zip code based on available data: it's fewer
len(df_biz_growth.zip_code.unique())
# we're getting only these zip codes
new_zip = df_biz_growth.zip_code.unique()
np.save('new_zip_codes', new_zip)
    load: np.load('new_zip_codes.npy')
Explanation: 5. Predictor: growth rate per business category
End of explanation
<END_TASK>