<jupyter_start><jupyter_text>Using Pivot Tables<jupyter_code>import transportation_tutorials as tt
import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Questions 1. Within the Jupiter study area, what is the average distance for bike tours to work?   *(Hint: It is 4.03 miles)*2. What tour purpose has the highest average tour distance? *(Hint: Work tours)*2. What is the median distance of walking for all tour purposes? *(Hint: 0.548 miles)*  DataTo answer the questions, use the following data file:<jupyter_code>tour = pd.read_csv(tt.data('SERPM8-BASE2015-TOURS'))
tour.head()
tour_mode_dict = {
    1: "DRIVEALONEFREE",
    2: "DRIVEALONEPAY",
    3: "SHARED2GP",
    4: "SHARED2PAY",
    5: "SHARED3GP",
    6: "SHARED3PAY",
    7: "TNCALONE",
    8: "TNCSHARED",
    9: "WALK",
    10: "BIKE",
    11: "WALK_MIX",
    12: "WALK_PRMW",
    13: "WALK_PRMD",
    14: "PNR_MIX",
    15: "PNR_PRMW",
    16: "PNR_PRMD",
    17: "KNR_MIX",
    18: "KNR_PRMW",
    19: "KNR_PRMD",
    20: "SCHBUS",
}<jupyter_output><empty_output><jupyter_text>Solution To find the answers in this exercise, we need to deal with three variables: `tour_mode`, `tour_purpose` and `tour_distance`. Let's look at the data type of these three variables. We use `.info()` to view the data types of dataframe columns.<jupyter_code>tour[['tour_mode', 'tour_purpose', 'tour_distance']].info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 47485 entries, 0 to 47484
Data columns (total 3 columns):
tour_mode        47485 non-null int64
tour_purpose     47485 non-null object
tour_distance    47485 non-null float64
dtypes: float64(1), int64(1), object(1)
memory usage: 1.1+ MB<jupyter_text>As we can see, `tour_mode` column in the data is categorized as integers. It is often helpful to have string category for easier exploration. Given that we know the notations of integer categories, we can convert the integer categories to string categories using a dictionary like `tour_mode_dict`.<jupyter_code>tour['tour_mode'] = tour['tour_mode'].map(tour_mode_dict)
tour.tour_mode.value_counts()
tour.tour_purpose.value_counts()<jupyter_output><empty_output><jupyter_text>Now that we have the perfect data types for required variables, we create a pivot table using `.pivot_table()` method and set the categorical columns (`tour_mode` and `tour_purpose`) in `tour` dataframe as `index` and `columns`. Then, we can set the continuous variable column, `tour_distance` as `values` in the `.pivot_table()` method.Please note that `.pivot_table()` method by default calculates the average values of the column that is set as `values`.<jupyter_code>tour.pivot_table(index = 'tour_mode', columns = 'tour_purpose', values = 'tour_distance')<jupyter_output><empty_output><jupyter_text>From the pivot table that we just created, we can see that the average distance of biking to work is 4.034763 mile, which is the answer to our first question listed above. ____ To answer the second question, we just need a one-dimensional pivot table that we can create by ignoring either `index` or `columns` in `.pivot_table()` method.<jupyter_code>tour.pivot_table(columns = 'tour_purpose', values = 'tour_distance')<jupyter_output><empty_output><jupyter_text>It is pretty clear that the work tours have the highest average tour distance as shown in the table above.  _____ If we want to create a pivot table with median values of the column assigned in `values`, we can use `aggfunc` argument to specify that requirement. Again, we can use a one-dimensional pivot table to find the answer of third question.<jupyter_code>tour.pivot_table(index = 'tour_mode', values = 'tour_distance', aggfunc = ['median'])<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup