Data Analytics for Beginners >

Tutorial 3: Descriptive Statistics and Exploratory Data Analysis

Tutorial 3: Descriptive Statistics and Exploratory Data Analysis

Hi there,

Welcome to tutorial three of your Data Analytics for Beginners Course—you’re making great progress! This tutorial marks the halfway point, and things are about to get interesting 🤩 In the previous tutorial, you cleaned your data, ready for analysis. Now, you’re going to start gleaning valuable insights through exploratory data analysis; namely, using descriptive statistics and pivot tables.

The goal of this tutorial is to learn about descriptive statistics and practice calculating the mean, median, and range for certain variables. You’ll then get familiar with pivot tables to gain further insights from your data. In doing so, you’ll find answers to four of the five questions we set out in the first tutorial:

  1. What are the most popular pick-up locations across the city for Citi Bike rental?
  2. How does the average trip duration vary across different age groups, and over time?
  3. Which age group rents the most bikes?
  4. How does bike rental vary across the two user groups (one-time users vs long-term subscribers) on different days of the week?

Answering these questions will give us valuable insights into who our users are and how they use the Citi Bike rental service. All very useful for making business decisions, as we’ll see later on in the course! 🚴

In this tutorial, you will:

  • Calculate descriptive statistics
  • Create pivot tables using different variables
  • Answer four of our five main questions about the dataset for stakeholders

As always, we’ll start with some theory before jumping into the practical component. We’ve structured the tutorial as follows:

  1. What is exploratory data analysis (EDA)?
  2. What are descriptive statistics?
  3. What is a pivot table and what are they used for?
  4. Practical exercise: Descriptive statistics and pivot tables
  5. Key takeaways and further reading

Got feedback or questions? We’d love to hear from you! Just reply to any of the course emails you’ve received so far ✉️

Ready? Let’s begin!

1. What is exploratory data analysis?

Before you start conducting in-depth analysis, it’s important to first get acquainted with your dataset—to get the “lay of the land,” if you will. This is where exploratory data analysis comes in.

You can think of exploratory data analysis as an initial investigation of your dataset where you seek to understand and summarize its main characteristics. EDA is useful because it helps you to understand how your data is structured, to spot potential patterns and trends, and to catch any anomalies. EDA is also important for determining if the methods of analysis you are planning to use later on are actually appropriate for your dataset.

We encourage you to learn more about exploratory data analysis once you’ve completed this tutorial 😀 For now, let’s move on to a crucial component of exploratory data analysis: Descriptive statistics

2. What are descriptive statistics?

In a nutshell, descriptive statistics help you to summarize or describe the characteristics of your dataset in a meaningful way.

Imagine you have a dataset containing hundreds or even thousands of values. It would be impossible to look at that raw data with the naked eye and make any sense of it.

For example, if you collected data on the test scores of three hundred students, you might want to gauge their overall performance. You wouldn’t be able to do this simply by looking at a spreadsheet with all the raw data, but you could calculate the average (or mean) score. That’s an example of descriptive statistics!

Descriptive statistics are also useful for spotting potential errors or strange occurrences within your dataset. For example, in calculating the minimum and maximum values for a certain variable, you might notice that the maximum value falls outside of what could be considered a reasonable range. Imagine you’ve collected height data for a group of school children and calculated a minimum value of 20cm. That doesn’t seem like a realistic height for a child. Based on that, you’d investigate further to see what’s going on (and make sure that your dataset is in a fit state for analysis) 👀

Up next: What are the different types of descriptive statistics?

What are the different types of descriptive statistics?

The three main types of descriptive statistics are:

  • Frequency distribution, which tells you how frequently (i.e. how many times) a certain value occurs within your dataset.
  • Measures of central tendency, which estimate the middle or average values within your dataset. Measures of central tendency are the mean, median, and mode.
  • Measures of variability help you gauge how much variability or “spread” there is within your dataset; in other words, how spread out the values are. Measures of variability are range, standard deviation, and variance.

You’ll learn how these values are calculated when we get to section four, the practical component of our tutorial. For now, there’s one last piece of theory we need to complete the jigsaw. Proceed to section three! 🏃🏻

3. What is a pivot table?

A pivot table summarizes large amounts of data in a more digestible, at-a-glance format. It does this by grouping the data in a meaningful way, for example by showing the sum or average values of certain variables.

Let’s imagine you have data for a chain of department stores. Your dataset includes data on the sales made each month in two different store locations. You want to be able to see, at a glance, how each store performed across the year, but it’s impossible to know when faced with thousands of rows of data in a spreadsheet. Here’s where a pivot table can help. You’d add up the total sales for each store for each month of the year, and summarize the data in a pivot table, like so:

A pivot table in Google Sheets, summarising sales data across different stores and months of the year

You can find a more in-depth explanation of pivot tables in this guide, and you’ll learn how to create your own in the next section—our practical task for today! 🙌

4. Practical exercise: Descriptive statistics and pivot tables

It’s time to get hands on!

Now that you’re familiar with some of the theory behind descriptive statistics and pivot tables, let’s explore some of their applications in our Citi Bike dataset. We’ll be focusing on some of the key research questions we laid out at the beginning of the course, namely:

  1. What are the most popular locations across the city for Citi Bike rental?
  2. How does the average trip duration vary across different age groups, and over time? 
  3. Which age group rents the most bikes?
  4. How does bike rental vary across the two user groups (one-time users vs long-term subscribers) on different days of the week? 

You’ll be calculating the frequency distribution to identify the most popular pick-up locations for Citi Bike bicycles. We’ll also be calculating various descriptive statistics for the variables “Trip duration” and “User age” in order to get an idea of how Citi Bike usage varies across different age groups and at different times of the year. For these variables, we’ll calculate the mean and median values (measures of central tendency), as well as the minimum and maximum values.

To answer the first three questions, we’ll conduct a descriptive analysis using the following columns (variables) in our dataset:

  • Start station name (this indicates where the bike was picked up from)—column D in your dataset
  • Age (of the user)—column J in your dataset
  • Month—column N in your dataset. Each month of the year is denoted by a number, so January is represented by 1, February by 2, and so on.
  • Season (spring, summer, autumn, or winter)—column O in your dataset
  • Trip duration in minutes—column M in your dataset

Our analysis will consist of two parts:

  1. Calculating some of the descriptive statistics we covered earlier on: the mean, median, minimum, and maximum values for two variables of interest (trip duration in minutes and user age).
  2. Creating pivot tables to summarize our descriptive statistics.

So, let’s begin with some basic descriptive statistics. Onto task one!

