<jupyter_start><jupyter_text>Data Cleaning[www.vexpower.com](www.vexpower.com)<jupyter_code># Set the right folder

import sys
import os
import pandas as pd
import numpy as np
import datetime as dt

if not os.path.isdir("mmm"):
    module_path = os.path.abspath(os.path.join('..'))
    if module_path not in sys.path:
        sys.path.append(module_path)

import mmm<jupyter_output><empty_output><jupyter_text>Facebook AdsLoad and clean Facebook Ads data<jupyter_code># Load example Facebook Campaign data
file_name = "Goolybib-Campaigns-Mar-19-2021-Apr-17-2021.csv"
data = pd.read_csv('../data/'+file_name)
data.head()
from mmm.select import get_cols_containing
from mmm.clean import rename_column

spend_col = get_cols_containing(data, 'Amount Spent')[0] # needed because column name contains currency

df = data.copy()
rename_column(df, 'Reporting Starts', 'date')
rename_column(df, spend_col, 'facebook_cost')
rename_column(df, 'Campaign Name', 'campaign_name')

df = df[['date', 'facebook_cost', 'campaign_name']]
df.head()
from mmm.clean import unstack_data

df = unstack_data(df, metric_column='facebook_cost', unstack_column='campaign_name')

df.head()
df.to_csv('../data/facebook_clean.csv', index=False)<jupyter_output><empty_output><jupyter_text>Google AdsLoad and clean Google Ads data<jupyter_code># Load example Google Campaign data
file_name = "Day (1).csv"
data = pd.read_csv('../data/'+file_name)
data.head()
data = pd.read_csv('../data/'+file_name, skiprows=2)
data.head()
from mmm.clean import rename_column

df = data.copy()
rename_column(df, 'Day', 'date')
rename_column(df, 'Cost', 'google_cost')
rename_column(df, 'Campaign', 'campaign_name')

df = df[['date', 'google_cost', 'campaign_name']]
df.head()
from mmm.clean import unstack_data

df = unstack_data(df, metric_column='google_cost', unstack_column='campaign_name')

df.head()
df.to_csv('../data/google_clean.csv', index=False)<jupyter_output><empty_output><jupyter_text>Google AnalyticsLoad and clean Google Analytics data<jupyter_code># Load example Google Analytics data
file_name = "GoolyBib-trafficsources-all-traffic.csv"
data = pd.read_csv('../data/'+file_name)
data.head()
data = pd.read_csv('../data/'+file_name, skiprows=6)
data.head()
from mmm.clean import rename_column

df = data.copy()
rename_column(df, 'Date', 'date')
rename_column(df, 'Source/Medium', 'source_medium')
rename_column(df, 'Revenue', 'revenue')

df = df[['date', 'revenue', 'source_medium']]
df.head()
from mmm.clean import unstack_data

df = unstack_data(df, metric_column='revenue', unstack_column='source_medium')

df.head()
df.to_csv('../data/revenue_clean.csv', index=False)<jupyter_output><empty_output><jupyter_text>TV DataTranspose and interpolate TV data<jupyter_code># Load example TV data
file_name = "TV Vexpower - Sheet1.csv"
data = pd.read_csv('../data/'+file_name)
data.head()
# Clean data
df = data.copy()
df.dropna(thresh=2, inplace=True) # drop NaN rows
df.dropna(thresh=2, axis=1, inplace=True) # drop NaN cols
df.head()
from mmm.clean import transpose_data

# Transpose data

df = transpose_data(df)
df.drop(columns=['Total'], inplace=True)
df.head()
df.to_csv('../data/tv_clean.csv', index=False)
# the first column needs to be week number
# we also need the column in yyyy-ww format

date_col = df.columns[0]
df[date_col] = df[date_col].apply(lambda x: f"2020-{x.split()[-1]}") # year-weeknum
df.head()
df.tail()
df = df[:-1] # cut off total at bottom
df.tail()
from mmm.clean import interpolate_weekly_data

# Interpolate the weekly data to daily

