FitLife Dataset Changelog

This dataset is too large to view in a spreadsheet, so this notebook is a changelog for using SQL to clean the health_fitness_dataset on google’s BigQuery.

1. Investigating NULL Values

Variations of the following script were run for every column to discover if there were any NULL values.

SELECT  (*)
FROM `complete-will-468115-p2.fitlife360_synthetic_health_data.fitlife`
WHERE 
  particpant_id IS NULL;

As expected from a synthetic dataset, there were no rows containing NULL values that needed to be excluded from the dataset.

2. Summarizing Data

The following queries were run to summarize the data. This summary table was created in Google Sheets to compile these results.

Summarizing Gender

SELECT  
  gender,
  COUNT(DISTINCT participant_id) AS user_count
FROM `complete-will-468115-p2.fitlife360_synthetic_health_data.fitlife`
GROUP BY gender;

Summarizing Activity Types

First, this query was run to summarize the activity types, split up by intensity:

SELECT
  activity_type, intensity,
  COUNT(DISTINCT participant_id) AS activity_type_count,
  COUNT(participant_id) AS activity_session_count,
  SUM(duration_minutes) AS activity_duration_count,
  ROUND(AVG(duration_minutes), 2) AS activity_duration_average_in_minutes,
  ROUND(MIN(duration_minutes), 2) AS activity_duration_min_in_minutes,
  ROUND(MAX(duration_minutes), 2) AS activity_duration_max_in_minutes,
FROM `complete-will-468115-p2.fitlife360_synthetic_health_data.fitlife` 
GROUP BY activity_type, intensity
ORDER BY activity_type,
  CASE intensity
        WHEN 'Low' THEN 1
        WHEN 'Medium' THEN 2
        WHEN 'High' THEN 3
        ELSE NULL
        END

Intensity minutes breakdown

SELECT
  intensity,
  COUNT(intensity) AS intensity_session_total_count
FROM `complete-will-468115-p2.fitlife360_synthetic_health_data.fitlife`
GROUP BY  
  intensity
ORDER BY  
  CASE intensity
        WHEN 'Low' THEN 1
        WHEN 'Medium' THEN 2
        WHEN 'High' THEN 3
        ELSE NULL
        END

Min, Max, Avg Summaries by User

SELECT
  intensity,
  DISTINCT participant_id,
  ROUND(AVG(stress_level), 2) AS stress_average,
  ROUND(MIN(stress_level), 2) AS stress_min,
  ROUND(MAX(stress_level), 2) AS stress_max,
  ROUND(AVG(hours_sleep), 2) AS hours_sleep_average,
  ROUND(MIN(hours_sleep), 2) AS hours_sleep_min,
  ROUND(MAX(hours_sleep), 2) AS hours_sleep_max,
  ROUND(AVG(weight_kg), 2) AS weight_kg_average,
  ROUND(MIN(weight_kg), 2) AS weight_kg_min,
  ROUND(MAX(weight_kg), 2) AS weight_kg_max,
  ROUND(AVG(hydration_level), 2) AS hydration_level_average,
  ROUND(MIN(hydration_level), 2) AS hydration_level_min,
  ROUND(MAX(hydration_level), 2) AS hydration_level_max,
  ROUND(AVG(daily_steps)) AS daily_steps_average,
  ROUND(MIN(daily_steps)) AS daily_steps_min,
  ROUND(MAX(daily_steps)) AS daily_steps_max,
  ROUND(AVG(age), 2) AS average_age,
  ROUND(AVG(duration_minutes)) AS duration_average_in_minutes,
  ROUND(MIN(duration_minutes)) AS duration_min_in_minutes,
  ROUND(MAX(duration_minutes)) AS duration_max_in_minutes,
  ROUND(AVG(bmi)) AS bmi_average,
  ROUND(MIN(bmi)) AS bmi_min,
  ROUND(MAX(bmi)) AS bmi_max,
  ROUND(AVG(resting_heart_rate)) AS resting_heart_rate_average,
  ROUND(MIN(resting_heart_rate)) AS resting_heart_rate_min,
  ROUND(MAX(resting_heart_rate)) AS resting_heart_rate_max,
  ROUND(AVG(blood_pressure_diastolic)) AS blood_pressure_diastolic_average,
  ROUND(MIN(blood_pressure_diastolic)) AS blood_pressure_diastolic_min,
  ROUND(MAX(blood_pressure_diastolic)) AS blood_pressure_diastolic_max,
  ROUND(AVG(blood_pressure_systolic)) AS blood_pressure_systolic_average,
  ROUND(MIN(blood_pressure_systolic)) AS blood_pressure_systolic_min,
  ROUND(MAX(blood_pressure_systolic)) AS blood_pressure_systolic_max,
  ROUND(AVG(calories_burned)) AS calories_burned_average,
  ROUND(MIN(calories_burned)) AS calories_burned_min,
  ROUND(MAX(calories_burned)) AS calories_burned_max,