Task 1: Calculate descriptive statistics for “Trip duration” and “User age”

  1. Log into your Google account and open up your (now clean) dataset in Google Sheets. You can access our view-only clean dataset here for reference. We’ve named this version “New York Citi Bikes_Clean ‘’.

    Tip 💡: If you got stuck on the data cleaning task in tutorial 2, make a copy of our clean dataset and use that version to continue :)
     
  2. Create a brand new sheet by clicking on the “+” sign in the bottom left corner:

    A dataset in Google Sheets. The plus icon has been selected to create a new blank sheet.
    Figure 1.

    By default, it will be called “Sheet 2” so we’re going to rename it “Descriptive statistics.” To do this, click on the downward arrow next to where it currently says “Sheet 2” and select “Rename” from the menu. Type “Descriptive Statistics” (the new name for your sheet) in the label field.

    The menu that appears in Google Sheets when you create a new blank sheet. The "Rename" option has been selected.
    Figure 2.


    Figure 3.
     
  3. Next, we’re going to create a very basic table. This will eventually contain all the descriptive statistics we’re about to calculate. Type “Statistic,” “Trip duration,” and “User age” into cells A1, B1, and C1 respectively. Then, under the “Statistic” column, type “Mean,” “Median,” “Minimum,” and “Maximum” into cells A2, A3, A4, and A5 respectively. You should end up with a very simple table like this one:

    A datasheet in Google Sheets with the column headings "Statistic," "Trip duration," and "Age." The "Statistic" column contains the categories "Mean, median, minimum, and maximum"
    Figure 4.

    We’ll now use formulas to calculate the relevant descriptive statistics for each of our two variables of interest: Trip duration in minutes and User age.
     
  4. First, we’re going to calculate the mean value for our first variable of interest: Trip duration in minutes. This will give us insight into the average length of a Citi Bike trip. Do Citi Bike users rent bikes for long durations, or do they tend to take shorter trips? Let’s find out!

    We’re going to use the AVERAGE formula to sum together all of our trip duration data, and then divide it by the number of values. So: Click in cell B2 (under “Trip duration” and next to “Mean”) and type the following formula:

    =AVERAGE(NYCitiBikes!M2:M146547)

    Here you can see it in action:

    The average formula typed into a cell in a Google Sheets file
    Figure 5.

    Let’s explain the workings of this formula. Our variable of interest (Trip_duration_in_min) is located in column M of the “NYCitiBikes” tab in our data file. Because the data we want is located in another tab, we start the formula with the notation “NYCitiBikes!”. Then we specify which column contains the data we want (column M) and the range to be considered (M2:M146547). Essentially, this formula specifies that the calculation of the AVERAGE should be made using the data from column M in the sheet named “NYCitiBikes.”
     
  5. Once you’ve typed the formula in cell B2, press “Enter.” The formula should work its magic and the mean value will now appear! This is the average trip duration taken with Citi Bike rental bikes (in minutes).

    A formula used to calculate the average value of a set of variables in Google Sheets
    Figure 6.
     
  6. Next, we’ll calculate the median value for our “Trip duration” variable. This will tell us the middle value in our dataset, which is useful because it’s a more robust measure of central tendency (compared to the mean, which is sensitive to extreme values). You can see a visual representation of the median of a set of numbers here:

    A string of numbers with the median (middle value) highlighted

    Again, we’re going to use a formula. Click in cell B3 and type the following formula, then press “Enter:”

    =MEDIAN(NYCitiBikes!M2:M146547)

    The median value will automatically appear in cell B3.

  7. Now let’s calculate the minimum and maximum values for our “Trip duration” variable. These are interesting because they show us how much variability there is within our dataset—in other words, how spread out are our trip duration values? Is there a huge difference in terms of the shortest trip (the minimum value) and the longest trip (the maximum value)? To calculate the minimum and maximum values, type the following formulas into cells B4 and B5 respectively:

    =MIN(NYCitiBikes!M2:M146547)

    =MAX(NYCitiBikes!M2:M146547)


    Again, the values will appear as soon as you press “Enter.” Here’s how your descriptive statistics table should now look:

    A simple table in Google Sheets. The median, mean, minimum, and maximum values have been calculated for the variable "trip duration" using formulas
    Figure 7.

    We now have a full set of descriptive statistics for our first variable of interest. Bravo! 💃 Let’s do the same again for our second variable: User age.
     
  8. We’ll use all the same formulas as before, but we’ll replace the range. This time, we want to pull data from the “Age” column, which is in column J of our “NYCitiBikes” sheet. All done? Hopefully, you now have a fully populated table of descriptive statistics:

    A simple table in Google Sheets. The median, mean, minimum, and maximum values have been calculated for the variable "Age" using formulas
    Figure 8.

Great work! 💫

Reviewing our descriptive statistics

As we mentioned earlier, descriptive statistics can help you to spot if something’s “off” with your dataset, or with a particular value. Looking at your table of descriptive statistics, can you spot anything fishy? 🐟

You’ll notice that the maximum values for our two variables, “Trip duration” and “User age,” are rather unrealistic. It’s highly unlikely that someone rode a Citi Bike for 337,670 minutes (that’s about 5,627 hours, or 234 days!) It’s also hard to believe that one of our Citi Bike users is 121 years old. Something’s clearly gone awry!

