First of all, all the needed libraries must be installed and imported.
install.packages('tidyverse')
install.packages("RSQLite")
install.packages("corrplot")
install.packages("gridExtra")
library(tidyverse)
library(RSQLite)
library(ggplot2)
library(corrplot)
library(dplyr)
library(gridExtra)
Now the .db database file that was created previously in SQLite is imported to store relevant tables into local variables.
# Open connection with SQLite Database
con <- dbConnect(RSQLite::SQLite(), dbname = "Bellabeat_CaseStudy.db")
#Assign desired tables to local variables
dailyactivity_clean <- dbGetQuery(con, "SELECT * FROM dailyactivity_clean")
users_vs_entries <- dbGetQuery(con, "SELECT * FROM users_vs_entries")
hourly_parameters <- dbGetQuery(con, "SELECT * FROM Hourly_parameters")
heartrate_seconds <- dbGetQuery(con, "SELECT * FROM heartrate_seconds_merged")
no_values <- dbGetQuery(con, "SELECT * FROM No_Values")
sleepDay <- dbGetQuery(con, "SELECT * FROM sleepDay_merged")
# Close connection with SQLite Database
dbDisconnect(con)
str(dailyactivity_clean)
## 'data.frame': 940 obs. of 15 variables:
## $ Id :integer64 1503960366 1503960366 1503960366 1503960366 1503960366 1503960366 1503960366 1503960366 ...
## $ NewActivityDate : chr "04-12-2016" "04-13-2016" "04-14-2016" "04-15-2016" ...
## $ TotalSteps : int 13162 10735 10460 9762 12669 9705 13019 15506 10544 9819 ...
## $ Calories : int 1985 1797 1776 1745 1863 1728 1921 2035 1786 1775 ...
## $ TotalDistance : num 8.5 6.97 6.74 6.28 8.16 6.48 8.59 9.88 6.68 6.34 ...
## $ TrackerDistance : num 8.5 6.97 6.74 6.28 8.16 6.48 8.59 9.88 6.68 6.34 ...
## $ LoggedActivitiesDistance: num 0 0 0 0 0 0 0 0 0 0 ...
## $ SedentaryActiveDistance : num 0 0 0 0 0 0 0 0 0 0 ...
## $ LightActiveDistance : num 6.06 4.71 3.91 2.83 5.04 2.51 4.71 5.03 4.24 4.65 ...
## $ ModeratelyActiveDistance: num 0.55 0.69 0.4 1.26 0.41 0.78 0.64 1.32 0.48 0.35 ...
## $ VeryActiveDistance : num 1.88 1.57 2.44 2.14 2.71 3.19 3.25 3.53 1.96 1.34 ...
## $ SedentaryMinutes : int 728 776 1218 726 773 539 1149 775 818 838 ...
## $ LightlyActiveMinutes : int 328 217 181 209 221 164 233 264 205 211 ...
## $ ModeratelyActiveMinutes : int 13 19 11 34 10 20 16 31 12 8 ...
## $ VeryActiveMinutes : int 25 21 30 29 36 38 42 50 28 19 ...
summary (select(dailyactivity_clean, TotalSteps,Calories,TotalDistance,TrackerDistance))
## TotalSteps Calories TotalDistance TrackerDistance
## Min. : 0 Min. : 0 Min. : 0.000 Min. : 0.000
## 1st Qu.: 3790 1st Qu.:1828 1st Qu.: 2.620 1st Qu.: 2.620
## Median : 7406 Median :2134 Median : 5.245 Median : 5.245
## Mean : 7638 Mean :2304 Mean : 5.490 Mean : 5.475
## 3rd Qu.:10727 3rd Qu.:2793 3rd Qu.: 7.713 3rd Qu.: 7.710
## Max. :36019 Max. :4900 Max. :28.030 Max. :28.030
summary (select(dailyactivity_clean, SedentaryMinutes,LightlyActiveMinutes, ModeratelyActiveMinutes, VeryActiveMinutes))
## SedentaryMinutes LightlyActiveMinutes ModeratelyActiveMinutes
## Min. : 0.0 Min. : 0.0 Min. : 0.00
## 1st Qu.: 729.8 1st Qu.:127.0 1st Qu.: 0.00
## Median :1057.5 Median :199.0 Median : 6.00
## Mean : 991.2 Mean :192.8 Mean : 13.56
## 3rd Qu.:1229.5 3rd Qu.:264.0 3rd Qu.: 19.00
## Max. :1440.0 Max. :518.0 Max. :143.00
## VeryActiveMinutes
## Min. : 0.00
## 1st Qu.: 0.00
## Median : 4.00
## Mean : 21.16
## 3rd Qu.: 32.00
## Max. :210.00
summary (select(sleepDay, TotalSleepRecords, TotalMinutesAsleep, TotalTimeInBed))
## TotalSleepRecords TotalMinutesAsleep TotalTimeInBed
## Min. :1.000 Min. : 58.0 Min. : 61.0
## 1st Qu.:1.000 1st Qu.:361.0 1st Qu.:403.0
## Median :1.000 Median :433.0 Median :463.0
## Mean :1.119 Mean :419.5 Mean :458.6
## 3rd Qu.:1.000 3rd Qu.:490.0 3rd Qu.:526.0
## Max. :3.000 Max. :796.0 Max. :961.0
print(paste("Number of Activity IDs:",n_distinct(dailyactivity_clean$Id)))
## [1] "Number of Activity IDs: 33"
print(paste("Number of BPM IDs:",n_distinct(heartrate_seconds$Id)))
## [1] "Number of BPM IDs: 14"
print(paste("Number of hourly IDs:",n_distinct(hourly_parameters$Id)))
## [1] "Number of hourly IDs: 33"
print(paste("Duplicated entries in Activity:", sum(duplicated(dailyactivity_clean))))
## [1] "Duplicated entries in Activity: 0"
print(paste("Duplicated entries in Hourly:", sum(duplicated(hourly_parameters))))
## [1] "Duplicated entries in Hourly: 0"
hourly_parameters <- hourly_parameters %>% distinct() %>% drop_na()
As a first step, it can be benefitial to visualize the number of users and its representation in the survey.
users_vs_entries %>% ggplot(mapping = aes(1:33,Count)) +
geom_col(fill = "black") +
#Include title, subtitle and axis
labs(title = "Sample Size - Number of participants", y = "Number of Entries", x = "User") +
#Indicates numerical value for each col
geom_text(aes(label= Count), vjust=2.2, size=3,col = "white") +
scale_x_continuous(breaks = 1:33) + scale_y_continuous(expand = expansion(add = c(0, 4))) +
#Adds each user number from 1 to 33 and then scales y axis to give a bit more extra space
theme_minimal() +
#Centers title to plot
theme(legend.position = "none") + theme(plot.title.position = 'plot', plot.title = element_text(hjust = 0.5))
User 1 with Id 4057192912 has only 4 entries, therefore it will be removed as its activity is not representative. Also, 13 entries are removed that do not match the TotalDistance with the TrackerDistance.
dailyactivity_clean <- subset(dailyactivity_clean, Id != "4057192912")
dailyactivity_clean <- subset(dailyactivity_clean, TotalDistance - TrackerDistance < 0.3)
It can be interesting to explore how the sleep correlates with the activity: As it can be seen in the following figures, there is not a meaningful correlation between them as for same values of sleep time, distance and calories are widely distributed.
sleepDay$Day <- as.Date(sleepDay$SleepDay, format = "%m/%d/%Y")
sleepDay$Day <- format(sleepDay$Day, "%m-%d-%Y")
sleep_vs_activity <- dailyactivity_clean %>% inner_join(sleepDay, by = c("Id", "NewActivityDate" = "Day"))
p1 <- ggplot(sleep_vs_activity, aes(TotalTimeInBed, TotalSteps)) +
geom_point() + geom_smooth() + theme_minimal() +
theme(legend.position = "none")
p2 <- ggplot(sleep_vs_activity, aes(TotalTimeInBed, Calories)) +
geom_point() + geom_smooth() + theme_minimal() +
theme(legend.position = "none")
grid.arrange(p1, p2, ncol = 2, widths = c(1, 1))
To understand how all fields correlate with each other, it is beneficial to print the correlation matrix. The results should indicate a very high correlation between TotalSteps, Calories and Distances, as well as Distances and Minutes by Intensity. This step allows finding hidden relationships and checking data integrity for straightforward related fields.
#Non numeric data for computing the correlation
num_dailyActivity_clean <- subset(dailyactivity_clean, select = -NewActivityDate)
correlation_matrix <- cor(num_dailyActivity_clean, use = "complete.obs")
col <- colorRampPalette(c("#BB4444", "#EE9988", "#FFFFFF", "#77AADD", "#4477AA"))
corrplot(correlation_matrix, method = "shade", shade.col = NA, col = col(100), addCoef.col = "black", tl.col = "black",
tl.cex = 0.7,number.cex = 0.7, tl.srt = 45, addCoefasPercent = TRUE, cl.pos = "n", mar=c(2,0,0,0))
mtext("Correlation Matrix", at=7.8, line=-20.3, cex=1.8)
Now, lets identify patterns in the average number of steps in time. This can help defining general improvement over time.
steps_in_time <- dailyactivity_clean %>% select('Id', 'NewActivityDate', 'TotalSteps') %>%
group_by(NewActivityDate) %>%
summarize(Avg_Steps = mean(TotalSteps))
steps_in_time$NewActivityDate <- as.Date(steps_in_time$NewActivityDate, format = "%m-%d-%Y")
ggplot(steps_in_time,mapping= aes(NewActivityDate, Avg_Steps)) + geom_point(color="black") +
geom_smooth(color = "orange") + theme(axis.text.x = element_text(angle = 45, hjust= 1)) +
scale_y_continuous(expand = expansion(add = c(3500, 2400))) +
scale_x_date(date_breaks = "1 week", date_labels = "%d %b")+
labs(title = "Average Steps by all users in time - 2016", y = "Average Steps", x = "Date") +
theme_minimal() +
theme(legend.position = "none") + theme(plot.title.position = 'plot', plot.title = element_text(hjust = 0.5))
The tendency seems to stay stable, without significant decrease or raise over time. But is there a meaningful difference in activity levels depending on the day of the week?
# Get the weekday for each date
dailyactivity_clean$NewActivityDate <- as.Date(dailyactivity_clean$NewActivityDate, format = "%m-%d-%Y")
dailyactivity_weekday <- dailyactivity_clean%>%
mutate(Weekday = weekdays(NewActivityDate),
Weekday = factor(Weekday, levels = c("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")))
dailyactivity_weekday %>% filter(TotalSteps > 0) %>%
group_by(Weekday) %>%
summarize(Avg_Steps = mean(TotalSteps)) %>%
arrange(-Avg_Steps) %>%
ggplot(mapping = aes(Weekday, Avg_Steps)) + geom_col(aes(fill = Weekday)) +
geom_text(aes(label = round(Avg_Steps, digits = 0)), vjust = 2.2, size = 3, col = "white") +
scale_y_continuous(expand = expansion(add = c(-5000, 700))) +
labs(title = "Average Steps by all users by Weekday", y = "Average Steps", x = "Weekday") +
theme_minimal() +
theme(legend.position = "none") + theme(plot.title.position = 'plot', plot.title = element_text(hjust = 0.5))
This information can be relevant for future marketing campaigns. For example, if wanting to reach people in their most or least active day. Users walk on Tuesday/Saturday an average of 1100+ steps more than on Sunday/Friday.
In order to find a possible cause for daily registers with 0 activity, a bar chart by weekday can be created. However, it does not provide any meaningful insights.
dailyactivity_weekday %>% filter(TotalDistance == 0) %>%
group_by(Weekday) %>% summarise(Count = n()) %>%
ggplot(mapping = aes(Weekday, Count)) + geom_col(aes(fill = Weekday)) +
geom_text(aes(label = Count), vjust = 2.2, size = 3, col = "white") + labs(title = "Number of 0 - values by Weekday", y = "Count of 0 - Values", x = "Weekday") +
theme_minimal() +
theme(legend.position = "none") + theme(plot.title.position = 'plot', plot.title = element_text(hjust = 0.5))
To continue on the zero values exploration, it is wanted to know if it responds to the device charging time. It is easy to see that most of the entries correspond to few users. Therefore, it does not respond to a general charging need for all users, but rather a personal use. However, almost 10% of the users have problems using the device consistently.
no_values %>% group_by(Id) %>% summarise(Count = n()) %>% arrange(-Count)
## # A tibble: 15 × 2
## Id Count
## <int64> <int>
## 1 1927972279 14
## 2 4020332650 14
## 3 1844505072 11
## 4 8792009665 10
## 5 6775888955 9
## 6 6117666160 5
## 7 6290855005 5
## 8 5577150313 2
## 9 7007744171 2
## 10 1503960366 1
## 11 4057192912 1
## 12 4702921684 1
## 13 7086361926 1
## 14 8253242879 1
## 15 8583815059 1
How much percentage of total entries contains zero activity?
no_values_percent <- dailyactivity_clean %>% group_by(Id) %>%
mutate(status= ifelse(TotalSteps == 0, "Zero", "Whole")) %>% ungroup() %>%
mutate(TotalEntries = n()) %>%
group_by(status,TotalEntries) %>%
summarise(Percent = (n())) %>%
transform(Percent= round(Percent/TotalEntries*100,1)) %>%
summarise(status,Percent)
# Creating the polar bar chart
ggplot(no_values_percent, aes(x = "", y = Percent, fill = status)) +
geom_bar(stat = "identity", width = 1) +
coord_polar("y", start = 0) +
theme_void() + labs(title = "Percentage of 0 values vs total") +
geom_label(aes(x=1.1,label = paste0(Percent, "%")) , position = position_stack(vjust = 0.5),
colour="white",size=5, show_guide=F) +
theme(plot.title.position = 'plot', plot.title = element_text(hjust = .5,size=20), legend.title=element_text(size=15),
legend.text = element_text(size=13)) +
scale_fill_manual(values=c("darkgreen", "red"))
For the next analysis steps, a unique number from 1 to 33 is assigned to each Id to improve readability. New Id values are consistent with previous code chunks.
users_id <- users_vs_entries %>% mutate(Id_Number = row_number()) %>% select (Id,Id_Number)
dailyactivity_id <- dailyactivity_clean %>% inner_join(users_id, by = "Id")
head(users_id)
## Id Id_Number
## 1 4057192912 1
## 2 2347167796 2
## 3 8253242879 3
## 4 3372868164 4
## 5 6775888955 5
## 6 7007744171 6
The following figure allows checking how zero values are distributed among all participants.
no_values_user <- dailyactivity_id %>%
mutate(status = ifelse(TotalSteps == 0, "Zero", "Whole")) %>%
group_by(Id_Number, status) %>%
summarise(Count = n(),.groups="drop", Zeros= sum(status=="Zero")) %>%
group_by(Id_Number) %>%
mutate(TotalEntries = sum(Count)) %>%
ungroup()
ggplot(no_values_user , mapping=aes(x = reorder(Id_Number,Zeros), y=Count, fill=status)) + geom_col() +
scale_fill_manual(values = c("Zero" = "red", "Whole" = "darkgreen")) +
theme_minimal() +
geom_text(aes(label = Count), hjust = 1.1, color = "white", size=3) +
labs(title = "Number of zero values per user", y = "Number of Entries", x = "User") +
scale_x_discrete(breaks = 1:33) + coord_flip() +
theme(axis.text.y = element_text(margin = margin(r = -6))) +
theme(axis.text.x = element_text(margin = margin(t = -2)))
Getting deeper into the device use, lets analyze type of users based on how many days of use they have per month.
sleepDay$Day <- as.Date(sleepDay$SleepDay, format = "%m/%d/%Y")
use_profiles <- dailyactivity_clean %>% full_join(sleepDay, by = c("Id", "NewActivityDate" = "Day")) %>% filter(TotalSteps>0) %>%
group_by(Id, NewActivityDate) %>%
group_by(Id) %>% summarize(Days = n_distinct(NewActivityDate)) %>% mutate(MonthlyUse=case_when(Days>21 ~ "High", Days<=14 ~ "Low", .default = "Moderate")) %>%
group_by(MonthlyUse) %>% summarise(Users=n()) %>% mutate(Total= sum(Users), Percent = round((Users/Total*100), 1)) %>% select(MonthlyUse, Percent)
ggplot(use_profiles,mapping= aes("", Percent, fill=MonthlyUse)) + geom_bar(stat = "identity", width = 1) +
coord_polar("y", start = 0) +
theme_void() + labs(title = "Users by Monthly Usage") +
geom_label(aes(x=1.1,label = paste0(Percent, "%")) , position = position_stack(vjust = 0.5),
colour="white",size=5, show_guide=F) +
theme(plot.title.position = 'plot', plot.title = element_text(hjust = .5,size=20), legend.title=element_text(size=15),
legend.text = element_text(size=13)) +
scale_fill_manual(name= "Usage by days per month",values=c("purple", "lightblue","yellow3"),
labels = c("High use - More than 21", "Moderate use - Between 14 and 21", "Low use - Less than 14"))
Most users wear the device on a daily basis. It is now interesting to check how many hours per day on average. Do they wear it 24 hours or only during exercise time? The pie chart shows a Moderate and High Use during the day.
daily_use <- dailyactivity_clean %>% group_by(Id) %>%
summarise(AvgDailyUsageHours = round((mean(SedentaryMinutes) + mean(LightlyActiveMinutes)+ mean(ModeratelyActiveMinutes)+ mean(VeryActiveMinutes))/60,1)) %>%
mutate(DailyUse = case_when(AvgDailyUsageHours>20 ~ "High", AvgDailyUsageHours<8 ~ "Low", .default = "Medium")) %>% group_by(DailyUse) %>%
summarise(count= n()) %>% mutate(Total=sum(count), Percent = round(count/Total*100,1)) %>%
select(DailyUse, Percent)
ggplot(daily_use,mapping= aes("", Percent, fill=DailyUse)) + geom_bar(stat = "identity", width = 1) +
coord_polar("y", start = 0) +
theme_void() + labs(title = "Users by Average Daily Usage") +
geom_label(aes(x=1.1,label = paste0(Percent, "%")) , position = position_stack(vjust = 0.5),
colour="white",size=5, show_guide=F) +
theme(plot.title.position = 'plot', plot.title = element_text(hjust = .5,size=20), legend.title=element_text(size=15),
legend.text = element_text(size=13)) +
scale_fill_manual(name= "Usage by hours per day",values=c("purple", "lightblue"),
labels = c("High use - More than 20", "Moderate use - Between 8 and 20"))
Now a dynamic dashboard is created in Tabelau, to visualize the Total Steps per day by each user separately. This allows singular user behavior evaluation. Before going to Tableau, the tables that will be used need to be exported in .csv format.
write.csv(users_id,file = "user_id.csv")
write.csv(dailyactivity_clean, file = "dailyactivity_clean.csv")
#write.csv(hourly_parameters, file = "hourly_parameters.csv")
#write.csv(heartrate_seconds, file = "heartrate_seconds.csv")
Now the type of users will be analyzed by their activity level. By creating a table with avg steps by user, users can be classified by walking close to the average population, significantly below or average.
“The Medicine and Science in Sports and Exercise study found that, on average, adult males took about 5,340 steps per day, whereas adult females took around 4,912 steps per day”
user_profile <-dailyactivity_id %>% group_by(Id_Number) %>%
summarise(Average= mean(TotalSteps)) %>%
mutate(Profile=ifelse(Average<4500,"Sedentary", ifelse(Average> 5700, "Active", "Average"))) %>%
group_by(Profile) %>% summarise(Percent= round(n()/33*100,1))
ggplot(user_profile, aes(x = "", y = Percent, fill = Profile)) +
geom_bar(stat = "identity", width = 1, color = "white") +
coord_polar(theta = "y") +
theme_void() +
theme(legend.position = "right") +
labs(title = "Profile Distribution") +
geom_text(aes(x=1.1,label = paste0(round(Percent, 1), "%")), position = position_stack(vjust = 0.5),
colour="white",size=5,show_guide=F) +
guides(fill = guide_legend(title = "Profile")) +
theme(plot.title.position = 'plot', plot.title = element_text(hjust = .5,size=20),
legend.title=element_text(size=15), legend.text = element_text(size=13)) +
scale_fill_manual(values=c("blue2", "yellow3", "purple"))
Now lets evaluate the tendency of steps over time by user (improvement), and classify them accordingly. Then, plot their performance by user profile.
steps_in_time <- dailyactivity_id %>% select(Id_Number, NewActivityDate, TotalSteps) %>%
filter (TotalSteps!=0)
# Create an empty data frame to store results
user_improvement <- data.frame(Id_Number = numeric(), slope = numeric())
# Loop through each user
for (user_id in unique(steps_in_time$Id_Number)) {
user_data <- steps_in_time %>% filter(Id_Number== user_id)
# Fit a linear model
lm_model <- lm(TotalSteps ~ NewActivityDate, data = user_data)
# Extract the slope
slope<- coef(lm_model)[2]
# Store the results
user_improvement <- rbind(user_improvement, data.frame(Id_Number = user_id, slope = slope))
}
# Print the results
head(arrange(user_improvement,-slope))
## Id_Number slope
## NewActivityDate29 32 169.1969
## NewActivityDate6 18 144.0444
## NewActivityDate24 6 129.9066
## NewActivityDate25 29 115.5885
## NewActivityDate12 21 106.0191
## NewActivityDate30 9 104.9622
By joining this table with the one classifying the user profiles, it is possible to obtain the desired table.
user_profile <-dailyactivity_id %>% group_by(Id_Number) %>%
summarise(Average= mean(TotalSteps)) %>%
mutate(Profile=ifelse(Average<4500,"Sedentary", ifelse(Average> 5700, "Active", "Average")))
improvement_by_profile <- user_profile %>% inner_join(user_improvement, by= 'Id_Number') %>%
rename(Id = Id_Number, Slope=slope) %>%
mutate(Improvement = if_else(Slope>0, "Yes","No"))%>% select(Id, Profile, Improvement)
ggplot(improvement_by_profile, mapping= aes(Improvement, fill=Improvement)) + geom_bar() +
labs(title = "Did users improve their Steps over time?", y = "Nº of Users", x = "Improved?") +
theme_minimal() + theme(plot.title.position = 'plot', plot.title = element_text(hjust = .5,size=18),
legend.position = "none") +
geom_text(stat = "count", position = position_stack(vjust = 0.5), aes(label = after_stat(count)))
ggplot(improvement_by_profile, mapping= aes(Profile, fill=Improvement)) + geom_bar() + theme_minimal() +
labs(title = "Improvement by profile", y = "Users") +
theme(plot.title.position = 'plot', plot.title = element_text(hjust = .45, size=18)) +
geom_text(stat = "count", position = position_stack(vjust = 0.5), aes(label = after_stat(count)))
February 2024 | by Ignacio Jimenez Verdinelli.