Transport for London (TfL) Cycle Data Analysis Project – Programming & Data Science

a. Make a list containing the three station names assigned to your group.

b. Create a counter (set to zero) which will be used to number the rows in your output file.

The main part of this step will append rows to the output file. If we run this more than once the file will get too large, so we will start by ensuring that the output file is empty. To do this

c. Open the file project_data.csv using the option “w” to write to the file, and then use the command pass to do nothing with this file open. This will create an empty version of the file.

The main part of your code for this step will loop through the various csv data files and import the relevant data. This will be much easier if you ensure that the data files are in the same directory as your code. You will also need to unzip the files from the zipped version given on Moodle.

d. Create a loop variable i running over the years 2014-2019, and inside this create a second loop variable j running from 1 to 4. Inside these loops complete the following steps.

e. Create a string corresponding to the file name for the data in quarter j of year i. For example if i=2015 and j=3 then the string should be “2015-Q3-Central.csv”.

f. Open the csv file with the given string as filename and while it is open complete the following steps.

g. For each row in the file, if the entry in position 1 corresponds to one of the stations in your station list then:

1. Replace the entry in position 0 of the row by a string made up of the year and quarter of the file. For example, with the file mentioned above this should be 2015Q3.

2. Recall that your row is regarded as a list while you work with it. Make a new list called out_row by appending the current counter value to the beginning of the list. Then increase the counter value by 1.

3. Open the file project_data.csv in append mode and add the out_row to this file.

After you have done the above your file project_data.csv should contain all of the data for your three stations with the year and quarter in a better format and with a new initial column numbering the rows. The looped code above might take a while to run so do not be concerned if it takes some time! To test it you might want to initially run a version with fewer years in it.

h. Define a list called col_names containing the column names “Quarter”, “Station”, “Date”, “Weather”, “Time”, “Day”, “Drop1”, “Direction”, “Drop2”, “Mode” and “Count”. These are almost the names in the original file, but we have used “Drop1” and “Drop2” to label the columns we plan to drop.

i. Use Pandas to read the file project_data.csv that you have just created as a dataframe using col_names for the column names.

j. Drop the columns labelled “Drop1” and “Drop2” from the dataframe.

k. Create a new column in your dataframe labelled “Full_time” by concatenating the entries in the columns labelled by “Date” and “Time” with a space inserted between them.

l. Output your final dataframe as the file CycleData.xlsx on a sheet called Sheet1.

a. Load the file called CycleData.xlsx as a dataframe using Pandas, using column 0 as the index column. Call this dataframe my_data.

b. Ask the user to enter various choices of data using the following prompts:

1. “Enter desired station: ” (store this as station)

2. “Enter desired direction: ” (store this as direction)

3. “Do you want to restrict to private cycles only (Y/N)?: ” (store this as private_only)

4. “Do you want to display the date by time (T) or by date and time (D)?: ” (store this as period_type)

5. “Do you want to colour code by Weather, Direction, or Mode?: ” (store this as shade)

c. For each prompt your code should use an appropriate method to ensure that it is in a consistent format (eg always upper case, or lower case, etc.).

d. Write a function which given a station, a shade, and a period_type will produce a Seaborn scatterplot using for x values either the “Time” column (if T was chosen) or the “Full_time” column (if D was chosen) and for y values the “Count” column, with hue given by the choice of shade, and data restricted to the rows of the dataframe corresponding to the station choice.

e. Try out your function using the date and time option. You will see the default output is too compressed. Adjust your function so that the scatterplot has figsize(20,5) and try again.

We now want to create three variants of the function defined in steps d and e.

f. Create three further copies of your function (with different names but keeping the same figsize) and modify them as follows:

1. One version should restrict the data to the rows corresponding to those with the given station choice and only where the Mode is “Private cycles”.

2. One version should use an additional input variable called direction. This version should restrict the data to the rows corresponding to those with both the given station choice and direction choice.

3. The final version should also use an additional input variable called direction. This version should restrict the data to the rows corresponding to those with the given station choice and direction choice and only where the Mode is “Private cycles”.

Write some code which depending on the various input values from step b above calls the relevant function from the four just defined. If “Any” is entered as the direction by the user your code should call the relevant function without restriction on direction. You should now be able to plot the data either by time or by date and time for a given station in various ways.

a. Load the file called CycleData.xlsx as a dataframe using Pandas, using column 0 as the index column. Call this dataframe my_data.

b. As in Step 2, ask the user to enter a desired station and direction, and ensure that these are in a consistent format using a suitable method to adjust them.

c. Write a function which given the user’s choice of station and direction, plots the average number of cycles at that time for that choice of variables. To do this:

1. Create a new dataframe by restricting to the rows of the original dataframe corresponding to the given choice of station and direction.

2. Use the groupby method to create a new dataframe where you group by “Date” and “Time” and apply the groupby.sum method to the “Count” column. This adds the private and hired values together for each date and time.

3. Take this new dataframe and apply the groupby method again to create another dataframe where you only group by “Time” and apply the groupby.mean function to the “Count” column. This calculates the mean of the various values for each given timeslot.

4. Plot a scatterplot as in step 3 of “Time” against “Count” for this final dataframe. You should have a much simpler plot with just one point per time period.

a. Load the file called CycleData.xlsx as a dataframe using Pandas, using column 0 as the index column. Call this dataframe data.

b. Write a function which given a choice of station does the following:

1. Makes a new dataframe by using a mask to only copy the rows for the given station choice.

2. We now want to fit a decision tree to our data, comparing Time and Count. An example of this procedure was carried out in the handbook, after the example of a linear regression.

3. Unfortunately the time variable as provided is regarded as a string by Python. So we need to convert it into a datetime variable. Use the Pandas to_datetime method on the column of Time data where the format is given by “%H:%M:%S” to create a new column in your dataframe called RealTime. You will need to look this up on the web. Once done the RealTime column can be used as numerical data.

4. Form arrays x and y corresponding to RealTime and Count by reshaping them.

5. Split the data into training sets and test sets using the train_test_split function from sklearn.model_selection. To ensure that your training sets match those in my answer use random_state=1 inside this function.

6. Use a DecisionTreeRegressor to fit the training data. You will need to choose a max_depth value; the plots at the end of this question will enable you to tweak this to the value you think most appropriate, but try starting with depth 2 or 3.

7. Plot your decision tree (it should look like the example in Figure 26.1 of the handbook).

8. Add a column to the dataframe of the predicted values from your regressor for all values (not just those in the test set).

9. Use the Seaborn scatterplot to plot the Count column (on the y axis) against the RealTime column (on the x axis) as in the previous steps.

10. Add a scatterplot of this new column against the RealTime column to your previous scatterplot (you can do this just by calling the scatterplot command again).

We want to use the predicted values to suggest two periods of time each day when TfL should charge for peak usage. Try different values for the max_depth, and pick the one that you think is the most appropriate, based on the reasonableness of the scatterplot.

Notice that the decision tree does not present the datetime version of the data in an easily readable form. If we had more time we would work out what time periods correspond to the choice you have made.