bike sharing project
Project Summary
My goal in this project was to test and develop my Excel abilities with a subject treating data other than financial. In this project, I focused on the overall performance of bike sharing. As many factors influenced its usage on the daily basis, my main challenge was determining which data to use and how.
Project Environment
Project Scope
- Firstly, I determined that the key element I wanted to showcase in this project was the overall performance of bike sharing, so I wanted to select data that would affect the bike sharing usage the most.
- After formatting the data, I was able to determine better how I could use the data. ⠀⠀⠀⠀⠀⠀⠀⠀⠀
- Then, for the dashboard, I wanted to integrate yearly overviews, but also granular data like the types of days, weekdays, weather conditions, hourly traffic and daily/hourly weather. There were a lot of different types of data to display effectively. ⠀⠀⠀⠀⠀⠀⠀⠀⠀
- To conclude, after having integrated the yearly weather and hourly and yearly traffic as well as the slicers and timeline, I wanted to add visually personalized data to give a broader performance evaluation of casual and registered bike sharing.
- I added the donut charts for the % of yearly bike sharing as well as the static visual “chart” for year on year growth.
Project Steps
Data cleaning
In this case, I didn’t have much data cleaning to do:
Verifying if the original data information is accurate
- I corrected the season data that was in the wrong order on the original data set (Dataset Infos sheet)
Formatting
IF function
-
Season from 1,2,3,4 to Winter, Spring, Summer and Fall =IF(C2=1,“Winter”,IF(C2=2,“Spring”,IF(C2=3,“Summer”,IF(C2=4,“Fall”,“Error”))))
-
Weather Condition from 1,2,3,4 to Very Good, Good, Bad, Very Bad =IF(J2=1,“Very Good”,IF(J2=2,“Good”,IF(J2=3,“Bad”,IF(J2=4,“Very Bad”, “Error”))))
-
Did the same for Year, Weekday, and Type of Day
More
- Changed the temperature, Humidity, and the Wind Speed from normalized to desired formatting with multiplication manipulation
- Changed the date formatting
Dashboard
From Pivot Tables to Pivot Charts
- Yearly Weather
- Hourly Traffic
- Yearly Traffic
- Yearly bike sharing in %
- Year on Year Growth (fixed “chart”)
Slicers and timeline
- Timeline
- Weekday
- Weekend and/or Holiday
- Weather Condition
- Very good - Clear | Few clouds | Partly cloudy
- Good - Mist & Cloudy | Mist & Broken clouds | Mist & Few clouds | Mist
- Bad - Light Snow | Light Rain & Thunderstorm & Scattered Clouds | Light Rain & Scattered Clouds
- Very Bad - Heavy Rain & Ice Pallets & Thunderstorm & Mist | Snow & Fog
Project Analysis
I could conclude a few things by quickly looking at this dashboard.
-
The overall ratio of casual and registered traffic between 2011 and 2012 hasn’t changed significantly with a 2% increase of registered usage.
-
The growth in the amount of usage between 2011 and 2012 has known a significant rise with 51% and 68% respectively for casual and registered bike sharing.
⠀⠀⠀⠀⠀⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠀⠀⠀
- Registered users commute with the bike sharing service for work and they represent the vast majority of users. As we would expect, registered users mainly use the service to commute for work. In contrast, casual bike sharers use renting bikes for leisure. The curve of bike traffic for casual users is steadier and its distribution varies between weekends/holidays and working days. Factors of influence like weather and events (not in the report) will have an impact on traffic as well.
