{"id":4103,"date":"2024-08-17T20:52:35","date_gmt":"2024-08-18T00:52:35","guid":{"rendered":"https:\/\/www.econai.tech\/?page_id=4103"},"modified":"2024-08-18T07:04:33","modified_gmt":"2024-08-18T11:04:33","slug":"data-cleaning","status":"publish","type":"page","link":"https:\/\/tomomitanaka.ai\/?page_id=4103","title":{"rendered":"Data Cleaning"},"content":{"rendered":"\n

Data cleaning is a crucial step in the data science pipeline. It involves preparing and transforming raw data into a format suitable for analysis. <\/p>\n\n\n\n

In this post, we\u2019ll explore how to clean and prepare data from Google Analytics Sample Dataset in BigQuery<\/a> for various machine learning tasks. <\/p>\n\n\n\n

We\u2019ll go through key steps such as handling missing values, feature scaling, and encoding categorical variables, using Python and popular data processing libraries.<\/p>\n\n\n\n


You can find the complete code<\/a> in my GitHub repository.<\/p>\n\n\n\n

The Importance of Data Cleaning<\/h3>\n\n\n\n

This data cleaning process transforms our raw, messy data into a structured format that’s ready for analysis. <\/p>\n\n\n\n

By flattening nested structures, handling missing data, converting data types, and encoding categorical variables, we’ve addressed many common data quality issues.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Clean data allows us to<\/div>\n
    \n
  1. Perform more accurate analyses<\/li>\n\n\n\n
  2. Build more reliable machine learning models<\/li>\n\n\n\n
  3. Make data-driven decisions with confidence<\/li>\n\n\n\n
  4. Save time in the long run by avoiding errors and inconsistencies<\/li>\n<\/ol>\n<\/div>\n\n\n\n

    The Dataset<\/h3>\n\n\n\n

    We’re working with Google Analytics data, which is stored in BigQuery. This dataset contains rich information about website visits, including user interactions, traffic sources, and transaction data. <\/p>\n\n\n\n

    However, it comes with its own set of challenges, including nested data structures.<\/p>\n\n\n\n

    The Data Cleaning Process<\/h3>\n\n\n\n

    Let’s break down our data cleaning process into several key steps.<\/p>\n\n\n\n

    <\/p>\n\n\n\n

    1. Data Extraction<\/h4>\n\n\n\n

    First, we extract the data from BigQuery using a SQL query:<\/p>\n\n\n\n

    Python<\/span><\/path><\/path><\/svg><\/span>
    query = <\/span>"""<\/span><\/span>\nSELECT *<\/span><\/span>\nFROM bigquery-public-data.google_analytics_sample.ga_sessions_20170801<\/span><\/span>\nLIMIT 10000<\/span><\/span>\n"""<\/span><\/span>\ndf = client.query(query).to_dataframe()<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/p>\n\n\n\n
    2. Flattening Nested Columns<\/h4>\n\n\n\n
    One of the challenges with this dataset is that some columns contain nested data. We flatten these nested structures to make the data easier to work with:<\/p>\n\n\n\n
    Python<\/span><\/path><\/path><\/svg><\/span>
    def<\/span> <\/span>flatten_nested_columns<\/span>(<\/span>df<\/span>):<\/span><\/span>\n    nested_columns = [<\/span>'totals'<\/span>, <\/span>'trafficSource'<\/span>, <\/span>'device'<\/span>, <\/span>'geoNetwork'<\/span>]<\/span>\n    flattened_dfs = []<\/span>\n<\/span>\n    <\/span>for<\/span> col <\/span>in<\/span> nested_columns:<\/span><\/span>\n        flattened = pd.json_normalize(df[col])<\/span><\/span>\n        flattened.columns = [<\/span>f<\/span>'<\/span>{<\/span>col<\/span>}<\/span>_<\/span>{<\/span>subcol<\/span>}<\/span>'<\/span> <\/span>for<\/span> subcol <\/span>in<\/span> flattened.columns]<\/span>\n        flattened_dfs.append(flattened)<\/span><\/span>\n<\/span>\n    df_flattened = pd.concat([df.drop(nested_columns, <\/span>axis<\/span>=<\/span>1<\/span>)] + flattened_dfs, <\/span>axis<\/span>=<\/span>1<\/span>)<\/span><\/span>\n    <\/span><\/span>\n    <\/span>return<\/span> df_flattened<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/p>\n\n\n\n
    3. Handling Missing Data<\/h4>\n\n\n\n
    Missing data can skew our analysis, so we handle it using imputation techniques:<\/p>\n\n\n\n
    Python<\/span><\/path><\/path><\/svg><\/span>
    numeric_imputer = SimpleImputer(<\/span>strategy<\/span>=<\/span>'median'<\/span>)<\/span><\/span>\ndf_cleaned[numeric_columns] = numeric_imputer.fit_transform(df_cleaned[numeric_columns])<\/span><\/span>\n<\/span>\ncategorical_imputer = SimpleImputer(<\/span>strategy<\/span>=<\/span>'most_frequent'<\/span>)<\/span><\/span>\ndf_cleaned[categorical_columns] = categorical_imputer.fit_transform(df_cleaned[categorical_columns])<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/p>\n\n\n\n
    4. Data Type Conversion<\/h4>\n\n\n\n
    We ensure that our data types are consistent and appropriate for analysis:<\/p>\n\n\n\n
    Python<\/span><\/path><\/path><\/svg><\/span>
    df_cleaned[<\/span>'date'<\/span>] = pd.to_datetime(df_cleaned[<\/span>'date'<\/span>], <\/span>format<\/span>=<\/span>'%Y%m<\/span>%d<\/span>'<\/span>)<\/span><\/span>\n<\/span>\nfor<\/span> col <\/span>in<\/span> df_cleaned.columns:<\/span><\/span>\n    <\/span>if<\/span> col <\/span>in<\/span> numeric_columns:<\/span><\/span>\n        df_cleaned[col] = pd.to_numeric(df_cleaned[col], <\/span>errors<\/span>=<\/span>'coerce'<\/span>)<\/span><\/span>\n    <\/span>elif<\/span> col <\/span>in<\/span> categorical_columns:<\/span><\/span>\n        df_cleaned[col] = df_cleaned[col].astype(<\/span>str<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/p>\n\n\n\n
    5. Feature Scaling<\/h4>\n\n\n\n
    Scaling is essential for machine learning algorithms that rely on distance metrics, such as linear regression and SVMs. We\u2019ll standardize our numeric features using StandardScaler.<\/p>\n\n\n\n
    Python<\/span><\/path><\/path><\/svg><\/span>
    scaler = StandardScaler()<\/span><\/span>\ndf_cleaned[numeric_columns] = scaler.fit_transform(df_cleaned[numeric_columns])<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/p>\n\n\n\n
    6. Encoding Categorical Variables<\/h4>\n\n\n\n
    Categorical variables need to be converted into numerical format before they can be used in machine learning models. We\u2019ll use LabelEncoder for this purpose.<\/p>\n\n\n\n
    Python<\/span><\/path><\/path><\/svg><\/span>
    le = LabelEncoder()<\/span><\/span>\nfor<\/span> col <\/span>in<\/span> categorical_columns:<\/span><\/span>\n    df_cleaned[col] = le.fit_transform(df_cleaned[col])<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/p>\n\n\n\n
    7. Extracting Hit-Level Data<\/h4>\n\n\n\n
    In addition to session-level data, we can extract hit-level data, which provides more granular information about user interactions.<\/p>\n\n\n\n
    Python<\/span><\/path><\/path><\/svg><\/span>
    def<\/span> <\/span>extract_hit_level_data<\/span>(<\/span>df<\/span>):<\/span><\/span>\n    <\/span># Flatten the main dataframe<\/span><\/span>\n    df_flat = pd.json_normalize(df.to_dict(<\/span>'records'<\/span>))<\/span><\/span>\n    <\/span><\/span>\n    <\/span># Extract hit-level data<\/span><\/span>\n    hits_data = df_flat.apply(<\/span>lambda<\/span> <\/span>row<\/span>: pd.json_normalize(row[<\/span>'hits'<\/span>]), <\/span>axis<\/span>=<\/span>1<\/span>).explode().reset_index()<\/span><\/span>\n    <\/span><\/span>\n    <\/span># Rename 'index' to 'session_id' for clarity<\/span><\/span>\n    hits_data = hits_data.rename(<\/span>columns<\/span>={<\/span>'index'<\/span>: <\/span>'session_id'<\/span>})<\/span><\/span>\n    <\/span><\/span>\n    <\/span># Add session-level identifiers to the hit-level data<\/span><\/span>\n    hits_data[<\/span>'fullVisitorId'<\/span>] = df_flat[<\/span>'fullVisitorId'<\/span>].repeat(df_flat[<\/span>'hits'<\/span>].str.len()).reset_index(<\/span>drop<\/span>=<\/span>True<\/span>)<\/span><\/span>\n    hits_data[<\/span>'visitId'<\/span>] = df_flat[<\/span>'visitId'<\/span>].repeat(df_flat[<\/span>'hits'<\/span>].str.len()).reset_index(<\/span>drop<\/span>=<\/span>True<\/span>)<\/span><\/span>\n    hits_data[<\/span>'date'<\/span>] = pd.to_datetime(df_flat[<\/span>'date'<\/span>].repeat(df_flat[<\/span>'hits'<\/span>].str.len()).reset_index(<\/span>drop<\/span>=<\/span>True<\/span>), <\/span>format<\/span>=<\/span>'%Y%m<\/span>%d<\/span>'<\/span>)<\/span><\/span>\n    <\/span><\/span>\n    <\/span># Select and rename relevant columns<\/span><\/span>\n    columns_to_keep = [<\/span><\/span>\n        <\/span>'session_id'<\/span>, <\/span>'fullVisitorId'<\/span>, <\/span>'visitId'<\/span>, <\/span>'date'<\/span>,<\/span><\/span>\n        <\/span>'hitNumber'<\/span>, <\/span>'time'<\/span>, <\/span>'hour'<\/span>, <\/span>'minute'<\/span>,<\/span><\/span>\n        <\/span>'isEntrance'<\/span>, <\/span>'isExit'<\/span>, <\/span>'page.pagePath'<\/span>, <\/span>'page.pageTitle'<\/span>,<\/span><\/span>\n        <\/span>'eventInfo.eventCategory'<\/span>, <\/span>'eventInfo.eventAction'<\/span>, <\/span>'eventInfo.eventLabel'<\/span>,<\/span><\/span>\n        <\/span>'transaction.transactionId'<\/span>, <\/span>'transaction.transactionRevenue'<\/span>,<\/span><\/span>\n        <\/span>'item.productName'<\/span>, <\/span>'item.productCategory'<\/span>, <\/span>'item.productSKU'<\/span>, <\/span>'item.itemRevenue'<\/span><\/span>\n    ]<\/span>\n    <\/span><\/span>\n    hits_df = hits_data[columns_to_keep].copy()<\/span><\/span>\n    <\/span><\/span>\n    <\/span>return<\/span> hits_df<\/span><\/span>\n<\/span>\nhit_level_df = extract_hit_level_data(df)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/p>\n\n\n\n
    Conclusion<\/strong><\/h3>\n\n\n\n
    In this blog post, we walked through the process of cleaning and preparing the Google Analytics Sample Dataset for machine learning tasks. <\/p>\n\n\n\n
    By flattening nested columns, handling missing values, scaling features, and encoding categorical variables, we prepared the data for advanced analysis, such as predicting e-commerce sales, customer segmentation, and more. <\/p>\n\n\n\n
    This cleaned dataset can now be used to train and evaluate machine learning models, leading to actionable insights and data-driven decisions.<\/p>\n\n\n\n
    <\/p>\n\n\n\n
    <\/p>\n","protected":false},"excerpt":{"rendered":"
    Data cleaning is a crucial step in the data science pipeline. It involves preparing and transforming raw data into a format suitable for analysis. In this post, we\u2019ll explore how to clean and prepare data from Google Analytics Sample Dataset in BigQuery for various machine learning tasks. We\u2019ll go through key steps such as handling<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":3822,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-4103","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/4103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4103"}],"version-history":[{"count":35,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/4103\/revisions"}],"predecessor-version":[{"id":4187,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/4103\/revisions\/4187"}],"up":[{"embeddable":true,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/3822"}],"wp:attachment":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}