<jupyter_start><jupyter_text>Data PreparationClean the data and combine the five versions of the dataset together into a single CSV  Step 1: Import Libraries<jupyter_code># Import Libraries
from pathlib import Path
import pandas as pd<jupyter_output><empty_output><jupyter_text>Step 2: Import CSVs into DataFrames  a) Identify paths to raw data<jupyter_code># identify path to raw data
csvpath_v5 = Path('../raw_data/vehicles_v5.csv')
csvpath_v6 = Path('../raw_data/vehicles_v6.csv')
csvpath_v7 = Path('../raw_data/vehicles_v7.csv')
csvpath_v9 = Path('../raw_data/vehicles_v9.csv')
csvpath_v10 = Path('../raw_data/vehicles_v10.csv')<jupyter_output><empty_output><jupyter_text>b) Load raw data into dataframes<jupyter_code># load datasets into DataFrames
vehicles_v5_df = pd.read_csv(csvpath_v5)
vehicles_v6_df = pd.read_csv(csvpath_v6)
vehicles_v7_df = pd.read_csv(csvpath_v7)
vehicles_v9_df = pd.read_csv(csvpath_v9)
vehicles_v10_df = pd.read_csv(csvpath_v10)<jupyter_output><empty_output><jupyter_text>Step 3: Prepare Data for Analysis  a) Create "**uid**" column for index by parsing "**url**" column<jupyter_code># create 'uid' column based on parse of 'url' column
vehicles_v5_df['uid'] = vehicles_v5_df['url'].apply(lambda x: x.split('.html')[0][-10:])
vehicles_v6_df['uid'] = vehicles_v6_df['url'].apply(lambda x: x.split('.html')[0][-10:])
vehicles_v7_df['uid'] = vehicles_v7_df['url'].apply(lambda x: x.split('.html')[0][-10:])
vehicles_v9_df['uid'] = vehicles_v9_df['url'].apply(lambda x: x.split('.html')[0][-10:])
vehicles_v10_df['uid'] = vehicles_v10_df['url'].apply(lambda x: x.split('.html')[0][-10:])<jupyter_output><empty_output><jupyter_text>b) Drop columns irrelevant to analysis<jupyter_code># drop irrelevant columns from each version of the dataset

# (oldest) VERSION 5: url/image_url
vehicles_v5_df.drop(columns='url',inplace=True)
vehicles_v5_df.drop(columns='image_url',inplace=True)

# VERSION 6: url/city_url/image_url
vehicles_v6_df.drop(columns='url',inplace=True)
vehicles_v6_df.drop(columns='city_url',inplace=True)
vehicles_v6_df.drop(columns='image_url',inplace=True)

# VERSION 7: url/city_url/image_url
vehicles_v7_df.drop(columns='url',inplace=True)
vehicles_v7_df.drop(columns='city_url',inplace=True)
vehicles_v7_df.drop(columns='image_url',inplace=True)

# VERSION 9: id/url/region_url/image_url/county/state
vehicles_v9_df.drop(columns='id',inplace=True)
vehicles_v9_df.drop(columns='url',inplace=True)
vehicles_v9_df.drop(columns='region_url',inplace=True)
vehicles_v9_df.drop(columns='image_url',inplace=True)
vehicles_v9_df.drop(columns='county',inplace=True)
vehicles_v9_df.drop(columns='state',inplace=True)