So what happens now?
You’ll need to do another iteration of data cleaning in order to remove those erroneous values. Here’s what you need to do:

  1. Navigate back to the “NYCitiBikes” tab and select the entire dataset. Once all the data is highlighted, click “Data” and then select “Sort range” from the dropdown menu. You’re going to sort the data by the column “Trip duration_in_min” in descending order:

    The pop-up window that appears in Google Sheets when the user selects to sort the data
    Figure 9.
     
  2. Scroll along to the right, to column M (Trip_duration_in_min), and you’ll now see the erroneous, extreme values right at the top:

    A column in Google Sheets showing data for the variable "Trip duration in minutes." Two outliers (extremely high values) have been selected
    Figure 10.

    To remove these outliers from your dataset and stop them skewing your results, you’re going to delete the entire row associated with them. This might ring a bell from tutorial 2! 🔔 To delete the rows containing the extreme values:
     
  3. Select both rows so they’re highlighted blue, then right-click and select “Delete rows 2-3” from the drop-down menu.

    Two rows of data selected in a Google Sheets file
    Figure 11.
     
  4. Now you need to do the same for the “Age” column. Follow the same steps used previously to “sort range” by “Age” (column J) in descending order. Again, you’ll see those unlikely values right at the top of the dataset (six rows containing a value of 121 for age). Select and delete those entire rows, too!

    The "Age" column in a dataset with extreme variables at the top. These extreme variables have been highlighted, ready to be deleted
    Figure 12.
    ​​​​
    Once you’ve removed those extreme (and erroneous) values from the “NYCitiBikes” dataset, navigate back to your “Descriptive Statistics” tab. You’ll notice that the values in your table have updated automatically. That’s the beauty of formulas!

    A simple table in Google Sheets showing the mean, median, minimum, and maximum values for the variables "Age" and "Trip duration."
    Figure 13.
    ​​​​​​

After this second iteration of data cleaning, we can see that the oldest Citi Bike user is 90, and the longest trip was 35,069 minutes (about 24 days!)—that’s still pretty impressive, but much more realistic than our previous calculations.

Having calculated some descriptive statistics for our dataset, we are starting to see some initial insights about our Citi Bike users. Have a go at answering the following questions to paint a picture of the Citi Bike user base:

  • What’s the average age of Citi Bike users?
  • What’s the average trip duration for Citi Bike users?
  • What were the shortest and longest trips taken by Citi Bike users?

These insights provide an interesting start, but they’re not enough to answer the main questions we set out at the beginning of the course. We’ve laid the foundations; now let’s start answering those key questions with another super-powerful tool: the pivot table.

Task 2: Create pivot tables

Pivot tables are a true staple in data analysis because of their versatility and the plethora of functions they offer. We’re going to use them now to answer four of our main questions:

  • What are the most popular pick-up locations across the city for Citi Bike rental?
  • How does the average trip duration vary across different age groups, and over time?
  • Which age group rents the most bikes?
  • How does bike rental vary across the two user groups (one-time users vs long-term subscribers) on different days of the week? 

Ready to create some pivot tables? Great!

Psst! If you got stuck with calculating descriptive statistics, or with that second iteration of data cleaning, no worries! You’ll find a cleaned dataset with all the right descriptive statistics here. We’ve called this version “New York Citi Bikes_Clean_v2.” Feel free to make a copy of it and use it for the next practical exercise. *

