<jupyter_start><jupyter_text>Overview. This file essentially grabs the month by county files from the [Quarterly Census of Employment and Wages](https://www.bls.gov/cew/) files from the BLS and then creates employment measures at the county-level, monthly frequency. A comment about the data. This file uses the ``County High-Level`` file. The layouts are [here](https://data.bls.gov/cew/doc/layouts/cty_high_level_layout.htm). In this file, there are only supersectors, not at the finer NAICS level. To get stuff at the finer NAICS level, then one needs to use the single files (which are large about 500mb for each qurter)A couple of comments about the code:   - In the funciton ``clean_bls_quarter`` it has a set of catagories (NAICS or NAICS supersectors) and then will go through the file and grab the appropriate employment statistic. This later part is performed by the function ``bls_quarter_cat``           - It now goes from 2014 data (and further back if modified) and up to 2019 (which only have Q1 values. See the relese calander when updates will be made.      - I'm using the super sector definitions from the bls here [https://www.bls.gov/iag/tgs/iag_index_naics.htm](https://www.bls.gov/iag/tgs/iag_index_naics.htm) Now what I do is pull out construction from the goods producing supersector, this just is pulling out super catagory 1012 in the stuff below.     Step 1Bring in the trade/tariff data for which we will merge stuff....<jupyter_code>cwd = os.getcwd()

trade_data = pq.read_table(cwd + "\\data\\total_trade_data_2015.parquet").to_pandas()

trade_data["time"] = pd.to_datetime(trade_data.time)

trade_data.set_index(["area_fips", "time"],inplace = True)
trade_data.head()

exposure = pd.qcut(trade_data.xs('2018-12-1', level=1).tariff, 4 ,labels = False)

most_exposed = exposure[exposure == 3].index.tolist()

trade_data.loc[most_exposed].xs('2018-12-1', level=1).tariff.mean()<jupyter_output><empty_output><jupyter_text>Most exposed guys should always be about 6.46. If not you got wrong dataset.This is ultra-clunky. Should fix in the future. But it takes names (which is how the BLS files are written) and then will map them into a datatime value.<jupyter_code>years = [14,15,16,17,18,19]

empl_time_dict = {}

foo = 0

for xxx in years:
    
    
    year = int("20" + str(xxx))
    
    
    empl_time_dict["year_{0}".format(year)] = {"January Employment":dt.datetime(year,1,1),
                 "February Employment":dt.datetime(year,2,1),
                 "March Employment":dt.datetime(year,3,1),
                 "April Employment":dt.datetime(year,4,1),
                 "May Employment":dt.datetime(year,5,1),
                 "June Employment":dt.datetime(year,6,1),
                 "July Employment":dt.datetime(year,7,1),
                 "August Employment":dt.datetime(year,8,1),
                 "September Employment":dt.datetime(year,9,1),
                 "October Employment":dt.datetime(year,10,1),
                 "November Employment":dt.datetime(year,11,1),
                 "December Employment":dt.datetime(year,12,1),}
clistQ1 = ['Area\nCode','NAICS','Qtr','January Employment', 'February Employment',
       'March Employment', 'Total Quarterly Wages', 'Average Weekly Wage','Own',"Area Type"]<jupyter_output><empty_output><jupyter_text>Step 2: DownloadThis downloads the ``.zip`` files for which we can grab the data. They are all in excell format.<jupyter_code>url = "https://data.bls.gov/cew/data/files/2014/xls/2014_all_county_high_level.zip"
# This will read in the annual, single file. It's big, but has all we want...

r = requests.get(url) 

# convert bytes to zip file  
bls_q2014 = zf.ZipFile(io.BytesIO(r.content)) 
bls_q2014.extractall(cwd + "\\bls_files")
url = "https://data.bls.gov/cew/data/files/2015/xls/2015_all_county_high_level.zip"
# This will read in the annual, single file. It's big, but has all we want...

r = requests.get(url) 

# convert bytes to zip file  
bls_q2015 = zf.ZipFile(io.BytesIO(r.content)) 
bls_q2015.extractall(cwd + "\\bls_files")
url = "https://data.bls.gov/cew/data/files/2016/xls/2016_all_county_high_level.zip"
# This will read in the annual, single file. It's big, but has all we want...

r = requests.get(url) 

# convert bytes to zip file  
bls_q2016 = zf.ZipFile(io.BytesIO(r.content)) 
bls_q2016.extractall(cwd + "\\bls_files")

url = "https://data.bls.gov/cew/data/files/2017/xls/2017_all_county_high_level.zip"
# This will read in the annual, single file. It's big, but has all we want...

r = requests.get(url) 

# convert bytes to zip file  
bls_q2017 = zf.ZipFile(io.BytesIO(r.content)) 
bls_q2017.extractall(cwd + "\\bls_files")

url = "https://data.bls.gov/cew/data/files/2018/xls/2018_all_county_high_level.zip"

r = requests.get(url) 

bls_q2018 = zf.ZipFile(io.BytesIO(r.content)) 
bls_q2018.extractall(cwd + "\\bls_files")
url = "https://data.bls.gov/cew/data/files/2019/xls/2019_all_county_high_level.zip"

r = requests.get(url) 

bls_q2019 = zf.ZipFile(io.BytesIO(r.content)) 
bls_q2019.extractall(cwd + "\\bls_files")
bls_q2019.filelist<jupyter_output><empty_output><jupyter_text>Step 3: Clean and Shape it      Below is a function that takes in an excell sheet and does what we want to it. Then below we will work through a for loop over all the sheets.<jupyter_code>def bls_quarter_cat(df,cat,var_name, time_dict):
    
    # Take only private

    df = df[df["Own"] == 5] 

# Take aggregate

    #df = df[df["NAICS"] == 101] # Take goods producing 
    
    df = df[df["NAICS"] == cat] # Take all employment in all sectors

# Take only counties 
    df = df[df["Area Type"] == "County"] 

    df.rename({"Area\nCode": "GEOFIPS"},axis = 1, inplace = True)

    df["GEOFIPS"] = df["GEOFIPS"].astype(int)

    df.set_index("GEOFIPS", inplace = True)

    df = df.reindex(trade_data.index.get_level_values(0).unique().astype(int).tolist())
    # this part makes sure that we are always getting same counties as in the trade data set
    # if we don't do this, things are floating around. The issue I think is for some of the really
    # small counties, the employment in the catagory is withehld, so when we grab this stuff,
    # the county goes missing

    df = df.iloc[:,[13,14,15]].reset_index()
    # This grabs only values we want, i.e. the employment for that quarter. So for example,
    # in Q1, 13 = January, 14 = Febuary, 15 = March. And so forth for Q2...

    df = df.melt("GEOFIPS")

    df.replace(time_dict,inplace = True)

    df.rename({"variable":"time", "value": var_name, "GEOFIPS": "area_fips"}, axis = 1, inplace = True)
    
    df["area_fips"] = df["area_fips"].astype(str)
    
    df.set_index(["area_fips", "time"], inplace = True)
    
    return df
def clean_bls_quarter(excell_sheet, time_dict):

    foo = pd.read_excel(excell_sheet, sheet_name = "US_St_Cn_MSA")
    
    naics_cats = [10,101,1021]
    
    var_name = {10: "emp_all", 101:"emp_gds", 1021: "emp_n_gds"}
    
    cat_dict = {}
    
    for xxx in naics_cats:
        
        foo_df = bls_quarter_cat(foo, xxx, var_name[xxx], time_dict)
        
        cat_dict["cat_{0}".format(xxx)] = foo_df
                
    df = cat_dict["cat_10"].merge(cat_dict["cat_101"], left_index = True, right_index = True)
    
    df = df.merge(cat_dict["cat_1021"], left_index = True, right_index = True)
    
    #df = df.merge(cat_dict["cat_1012"], left_index = True, right_index = True)

    return df<jupyter_output><empty_output><jupyter_text>---<jupyter_code>root_name = cwd + "\\bls_files\\"

root_name = root_name + "allhlcn18"

file_name = root_name + "1" + ".xlsx"

print(file_name)
df = pd.read_excel(file_name, sheet_name = "US_St_Cn_MSA")

#test = bls_quarter_cat(df,1021,"retail", empl_time_dict["year_2018"])
clean_bls_quarter(file_name, empl_time_dict["year_2018"])<jupyter_output><empty_output><jupyter_text>--- Then given the function above, work through the file list.<jupyter_code>df = pd.DataFrame([])

############################################################################
root_name = cwd + "\\bls_files\\"

root_name = root_name + "allhlcn14"

quarter = ["1","2","3","4"]

for item in quarter:
    
    file_name = root_name + item + ".xlsx"
    
    df = df.append(clean_bls_quarter(file_name,empl_time_dict["year_2014"]))

############################################################################
root_name = cwd + "\\bls_files\\"

root_name = root_name + "allhlcn15"

quarter = ["1","2","3","4"]

for item in quarter:
    
    file_name = root_name + item + ".xlsx"
    
    df = df.append(clean_bls_quarter(file_name,empl_time_dict["year_2015"]))

############################################################################

root_name = cwd + "\\bls_files\\"

root_name = root_name + "allhlcn16"

quarter = ["1","2","3","4"]

for item in quarter:
    
    file_name = root_name + item + ".xlsx"
    
    df = df.append(clean_bls_quarter(file_name,empl_time_dict["year_2016"]))
    
############################################################################

root_name = cwd + "\\bls_files\\"

root_name = root_name + "allhlcn17"

quarter = ["1","2","3","4"]

for item in quarter:
    
    file_name = root_name + item + ".xlsx"
    
    df = df.append(clean_bls_quarter(file_name,empl_time_dict["year_2017"]))
    
############################################################################  
root_name = cwd + "\\bls_files\\"

root_name = root_name + "allhlcn18"

quarter = ["1","2","3","4"]

for item in quarter:
    
    file_name = root_name + item + ".xlsx"
    
    df = df.append(clean_bls_quarter(file_name,empl_time_dict["year_2018"]))
    
############################################################################  
root_name = cwd + "\\bls_files\\"

root_name = root_name + "allhlcn19"

quarter = ["1"]

for item in quarter:
    
    file_name = root_name + item + ".xlsx"
    
    df = df.append(clean_bls_quarter(file_name,empl_time_dict["year_2019"]))<jupyter_output><empty_output><jupyter_text>Then just checksome stuff, reshape, then save for the analysis part. Note how this is working (again clunky), if you want the goods employment, uncomment out that. If you want total employment do the other one.<jupyter_code>df.sort_values(["area_fips", "time"], inplace = True)
df.dtypes
trade_employ = trade_data.merge(df, left_index = True, right_index = True, how = "right")
# This is a place to be mindfull about time period, if we want 
# do left if you just want to conform with the trade data
trade_employ.total_employment.fillna(method='bfill', inplace = True)

trade_employ.tariff.fillna(method='bfill', inplace = True)<jupyter_output><empty_output><jupyter_text>Now I'm going to layer in some stuff from the Census. First, bring in the 2010 population and the rural share<jupyter_code>dfrural = pd.read_excel("https://www2.census.gov/geo/docs/reference/ua/County_Rural_Lookup.xlsx", skiprows=[0,1,2],
                       nrows = 3142)

