Google Data Analyst Capstone Project: How does a bike-share navigate speedy success?

Hi everyone, these are the things I encountered while analysing data for my Google Data Analyst Practitioner Certificate. The Capstone project is where you put all of your skills learnt during the course together. It's the first time you get to analyze data. So here are some insights!

Source Data:

I had to analyze a year's worth of bike ride data. There are 12 csv files, 1 for each month. I choose June 2022 to May 2023. The data was quite sizeable.

For realistic estimates sake, when combined (using R Desktop), the combined file was over 1.3 gigabytes in size. There are over 5 million rows of data. That's a lot of data. Each row represents a bicycle ride.

What I did:

I looked at the data via Excel, and tried to get a feel of it. I attempted to combine it into a data source, using Microsoft Power Query: (for those of you, it's a feature in Microsoft Excel and PowerBI). However, the long loading times and times my laptop hung meant that I needed something a little more reliable. I decided to use R Desktop to manage my data.

Using R-Desktop

After reading thru a few other articles written by my coursemates who have completed their projects, I decided to try it out on my own to combine the data into 1 CSV file, and to clean up and filter out any abnormalities.

Here are the major steps I took in R Desktop:

• Loaded the major packages and libraries like dplyr, ggplot2, lubridate, and data.table.
• Set the working directory to my source file directory.
• Using lapply() and rbindlist(), combine the data into a single data frame called merged_data.
• I had a bit of issue with merging the data as not all the files and column names were of the same data type. But managed to remedy it by redefining the data type and changing the format.
• After that, using R Desktop, regenerate new columns. These columns are: Day_name, trip_duration, trip_duration_hms, month_name, start_hour, start_time_classification

• Do note that MS Power Query also has the feature to create day names and month names and start hour timings, however they don't have the feature to automatically allocate classifications for timings unless you do Index Match or Vlookup.
• Some trips only last less than a minute. That is not exactly correct. We will remove them or filter them out. Filter out all trip data below one minute.
• Next we will also check for duplicates within the TripID. No duplicates found.
• Next we will output all the data (filtered data, that is, with all negative values removed, all sub 1 min trips removed.)
• The command is write.csv(unique_filtered_data, "unique_filtered_data.csv", row.names = FALSE)

I am largely finished with R Desktop at this point in time, so I decided to try MS Power Query on Excel, and using Pivot Tables for visualizations and data viewing. The reason why I decided to use MS Excel and Power Query is because, we are all familiar with Excel, and with Excel, even if we are limited to about a million rows, Pivot Tables are still very useful.

Using Excel and Power Query

Now that the csv file is combined (after 15 minutes of loading, on my old laptop), we will proceed to get the data. I'm showing you the button that is used to get the data from our combined csv file.

• On Power Query, click Transform and Load
• Select: Connection Only
• Close Power Query, and when it's done, click on Insert Pivot Table

And now, we'll use Insert Pivot Table to add in our data, and manipulate what you want to see.

Once you have reached the stage of Pivot Tables, you are able to create Pivot Charts from them, and you should be able to manipulate and present your findings.

I also referred to and 'discussed' my process with ChatGPT and also read other examples of accounts that are published.

Good luck!