Phase I - Ask

The Task:

Phase II - Prepare

The Data:

Possible issues with the data:

Phase III - Process

-- Drop temp table from previous queries
DROP TABLE IF EXISTS bellabeat.temp;

-- Create the new table to hold the dataset (rename and restructure for each dataset)
-- The composite primary key will prevent duplicate entries,
-- and also eliminate null values in Id or Time.
CREATE TABLE IF NOT EXISTS bellabeat.april_steps_minute(
    Id BIGINT, Time DATETIME, Steps INT, PRIMARY KEY(Id, Time)
    );

-- Create "temporary" table to hold CSV data. Can be used later if INSERT query fails
CREATE TABLE bellabeat.temp (
    Id BIGINT,
    Time TEXT,
    Value INT
);

-- Load the CSV into the temporary table
LOAD DATA INFILE '.April_minuteStepsNarrow_merged.csv'
INTO TABLE bellabeat.temp
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS; -- Ignore headers

-- Add a new column and fill it with converted datetime objects
-- from the strings in the datasets
ALTER TABLE bellabeat.temp ADD newTime DATETIME;
SET SQL_SAFE_UPDATES = 0;
UPDATE bellabeat.temp SET newTime = STR_TO_DATE(Time, '%c/%e/%Y %l:%i:%s %p');
-- Drop the original column and rename the new column to the original name
ALTER TABLE bellabeat.temp DROP COLUMN Time;
ALTER TABLE bellabeat.temp CHANGE newTime Time DATETIME;
SET SQL_SAFE_UPDATES = 1;

-- Add this data to our new table created above
INSERT INTO bellabeat.april_steps_minute (Id, Time, Steps) -- include all columns
SELECT th.Id, th.Time, th.Value
FROM bellabeat.temp AS th
-- Duplicates will automatically be ignored:
ON DUPLICATE KEY UPDATE april_steps_minute.Id = th.Id;

CREATE TABLE IF NOT EXISTS bellabeat.steps(
  Id BIGINT, Time DATETIME, Steps INT, PRIMARY KEY(Id, Time)
  );
    
INSERT INTO bellabeat.steps (Id, Time, Steps)
SELECT Id, Time, Steps
FROM bellabeat.march_steps_minute AS th
ON DUPLICATE KEY UPDATE
    bellabeat.steps.Id = th.Id;

INSERT INTO bellabeat.steps (Id, Time, Steps)
SELECT Id, Time, Steps
FROM bellabeat.april_steps_minute AS th
-- Duplicates ignored again here, as there is some overlap between the time periods
ON DUPLICATE KEY UPDATE
    bellabeat.steps.Id = th.Id;

SELECT DISTINCT(Id) AS activities
FROM bellabeat.activity;
-- 35 rows returned
SELECT DISTINCT(DATE(Time)) AS activities
FROM bellabeat.activity;
-- 62 rows returned
SELECT DISTINCT(Id) AS heartrates
FROM bellabeat.heartrate;
-- 15 rows returned
SELECT DISTINCT(DATE(Time)) AS heartrates
FROM bellabeat.heartrate;
-- 45 rows returned
SELECT DISTINCT(Id) AS sleeps
FROM bellabeat.sleep;
-- 23 rows returned
SELECT DISTINCT(Date) AS sleeps
FROM bellabeat.sleep;
-- 63 rows returned
SELECT DISTINCT(Id) AS cals
FROM bellabeat.calories;
-- 35 rows returned
SELECT DISTINCT(DATE(Time)) AS cals
FROM bellabeat.calories;
-- 62 rows returned
SELECT DISTINCT(Id) AS intensities
FROM bellabeat.intensities;
-- 35 rows returned
SELECT DISTINCT(DATE(Time)) AS intensities
FROM bellabeat.intensities;
-- 62 rows returned
SELECT DISTINCT(Id) AS weight
FROM bellabeat.weight;
-- 13 rows returned
SELECT DISTINCT(Date) AS weight
FROM bellabeat.weight;
-- 44 rows returned
SELECT DISTINCT(Id) AS mets
FROM bellabeat.mets;
-- 35 rows returned
SELECT DISTINCT(DATE(Time)) AS mets
FROM bellabeat.mets;
-- 62 rows returned
SELECT DISTINCT(Id) AS steps
FROM bellabeat.steps;
-- 35 rows returned
SELECT DISTINCT(DATE(Time)) AS steps
FROM bellabeat.steps;
-- 62 rows returned

DROP TABLE IF EXISTS bellabeat.users;
CREATE TABLE IF NOT EXISTS bellabeat.users(id BIGINT PRIMARY KEY, activity VARCHAR(5),
    weekday_activity DOUBLE, weekend_activity DOUBLE, step INT, step_rank VARCHAR(4),
    intensity DOUBLE, int_rank VARCHAR(4), daily_use DOUBLE, daily_rank VARCHAR(4),
    percent_days DOUBLE, day_rank VARCHAR(4), mets DOUBLE, mets_rank VARCHAR(4));

