<jupyter_start><jupyter_text>Atlas State EV Registration DataThis script consolidates multiple EV registration .CSV files from the Atlas State EV website.   A final dataset with all states is created and saved to disk.   The source data is inconsistent and has data quality issues.  This script corrects issues when possible and cleans or creates new columns. **Final Columns:**  'Make', 'Model', 'Model Year', 'Registration Expiration Date', 'Registration Valid Date', 'Registration Valid Month', 'State', 'Technology', 'ZIP Code'https://github.com/emskiphoto/Alpaca-Strategy-DevelopmentCopyright 2021 <NAME>November 5, 2021  Reference:https://www.atlasevhub.com/materials/state-ev-registration-data/data<jupyter_code>import pandas as pd
import time
import matplotlib.pyplot as plt
import seaborn as sns
from numpy import histogram, logical_and, NAN
from numpy.random import choice

%config Completer.use_jedi = False 
pd.options.display.max_rows = 200<jupyter_output><empty_output><jupyter_text>Which URLS and .csv files are available?<jupyter_code># static list created for all states available on Atlas as of 10/25/2021
urls_raw = ['https://www.atlasevhub.com/public/dmv/ca_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/co_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/ct_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/fl_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/mt_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/mi_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/mn_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/nj_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/ny_ev_registrations_public.csv',
          'https://www.atlasevhub.com/public/dmv/or_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/tn_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/tx_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/vt_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/va_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/wa_ev_registrations_public.csv',
           'https://www.atlasevhub.com/public/dmv/wi_ev_registrations_public.csv']
urls = {url.split('dmv/')[-1].split('_ev')[0].upper():url for url in urls_raw}
urls<jupyter_output><empty_output><jupyter_text>Preview a table from one state<jupyter_code>df = pd.read_csv(urls[list(urls.keys())[-1]], nrows=100)
df<jupyter_output><empty_output><jupyter_text>Read all csv files from list of URLs and store in dictionary<jupyter_code>%%time
csvs_by_state = {}
global total_records
global n_states
total_records = 0
n_states = len(urls.keys())
count = 1
for state in urls:
    t0 = time.time()
    print(state.upper())
    temp = pd.read_csv(urls[state], parse_dates=['Registration Expiration Date', 'Registration Valid Date'])
    csvs_by_state[state.upper()] = temp
    t1 = time.time()
    total = t1-t0
    total_records += temp.shape[0] 
    print(f'{count} of {n_states}:  "{state}" has {temp.shape[0]:,} rows and required {round(total,1)} seconds to load')
    count+=1
print(f'\n***  There are {total_records:,} records for {n_states} states ***')<jupyter_output>CA<jupyter_text>The Columns used across all 16 state's csv's are not consistent<jupyter_code>csvs_by_state['MN'].head(2)
csvs_by_state['CO'].head(2)<jupyter_output><empty_output><jupyter_text>Identify Columns common to all csv's<jupyter_code>all_csvs_columns = []
for state in csvs_by_state:
    all_csvs_columns.append(list(csvs_by_state[state].columns))
    
common_cols = list(set.intersection(*[set(list) for list in all_csvs_columns]))
common_cols
csv_columns_dict = dict(zip(csvs_by_state.keys(), all_csvs_columns))
for state in csvs_by_state:
    print(state)
    display(csvs_by_state[state].head(2))
all_csvs_columns<jupyter_output><empty_output><jupyter_text>Only a few columns are found in all of the states csv files - which column names are more frequent?<jupyter_code>all_columns = [i[x] for i in all_csvs_columns for x in range(len(i))]
pd.Series(all_columns).value_counts()<jupyter_output><empty_output><jupyter_text>Format desired columns to create a single comprehensive dataset<jupyter_code># csvs_by_state_backup = csvs_by_state.copy()
# csvs_by_state = csvs_by_state_backup.copy()<jupyter_output><empty_output><jupyter_text>Registration Valid DateAre all states reporting this value and is date format converted to datetime?  Count number of invalid (NaN) dates are present in Registration Valid Date for each state<jupyter_code>nan_registration_valid_dates = {}
nan_reg_valid_and_expiration_dates = {}
nan_model_year_and_expiration_dates = {}
for state in csvs_by_state:
    nan_valid_dates = pd.to_datetime(csvs_by_state[state]['Registration Valid Date'], errors='ignore').isna()
    nan_expiration_dates = pd.to_datetime(csvs_by_state[state]['Registration Expiration Date'], errors='ignore').isna()