# VERSION 10: id/url/region_url/image_url/county/state
vehicles_v10_df.drop(columns='id',inplace=True)
vehicles_v10_df.drop(columns='url',inplace=True)
vehicles_v10_df.drop(columns='region_url',inplace=True)
vehicles_v10_df.drop(columns='image_url',inplace=True)
vehicles_v10_df.drop(columns='county',inplace=True)
vehicles_v10_df.drop(columns='state',inplace=True)<jupyter_output><empty_output><jupyter_text>c) Rename '**description**' column to '**desc**' in versions 9/10<jupyter_code># rename 'description' to 'desc'
vehicles_v9_df = vehicles_v9_df.rename(columns={'description':'desc'})
vehicles_v10_df = vehicles_v10_df.rename(columns={'description':'desc'})<jupyter_output><empty_output><jupyter_text>d) Set index to '**uid**' column<jupyter_code># set 'uid' as index
vehicles_v5_df.set_index('uid', inplace=True)
vehicles_v6_df.set_index('uid', inplace=True)
vehicles_v7_df.set_index('uid', inplace=True)
vehicles_v9_df.set_index('uid', inplace=True)
vehicles_v10_df.set_index('uid', inplace=True)<jupyter_output><empty_output><jupyter_text>Step 4: Normalize Columns Across Versions of the Dataset  a) Verify size of the versions of the dataset<jupyter_code>print(f'VERSION 5:\t{len(vehicles_v5_df.columns.values)} columns\n\n{vehicles_v5_df.columns.values}\n')
print(f'VERSION 6:\t{len(vehicles_v6_df.columns.values)} columns\n\n{vehicles_v6_df.columns.values}\n')
print(f'VERSION 7:\t{len(vehicles_v7_df.columns.values)} columns\n\n{vehicles_v7_df.columns.values}\n')
print(f'VERSION 9:\t{len(vehicles_v9_df.columns.values)} columns\n\n{vehicles_v9_df.columns.values}\n')
print(f'VERSION 10:\t{len(vehicles_v10_df.columns.values)} columns\n\n{vehicles_v10_df.columns.values}\n')<jupyter_output>VERSION 5:	18 columns

['city' 'price' 'year' 'manufacturer' 'make' 'condition' 'cylinders'
 'fuel' 'odometer' 'title_status' 'transmission' 'VIN' 'drive' 'size'
 'type' 'paint_color' 'lat' 'long']

VERSION 6:	19 columns

['city' 'price' 'year' 'manufacturer' 'make' 'condition' 'cylinders'
 'fuel' 'odometer' 'title_status' 'transmission' 'VIN' 'drive' 'size'
 'type' 'paint_color' 'desc' 'lat' 'long']

VERSION 7:	19 columns

['city' 'price' 'year' 'manufacturer' 'make' 'condition' 'cylinders'
 'fuel' 'odometer' 'title_status' 'transmission' 'VIN' 'drive' 'size'
 'type' 'paint_color' 'desc' 'lat' 'long']

VERSION 9:	19 columns

['region' 'price' 'year' 'manufacturer' 'model' 'condition' 'cylinders'
 'fuel' 'odometer' 'title_status' 'transmission' 'VIN' 'drive' 'size'
 'type' 'paint_color' 'desc' 'lat' 'long']

VERSION 10:	20 columns

