The Data, The Cleaning Process, And The Tools I Used

---

Tools Used For This Case Study…

Microsoft Excel – I used Excel to initialially look at the data, do minor cleaning and preperation. I used Excel because I have more experience with it.

Microsoft SQL Server Management Studio – I used SSMS to do more data cleaning. I used SSMS to prepare the data for further queries and to build CSV files for import into Tableau. I used SSMS because I have a lot more experience with it. I knew I was going to use SQL more so I went with what I was comfortable with.

Tableau Public – I used Tableau for my data visualization. I did so for a couple reasons. Firstly, it was the system taught in the Google Data Anayst Pro course. Secondly, my current employer uses Tableau and I felt that it probably would be good to learn it a little more.

Microsoft Powerpoint – I used Powerpoint for my presentation.

---

Cleaning Process

This prompted me to look further into the files and I started noticing that there were quite a few empty values in important areas of the data. Primarily things such as missing station data and the geo tracking data (latitude and longitude).

I knew from my initial thoughts on the data I wanted to include geo data in someway. I noticed that when a station name was missing it was also – in most cases – accompanied with less than precise geo data. I also knew the data size was far to large to work with so I determined that I would remove items to make a more suitable sample size. I could have removed the rows in Excel but decide to do that step with SQL in SSMS as it is better suited to handle larger data sets. These are the things I felt needed to be done in Excel. Due to the large data sets I felt that SQL was also better suited to combine all the data into one large set of data. I exported the data as a CSV (comma seperated value) file for import in SSMS as a flat file.

I also found a station that wasnt complete but had more complete data. This was a station witha station name ‘351’. Later I found a station that looked like a testing warehouse. It was the Divvy Warehouse (‘Hubbard Bike-checking (LBS-WH-TEST)’). I removed this later with a different script.

I removed the NULL rows and station ‘351’ in the initial creation of the ‘master’ tripdata table. During this time, I also added a column with ride length. I used the code below to query the Cyclistic Tripdata for Aug. 2021 to July 2022. This also alerted me to rides that were interestily small. Like rides only a minute or two. So, I ammended the script to cull out rides less than 5 minutes. My thought being that these rides may not be actual rides. Again thinking that maybe someone actually starts the process of renting a bike but does not complete it.

SELECT 
	[ride_id],
	[rideable_type],
	[started_at],
	[ended_at],
	DATEADD(SECOND, DATEDIFF(SECOND, started_at, ended_at), CONVERT(time(0), '00:00')) AS ride_length,
	DATENAME(WEEKDAY, started_at) AS day_of_week, -- adds col for named day of the week
	DATENAME(MONTH, started_at) AS month_of_trip, -- adds col for named month
	[start_station_name],
	[start_station_id],
	[end_station_name],
	[end_station_id],
	[start_lat],
	[start_lng],
	[end_lat],
	[end_lng],
	[member_casual]
  FROM [dbo].[cyclistic_tripdata_2021_2022]
  WHERE ( -- this set will remove all rows with nulls.
	ride_id IS NOT NULL AND
	rideable_type IS NOT NULL AND
	started_at IS NOT NULL AND
	ended_at IS NOT NULL AND
	start_station_name IS NOT NULL AND
	start_station_id IS NOT NULL AND
	end_station_name  IS NOT NULL AND
	end_station_id  IS NOT NULL
	) 
		AND
		DATEDIFF(MINUTE, started_at, ended_at) > 5 -- removes rides less than 5 minutes
		AND
		start_station_name != '351' -- removes the station 351 (unknown)
ORDER BY started_at

This script also adds 2 columns: one for day of the week and one for month. This query was then made into a new table to be used as a base for further queries later in the preperation phase.

---

Analysis Preparation

When originally looking at the data, I immediately saw a few things that I felt would be useful to use in my presentation. The first thing I wanted to add was a “Ride per day of the week” chart. I thought it would be nice to see if there was any difference between members and casual riders based on the day of the week. To do that, I wrote a script to count all the rides by member and casual, and sort by the day of the week.

