<jupyter_start><jupyter_text>Traffic count data processing Traffic studies often start with traffic count data that consist of vehicle type, cardinal direction and time a vehicle or pedestrian moved through an intersection.  This notebook will walk you through some ways to use python to clean and organize the data for further analysis. *If you do not want the walk through, just skip to the last cell*  Python modules that need to be imported<jupyter_code># imports
import pandas as pd
import numpy as np
import datetime
from datetime import timedelta
import ast<jupyter_output><empty_output><jupyter_text>Move sample data into a pandas dataframe  Below is a picture of the sample data.  *sample data consists of three columns1. movment type (mvmt) 2. timestamp (time) 3. type of vehical (veh_type)  Read the CSV file to a pandas dataframe<jupyter_code># define a variable that contains the raw string file path to where you saved the csv file
# * using the raw format is probably not nessecary for Mac users
file_path = r'.....\ETL-Traffic_Counts\sample-data\csv_sample.csv'
# Read the csv into a panda's dataframe
df_0 = pd.read_csv(file_path)
df_0.head()<jupyter_output><empty_output><jupyter_text>Define some helper functions that takes the sample traffic count data in a dataframe and modifies the pedestrian movements and re-labels the semi truck vehicle type to just Truck<jupyter_code># map_mvmt_labels takes a dataframe and re-labels the pedestrian movement types so that they are conform to standard labeling 
def map_mvmt_labels(df):
    # Mvmt type map
    direction_map = {'EBL':'EBL', 'EBR':'EBR', 'EBT':'EBT', 'NBL':'NBL', 'NBR':'NBR', 'NBT':'NBT',
                     'SBL':'SBL', 'SBR':'SBR', 'SBT':'SBT', 'WBL':'WBL', 'WBR':'WBR', 'WBT':'WBT',
                     'PNER':'WBT', 'PNWL':'SBL', 'PSEL':'NBL', 'PSER':'NBT', 'PSWL':'EBL',
                     'PSWR':'EBT'}
    
    # applies mvmt type map 
    mapped_mvmt = []
    for direction in df.mvmt:
        mapped_mvmt.append(direction_map[direction])
    df.mvmt = mapped_mvmt
    
    return df

# map_veh_type_labels takes a dataframe and re-labels semi-trucks to Truck
def map_veh_type_labels(df):
    # veh_type map that converts semi trucks to trucks
    veh_map = {'car':'Car', 'truck':'Truck', 'semi trucks':'Truck', 'Pedestrian':'Pedestrian'}
    # applies veh_type map
    mapped_veh_type = []
    for veh in df.veh_type:
        mapped_veh_type.append(veh_map[veh])
    df.veh_type = mapped_veh_type
    return df<jupyter_output><empty_output><jupyter_text>Clean the data by applying the helper functions and converting the 'time' series to datetime objects<jupyter_code># apply all the helper functions and convert time to datetime
def clean_data(df):
    df.index = pd.to_datetime(df.time)
    df = map_mvmt_labels(df)
    df = map_veh_type_labels(df)
    return df
# clean the sample data
cleaned_df = clean_data(df_0)
cleaned_df.head()<jupyter_output><empty_output><jupyter_text>Once the dataframe has been cleaned, it needs to be reshaped so that it is formatted like the industry standard traffic count data table<jupyter_code># takes the sample dataframe and returns a numpy array of ones with the same length
def count_veh(df):
    return np.ones(len(df.time))

# re-format the cleaned data using a pivot table
def create_table(df):
    df['count']= count_veh(df)
    table = df.pivot_table(index=df.index, columns = ['mvmt','veh_type'], values = 'count').fillna(value=0)
    return table
table = create_table(cleaned_df)
table.head()<jupyter_output><empty_output><jupyter_text>The formatted table has a row for almost every second, which is not very useful. Therefore, the next step is to bin the table into larger time chunks. The industry standard is to have data in hour chunks, which is the default value.<jupyter_code>def re_sample_table(table, time_interval = '60T'):
    return table.resample(time_interval).sum()