['region' 'price' 'year' 'manufacturer' 'model' 'condition' 'cylinders'
 'fuel' 'odometer' 'title_status' 'transmission' 'VIN' 'drive' 'size'
 'type' 'paint[...]<jupyter_text>b) Add missing columns to versions of the dataset<jupyter_code># add timestamp from data versions were scraped

# data was scraped in October of 2018, unlike other dates was posted online November 21st 2018;
# assumption made to be last day of the month in October to cover entire month,
# but still listed to be October since only post stating date in content description
vehicles_v5_df['posting_date'] = '2018-10-31'
vehicles_v5_df['desc'] = None
vehicles_v6_df['posting_date'] = '2019-06-09'
vehicles_v7_df['posting_date'] = '2019-07-14'
vehicles_v9_df['posting_date'] = '2021-04-19'<jupyter_output><empty_output><jupyter_text>**NOTES:***Generate Timestamp String:*- VERSION 5: date derived from content description [here](https://www.kaggle.com/austinreese/craigslist-carstrucks-data/version/5)- VERSION 6: date taken from version timestamp at [here](https://www.kaggle.com/austinreese/craigslist-carstrucks-data/version/6)- VERSION 7: date taken from version timestamp at [here](https://www.kaggle.com/austinreese/craigslist-carstrucks-data/version/7)- VERSION 9: date taken from version timestamp at [here](https://www.kaggle.com/austinreese/craigslist-carstrucks-data/version/9)**REMINDER:** *VERSION 8 didn't have any data associated with it***REMINDER:** *VERSION 10 already contains 'posting_date' value for nearly all entries but the time needs to be stripped to include only the date*  c) Verify size of each version of the dataset before creating 'raw_data_combined.csv'<jupyter_code>print(f'VERSION 5:\t{len(vehicles_v5_df.columns.values)} columns\n\n{vehicles_v5_df.columns.values}\n')
print(f'VERSION 6:\t{len(vehicles_v6_df.columns.values)} columns\n\n{vehicles_v6_df.columns.values}\n')
print(f'VERSION 7:\t{len(vehicles_v7_df.columns.values)} columns\n\n{vehicles_v7_df.columns.values}\n')
print(f'VERSION 9:\t{len(vehicles_v9_df.columns.values)} columns\n\n{vehicles_v9_df.columns.values}\n')
print(f'VERSION 10:\t{len(vehicles_v10_df.columns.values)} columns\n\n{vehicles_v10_df.columns.values}\n')<jupyter_output>VERSION 5:	20 columns

['city' 'price' 'year' 'manufacturer' 'make' 'condition' 'cylinders'
 'fuel' 'odometer' 'title_status' 'transmission' 'VIN' 'drive' 'size'
 'type' 'paint_color' 'lat' 'long' 'posting_date' 'desc']

VERSION 6:	20 columns

['city' 'price' 'year' 'manufacturer' 'make' 'condition' 'cylinders'
 'fuel' 'odometer' 'title_status' 'transmission' 'VIN' 'drive' 'size'
 'type' 'paint_color' 'desc' 'lat' 'long' 'posting_date']

VERSION 7:	20 columns

['city' 'price' 'year' 'manufacturer' 'make' 'condition' 'cylinders'
 'fuel' 'odometer' 'title_status' 'transmission' 'VIN' 'drive' 'size'
 'type' 'paint_color' 'desc' 'lat' 'long' 'posting_date']

VERSION 9:	20 columns

['region' 'price' 'year' 'manufacturer' 'model' 'condition' 'cylinders'
 'fuel' 'odometer' 'title_status' 'transmission' 'VIN' 'drive' 'size'
 'type' 'paint_color' 'desc' 'lat' 'long' 'posting_date']

VERSION 10:	20 columns

['region' 'price' 'year' 'manufacturer' 'model' 'condition' 'cylinders'
 'fuel' 'odometer[...]<jupyter_text>Step 5: Output 'raw_data_combined.csv'  a) Combine versions of the dataset into one<jupyter_code># combine versions into single dataframe
all_dataframes = [
    vehicles_v5_df,
    vehicles_v6_df,
    vehicles_v7_df,
    vehicles_v9_df,
    vehicles_v10_df
]

raw_data_combined_df = pd.concat(all_dataframes, join='outer')<jupyter_output><empty_output><jupyter_text>b) Verify size of the combined dataframe<jupyter_code>raw_data_combined_df<jupyter_output><empty_output><jupyter_text>c) Write combined dataframe to csv<jupyter_code>raw_data_combined_df.to_csv(
    Path('../Resources/raw_data_combined_v2.csv'),
    index_label='uid'
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1509288776519732, 'fraction_numerical': 0.02594261844564142, 'mean_word_length': 8.016848364717543, 'pattern_counts': {'":': 0, '<': 51, '<?xml version=': 0, '>': 51, 'https://': 4, 'lorem ipsum': 0, 'www.': 4, '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': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '660425', 'license': 'MIT', 'n_tokens_mistral': 3612, 'n_tokens_neox': 3472, 'n_words': 657, 'path': 'notebooks/data_prep.ipynb', 'repo_name': 'fintechProgramAcct/project_01', 'size': '9097'}