#     nan_model_years = csvs_by_state[state]['Model Year'].isna().sum()
    nan_registration_valid_dates[state] = pd.to_datetime(csvs_by_state[state]['Registration Valid Date'], errors='ignore').isna().sum()
    nan_registration_valid_and_expiration_dates =  logical_and(nan_valid_dates, nan_expiration_dates)
#     nan_model_year_and_expiration_dates_ =  np.logical_and(nan_expiration_dates, nan_model_years)
    nan_reg_valid_and_expiration_dates[state] = nan_registration_valid_and_expiration_dates.sum()
#     nan_model_year_and_expiration_dates[state] = nan_registration_valid_and_expiration_dates_.sum()
#     display(pd.to_datetime(csvs_by_state[state]['Registration Valid Date']).isna().sum())
nan_registration_valid_dates
nan_reg_valid_and_expiration_dates<jupyter_output><empty_output><jupyter_text>Correct 'Registration Valid Date'**Logic:** If The 'Registration Expiration Date' is available, set the 'Registration Valid Date' equal to the 'Registration Expiration Date' minus one year.  If 'Registration Expiration Date' is not available (either) _and_ 'Model Year' is available, assume 'Registration Valid Date' is equal to January 01 of the 'Model Year'.  If 'Registration Expiration Date', 'Model Year' and 'Registration Valid Date' are not available, randomly assign dates based on the weighted distribution (histogram) from the valid dates in that state's dataset.  If none of these options is available, leave the dates as 'NaN/NaT'  Fill missing date using model year and random month based on known distribution of valid registration months<jupyter_code># (pd.to_datetime(csvs_by_state['MN']['Registration Expiration Date'], errors='ignore') - pd.Timedelta(days=365))
# (pd.to_datetime(csvs_by_state['MN']['Registration Expiration Date'], errors='ignore') - pd.Timedelta(days=365)).isna().sum()
nan_reg_date_states = [state for state in nan_registration_valid_dates if nan_registration_valid_dates[state] > 0]
for state in nan_reg_date_states:
    print(state)
    columns = list(csvs_by_state[state].columns)
#     nan_reg_date_records = csvs_by_state[state]['Registration Valid Date'].isna()
    month_weights = histogram(csvs_by_state[state]['Registration Valid Date'].dt.month.dropna(), range=(1,13),bins=12, density=True)[0]
    if 'Registration Valid Date' in columns and 'Model Year' in columns:
        csvs_by_state[state]['Registration Valid Date'] = pd.to_datetime(csvs_by_state[state]\
                         ['Registration Valid Date'], errors='ignore')\
                        .fillna(csvs_by_state[state]['Model Year']\
                        .apply(lambda model_year: pd.Timestamp(model_year,
                       int(choice(range(1,13), size=1, p=month_weights)),1)))
    else:
        print(f'{state}:  Registration Valid Date and Model Year not in columns')<jupyter_output>MN
OR
VT<jupyter_text>Check for remaining NaN values in 'Registration Valid Date'<jupyter_code># nan_registration_valid_dates = {}
for state in csvs_by_state:
    print(state, pd.to_datetime(csvs_by_state[state]['Registration Valid Date']).isna().sum())
#     display(pd.to_datetime(csvs_by_state[state]['Registration Valid Date']).isna().sum())
nan_registration_valid_dates
csvs_by_state[nan_reg_date_states[0]].loc[csvs_by_state[nan_reg_date_states[0]]['Registration Valid Date'].isna()]
for state in nan_registration_valid_dates:
    if nan_registration_valid_dates[state] > 0:
#         display(csvs_by_state[state].loc[csvs_by_state[state]['Registration Valid Date'].isna(),'Registration Valid Date'][:10])
#         if 'Registration Expiration Date'
        display(csvs_by_state[state].loc[csvs_by_state[state]['Registration Valid Date'].isna(),['Registration Valid Date','Registration Expiration Date']][:10])<jupyter_output><empty_output><jupyter_text>Registration Valid MonthIt's assumed that the month of registration is valuable to identify shorter-term trends<jupyter_code>for state in csvs_by_state:
#     print(state)
    csvs_by_state[state]['Registration Valid Month'] = csvs_by_state[state]['Registration Valid Date'].dt.month.astype('int')
for state in csvs_by_state:
    print(state)
    display(csvs_by_state[state].head(2))<jupyter_output>CA<jupyter_text>Statesome states do not include 'State' in their tables.  Obviously the state is known from the title of the source document.  Force a State column if the file does not already include one<jupyter_code>state_abbreviations = pd.read_csv('source/USA State Names_Abbreviations.csv', index_col=0)['State Abbreviation'].to_dict()
