<jupyter_start><jupyter_text>Part 1: Prepare 2017-2018 DataRaw Data Source: https://www.kaggle.com/datasnaek/youtube-new  1.1 Read Files<jupyter_code># Change directory to folder containing csv raw data
raw_data_path = os.path.join("Raw Data", "2017-2018")
os.chdir(raw_data_path)

# Print the current directory 
os.getcwd()
# Get all filenames with csv extension
extension = 'csv'
data_filenames = [i for i in glob.glob('*.{}'.format(extension))]
df_list = []

for file in data_filenames:
    temp_df = pd.read_csv(file, error_bad_lines=False)
    temp_df.drop(columns=['video_error_or_removed'], inplace=True)
    df_list.append(temp_df)
country_list = ["Canada", "Germany", "France", "Great Britain", "India", "Japan", "Korea", "Mexico", "Russia", "United States"]
index = 0

for df in df_list:
    # Add column "country"
    df["country"] = country_list[index]
    index += 1
    
    # Change date format for "trending_date"
    # Apply dummy time since time info not available
    df["trending_date"] = df['trending_date'].apply(lambda x: "20" + x[:2] + "-" + x[-2:] + "-" + x[3:5] + "T00:00:00.000Z")<jupyter_output><empty_output><jupyter_text>1.2 Match Category<jupyter_code># Get all filenames with json extension
extension = 'json'
category_filenames = [i for i in glob.glob('*.{}'.format(extension))]
print(category_filenames)
json_list = []

for file in category_filenames:
    
    with open(file) as f:
        temp_data = json.load(f)
        json_list.append(temp_data)
def extract_category(json_data):
    category_dict = {"category_id": [], "category": []}

    # Add category for id 29 (not available in json file)
    category_dict["category_id"].append(29)
    category_dict["category"].append("Nonprofits & Activism")
    
    # Access the list stored in "items"
    category_list = json_data["items"]

    # Loop through dict in category_list
    for i in range(len(category_list)):

        # Extract id
        category_id = int(category_list[i]["id"])
        category_dict["category_id"].append(category_id)

        # Extract category in nested dict
        category = category_list[i]["snippet"]["title"]
        category_dict["category"].append(category)
        
    # Convert dict to DataFrame
    category_df = pd.DataFrame.from_dict(category_dict)
    
    return category_df
category_df_list = []

for json_data in json_list:
    temp_df = extract_category(json_data)
    category_df_list.append(temp_df)
merged_list = []

for i in range(len(df_list)):
    left_join = pd.merge(df_list[i], category_df_list[i], on ='category_id', how ='left') 
    merged_list.append(left_join)<jupyter_output><empty_output><jupyter_text>1.3 Rearrange Columns<jupyter_code># Create list to store new column position
column_names = ['video_id',
                'trending_date',
                'title',
                'channel_title',
                'category_id',
                'category',
                'country',
                'publish_time',
                'tags',
                'views',
                'likes',
                'dislikes',
                'comment_count',
                'thumbnail_link',
                'comments_disabled',
                'ratings_disabled',
                'description']
# Rearrange column position
for i in range(len(merged_list)):
    merged_list[i] = merged_list[i].reindex(columns=column_names)<jupyter_output><empty_output><jupyter_text>1.4 Combine Files<jupyter_code># Combine all files in the list
combined_csv_1 = pd.concat(merged_list)<jupyter_output><empty_output><jupyter_text>Part 2: Prepare 2020-2021 Data Raw Data Source: https://www.kaggle.com/rsrishav/youtube-trending-video-dataset  2.1 Read Files<jupyter_code># Change directory to folder containing csv raw data
try:
    raw_data_path = os.path.join("Raw Data", "2020-2021")
    os.chdir(raw_data_path)
    
except FileNotFoundError:
    path_parent = os.path.dirname(os.getcwd())
    os.chdir(path_parent)
    raw_data_path = os.path.join("2020-2021")
    os.chdir(raw_data_path)

# Print the current directory 
os.getcwd()
# Get all filenames with csv extension
extension = 'csv'
data_filenames = [i for i in glob.glob('*.{}'.format(extension))]
print(data_filenames)
df_list = []

for file in data_filenames:
    temp_df = pd.read_csv(file) 
    
    # Remove column "channelID"
    temp_df.drop(columns=["channelId"], inplace=True)
    
    # Rename columns to match 2017-2018 dataset
    temp_df.rename(columns={
        "channelTitle": "channel_title",
        "categoryId":  "category_id",
        "view_count": "views",
        "publishedAt":  "publish_time",
        }, inplace=True)
    
    df_list.append(temp_df)
country_list = ["Canada", "Germany", "France", "Great Britain", "India", "Japan", "Korea", "Mexico", "Russia", "United States"]
index = 0

# Add column "country"
for df in df_list:
    df["country"] = country_list[index]
    index += 1<jupyter_output><empty_output><jupyter_text>2.2 Match Category<jupyter_code># Get all filenames with json extension
extension = 'json'
category_filenames = [i for i in glob.glob('*.{}'.format(extension))]
print(category_filenames)
json_list = []

for file in category_filenames:
    
    with open(file) as f:
        temp_data = json.load(f)
        json_list.append(temp_data)
category_df_list = []

for json_data in json_list:
    temp_df = extract_category(json_data)
    category_df_list.append(temp_df)
merged_list = []

for i in range(len(df_list)):
    left_join = pd.merge(df_list[i], category_df_list[i], on ='category_id', how ='left')
    #left_join["category"].fillna("Missing", inplace = True) 
    merged_list.append(left_join)<jupyter_output><empty_output><jupyter_text>2.3 Rearrange Columns<jupyter_code># Create list to store new column position
column_names = ['video_id',
                'trending_date',
                'title',
                'channel_title',
                'category_id',
                'category',
                'country',
                'publish_time',
                'tags',
                'views',
                'likes',
                'dislikes',
                'comment_count',
                'thumbnail_link',
                'comments_disabled',
                'ratings_disabled',
                'description']
# Rearrange column position
for i in range(len(merged_list)):
    merged_list[i] = merged_list[i].reindex(columns=column_names)<jupyter_output><empty_output><jupyter_text>2.4 Combine Files<jupyter_code># Combine all files in the list
combined_csv_2 = pd.concat(merged_list)<jupyter_output><empty_output><jupyter_text>Part 3: Combine All Data<jupyter_code># Combine 2017-2018 & 2020-2021 data
combined_all = pd.concat([combined_csv_1, combined_csv_2])

# Go up one directory
path_parent = os.path.dirname(os.getcwd())
os.chdir(path_parent)

# Export to csv
combined_all.to_csv( "combined_all.csv", index=False, encoding='utf-8-sig')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12467569904871721, 'fraction_numerical': 0.015998846929950995, 'mean_word_length': 3.3945535148828374, 'pattern_counts': {'":': 6, '<': 37, '<?xml version=': 0, '>': 37, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '112722', 'license': 'MIT', 'n_tokens_mistral': 2429, 'n_tokens_neox': 2278, 'n_words': 485, 'path': 'Data Preparation.ipynb', 'repo_name': 'Longannn/MapReduce', 'size': '6938'}