#LOAD IDS
INSERT INTO bellabeat.users(Id)
(SELECT DISTINCT Id FROM bellabeat.steps);

#UPDATE ACTIVITY
SET SQL_SAFE_UPDATES = 0;

WITH ranked_avg AS(
    WITH av AS (
        SELECT 
            AVG(VeryActiveMinutes) AS avg_very,
            AVG(FairlyActiveMinutes) AS avg_fair,
            AVG(LightlyActiveMinutes) AS avg_light,
            AVG(SedentaryActiveMinutes) AS avg_sedentary
        FROM bellabeat.activity
    ),
    daily_avg AS (
        SELECT 
            Id, 
            Date AS day, 
            (VeryActiveMinutes + FairlyActiveMinutes + LightlyActiveMinutes + 
              SedentaryActiveMinutes) AS distance,
            AVG(VeryActiveMinutes) AS very,
            AVG(FairlyActiveMinutes) AS fair,
            AVG(LightlyActiveMinutes) AS light,
            AVG(SedentaryActiveMinutes) AS sedentary
        FROM bellabeat.activity
        GROUP BY Id, Date
    ),
    diffs AS (
        SELECT 
            da.Id,
            AVG(da.distance) AS avg_minute,
            AVG(da.very) AS avg_very,
            AVG(da.fair) AS avg_fair, 
            AVG(da.light) AS avg_light,
            AVG(da.sedentary) AS avg_sedentary,
            -- Calculate the difference from the overall averages
            AVG(da.very) - (SELECT avg_very FROM av) AS diff_very,
            AVG(da.fair) - (SELECT avg_fair FROM av) AS diff_fair,
            AVG(da.light) - (SELECT avg_light FROM av) AS diff_light,
            AVG(da.sedentary) - (SELECT avg_sedentary FROM av) AS diff_sedentary
        FROM daily_avg AS da
        CROSS JOIN av
        GROUP BY da.Id
    )
    SELECT 
        d.Id,
        d.diff_very, d.diff_fair, d.diff_light, d.diff_sedentary,
        CASE
            WHEN d.diff_very > 0 THEN -- very or fairly active
                CASE
                    WHEN d.diff_fair > d.diff_light OR 
                      d.diff_very > d.diff_light THEN 'very'
                    ELSE "fair"
                END
            WHEN d.diff_fair > 0 THEN -- fairly or lightly active
                CASE
                    WHEN d.diff_light > 0 THEN 'fair'
                    ELSE "light"
                END
            ELSE "light"
        END AS ranking,
        d.avg_very, d.avg_fair, d.avg_light, d.avg_sedentary
    FROM diffs AS d
    ORDER BY FIELD(ranking, "very", "fair", "light"), avg_very DESC
    )
UPDATE bellabeat.users AS u
JOIN ranked_avg AS r ON u.Id = r.Id
SET 
    u.activity = r.ranking;

SET SQL_SAFE_UPDATES = 1;

#UPDATE USAGE BY DAY OF WEEK
SET SQL_SAFE_UPDATES = 0;

WITH weeks AS(
    WITH dow AS(
        SELECT DISTINCT(Date),
            CASE
                WHEN WEEKDAY(Date) < 5 THEN "weekday"
                ELSE "weekend"
            END AS dow
        FROM bellabeat.activity
    )
    SELECT Id,
        AVG(
            CASE
                WHEN dow.dow = 'weekday'
                THEN LightlyActiveMinutes + FairlyActiveMinutes + VeryActiveMinutes END
            ) AS weekday_activity,
        AVG(
            CASE
                WHEN dow.dow = 'weekend'
                THEN LightlyActiveMinutes + FairlyActiveMinutes + VeryActiveMinutes END
            ) AS weekend_activity
    FROM bellabeat.activity
    JOIN dow USING(Date)
    GROUP BY Id
    ORDER BY Id
)
UPDATE bellabeat.users AS u
JOIN weeks AS w USING(Id)
SET u.weekday_activity = w.weekday_activity, u.weekend_activity = w.weekend_activity;

SET SQL_SAFE_UPDATES = 1;

#UPDATE DAILY USAGE
SET SQL_SAFE_UPDATES = 0;

WITH ranked_avg AS (
    SELECT Id, per_day,
           NTILE(3) OVER (ORDER BY per_day) AS quartile
    FROM (
        SELECT Id, count(Date) / (SELECT count(distinct(Date)) 
          FROM bellabeat.activity) AS per_day
        FROM bellabeat.activity
        GROUP BY Id
    ) AS daily_use
)
UPDATE bellabeat.users AS u
JOIN ranked_avg AS r USING(Id)
SET
    u.percent_days = r.per_day,
    u.day_rank = 
            CASE
                WHEN r.quartile = 1 THEN "low"
                WHEN r.quartile = 2 THEN "avg"
                WHEN r.quartile = 3 THEN "high"
            END;