FROM `complete-will-468115-p2.fitlife360_synthetic_health_data.fitlife` 
GROUP BY activity_type, intensity
ORDER BY activity_type,
  CASE intensity
        WHEN 'Low' THEN 1
        WHEN 'Medium' THEN 2
        WHEN 'High' THEN 3
        ELSE NULL
        END

Min, Max, Avg Summaries by Date

SELECT
  DISTINCT date,
  ROUND(AVG(stress_level), 2) AS stress_average,
  ROUND(MIN(stress_level), 2) AS stress_min,
  ROUND(MAX(stress_level), 2) AS stress_max,
  ROUND(AVG(hours_sleep), 2) AS hours_sleep_average,
  ROUND(MIN(hours_sleep), 2) AS hours_sleep_min,
  ROUND(MAX(hours_sleep), 2) AS hours_sleep_max,
  ROUND(AVG(weight_kg), 2) AS weight_kg_average,
  ROUND(MIN(weight_kg), 2) AS weight_kg_min,
  ROUND(MAX(weight_kg), 2) AS weight_kg_max,
  ROUND(AVG(hydration_level), 2) AS hydration_level_average,
  ROUND(MIN(hydration_level), 2) AS hydration_level_min,
  ROUND(MAX(hydration_level), 2) AS hydration_level_max,
  ROUND(AVG(daily_steps)) AS daily_steps_average,
  ROUND(MIN(daily_steps)) AS daily_steps_min,
  ROUND(MAX(daily_steps)) AS daily_steps_max,
  ROUND(AVG(age), 2) AS average_age,
  ROUND(AVG(duration_minutes)) AS duration_average_in_minutes,
  ROUND(MIN(duration_minutes)) AS duration_min_in_minutes,
  ROUND(MAX(duration_minutes)) AS duration_max_in_minutes,
  ROUND(AVG(bmi)) AS bmi_average,
  ROUND(MIN(bmi)) AS bmi_min,
  ROUND(MAX(bmi)) AS bmi_max,
  ROUND(AVG(resting_heart_rate)) AS resting_heart_rate_average,
  ROUND(MIN(resting_heart_rate)) AS resting_heart_rate_min,
  ROUND(MAX(resting_heart_rate)) AS resting_heart_rate_max,
  ROUND(AVG(blood_pressure_diastolic)) AS blood_pressure_diastolic_average,
  ROUND(MIN(blood_pressure_diastolic)) AS blood_pressure_diastolic_min,
  ROUND(MAX(blood_pressure_diastolic)) AS blood_pressure_diastolic_max,
  ROUND(AVG(blood_pressure_systolic)) AS blood_pressure_systolic_average,
  ROUND(MIN(blood_pressure_systolic)) AS blood_pressure_systolic_min,
  ROUND(MAX(blood_pressure_systolic)) AS blood_pressure_systolic_max,
  ROUND(AVG(calories_burned)) AS calories_burned_average,
  ROUND(MIN(calories_burned)) AS calories_burned_min,
  ROUND(MAX(calories_burned)) AS calories_burned_max,
  ROUND(AVG(duration_minutes)) AS duration_minutes_average,
  ROUND(MIN(duration_minutes)) AS duration_minutes_min,
  ROUND(MAX(duration_minutes)) AS duration_minutes_max,
FROM `complete-will-468115-p2.fitlife360_synthetic_health_data.fitlife` 
GROUP BY date;

3. Summarizing While Filtering for Gender

Bellabeat has positioned itself as a health & wellness company for women, so the data has been filtered to exclude men by adding the following to each summary query written in the previous section to filter the men out of the dataset without altering the dataset itself.

WHERE gender <> "M" 

Summing activities by user and date without dividing by intensity

Breaking the sums up yesterday created a potential for confusing data as each user could be counted in each intensity category, meaning just summing those values would not be accurate in counting unique users for each activity.

SELECT
  activity_type,
  COUNT(DISTINCT participant_id) AS unique_users

FROM `complete-will-468115-p2.fitlife360_synthetic_health_data.fitlife`
GROUP BY
  activity_type
ORDER BY
  activity_type;

Activity Session Counts

SELECT
  activity_type,
  COUNT(activity_type) AS activity_session_count
FROM `complete-will-468115-p2.fitlife360_synthetic_health_data.fitlife`
GROUP BY
  activity_type
ORDER BY
  activity_type;

Activity Duration Summary

SELECT
  activity_type,
  SUM(duration_minutes) AS activity_duration_in_minutes
FROM `complete-will-468115-p2.fitlife360_synthetic_health_data.fitlife`
GROUP BY
  activity_type
ORDER BY
  activity_type;

Data Visualization

The data was visualized using various tables in Tableau Public. This dataset was found to only display trends that were steady and show no growth or decrease. This makes sense as the data is synthetic, and thus wouldn’t have any surprising trends to be discovered. These visualiztions were not used in the final report.

Conclusion

This dataset is not useful for this case study but was a good chance to practice cleaning and analyzing data using various tools such as BigQuery, spreadsheets, and Tableau Public.