state_abbreviations = {v:k for k, v in state_abbreviations.items()}
# state_abbreviations
for state in csvs_by_state:
#     print(state)
    csvs_by_state[state]['State'] = state_abbreviations[state]
#     display('state' in csvs_by_state[state].columns.str.lower().to_list())
#     display(any('state' in col for col in csvs_by_state[state].columns.str.lower().to_list()))
#     display('tate' in csvs_by_state[state].columns.any())
#     display(csvs_by_state[state]['Registration Valid Date'].dtype)
csvs_by_state[state][:2]<jupyter_output><empty_output><jupyter_text>Vehicle Model YearSome tables contain this data, but not all.  The Model year can be derived from the VIN number.  "The model year is encoded in position 10 of North American VIN codes." https://en.wikipedia.org/wiki/Vehicle_identification_number<jupyter_code>vin_file = "source/VIN Model Year.csv"
df_vin_file = pd.read_csv(vin_file, index_col=0)
model_year_by_VIN = dict(df_vin_file['Model Year'])
# model_year_by_VIN
df_vin_file.iloc[13:23]<jupyter_output><empty_output><jupyter_text>The VIN digit or VIN number is not available for all statesASSUME that the 'year' of the Registration Valid Date is also the Model Year of the vehicle.  Note that this may not be accurate, particularly when the original owner sells the vehicle and it is re-registered under another name.**Model Year assignment logic:**If 'Model Year' is present, retain the value and make no changes.   If not, search for "VIN Model Year" column.  If the "VIN Model Year" contains 4-digit numbers only, copy this column as the 'Model Year'.  If the "VIN Model Year" contains a mix of single-character letters or numbers, convert these characters to 'Model Year' using the model_year_by_VIN dictionary.  If the model_year_by_VIN dictionary returns a 'Model Year' that is more than 2 years in the future, replace the value with a Model Year equal to the "Registration Valid Date". If no "VIN Model Year" or "Model Year" column is present, assume the Model Year is equal to the "Registration Valid Date".<jupyter_code># csvs_by_state['WI']
# for state in csvs_by_state:
#     print(state)
#     try:
#         display(csvs_by_state[state]['VIN Model Year'].str.len().value_counts())
#     except:
#         print(f'{state} does not have "VIN Model Year"')
# csvs_by_state_backup = csvs_by_state.copy()
# csvs_by_state = csvs_by_state_backup.copy()
# for state in csvs_by_state:
#     print(state)
#     try:
#         display(csvs_by_state[state]['Model Year'].value_counts().sort_index(ascending=False)[:5])
#     except:
#         print(f'{state} does not have "Model Year"')
max_year = time.localtime().tm_year + 2

for state in csvs_by_state:
    columns = [col.lower() for col in csvs_by_state[state].columns]
    if 'model year' in columns:
        csvs_by_state[state]['Model Year'] = csvs_by_state[state]['Model Year'].astype('int')
        pass
    elif "vin model year" in columns:
        if csvs_by_state[state]['VIN Model Year'].str.len().value_counts().shape[0] == 1:
            model_years = csvs_by_state[state]['VIN Model Year'].map(model_year_by_VIN)
            model_year_mode = int(model_years.mode())
            csvs_by_state[state]['Model Year'] = model_years.fillna(model_year_mode).astype('int')
        elif csvs_by_state[state]['VIN Model Year'].str.len().value_counts().shape[0] > 1:
            print(f'Need to Define logic to process "VIN Model Year" for {state}')
    elif 'model year' not in columns and "vin model year" not in columns:
        model_years = csvs_by_state[state]['Registration Valid Date'].dt.year
        model_year_mode = int(model_years.mode())
        csvs_by_state[state]['Model Year'] = model_years.fillna(model_year_mode).astype('int')
        
    model_year_GT_max_year = csvs_by_state[state]['Model Year'] > max_year
    if model_year_GT_max_year.sum() > 0:
#         csvs_by_state[state].loc[model_year_GT_max_year,'Model Year'].where(csvs_by_state[state]['Model Year']<2023, csvs_by_state[state]['Registration Valid Date'].dt.year, axis=0)
        csvs_by_state[state]['Model Year'] = csvs_by_state[state]['Model Year'].where(~model_year_GT_max_year, csvs_by_state[state]['Registration Valid Date'].dt.year, axis=0)