SELECT
	DATEPART(WEEKDAY, started_at) AS week_day_number,
	day_of_week,
	member_casual,
	COUNT(*) AS ride_total_by_day
FROM dbo.cyclistic_tripdata_2021_2022
GROUP BY DATEPART(WEEKDAY, started_at), day_of_week, member_casual
ORDER BY DATEPART(WEEKDAY, started_at)

I then determined that a breakdown similar to the day of week would shed light on whether the month of year had any bering on ride totals. The code was quite similar to the above code.

SELECT
	DATEPART(MONTH, started_at) AS month_number,
	month_of_trip,
	member_casual,
	COUNT(*) AS ride_total_by_month
FROM dbo.cyclistic_tripdata_2021_2022
GROUP BY DATEPART(MONTH, started_at), month_of_trip, member_casual
ORDER BY DATEPART(MONTH, started_at)

Seeing this monthly ride information I noticed that there was also a noticable difference between members and casual riders in regards to how long they used the actual bikes. So I looked at rides within specific ranges. <15 mins, 15-30 mins, 30-45mins, 45-60mins, and >60. This actually shed a lot more light on things than I originally thought. The query for the 15-30 min range is below.

/* The #'s in the WHERE clause can be changed to make the query find different ranges. */
SELECT
	member_casual,
	COUNT(*) AS rides_between_15_and_30
FROM dbo.cyclistic_tripdata_2021_2022
WHERE ( 
	DATEDIFF(MINUTE, started_at, ended_at) >= 15 AND 
	DATEDIFF(MINUTE, started_at, ended_at) < 29
	)
GROUP BY member_casual

I then wondered whether there was preference in bike offerings between the members and casual customers.

SELECT
	rideable_type,
	member_casual,
	COUNT(*) AS type_ttl
FROM dbo.cyclistic_tripdata_2021_2022
GROUP BY rideable_type, member_casual

When I first saw the data (during the cleaning process) I really liked the geographical data (latitude and longitude). The main issue was the data was somewhat inaccurate. It wasnt that it was not trust worthy. It was that it was to trust worthy. By this I mean that it was incredibly accurate. So the biggest problem was that when someone rented a bike it would log the geo data….within feet. What that meant was that a rental done at a location such as Michigan Ave & Oak St may have many different latitudes and longitudes. This caused grouping and sorting issues. I had to devise a way to group the stations (so only one version of the station would show up in the query result) with a single latitude and longitude for that station. I had to do a lot of verification with google maps to confirm my results matched the actual location. I decided to average the geo data and also round it up to the 4th decimal place. I was able to confirm that the locations and the geo data somewhat matched up to their real life location. The following query is what I used to produce the result used in my presentation.

SELECT 
	station_max.start_station_name,
	station_max.start_station_id,
	ROUND(AVG(station_max.start_lat),3) AS latitude,
	ROUND(AVG(station_max.start_lng),3) AS longitude,
	MAX(station_max.station_ttl) AS max_ttl
FROM (
	SELECT
		[start_station_name],
		[start_station_id],
		[start_lat],
		[start_lng],
		COUNT(start_station_name) AS station_ttl
	FROM [dbo].[cyclistic_tripdata_2021_2022]
	WHERE (start_station_id != 'Hubbard Bike-checking (LBS-WH-TEST)')
	GROUP BY
		[start_station_name],
		[start_station_id],
		[start_lat],
		[start_lng]
	) AS station_max
GROUP BY 
	station_max.start_station_id,
	station_max.start_station_name

Knowing I needed a accurate total per station (as the geo data table would not produce an accurate count). I queried a simple total for each individual station.

SELECT
	start_station_name,
	COUNT(*)
FROM dbo.cyclistic_tripdata_2021_2022
GROUP BY start_station_name

At this point I determined I had enough data extracted to make some solid recommendations with some compelling visuals.