dfrural["area_fips"] = dfrural["2015 GEOID"].astype(int)

trade_employ.reset_index(inplace = True)

trade_employ["area_fips"] = trade_employ["area_fips"].astype(int)

trade_employ = trade_employ.merge(dfrural[["area_fips", "2010 Census \nPercent Rural",
                             "2010 Census Total Population"]], left_on = "area_fips", right_on = "area_fips",
                                 how = "left")

trade_employ.set_index(["area_fips", "time"],inplace = True)

trade_employ.rename({"2010 Census \nPercent Rural": "rural_share",
            "2010 Census Total Population": "2010_population"}, inplace = True, axis = 1)

trade_employ["rural_share"] = 0.01*trade_employ["rural_share"]<jupyter_output><empty_output><jupyter_text>Then lets bring in the stuff from the ACS5, so population and income level in 2017.<jupyter_code>my_api_key = '<KEY>'
# This is my api_key

c = Census(my_api_key)
# This will create an object c which has methods associated with it.
# We will see  these below.

type(c) 
# Per the discussion below, try c.tab and see the options. 

code = ("NAME","B01001_001E","B19013_001E") # Same Codes:

county_2017 = pd.DataFrame(c.acs5.get(code, 
                                         {'for': 'county:*'}, year=2017))
                                         # Same deal, but we specify county then the wild card
                                         # On the example page, there are ways do do this, only by state
        