Task 2.1. Create a pivot table for the top 20 pick-up locations:

  1. Navigate to the “NYCitiBikes” sheet and select the entire dataset by clicking in the top left (the space between column A and row 1). Then click “Data” in the toolbar and select “Pivot table” from the dropdown menu.

    A dataset in Google Sheets. The “Data” option has been selected from the toolbar, and the “Pivot table” option selected from the drop-down menu.
    Figure 14.
     
  2. This will prompt a window which will ask you to confirm the range of selected data and to specify where the pivot table should be created (in a new, blank sheet, or in an existing sheet). It’s a good idea to put it in a new sheet, so make sure the “New sheet” option is selected and then click “Create.”

    The “Create a pivot table” pop-up window in Google Sheets. The “New Sheet” and “Create” options have been selected.
    Figure 15.

    This will create a new sheet called “Pivot table 1.”Let’s take a look at how this new sheet is structured. On the left hand side, you’ll see the beginnings of a table with labels for rows, columns, and values. The table is empty for now as we haven’t yet “poured” information into it. This is where the results will show up once we’ve customized the pivot table:

    A blank pivot table in Google Sheets
    Figure 16.

    Click on any cell in the pivot table and it’ll bring up the pivot table editor on the right hand side. You’ll use this editor to construct and customize your pivot tables.

    The pivot table editor window in Google Sheets
    Figure 17.

    You can see that there are some suggestions for summary statistics provided at the top of the pivot table editor, under the “Suggested” heading. As we have specific questions and goals in mind, we’ll ignore those for now and continue with our own plan of action!

    The first question we want to answer is What are the most popular pick-up locations across the city for Citi Bike rental? In this case, we’re interested in the “Start station name” column. We’ll calculate the frequency (another descriptive statistic) to see how many times each pick-up station occurs within the dataset. This will allow us to see which ones occur most frequently, and are therefore the most popular for Citi Bike pick-up.

    We want to add the “Start station name” variable under the “Rows” section of our pivot table and then count the number of occurrences for each station in the “Values” column.

    So:

  3. In the pivot table editor, click on “Add” next to the “Rows” item and select “Start Station Name” from the list of options provided.

    The pivot table editor in Google Sheets. The "Add" button next to "Rows" has been selected
    Figure 18.

    The pivot table editor in Google Sheets. The variable "Start station name" has been selected.
    Figure 19.


    For now, we’ll keep them in ascending order, so no need to change any of the default settings.

    The pivot table editor in Google Sheets
    Figure 20.
     
  4. You’ll see that the left-hand side of your pivot table has been populated with all the values from the “Start Station Name” column in your “NYCitiBikes” dataset:

    A pivot table in Google Sheets, showing the "Start station name" variable
    Figure 21.
     
  5. Now we’re going to calculate the frequency count for each station. In the pivot table editor, click “Add” next to the “Values” item and once again select “Start Station Name” from the options provided. Under “Summarize by,” leave the “COUNTA” option selected and “Show as → Default.”

    The "Values" field in the pivot table editor in Google Sheets
    Figure 22.

    Your pivot table will then be populated with the count (i.e. the frequency) of each start station. Success! 😃

    A pivot table showing the most popular start stations for bike rental in New York City
    Figure 23.

    We’ll use the “Pivot table 1” tab to create all the pivot tables we need, and we’ll copy the output of each pivot table into a new tab each time. So:
     
  6. Click on the “Add Sheet” button (the plus icon in the bottom left) and name your new sheet “Task 2.1. Top 20 pick-up locations.”
     
  7. In this new sheet, type “Start station name” into cell A1, and “COUNT” into cell B2.
     
  8. Now you’re going to copy the data from your pivot table into this new sheet. So, navigate back to the “Pivot Table” tab and select the data in the “Start Station Name” column. You only need to select the station names, not the column heading or the grand total. With the station names highlighted, right click and select “Copy”:

    A pivot table in Google Sheets
    Figure 24.
     
  9. Navigate to the “Task 2.1. Top 20 pick-up locations” tab and paste your copied data into the “Start station name” column, like so:

    A column of data in a Google Sheet, showing start station names for bike pick-up in New York City
    Figure 25.
     
  10. Now copy the “COUNTA of Start Station Name” data (excluding the column heading and the total) from your Pivot Table sheet into your “Task 2.1” sheet, this time placing it in the “Count” column:

    A simple table in Google Sheets, showing the count of each bike station pick-up location
    Figure 26.
     
  11. In the “Task 2.1. Top 20 pick-up locations” sheet, you’re going to sort the data by count, in descending order. Select the entire range of data by clicking in the top left corner (between A and 1), then select “Data” from the top toolbar and click “Sort range”:

    A dataset in Google Sheets. The “Data” option has been selected from the toolbar, and then “Sort range” has been selected from the drop-down menu.
    Figure 27.

    Sort the selected range by “Count” in descending order (Z → A):

    The pop-up window that appears when the user selects to sort the data in Google Sheets
    Figure 28.
    ​​​​

Your data is now arranged in such a way that you can easily pinpoint the top 20 pick-up locations for Citi Bike rentals in New York. There’s our answer to question one 🎉

Task 2.2. Create a pivot table to see how bike trip duration varies across different age groups, and over time:

Next, we want to explore how the average trip duration varies across different age groups, and over time. Once again, we’ll tap into the power of the mighty pivot table!