for state in csvs_by_state:
    print(state)
    try:
        display(csvs_by_state[state]['Model Year'].value_counts().sort_index(ascending=False)[:5])
    except:
        print(f'{state} does not have "Model Year"')
csvs_by_state['NY'].loc[csvs_by_state['NY']['Model Year']>max_year]<jupyter_output><empty_output><jupyter_text>Vehicle Model and MakeIdeally all sources would provide 'Make' and 'Model' fields.  However, some sources provide 'Vehicle Name' instead.  Next steps will parse the 'Vehicle Name' in to 'Make' and 'Model' by using the existing Make and Model information as a search term reference.<jupyter_code>states_w_make_model = []
for state in csv_columns_dict:
    print(state)
    columns = [col.lower() for col in csv_columns_dict[state]]
    if 'make' in columns and 'model' in columns:
        print(f'{state} has both Make and Model')
        states_w_make_model.append(state)
    states_w_out_make_model = list(set(csvs_by_state.keys()).difference(set(states_w_make_model)))
states_w_make_model, states_w_out_make_model
for state in states_w_make_model:
    display(csvs_by_state[state].head(3))
for state in states_w_out_make_model:
    display(csvs_by_state[state].head(3))
df_make_model = pd.concat([csvs_by_state[state][['Make','Model']] for state in states_w_make_model ])
df_make_model = df_make_model.drop_duplicates().sort_values(by=['Make', 'Model']).reset_index(drop=True)
df_make_model<jupyter_output><empty_output><jupyter_text>Almost all Vehicle Names fields are formatted such that the first term is the Make.  In the interest of simplicity, the Vehicle Name will be parsed in a way that the first term prior to the first space is the Make.  All other terms will be interpreted as the Model.<jupyter_code>df_vehicle_names = pd.concat([csvs_by_state[state][['Vehicle Name']] for state in states_w_out_make_model ])
vehicle_names = df_vehicle_names.drop_duplicates().sort_values(by=['Vehicle Name']).reset_index(drop=True)['Vehicle Name'].to_list()
vehicle_names
vehicle_name_makes = sorted(set([name.split(' ')[0] for name in vehicle_names]))
vehicle_name_makes[::3]
vehicle_name_models = sorted(set([' '.join(name.split(' ')[1:]) for name in vehicle_names]))
vehicle_name_models[::3]<jupyter_output><empty_output><jupyter_text>Assign Make and Model columns<jupyter_code>for state in states_w_out_make_model:
    csvs_by_state[state]['Make'] = csvs_by_state[state]['Vehicle Name'].apply(lambda x: x.split(' ')[0])
    csvs_by_state[state]['Model'] = csvs_by_state[state]['Vehicle Name'].apply(lambda x: ' '.join(x.split(' ')[1:]))
for state in states_w_out_make_model:
    display(csvs_by_state[state][['Vehicle Name', 'Make','Model']].head(3))<jupyter_output><empty_output><jupyter_text>Vehicle TechnologyWhat kind of EV is the vehicle?  Possible classifications will be forced to conform to the table below.<jupyter_code>technology_type_file = "source/EV Technology Names_Abbreviations.csv"
df_technology_type = pd.read_csv(technology_type_file)
df_technology_type<jupyter_output><empty_output><jupyter_text>Which states do or do not provide 'technology'?<jupyter_code>states_w_technology = []
for state in csv_columns_dict:
#     print(state)
    columns = [col.lower() for col in csv_columns_dict[state]]
    if 'technology' in columns:
#         print(f'{state} has "Technology"')
        states_w_technology.append(state)
    states_w_out_technology = list(set(csvs_by_state.keys()).difference(set(states_w_technology)))
states_w_technology, states_w_out_technology
for state in states_w_technology:
    display(csvs_by_state[state].head(3))
for state in states_w_out_technology:
    display(csvs_by_state[state].head(3))<jupyter_output><empty_output><jupyter_text>Establish table of technology by Make and Model using state-provided data that _does_ include a technology classification<jupyter_code>df_technology = pd.concat([csvs_by_state[state][['Make','Model','Technology']] for state in states_w_technology])