df = old_df.copy()
df = interpolate_weekly_data(df)
df.head()
df.to_csv('../data/tv_clean_daily.csv', index=False)<jupyter_output><empty_output><jupyter_text>Search Trends DataLoad and clean search trends data[https://trends.google.com/trends/](https://trends.google.com/trends/)<jupyter_code># Load example search trends data
file_name = "multiTimeline.csv"
data = pd.read_csv('../data/'+file_name)
data.head()
data = pd.read_csv('../data/'+file_name, skiprows=2)
data.head()
from mmm.clean import handle_search_trends_data

# Clean data
df = data.copy()
    
df = handle_search_trends_data(df)

df.head()
df.to_csv('../data/search_trends_clean.csv', index=False)<jupyter_output><empty_output><jupyter_text>COVID Mobility DataLoad and clean COVID mobility data[https://www.google.com/covid19/mobility/](https://www.google.com/covid19/mobility/)<jupyter_code># Load example COVID mobility data
file_name = "2020_US_Region_Mobility_Report.csv"
data = pd.read_csv('../data/'+file_name)
data.head()
data.shape
data.sample(10)
region = "Texas"
texas = data[data['sub_region_1'] == region]
texas.shape
texas.sample(10)
texas.head()
texas = texas[texas['sub_region_2'].isnull()]
texas.shape
texas.sample(10)
texas[texas.columns[8:]].plot(figsize=(15,8));
from mmm.clean import handle_covid_data

df = handle_covid_data(data, sub_region_1="Florida")

print(df.shape)
df.head()
df.plot(figsize=(15,8));
# Load and append 2021 data
file_name = "2021_US_Region_Mobility_Report.csv"
data2 = pd.read_csv('../data/'+file_name)
data2.head()
data2.shape
df2 = handle_covid_data(data2, sub_region_1="Florida")
df2.shape
df2.head()
merged = df.append(df2)
print(merged.shape)
merged.sample(10)
merged.reset_index(inplace=True, drop=True)
merged.plot(figsize=(15,8));
df.to_csv('../data/covid_mobility_clean.csv', index=False)<jupyter_output><empty_output><jupyter_text>Weather DataLoad and clean weather data[https://power.larc.nasa.gov/data-access-viewer/](https://power.larc.nasa.gov/data-access-viewer/)<jupyter_code># Load weather data
file_name = "POWER_SinglePoint_Daily_20200426_20201022_040d74N_74d40W_8aaec005.csv"
data = pd.read_csv('../data/'+file_name)
data.head()
data = pd.read_csv('../data/'+file_name, skiprows=16)
data.head()
from mmm.clean import handle_weather_data

df = data.copy()

df = handle_weather_data(df)

df.sample(10)
df.to_csv('../data/weather_clean.csv', index=False)<jupyter_output><empty_output><jupyter_text>Holidays DataLoad holidays and create dummies<jupyter_code># Load holidays data
file_name = "Holidays - Sheet1.csv"
data = pd.read_csv('../data/'+file_name)
data.head()
from mmm.clean import create_holiday_dummies

df = data.copy()

df = create_holiday_dummies(df)

df.tail()
df.to_csv('../data/holidays_clean.csv', index=False)<jupyter_output><empty_output><jupyter_text>Date Ranges (Zodiac)Add dummy variables for important date ranges(this was from a April Fools campaign, but can be used for any date ranges)<jupyter_code># Load star signs (or other important date ranges)
file_name = "star-signs - Sheet1.csv"
data = pd.read_csv('../data/'+file_name)
data.head()
from mmm.clean import create_date_range_dummies

df = data.copy()

df = create_date_range_dummies(df)

df.head()
df.to_csv('../data/dates_clean.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14916397770607215, 'fraction_numerical': 0.015987092989146377, 'mean_word_length': 6.387865655471289, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 6, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '111717', 'license': 'MIT', 'n_tokens_mistral': 2645, 'n_tokens_neox': 2541, 'n_words': 497, 'path': 'notebooks/Data Cleaning.ipynb', 'repo_name': 'jamesaphoenix/VEX-MMM', 'size': '6818'}