The first two variables we’ll look at are “Age group” and “Trip duration in minutes.” Still working in your “Pivot Table 1” tab, use the pivot table editor to do the following:

  1. Empty out your existing pivot table by clicking the “X” next to the current values. This will delete them, leaving you with a blank pivot table.

    The pivot table editor in Google Sheets
    Figure 29.
     
  2. Next, add “Age Groups” to the “Rows” field, and “Trip_duration_in_min” to the “Values” field. For “Trip duration,” select the option to summarize by average (rather than sum). Here’s how the pivot table editor will look now:

    The pivot table editor in Google Sheets
    Figure 30.

    Once again, the pivot table will fill up with the values you’ve selected. Now, you can see the average trip duration for each age group:

    A pivot table in Google Sheets showing average bike trip duration across different age groups
    Figure 31.
     
  3. Just as we did before, create a new sheet and name it “Task 2.2. Trip duration / age group.” Type “Age group” and “Average trip duration” into cells A1 and B1 respectively. These are your column headings, ready for you to copy the data over from your pivot table.
     
  4. Navigate back to the pivot table tab and copy first the age group data into your new “Task 2.2.” sheet, and then the average trip duration data. As before, you don’t need to copy over the headings or the grand total from your pivot table.

    Your “Task 2.2. Trip duration / age group” sheet should now look like this:

    A simple table in Google Sheets showing the average bike trip duration across different age groups
    Figure 32.
     
  5. Use the “Data → Sort range” function we used before to sort the data by “Average trip duration.” This will allow you to see, at a glance, which age groups took the shortest trips and which age groups took the longest.

    Again, in just a few clicks, we’ve answered another of our key questions! The resulting pivot table gives us clear, at-a-glance insights into how bike tripe durations vary across different age groups. We can see that those in the 75+ category tend to take the longest trips on average, while 65-74 year olds take the shortest trips on average. Pretty cool, huh? 😎

    Now we want to see how the average trip duration varies over time—that is, at different times of the year. This time, we’ll be looking at the variables “Trip duration in minutes” and “Month.” In the “Pivot Table 1” sheet:

  6. Remove the “Age group” variable that is currently selected for “Rows” (in the pivot table editor) and replace it with “Month.” You can leave the “Values” section as it is, as the variable we want is already selected (average trip duration in minutes). Your pivot table will now look something like this:

    A pivot table in Google Sheets showing average trip duration taken on rental bikes in each month of the year
    Figure 33.

    Looking at our pivot table, we get a broad idea of how the average trip duration fluctuates throughout the year. At a glance, we can see that Citi Bike users tend to take the shortest trips in January, and the longest trips in September. Any guesses as to why that might be? ❄️🍂
     
  7. Last but not least, be sure to copy the output of your pivot table into a new sheet. Create a new tab and name it “Task 2.2. Trip duration / month,” type “Month” and “Average trip duration in minutes” as your column headings (in cells A1 and B1 respectively), and then copy the data over from your pivot table. As always, you can exclude the grand total. You should end up with something that looks like this:

A simple table in Google Sheets showing the average trip duration in minutes taken with Citi Bikes in each month of the year
Figure 34.

Task 2.3. Create a pivot table to see which age group rents the most bikes

Based on one of the pivot tables we created previously (average trip duration per age group), you might jump to the conclusion that the 75+ age group makes up the biggest segment of our Citi Bike customer base. They take the longest trips on average, after all! But does this mean that they rent more bikes than users in other age groups do?

Not necessarily. In order to answer our third question—which age group rents the most bikes?—we need to create another pivot table. This time, we’re looking at the number of unique bike rentals (calculated using the variable “Bike ID”) and summarizing the count by user age (using the “Age group” variable). So:

  1. In the pivot table tab, clear out the contents of the pivot table by removing the current selections in the pivot table editor.
  2. Next, add the “Age groups” variable to the “Rows” field and add the “Bike ID” variable to the “Values” field, summarizing by “COUNTA.” Your new pivot table should look something like this:

    A pivot table in Google Sheets showing the number of bikes rented across different age groups
    Figure 35.
     
  3. Copy your pivot table output into a new sheet named “Task 2.3. Bike rental / age group.” This time, your column headings will be “Age group” and “Count of Bike ID”.

    As you’re now accustomed to doing, you can sort the data in your “Task 2.3” sheet by “Count of Bike ID” in descending order. This way, you’ll be able to see, at a glance, what the data is telling you. Your new sheet will look something like this:

    A simple table in Google Sheets showing the number of bikes rented across different age groups
    Figure 36.