# technology = df_technology.drop_duplicates().sort_values(by=['Technology']).reset_index(drop=True)['Technology'].to_list()
# technology
df_technology.drop_duplicates().sort_values(by=['Make','Model']).reset_index(drop=True)
df_technology.value_counts()
df_technology_type<jupyter_output><empty_output><jupyter_text>Learn Technology assignments from provided data.<jupyter_code># df_technology_lookup = df_technology.groupby(by=['Make','Model']).mode()
# df_technology_lookup = df_technology.groupby(by=['Make','Model'])['Technology'].agg(pd.Series.mode).to_frame()
df_technology_lookup = df_technology.fillna('BEV').groupby(by=['Make','Model'])\
                ['Technology'].agg(lambda x: x.value_counts().index[0]).to_frame()
technology_replacements = {'FCV': 'FCEV'}
df_technology_lookup['Technology'] = df_technology_lookup['Technology'].replace(
                                    technology_replacements)<jupyter_output><empty_output><jupyter_text>Replace Technology values that do not conform to df_technology_type...if the technology name has too many characters<jupyter_code># Overwrite any 'Technology' classification whose name is longer than the longest
# technology acronym in the df_technology_type table.  Overwrite Technology term with most
# common Technology from df_technology_lookup table.
max_characters_technology = df_technology_type['Technology'].apply(lambda x: len(str(x))).max()
tech_name_GT_max = df_technology_lookup['Technology'].str.len() > max_characters_technology
df_technology_lookup.loc[tech_name_GT_max, 'Technology'] = df_technology_lookup['Technology']\
                                                            .mode().item()
df_technology_lookup
df_technology_lookup.isna().value_counts()<jupyter_output><empty_output><jupyter_text>Assign a Technology Type by Make & Model pairs<jupyter_code>def make_model_tech_lookup(make, model, df):
    idx = pd.IndexSlice
    try:
        tech = df.loc[idx[make, model]].item()
    except:
        tech = df_technology_lookup['Technology'].mode().item()
    return tech
for state in states_w_out_technology:
    # Group state csv to minimize repetitive Make Model lookups
    temp_consolidated = csvs_by_state[state][['Make','Model']].drop_duplicates()\
                        .sort_values(by=['Make','Model'])
#     assign technology to consolidated dataframe
    temp_consolidated['Technology'] = temp_consolidated.apply(lambda x: 
                  make_model_tech_lookup(x['Make'], x['Model'], df_technology_lookup), axis=1)
#     Create dictionary to quickly map technology to make, model pairs
    make_model_tech_dict = {tuple([make, model]):tech for make, model, tech 
                            in temp_consolidated.values}
#     map dictionary values to make, model pair keys in csvs_by_state dataframe
    csvs_by_state[state]['Technology'] = csvs_by_state[state][['Make','Model']].apply(tuple, axis=1).map(make_model_tech_dict)
#     for make, model in 
# temp_consolidated
csvs_by_state[state][['Make','Model','Technology']]
# csvs_by_state_backup_2 = csvs_by_state.copy()
# csvs_by_state = csvs_by_state_backup_2.copy()<jupyter_output><empty_output><jupyter_text>ZIP CodesNot all states report zip codes and there doesn't appear to be an easy way to infer zip code.  Fortunately, CO and MN do report Zip Codes.  Random assignment of Zip Codes based on population by Zip Code is an option, but for now simply return a NaN value for states with no Zip Codehttps://worldpopulationreview.com/zips<jupyter_code>ZIP_code_by_state = {}
for state in csvs_by_state:
    has_zip_code = 'ZIP Code' in csvs_by_state[state].columns
    ZIP_code_by_state[state] = has_zip_code
states_w_zip_code = [state for state in ZIP_code_by_state if ZIP_code_by_state[state]]
states_w_out_zip_code = sorted(list(set(csvs_by_state.keys()).difference(set(states_w_zip_code))))
# ZIP_code_by_state
states_w_zip_code, states_w_out_zip_code
for state in csvs_by_state:
    print(f'**** {state} ****')
    display(csvs_by_state[state].head(2))
    
csvs_by_state['VT']['ZIP Code'].apply(lambda x: str(x)[:5].replace('.','').zfill(5)).astype(str)<jupyter_output><empty_output><jupyter_text>ZIP Code processing1. Is ZIP code NaN?2. Does code have 5 digits?3. Is code an integer (no decimal point)?4. Is final code a 'string', padded with a '0' in the beginning to ensure 5 digits?<jupyter_code>state_nan_zip_dict = {}
for state in states_w_zip_code:
    n_nan_ZIP = csvs_by_state[state]['ZIP Code'].isna().sum()
    print(state, n_nan_ZIP)
    state_nan_zip_dict[state] = n_nan_ZIP