# resample the time to 1hr chunks
final_table = re_sample_table(table)
print(final_table)<jupyter_output>mvmt                  EBL        EBR        EBT                    NBL  \
veh_type              Car Truck  Car Truck  Car Pedestrian Truck   Car   
time                                                                     
2007-10-13 06:00:00   7.0   1.0  6.0   1.0  6.0        7.0   2.0   7.0   
2007-10-13 07:00:00   5.0   3.0  3.0   2.0  6.0        0.0   4.0  10.0   
2007-10-13 08:00:00   4.0   2.0  8.0   4.0  4.0        4.0   0.0  16.0   
2007-10-13 09:00:00  10.0   3.0  4.0   3.0  7.0        6.0   2.0   9.0   
2007-10-13 10:00:00   1.0   1.0  5.0   1.0  7.0        3.0   2.0   9.0   

mvmt                                  ...   SBR        SBT        WBL        \
veh_type            Pedestrian Truck  ...   Car Truck  Car Truck  Car Truck   
time                                  ...                                     
2007-10-13 06:00:00        1.0   4.0  ...   6.0   3.0  6.0   1.0  6.0   1.0   
2007-10-13 07:00:00        0.0   7.0  ...   9.0   1.0  3.0   2.0  3.0   0.0   
2007-10-13 0[...]<jupyter_text>The above table is the final product, an industry standard vehical count data table***  *If you are not interested in the individual steps you can simply run the cell below*<jupyter_code># imports
import pandas as pd
import numpy as np
import datetime

# helper functions
# map_mvmt_labels takes a dataframe and re-labels the pedestrian movement types so that they are conform to standard labeling 
def map_mvmt_labels(df):
    # Mvmt type map
    direction_map = {'EBL':'EBL', 'EBR':'EBR', 'EBT':'EBT', 'NBL':'NBL', 'NBR':'NBR', 'NBT':'NBT',
                     'SBL':'SBL', 'SBR':'SBR', 'SBT':'SBT', 'WBL':'WBL', 'WBR':'WBR', 'WBT':'WBT',
                     'PNER':'WBT', 'PNWL':'SBL', 'PSEL':'NBL', 'PSER':'NBT', 'PSWL':'EBL',
                     'PSWR':'EBT'}
    
    # applies mvmt type map 
    mapped_mvmt = []
    for direction in df.mvmt:
        mapped_mvmt.append(direction_map[direction])
    df.mvmt = mapped_mvmt
    
    return df

# map_veh_type_labels takes a dataframe and re-labels semi-trucks to Truck
def map_veh_type_labels(df):
    # veh_type map that converts semi trucks to trucks
    veh_map = {'car':'Car', 'truck':'Truck', 'semi trucks':'Truck', 'Pedestrian':'Pedestrian'}
    # applies veh_type map
    mapped_veh_type = []
    for veh in df.veh_type:
        mapped_veh_type.append(veh_map[veh])
    df.veh_type = mapped_veh_type
    return df
# apply all the helper functions and convert time to datetime
def clean_data(df):
    df.index = pd.to_datetime(df.time)
    df = map_mvmt_labels(df)
    df = map_veh_type_labels(df)
    return df 
# takes the sample dataframe and returns a numpy array of ones with the same length
def count_veh(df):
    return np.ones(len(df.time))

# re-format the cleaned data using a pivot table
def create_table(df):
    df['count']= count_veh(df)
    table = df.pivot_table(index=df.index, columns = ['mvmt','veh_type'], values = 'count').fillna(value=0)
    return table
def re_sample_table(table, time_interval = '60T'):
    return table.resample(time_interval).sum()
    
#
def etl_veh_counts():
    file_path = input("Enter your vehical counts file path: ")
    # Read the csv into a panda's dataframe
    df = pd.read_csv(file_path)
    df.index = pd.to_datetime(df.time)
    df = map_mvmt_labels(df)
    df = map_veh_type_labels(df)
    table = create_table(df)
    final_table = re_sample_table(table)
    return final_table

    
etl_veh_counts()<jupyter_output>Enter your vehical counts file pathC:\Users\aregel\Documents\ETL-Traffic_Counts\sample-data\csv_sample.csv
{'fraction_non_alphanumeric': 0.10373006458415711, 'fraction_numerical': 0.030974034532753394, 'mean_word_length': 3.004221635883905, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '593403', 'license': 'MIT', 'n_tokens_mistral': 2684, 'n_tokens_neox': 2510, 'n_words': 788, 'path': 'ETL-veh_counts.ipynb', 'repo_name': 'mtchem/ETL-Traffic_Counts', 'size': '7587'}