You can see that users aged 75+ rented the least bikes (they just took longer trips on them!) The highest number of bikes were actually rented by those in the 35-44 age range. Interesting!

Task 2.4. Create a pivot table to see how bike rental varies across the two user groups (one-time users vs. long-term subscribers) on different days of the week

At the start of the course, we posed another interesting question: **How does bike rental vary across the two user groups (one-time users vs long-term subscribers) on different days of the week? **

Once again, we can answer this question by creating a pivot table:

  1. In the pivot table tab, clear out the existing values. Once you have a blank pivot table, add “Weekday” to the “Rows” field:

    The pivot table editor in Google Sheets
    Figure 37.
     
  2. Then add “User Type” to the “Columns” field. The pivot table will still appear empty because we haven’t got anything in the “Values” field yet. So:
     
  3. Under “Values,” add “Bike ID” and summarize it by “COUNTA.” Here’s how the pivot table editor should look:

    The pivot table editor in Google Sheets
    Figure 38.

    The pivot table will now contain the data you’re interested in: the number of bikes rented on each day of the week for both one-time users and long-term Citi Bike subscribers:

    A pivot table showing the number of bikes rented by one-time users and subscribers on different days of the week
    Figure 39.
     
  4. As a final step, create a new sheet and call it “Task 2.4. User type / weekday.” Copy the data from your pivot table over to this new sheet, as you’ve done for all previous pivot tables. It should look like this:

    A simple table in Google Sheets showing the number of bikes rented by one-time users and subscribers on different days of the week
    Figure 40. 

This summary of the data is a good start in helping us to understand how bike rental varies across the two user groups on different days of the week, but it’s difficult to answer our question just by looking at the numbers in the columns. This is where data visualization comes in really handy—and, as luck would have it, that’s what we’ll be looking at in the next tutorial!

5. Key takeaways and further reading

As you’ve seen, the possibilities of pivot tables are endless! 🤩 In just a few clicks, we’ve uncovered some key insights into our data and what it tells us about Citi Bike’s customer base. We now know that Grove St Path and Exchange Place are the most popular pick-up locations for bike rental, and that users aged 35-44 rent the most bikes, but users aged 75+ tend to take the longest trips on average. We’ve also seen that the longest trips happen in September, while the shortest trips happen in January ❄️

Here’s a summary of what we covered:

  • In task 2.1, you created a pivot table to show the top 20 most popular pick-up locations for Citi Bikes. You looked at the frequency of the variable “Start Station Name.” Key finding: Grove St Path is the most popular pick-up station.
     
  • In task 2.2, you created a pivot table to see how bike trip duration varies (a) across different age groups, and (b) over time. First, you looked at the average trip duration in minutes for each age group. Key finding: Over 75s took the longest trips on average. You then looked at the average trip duration in minutes for each month of the year. Key finding: On average, Citi Bike users took the longest trips in September and the shortest trips in January.
     
  • In task 2.3, you created a pivot table to see which age groups rented the most bikes. You looked at the count of each individual bike ID across each age group. Key finding: 35-44 year olds rented the most bikes.
     
  • In task 2.4, you created a pivot table to see how bike rental varies across the two Citi Bike user groups (one-time users vs. subscribers) on different days of the week. You looked at the variables “Weekday,” “User Type,” and count of “Bike ID.” Key findings: Most Citi Bike users are long-term subscribers. With this more complex pivot table, it’s not so easy to get the insights you need. This is where data visualization helps, which we’ll look at in the next tutorial.

Whew! You’ve covered a lot of ground in a short space of time. It’s amazing what you can do with some simple data analysis techniques, isn’t it?! And this is just the tip of the iceberg. Imagine what you could achieve as a professional data analyst working in the field 🤩

In the next tutorial, we’ll create data visualizations to help us convey and communicate our key findings. Want to keep exploring in the meantime? We’ve got you covered:

See you in tutorial four! 👋

Take the quiz below to make sure you've learned all the important information—and that it really sticks! 

Alana

Senior Program

Advisor

Alana

Intrigued by a career in data analytics? Arrange a call with your program advisor today to find out if data analytics is a good fit for you—and how you can become a data analyst from scratch with the full CareerFoundry Data Analytics Program.