states_w_nan_zip_code = [state for state in state_nan_zip_dict if state_nan_zip_dict[state] > 0]
# str(5055)[:5].replace('.','').zfill(5)
for state in csvs_by_state:
    if state in states_w_out_zip_code:
        csvs_by_state[state]['ZIP Code'] = NAN
#     if state in states_w_nan_zip_code:
    if state in states_w_zip_code:
        ZIP_mode = csvs_by_state[state]['ZIP Code'].mode().item()
        csvs_by_state[state]['ZIP Code'].fillna(ZIP_mode, inplace=True)
#     LT_5_characters = csvs_by_state[state]['ZIP Code'].apply(lambda x: len(str(x)) < 5)
#     keep first 5 characters, remove decimal, and convert to string
        csvs_by_state[state]['ZIP Code'] = csvs_by_state[state]['ZIP Code'].apply(lambda x: str(x)[:5].replace('.','').zfill(5)).astype(str)
# 
for state in csvs_by_state:
    print(f'**** {state} ****')
    display(csvs_by_state[state]['ZIP Code'][:5])<jupyter_output>**** CA ****<jupyter_text>Combine all state datasets in to one  Select and sort final columnsDrop 'DMV ID' - State name is adequate<jupyter_code>all_csvs_columns_final = []
for state in csvs_by_state:
    all_csvs_columns_final.append(list(csvs_by_state[state].columns))
    
common_cols_final = sorted(list(set.intersection(*[set(list) for list in all_csvs_columns_final])))
common_cols_final.remove('DMV ID')
common_cols_final
all_csvs_columns_final = []
for state in csvs_by_state:
    all_csvs_columns_final.append(list(csvs_by_state[state].columns))
    
common_cols_final = sorted(list(set.intersection(*[set(list) for list in all_csvs_columns_final])))
common_cols_final
df = pd.concat([csvs_by_state[state][common_cols_final] for state in csvs_by_state])
df = df.sort_values(by=['State','Model Year']).reset_index(drop=True)
# df['Registration Valid Date'] = pd.to_datetime(df['Registration Valid Date'])

# add Registration Valid Year
df.insert(6,'Registration Valid Year2',df['Registration Valid Date'].dt.year.astype(int))
df['Registration Expiration Date'] = pd.to_datetime(df['Registration Expiration Date'], errors='ignore')
df['Technology'] = df['Technology'].replace(technology_replacements)
df.drop(columns=['DMV ID'], inplace=True)
df.sample(10)
df.head()
df.sample(50)<jupyter_output><empty_output><jupyter_text>Save Complete file to disk<jupyter_code>df.to_csv(f"data/Atlas_EV_complete_{time.strftime('%m-%d-%Y')}.csv")
df_state_year = df.groupby(by=['State','Model Year', 'Make','Model','Technology']).count()
df_state_year
df_state_year.to_csv(f"data/Atlas_EV_state_year_{time.strftime('%m-%d-%Y')}.csv")
for state in df.State.unique():
    df.loc[df['State']==state,:].to_csv(f"data/Atlas_EV_{state}_{time.strftime('%m-%d-%Y')}.csv")
df.Technology.value_counts()
df.Technology = df.Technology.replace('Hyundai Motor Company', 'PHEV')
df.info()
sns.set_context('talk')
plt.figure(figsize=(12,5))
# sns.countplot(data=df.loc[df['Model Year']>2010,:].sample(2000), x='Model Year', hue='Technology')
sns.countplot(data=df.loc[df['Model Year']>2010,:], x='Model Year', hue='Technology')
plt.grid(axis='y')
plt.legend(loc='upper left')
plt.title(f'{total_records:,} Atlas EV Registrations for {n_states} states')
plt.show()
plt.figure(figsize=(12,5))
sns.countplot(data=df.loc[df['Model Year']>2015,:].sample(2000), x='Model Year', hue='Make')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12244659740866114, 'fraction_numerical': 0.006419983658223416, 'mean_word_length': 5.790488771466315, 'pattern_counts': {'":': 0, '<': 112, '<?xml version=': 0, '>': 121, 'https://': 20, 'lorem ipsum': 0, 'www.': 17, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '28', 'dir': 'jupyter-structured-clean-dedup', 'id': '242533', 'license': 'Apache-2.0', 'n_tokens_mistral': 8816, 'n_tokens_neox': 8449, 'n_words': 1853, 'path': 'Atlas State EV Registration Data.ipynb', 'repo_name': 'emskiphoto/Atlas-EV-Hub', 'size': '25711'}