Data Preprocessing

This homework tests your abilities to manipulate pandas Dataframe and extract theinformationneeded.

1. Read the file 311_data.csv provided to you in the StarterCode section of your workbench and store the data in adataframe.

2. Create adataframecontaining the columns: 'Created Date','Closed Date','Borough','Descriptor','Complaint Type', 'Agency', 'Longitude', 'Latitude', 'Status'

3. Add the column 'processing_time'(in days) to thisdataframe.

4. Create a new column - 'start_time_window' - that contains the hour of the day that the incident report was created

5. output acsvfile named output1.csv containing thedataframe, the column should be in the order of'Created Date','Closed Date','Borough','Descriptor','Complaint Type', 'Agency', 'Longitude', 'Latitude', 'Status','processing_time', 'start_time_window'.(hint: usedf.to_csv('output1.csv', index = False)to output the csv. Note, the index = False means not to write a index column.)

The first few lines should look like:

Created Date Closed Date Borough Descriptor Complaint Type Agency Longitude Latitude Status processing time start time_window 2016-01-01 2016-01-01 Loud Noise - 00:00:09 01:57:32 BROOKLYN Music/Party Street/Sidewalk NYPD -73.982840 40.701823 Closed 1 0 days 01:57:23 0 2016-01-01 2016-01-01 00:00:40 03:12:53 BRONX Loud Music/Party Noise - Residential NYPD -73.886111 40.875565 Closed 0 days 03:12:13 0 2 2016-01-01 2016-01-21 00:01:09 09:20:55 BRONX NO LIGHTING ELECTRIC HPD -73.878885 40.884277 Closed 20 days 09:19:46 0 3 2016-01-01 2016-01-01 00:02:59 23:35:50 Unspecified Loud Music/Party Noise - Residential NYPD NaN NaN Closed 0 days 23:32:51 0 2016-01-01 2016-01-08 BRONX ENTIRE 00:03:03 01:13:00 BUILDING HEAT/HOT WATER HPD -73.896607 40.857841 Closed 7 days 01:09:57 0jupyter pandasprocessing (autosaved) Control Panel File Edit View Insert Cell Kernel Widgets Help Not Trusted | Python 3 [3.6] O B + 2 2 5 + + HC Code In [1]: import pandas as pd import numpy as np import datetime def transform( ): # ## ### YOUR CODE HERE # ## return df . to_cav( 'output1.cav') In [2]: # Read-only transform( ) df = pd. read_csv( 'output1. cav', index_col = 0) df Out [2] : Created Closed Date Borough Descriptor Complaint Type Agency Longitude Latitude Status processing_time start_time_window Date 2016-01- 2016-01- 01 BROOKLYN Loud Music/Party Noise - 0 days 01 00:00:09 01:57:32 Street/Sidewalk NYPD -73.982840 40.701823 Closed 01:57:23.000000000 2016-01- 2016-01- 01 BRONX Loud Music/Party Noise 0 days Residential o 00:00:40 03:12:53 NYPD -73.836111 40.875565 Closed 03:12:13.000000000 2016-01- 2016-01- NO LIGHTING ELECTRIC 20 days 2 01 00:01:09 09:20:55 BRONX HPD -73.878385 40.884277 Closed op:19:46.000000000 2016-01- 2016-01- 01 01 Unspecified Loud Music/Party Noise - Residential NEN NaN Closed 23:32:51.000000000 0 days 3 NYPD 00:02:58 23:35:50 O 2016-01- 2016-01- 00:03:03 01:13:00 BRONX ENTIRE BUILDING HEAT/HOT HPD -73.896607 40.857841 Closed 01:09:57.000000000 7 days WATER o 2016-01- 2016-01- 01 01 QUEENS Loud Music/Party Noise - 0 days Residential NYPD -73.86210 40.745728 Closed na:21-43 0noononon In [3]: ### ### AUTOGRADER TEST - DO NOT REMOVE ### Length of output. OK. 10/10 points In [4]: sample out [4] Unnamed: Created 0 Date Date Borough Descriptor Complaint Type Agency Longitude Latitude Status processing time start_time_ 2018- 2016- 0 01-01 01-01 Noise - 0 days 00:00:09 01:57:32 BROOKLYN Loud Music/Party Street/Sidewalk NYPD -73.982840 40.701823 Closed 01:57:23.000000000 2018- 2018- Noise - 0 day's 01-01 01-01 00:00:40 03:12:53 BRONX Loud Music/Party Residential NYPD -73.886111 40.875585 Closed 03:12:13.000000000 2016- 2018- 20 days 2 01-0 01-21 09:20:55 BRONX NO LIGHTING ELECTRIC HPD -73.878585 40.884277 Closed 00:01:09 08:19:46.000000000 2016- 2018- 0 days