<jupyter_start><jupyter_text>Creates CSVs for each group of trades as specified in the code below<jupyter_code>import pandas as pd
import numpy
import matplotlib.pyplot as plt
# First I checked to see the different Trades that were listed in the existing CSVs to 
# get a sense of what they were called

filename = '2017-18data/WorkforceUtilizationSummaryReport2017-2018201708.csv'
big_df = pd.read_csv(filename)

trades_list = big_df['CONSTRUCTION_TRADE'].unique() # Creating a list of the different unique values in the 
# Construction Trade column

trades_list
# Here I created larger groupings for the several different existing trades. I grouped trades by similarity and gave
# the jobs names of broader categories for ease of readibility and analysis
replacements = {'INSULATOR (PIPES & TANKS)' : 'INSUALTOR', 'BACKHOE/FRONT-END LOADER' : 'LOADER',
               'DRIVER / GROUNDMAN CDL' : 'DRIVER', 'EQUIPMENT OPERATOR (Class B CDL)' : 'EQUIP_OPERATOR',
               'EQUIPMENT OPERATOR (Class A CDL)' : 'EQUIP_OPERATOR', 'PUMP OPERATOR (CONCRETE)' : 'EQUIP_OPERATOR',
               'LASER BEAM OPERATOR' : 'EQUIP_OPERATOR','LABORER: HAZARDOUS WASTE/ASBESTOS REMOVER' : 'LABORER', 
                'LABORER: MASON TENDER' : 'LABORER','DEMO: WRECKING LABORER' : 'LABORER', 
                'LABORER: CEMENT FINISHER TENDER' : 'LABORER', 'ROLLER/SPREADER/MULCHING MACHINE' : 'ROLLER', 
                'TRAILERS FOR EARTH MOVING EQUIPMENT' : 'TRAILERS', 'IRONWORKER/WELDER' : 'IRONWORKER', 
                'IRONWORKER/WELDER' : 'IRONWORKER', 'SHEETMETAL WORKER' : 'IRONWORKER', 
                'BRICK/STONE/ARTIFICIAL MASONRY' : 'MASONRY', 'CEMENT MASONRY/PLASTERING' : 'MASONRY',
                'PAINTER / TAPER (BRUSH,REPAINT)' : 'PAINTER', 'PIPEFITTER & STEAMFITTER' : 'PIPEFITTER',
                'PLUMBERS & GASFITTERS' : 'PLUMBER', 'FIRE ALARM REPAIR / MAINTENANCE' : 'MAINTENANCE', 
                'HVAC (TESTING AND BALANCING - AIR)' : 'HVAC', 'HVAC (TESTING AND BALANCING - WATER)' : 'HVAC',
                'ADS/SUBMERSIBLE PILOT' : 'PILOT', 'JOURNEYMAN LINEMAN' : 'LINEMAN', 
                'FIELD ENG.PARTY CHIEF-BLDG,SITE,HVY/HWY' : 'ENGINEER', 'PAINTER / TAPER (BRUSH, NEW)' : 'PAINTER', 
               'LABORER: CARPENTER TENDER' : 'LABORER', 'SPRINKLER FITTER' : 'MAINTENANCE',
                'ASBESTOS REMOVER - PIPE / MECH. EQUIPT' : 'ASBESTOS', 'ASBESTOS INSULATOR (PIPES & TANKS)' : 'ASBESTOS',
               'HOISTING ENGINEER/CRANES/GRADALLS' : 'ENGINEER', 'OPERATING ENGINEERS' : 'ENGINEER',
               'MARBLE MASONS, TILELAYERS & TERRAZZO MECH' : 'MASONRY', 'FENCE & GUARD RAIL ERECTOR' : 'MAINTENANCE',
               'GLAZIER (GLASS PLANK/AIR BARRIER/INTERIOR SYSTEMS)' : 'GLAZIER', 
               'PAINTER (SPRAY OR SANDBLAST,REPAINT)' : 'PAINTER', 'FLOORCOVERER' : 'MAINTENANCE',
               'ELEVATOR CONSTRUCTOR' : 'CONSTRUCTOR', 'HVAC (DUCTWORK)' : 'HVAC', 'LABORER: TREE REMOVER' : 'LABORER',
               'HVAC (ELECTRICAL CONTROLS)' : 'HVAC', 'PAINTER (SPRAY OR SANDBLAST, NEW)' : 'PAINTER',
               'ELEVATOR CONSTRUCTOR HELPER' : 'CONSTRUCTOR', 'HVAC MECHANIC' : 'HVAC',
               'BRICK/PLASTER/CEMENT MASON' : 'MASONRY', 'TELECOMMUNICATION TECHNICIAN' : 'TECHNICIAN',
               'TELEDATA WIREMAN/INSTALLER/TECHNICIAN' : 'TECHNICIAN',
               'FIELD ENG.INST.PERSON-BLDG,SITE,HVY/HWY' : 'ENGINEER','MARBLE & TILE FINISHERS' : 'MASONRY',
               'PAINTER BRUSH NEW' : 'PAINTER', 'SPECIALIZED EARTH MOVING EQUIP < 35 TONS' : 'EQUIP_OPERATOR',
               'LABORER: MULTI-TRADE TENDER' : 'LABORER', 'OTHER POWER DRIVEN EQUIPMENT - CLASS II' : 'EQUIP_OPERATOR',
               'MECHANICS MAINTENANCE' : 'MAINTENANCE', 'FORK LIFT/CHERRY PICKER' : 'EQUIP_OPERATOR',
               'OILER (OTHER THAN TRUCK CRANES,GRADALLS)' : 'OILER', 'DEMO: CONCRETE CUTTER/SAWYER' : 'EQUIP_OPERATOR',
               'MILLWRIGHT (Zone 1)' : 'MILLWRIGHT', 'TRACTORS/PORTABLE STEAM GENERATORS' : 'EQUIP_OPERATOR',
               'PAINTER BRUSH REPAINT' : 'PAINTER', 'FIELD ENG.ROD PERSON-BLDG,SITE,HVY/HWY' : 'ENGINEER',
               'BULLDOZER/GRADER/SCRAPER' : 'EQUIP_OPERATOR', 'MARBLE- TILE-TERRAZZO MECHANIC' : 'MASONRY',
               'FIRE ALARM INSTALLER' : 'MAINTENANCE', 'GENERATOR/LIGHTING PLANT/HEATERS' : 'EQUIP_OPERATOR',
               'ROOFER SLATE / TILE / PRECAST CONCRETE' : 'MASONRY', 'ASPHALT RAKER' : 'RAKER',
               'CAISSON & UNDERPINNING LABORER' : 'LABORER', 'VAC-HAUL' : 'HVAC', 'PIPELAYER' : 'PIPEFITTER',
               'SPECIALIZED EARTH MOVING EQUIP > 35 TONS' : 'EQUIP_OPERATOR'}