SET SQL_SAFE_UPDATES = 1;

#UPDATE USAGE BY DAY OF WEEK
SET SQL_SAFE_UPDATES = 0;

WITH ranked_avg AS (
    SELECT Id, AVG(min) AS avg_min,
           NTILE(3) OVER (ORDER BY AVG(min)) AS quartile
    FROM (
        SELECT Id, Date, VeryActiveMinutes + FairlyActiveMinutes +
                          LightlyActiveMinutes + SedentaryActiveMinutes AS min
        FROM bellabeat.activity
        GROUP BY Id, Date
    ) AS daily_avg
    GROUP BY Id
)
UPDATE bellabeat.users AS u
JOIN ranked_avg AS r USING(Id)
SET 
    u.daily_use = r.avg_min,
    u.daily_rank = CASE
                    WHEN r.quartile = 1 THEN 'low'
                    WHEN r.quartile = 2 THEN 'avg'
                    WHEN r.quartile = 3 THEN 'high'
                END;
SET SQL_SAFE_UPDATES = 1;

SELECT * FROM bellabeat.users ORDER BY FIELD(activity, "very", "fair", "light");

import mysql.connector
import csv
import os


def export_table_to_csv(cursor, table_name, output_dir):
    query = f"SELECT * FROM {table_name}"

    cursor.execute(query)
    rows = cursor.fetchall()

    # Get columns
    column_names = [i[0] for i in cursor.description]

    # Define output path
    output_file = os.path.join(output_dir, f"{table_name}.csv")

    # Write data to CSV file
    with open(output_file, mode='w', newline='', encoding='utf-8') as file:
        writer = csv.writer(file)
        # Write the column names
        writer.writerow(column_names)
        # Write the data
        writer.writerows(rows)


def export_all_tables_to_csv(host, user, password, database, output_dir):
    try:
        # Connect to the MySQL database
        conn = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )

        cursor = conn.cursor()

        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()

        if not os.path.exists(output_dir):
            os.makedirs(output_dir)

        # Export new tables (all that aren't called march... or april...)
        for (table_name,) in tables:
            if "march" not in table_name and "april" not in table_name:
                export_table_to_csv(cursor, table_name, output_dir)
                print(f"Exported {table_name}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()
            print("MySQL connection closed.")

# MySQL credentials:
host = 'xxx.x.x.x'
user = 'root'
password = 'xxxxxxx'
database = 'bellabeat'
output_dir = './csv_exports'

export_all_tables_to_csv(host, user, password, database, output_dir)

# Convert the 'Time's to 'date's (removing the time component)
steps$date <- as.Date(steps$Time)
heartrate$date <- as.Date(heartrate$Time)
calories$date <- as.Date(calories$Time)

# Group by 'date' and 'id', then summarize for each day
daily_steps <- steps %>%
  group_by(id, date) %>%
  summarize(total_steps = sum(Steps, na.rm = TRUE))
daily_heartrate <- heartrate %>% 
  group_by(id, date) %>% 
  summarize(avg_rate = mean(Value, na.rm = TRUE)
            , min_rate = min(Value, na.rm = TRUE),
            .groups = 'drop')
daily_cals <- calories %>%
  group_by(id, date) %>%
  summarize(total_cals = sum(Calories, na.rm = TRUE))

# Join the dataframes
user_steps <- merge(user_db, daily_steps, by="id", all=TRUE)
user_heart <- merge(user_db, daily_heartrate, by="id", all=TRUE)
heart_steps <- merge(daily_heartrate, daily_steps, by=c("id", "date"))
heart_step_cal <- merge(heart_steps, daily_cals, by=c("id", "date"))
user_all <- merge(heart_step_cal, user_db, by="id")

# Summarize average usages (daily and total) by activity levels
average_daily_usage <- user_all %>%
  group_by(activity) %>%
  summarize(average_daily_use = mean(daily_use, na.rm = TRUE)) %>% 
  ungroup()
average_usage <- user_all %>%
  group_by(activity) %>%
  summarize(average_usage = mean(percent_days, na.rm = TRUE)) %>% 
  ungroup()

Phase IV - Analyze

for (key in names(categories)){
  cal <- ggplot(user_all) + 
    geom_smooth() +  
    geom_point(position = position_jitter()) + 
    aes(x = date, y = total_cals, color = !!sym(key)) +
    scale_color_manual(values = brewer.pal(3, "Set1"), labels=cap_categories) +
    scale_x_date(date_breaks = "5 day", date_labels = "%b %d") +
    scale_y_continuous(labels = comma) +
    labs(x = "Days", y = "Calories Burned", 
         title = glue("Calories Burned Over Time by {categories[[key]]}"), 
         color = categories[[key]]) +
    theme_minimal() +
    theme(axis.text.x = element_text(angle = 35, hjust = 1))
  ggsave(glue("{save_dir}/{categories[[key]]} - cals.png"),
    plot=cal, width = 8, height = 6, dpi = 300)
}