county_2017 = county_2017.rename(columns = {"B01001_001E":"2017_population", "B19013_001E":"2017_income"})

county_2017["GEOFIPS"] = (county_2017["state"] + county_2017["county"]).astype(int)

county_2017["2017_population"] = county_2017["2017_population"].astype(float)

county_2017["2017_income"] = county_2017["2017_income"].astype(float)
county_2017.set_index(["GEOFIPS"], inplace = True)
trade_employ.reset_index(inplace = True)

trade_employ = trade_employ.merge(county_2017[["2017_income","2017_population"]],
                                  left_on = "area_fips", right_index = True, how = "left")

#trade_employ.drop(labels = "index", axis = 1, inplace = True)

trade_employ.set_index(["area_fips", "time"],inplace = True)
trade_employ.loc[10003].tail(10)
file_path = os.getcwd() + "\\data\\trade_employment_retail.parquet"

pq.write_table(pa.Table.from_pandas(trade_employ.reset_index()), file_path)

#file_path = os.getcwd() + "\\data\\trade_employment_all.parquet"

#pq.write_table(pa.Table.from_pandas(trade_employ.reset_index()), file_path)
trade_employ.dtypes
trade_employ.loc[10003]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1617396458472253, 'fraction_numerical': 0.03615618285544936, 'mean_word_length': 4.012253991830672, 'pattern_counts': {'":': 20, '<': 38, '<?xml version=': 0, '>': 38, 'https://': 11, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '346479', 'license': 'MIT', 'n_tokens_mistral': 5090, 'n_tokens_neox': 4550, 'n_words': 1105, 'path': 'bls_quarterly_county.ipynb', 'repo_name': 'mwaugh0328/consumption_and_tradewar', 'size': '13532'}