filename = '2017-18data/WorkforceUtilizationSummaryReport2017-2018201708.csv'
big_df = pd.read_csv(filename)
big_df['CONSTRUCTION_TRADE'].replace(replacements, inplace=True) # Created one big Dataframe of all the CSVs we have

years = ['2017']
months = ['10', '11']

for y in years: 
    for m in months:
        filename = '2017-18data/WorkforceUtilizationSummaryReport2017-2018' + y + m +'.csv'
        df = pd.read_csv(filename)
        df['CONSTRUCTION_TRADE'].replace(replacements, inplace=True)
        big_df = pd.concat([big_df, df])
        
# For each month of every year, it replaces all the Trade values in the CONSTRUCTION_TRADE colunmn with the 
# appropriate broad category, then concatenates it to the larger Dataframe of all the data

# I repeat this down below for every year
years = ['2018']
months = ['01', '02', '05', '06', '08', '10', '11', '12']

for y in years:
    for m in months:
        filename = '2017-18data/WorkforceUtilizationSummaryReport2017-2018' + y + m +'.csv'
        df = pd.read_csv(filename)
        df['CONSTRUCTION_TRADE'].replace(replacements, inplace=True)
        big_df = pd.concat([big_df, df])
years = ['2019', '2020']
months = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']

for y in years:
    for m in months:
        filename = y + 'data/WorkforceUtilizationSummaryReport' + y + m +'.csv'
        df = pd.read_csv(filename)
        df['CONSTRUCTION_TRADE'].replace(replacements, inplace=True)
        big_df = pd.concat([big_df, df])
big_df
# Here we can see how we have simplified the different Trades across the years into a more readable version

trades_list = big_df['CONSTRUCTION_TRADE'].unique()

trades_list
grouped = big_df.groupby(big_df.CONSTRUCTION_TRADE) # This groups the different records in the Dataframe by their trade

for trade in trades_list: # For each trade, the loop creates its own CSV based on the grouping
    df = grouped.get_group(trade)
    filename = r'WorkforceUtilizationReport-' + trade + '.csv'
    df.to_csv(filename, index = False)
len(trades_list)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1264007171671896, 'fraction_numerical': 0.019871507545196475, 'mean_word_length': 3.3439325113562623, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 6, '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': '408498', 'license': 'MIT', 'n_tokens_mistral': 2625, 'n_tokens_neox': 2417, 'n_words': 602, 'path': 'WGBH-DCAMM/DCAMM/Trade_Separation.ipynb', 'repo_name': 'gyoon18/summer2021internship', 'size': '6693'}