Unnamed: 0 Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Address Type City Landmark Status Borough
2869 2869 10/30/2013 09:14:47 AM 10/30/2013 10:48:51 AM NYPD New York City Police Department Illegal Parking Double Parked Blocking Traffic Street/Sidewalk 11217.0 PLACENAME BROOKLYN BARCLAYS CENTER Closed BROOKLYN
23571 23571 10/25/2013 02:33:54 PM 10/25/2013 03:36:36 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 10000 PLACENAME NEW YORK CENTRAL PARK Closed MANHATTAN
41625 41625 10/22/2013 09:33:56 PM 10/24/2013 05:37:24 PM TLC Taxi and Limousine Commission For Hire Vehicle Complaint Car Service Company Complaint Street 11430 PLACENAME JAMAICA J F K AIRPORT Closed QUEENS
44331 44331 10/22/2013 07:25:35 AM 10/25/2013 10:40:35 AM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 11430 PLACENAME JAMAICA J F K AIRPORT Closed QUEENS
46913 46913 10/21/2013 05:03:26 PM 10/23/2013 09:59:23 AM DPR Department of Parks and Recreation Dead Tree Dead/Dying Tree Street 11215 PLACENAME BROOKLYN BARTEL PRITCHARD SQUARE Closed BROOKLYN
47459 47459 10/21/2013 02:56:08 PM 10/29/2013 06:17:10 PM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 10031 PLACENAME NEW YORK CITY COLLEGE Closed MANHATTAN
48465 48465 10/21/2013 10:44:10 AM 10/21/2013 11:17:47 AM NYPD New York City Police Department Illegal Parking Posted Parking Sign Violation Street/Sidewalk 11434 PLACENAME JAMAICA PS 37 Closed QUEENS
51837 51837 10/20/2013 04:36:12 PM 10/20/2013 06:35:49 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 10031.0 PLACENAME NEW YORK JACKIE ROBINSON PARK Closed MANHATTAN
51848 51848 10/20/2013 04:26:03 PM 10/20/2013 06:34:47 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 10031.0 PLACENAME NEW YORK JACKIE ROBINSON PARK Closed MANHATTAN
54089 54089 10/19/2013 03:45:47 PM 10/19/2013 04:10:11 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 10000.0 PLACENAME NEW YORK CENTRAL PARK Closed MANHATTAN
54343 54343 10/19/2013 01:27:43 PM 10/28/2013 08:42:12 AM DOT Department of Transportation Street Condition Rough, Pitted or Cracked Roads Street 10003.0 PLACENAME NEW YORK UNION SQUARE PARK Closed MANHATTAN
55140 55140 10/19/2013 02:02:28 AM 10/19/2013 02:19:55 AM NYPD New York City Police Department Noise - Vehicle Car/Truck Music Street/Sidewalk 11368.0 PLACENAME CORONA WORLDS FAIR MARINA Closed QUEENS
57789 57789 10/18/2013 11:55:44 AM 10/23/2013 02:42:14 PM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 11369.0 PLACENAME EAST ELMHURST LA GUARDIA AIRPORT Closed QUEENS
63119 63119 10/17/2013 06:52:37 AM 10/25/2013 06:49:59 PM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 11430.0 PLACENAME JAMAICA J F K AIRPORT Closed QUEENS
66242 66242 10/16/2013 01:56:24 PM 10/22/2013 03:09:11 PM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 11369 PLACENAME EAST ELMHURST LA GUARDIA AIRPORT Closed QUEENS
66758 66758 10/16/2013 11:52:43 AM 10/16/2013 04:35:34 PM NYPD New York City Police Department Vending Unlicensed Park/Playground 10036 PLACENAME NEW YORK BRYANT PARK Closed MANHATTAN
66786 66786 10/16/2013 11:42:23 AM 10/18/2013 04:57:04 PM TLC Taxi and Limousine Commission Taxi Complaint Insurance Information Requested Street 10003 PLACENAME NEW YORK BETH ISRAEL MED CENTER Closed MANHATTAN
66809 66809 10/16/2013 11:36:54 AM 10/16/2013 12:34:23 PM NYPD New York City Police Department Traffic Congestion/Gridlock Street/Sidewalk 11430 PLACENAME JAMAICA J F K AIRPORT Closed QUEENS
67465 67465 10/16/2013 09:14:35 AM 10/16/2013 12:43:06 PM NYPD New York City Police Department Traffic Drag Racing Street/Sidewalk 11367 PLACENAME FLUSHING QUEENS COLLEGE Closed QUEENS
72424 72424 10/15/2013 12:22:00 AM 10/21/2013 12:16:15 PM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 11217 PLACENAME BROOKLYN BARCLAYS CENTER Closed BROOKLYN
75531 75531 10/14/2013 10:59:20 AM 10/14/2013 03:09:51 PM NYPD New York City Police Department Vending In Prohibited Area Park/Playground 10000 PLACENAME NEW YORK CENTRAL PARK Closed MANHATTAN
77918 77918 10/13/2013 03:16:03 PM 10/13/2013 03:25:45 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 10000 PLACENAME NEW YORK CENTRAL PARK Closed MANHATTAN
78048 78048 10/13/2013 01:06:02 PM 10/21/2013 10:20:21 AM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 11369 PLACENAME EAST ELMHURST LA GUARDIA AIRPORT Closed QUEENS
78352 78352 10/13/2013 05:14:33 AM 10/16/2013 01:42:42 PM TLC Taxi and Limousine Commission For Hire Vehicle Complaint Car Service Company Complaint Street 11217 PLACENAME BROOKLYN BARCLAYS CENTER Closed BROOKLYN
78383 78383 10/13/2013 03:50:02 AM 10/13/2013 05:03:13 AM NYPD New York City Police Department Noise - Vehicle Car/Truck Music Street/Sidewalk 11368 PLACENAME CORONA WORLDS FAIR MARINA Closed QUEENS
79078 79078 10/12/2013 09:53:17 PM 10/13/2013 02:52:07 AM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 10011 PLACENAME NEW YORK WASHINGTON SQUARE PARK Closed MANHATTAN
84489 84489 10/10/2013 07:16:16 PM 10/10/2013 10:29:16 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 11215 PLACENAME BROOKLYN PROSPECT PARK Closed BROOKLYN
84518 84518 10/10/2013 07:02:29 PM 10/10/2013 10:29:16 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 11215 PLACENAME BROOKLYN PROSPECT PARK Closed BROOKLYN
84688 84688 10/10/2013 05:39:19 PM 10/10/2013 10:29:17 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 11215 PLACENAME BROOKLYN PROSPECT PARK Closed BROOKLYN
84695 84695 10/10/2013 05:37:04 PM 10/10/2013 10:30:19 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 11215 PLACENAME BROOKLYN PROSPECT PARK Closed BROOKLYN
88812 88812 10/09/2013 09:17:15 PM 10/23/2013 02:15:21 PM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 11430 PLACENAME JAMAICA J F K AIRPORT Closed QUEENS
89205 89205 10/09/2013 06:01:48 PM 10/09/2013 09:04:26 PM NYPD New York City Police Department Vending Unlicensed Park/Playground 10000 PLACENAME NEW YORK CENTRAL PARK Closed MANHATTAN
89382 89382 10/09/2013 04:53:01 PM 10/18/2013 08:35:02 AM DOT Department of Transportation Public Toilet Damaged Door Sidewalk 11238 PLACENAME BROOKLYN GRAND ARMY PLAZA Closed BROOKLYN
89734 89734 10/09/2013 03:13:23 PM 10/09/2013 05:10:45 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 10036 PLACENAME NEW YORK BRYANT PARK Closed MANHATTAN
93990 93990 10/08/2013 06:14:15 PM 10/09/2013 04:00:59 PM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 10003 PLACENAME NEW YORK BETH ISRAEL MED CENTER Closed MANHATTAN
99407 99407 10/07/2013 03:56:11 PM 10/08/2013 07:04:14 AM DPR Department of Parks and Recreation Overgrown Tree/Branches Traffic Sign or Signal Blocked Street 11430.0 PLACENAME JAMAICA J F K AIRPORT Closed QUEENS
99847 99847 10/07/2013 02:33:21 PM 10/09/2013 02:36:42 PM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 10036.0 PLACENAME NEW YORK PORT AUTH 42 STREET Closed MANHATTAN
100073 100073 10/07/2013 01:36:02 PM 10/09/2013 09:56:55 AM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 10024.0 PLACENAME NEW YORK MUSEUM NATURAL HIST Closed MANHATTAN
101013 101013 10/07/2013 10:05:18 AM 10/09/2013 03:36:23 PM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint Street 10017.0 PLACENAME NEW YORK GRAND CENTRAL TERM Closed MANHATTAN
104020 104020 10/06/2013 02:58:47 PM 10/07/2013 12:11:16 PM TLC Taxi and Limousine Commission For Hire Vehicle Complaint Car Service Company Complaint Street 11430.0 PLACENAME JAMAICA JFK Closed QUEENS
106118 106118 10/05/2013 03:24:47 PM 10/05/2013 04:20:34 PM NYPD New York City Police Department Noise - Park Loud Music/Party Park/Playground 10000.0 PLACENAME NEW YORK CENTRAL PARK Closed MANHATTAN
106499 106499 10/05/2013 11:52:13 AM 10/07/2013 08:00:28 AM DOT Department of Transportation Public Toilet Dirty/Graffiti Sidewalk 11369.0 PLACENAME EAST ELMHURST LA GUARDIA AIRPORT Closed QUEENS
this is how my data looks like. What I want to do here is to extract date from created date and closed date I have tried .extract method but I am new to this so, it didn't work. I want to calculate housrs from created date and closed which i can do like this:
pd.Timedelta(task3['Closed Date'] - task3['Created Date']).seconds / 60.0
In the end I want the output Find average completion time in hours for top-10 most frequent complaints. Also calculate how many data points you have for each complaint types. Do this analysis only for closed complaints. The sample output I want is as follows:
mean count
complaint Type closing_time_hours closing_time_hours
Blocked Driveway 3.00 4581
DOF Literature Request 30.16 5481
General Construction 66.38 798
Heating 54.88 6704
Illegal Parking 3.08 3336
Nonconst 65 100
Paint-Plaster 49 3281
Plumbing 65 666
Strret Condition 81 2610
Street Light Condition 90 4207
these mean and count values are randomly generated The sample output can be produced by groupby function once the hours are extracted from the data. dictionary format
{'Unnamed: 0': {2869: 2869,
23571: 23571,
41625: 41625,
44331: 44331,
46913: 46913,
47459: 47459,
48465: 48465,
51837: 51837,
51848: 51848,
54089: 54089,
54343: 54343,
55140: 55140,
57789: 57789,
63119: 63119,
66242: 66242,
66758: 66758,
66786: 66786,
66809: 66809,
67465: 67465,
72424: 72424,
75531: 75531,
77918: 77918,
78048: 78048,
78352: 78352,
78383: 78383,
79078: 79078,
84489: 84489,
84518: 84518,
84688: 84688,
84695: 84695,
88812: 88812,
89205: 89205,
89382: 89382,
89734: 89734,
93990: 93990,
99407: 99407,
99847: 99847,
100073: 100073,
101013: 101013,
104020: 104020,
106118: 106118,
106499: 106499},
'Created Date': {2869: '10/30/2013 09:14:47 AM',
23571: '10/25/2013 02:33:54 PM',
41625: '10/22/2013 09:33:56 PM',
44331: '10/22/2013 07:25:35 AM',
46913: '10/21/2013 05:03:26 PM',
47459: '10/21/2013 02:56:08 PM',
48465: '10/21/2013 10:44:10 AM',
51837: '10/20/2013 04:36:12 PM',
51848: '10/20/2013 04:26:03 PM',
54089: '10/19/2013 03:45:47 PM',
54343: '10/19/2013 01:27:43 PM',
55140: '10/19/2013 02:02:28 AM',
57789: '10/18/2013 11:55:44 AM',
63119: '10/17/2013 06:52:37 AM',
66242: '10/16/2013 01:56:24 PM',
66758: '10/16/2013 11:52:43 AM',
66786: '10/16/2013 11:42:23 AM',
66809: '10/16/2013 11:36:54 AM',
67465: '10/16/2013 09:14:35 AM',
72424: '10/15/2013 12:22:00 AM',
75531: '10/14/2013 10:59:20 AM',
77918: '10/13/2013 03:16:03 PM',
78048: '10/13/2013 01:06:02 PM',
78352: '10/13/2013 05:14:33 AM',
78383: '10/13/2013 03:50:02 AM',
79078: '10/12/2013 09:53:17 PM',
84489: '10/10/2013 07:16:16 PM',
84518: '10/10/2013 07:02:29 PM',
84688: '10/10/2013 05:39:19 PM',
84695: '10/10/2013 05:37:04 PM',
88812: '10/09/2013 09:17:15 PM',
89205: '10/09/2013 06:01:48 PM',
89382: '10/09/2013 04:53:01 PM',
89734: '10/09/2013 03:13:23 PM',
93990: '10/08/2013 06:14:15 PM',
99407: '10/07/2013 03:56:11 PM',
99847: '10/07/2013 02:33:21 PM',
100073: '10/07/2013 01:36:02 PM',
101013: '10/07/2013 10:05:18 AM',
104020: '10/06/2013 02:58:47 PM',
106118: '10/05/2013 03:24:47 PM',
106499: '10/05/2013 11:52:13 AM'},
'Closed Date': {2869: '10/30/2013 10:48:51 AM',
23571: '10/25/2013 03:36:36 PM',
41625: '10/24/2013 05:37:24 PM',
44331: '10/25/2013 10:40:35 AM',
46913: '10/23/2013 09:59:23 AM',
47459: '10/29/2013 06:17:10 PM',
48465: '10/21/2013 11:17:47 AM',
51837: '10/20/2013 06:35:49 PM',
51848: '10/20/2013 06:34:47 PM',
54089: '10/19/2013 04:10:11 PM',
54343: '10/28/2013 08:42:12 AM',
55140: '10/19/2013 02:19:55 AM',
57789: '10/23/2013 02:42:14 PM',
63119: '10/25/2013 06:49:59 PM',
66242: '10/22/2013 03:09:11 PM',
66758: '10/16/2013 04:35:34 PM',
66786: '10/18/2013 04:57:04 PM',
66809: '10/16/2013 12:34:23 PM',
67465: '10/16/2013 12:43:06 PM',
72424: '10/21/2013 12:16:15 PM',
75531: '10/14/2013 03:09:51 PM',
77918: '10/13/2013 03:25:45 PM',
78048: '10/21/2013 10:20:21 AM',
78352: '10/16/2013 01:42:42 PM',
78383: '10/13/2013 05:03:13 AM',
79078: '10/13/2013 02:52:07 AM',
84489: '10/10/2013 10:29:16 PM',
84518: '10/10/2013 10:29:16 PM',
84688: '10/10/2013 10:29:17 PM',
84695: '10/10/2013 10:30:19 PM',
88812: '10/23/2013 02:15:21 PM',
89205: '10/09/2013 09:04:26 PM',
89382: '10/18/2013 08:35:02 AM',
89734: '10/09/2013 05:10:45 PM',
93990: '10/09/2013 04:00:59 PM',
99407: '10/08/2013 07:04:14 AM',
99847: '10/09/2013 02:36:42 PM',
100073: '10/09/2013 09:56:55 AM',
101013: '10/09/2013 03:36:23 PM',
104020: '10/07/2013 12:11:16 PM',
106118: '10/05/2013 04:20:34 PM',
106499: '10/07/2013 08:00:28 AM'},
'Agency': {2869: 'NYPD',
23571: 'NYPD',
41625: 'TLC',
44331: 'TLC',
46913: 'DPR',
47459: 'TLC',
48465: 'NYPD',
51837: 'NYPD',
51848: 'NYPD',
54089: 'NYPD',
54343: 'DOT',
55140: 'NYPD',
57789: 'TLC',
63119: 'TLC',
66242: 'TLC',
66758: 'NYPD',
66786: 'TLC',
66809: 'NYPD',
67465: 'NYPD',
72424: 'TLC',
75531: 'NYPD',
77918: 'NYPD',
78048: 'TLC',
78352: 'TLC',
78383: 'NYPD',
79078: 'NYPD',
84489: 'NYPD',
84518: 'NYPD',
84688: 'NYPD',
84695: 'NYPD',
88812: 'TLC',
89205: 'NYPD',
89382: 'DOT',
89734: 'NYPD',
93990: 'TLC',
99407: 'DPR',
99847: 'TLC',
100073: 'TLC',
101013: 'TLC',
104020: 'TLC',
106118: 'NYPD',
106499: 'DOT'},
'Agency Name': {2869: 'New York City Police Department',
23571: 'New York City Police Department',
41625: 'Taxi and Limousine Commission',
44331: 'Taxi and Limousine Commission',
46913: 'Department of Parks and Recreation',
47459: 'Taxi and Limousine Commission',
48465: 'New York City Police Department',
51837: 'New York City Police Department',
51848: 'New York City Police Department',
54089: 'New York City Police Department',
54343: 'Department of Transportation',
55140: 'New York City Police Department',
57789: 'Taxi and Limousine Commission',
63119: 'Taxi and Limousine Commission',
66242: 'Taxi and Limousine Commission',
66758: 'New York City Police Department',
66786: 'Taxi and Limousine Commission',
66809: 'New York City Police Department',
67465: 'New York City Police Department',
72424: 'Taxi and Limousine Commission',
75531: 'New York City Police Department',
77918: 'New York City Police Department',
78048: 'Taxi and Limousine Commission',
78352: 'Taxi and Limousine Commission',
78383: 'New York City Police Department',
79078: 'New York City Police Department',
84489: 'New York City Police Department',
84518: 'New York City Police Department',
84688: 'New York City Police Department',
84695: 'New York City Police Department',
88812: 'Taxi and Limousine Commission',
89205: 'New York City Police Department',
89382: 'Department of Transportation',
89734: 'New York City Police Department',
93990: 'Taxi and Limousine Commission',
99407: 'Department of Parks and Recreation',
99847: 'Taxi and Limousine Commission',
100073: 'Taxi and Limousine Commission',
101013: 'Taxi and Limousine Commission',
104020: 'Taxi and Limousine Commission',
106118: 'New York City Police Department',
106499: 'Department of Transportation'},
'Complaint Type': {2869: 'Illegal Parking',
23571: 'Noise - Park',
41625: 'For Hire Vehicle Complaint',
44331: 'Taxi Complaint',
46913: 'Dead Tree',
47459: 'Taxi Complaint',
48465: 'Illegal Parking',
51837: 'Noise - Park',
51848: 'Noise - Park',
54089: 'Noise - Park',
54343: 'Street Condition',
55140: 'Noise - Vehicle',
57789: 'Taxi Complaint',
63119: 'Taxi Complaint',
66242: 'Taxi Complaint',
66758: 'Vending',
66786: 'Taxi Complaint',
66809: 'Traffic',
67465: 'Traffic',
72424: 'Taxi Complaint',
75531: 'Vending',
77918: 'Noise - Park',
78048: 'Taxi Complaint',
78352: 'For Hire Vehicle Complaint',
78383: 'Noise - Vehicle',
79078: 'Noise - Park',
84489: 'Noise - Park',
84518: 'Noise - Park',
84688: 'Noise - Park',
84695: 'Noise - Park',
88812: 'Taxi Complaint',
89205: 'Vending',
89382: 'Public Toilet',
89734: 'Noise - Park',
93990: 'Taxi Complaint',
99407: 'Overgrown Tree/Branches',
99847: 'Taxi Complaint',
100073: 'Taxi Complaint',
101013: 'Taxi Complaint',
104020: 'For Hire Vehicle Complaint',
106118: 'Noise - Park',
106499: 'Public Toilet'},
'Descriptor': {2869: 'Double Parked Blocking Traffic',
23571: 'Loud Music/Party',
41625: 'Car Service Company Complaint',
44331: 'Driver Complaint',
46913: 'Dead/Dying Tree',
47459: 'Driver Complaint',
48465: 'Posted Parking Sign Violation',
51837: 'Loud Music/Party',
51848: 'Loud Music/Party',
54089: 'Loud Music/Party',
54343: 'Rough, Pitted or Cracked Roads',
55140: 'Car/Truck Music',
57789: 'Driver Complaint',
63119: 'Driver Complaint',
66242: 'Driver Complaint',
66758: 'Unlicensed',
66786: 'Insurance Information Requested',
66809: 'Congestion/Gridlock',
67465: 'Drag Racing',
72424: 'Driver Complaint',
75531: 'In Prohibited Area',
77918: 'Loud Music/Party',
78048: 'Driver Complaint',
78352: 'Car Service Company Complaint',
78383: 'Car/Truck Music',
79078: 'Loud Music/Party',
84489: 'Loud Music/Party',
84518: 'Loud Music/Party',
84688: 'Loud Music/Party',
84695: 'Loud Music/Party',
88812: 'Driver Complaint',
89205: 'Unlicensed',
89382: 'Damaged Door',
89734: 'Loud Music/Party',
93990: 'Driver Complaint',
99407: 'Traffic Sign or Signal Blocked',
99847: 'Driver Complaint',
100073: 'Driver Complaint',
101013: 'Driver Complaint',
104020: 'Car Service Company Complaint',
106118: 'Loud Music/Party',
106499: 'Dirty/Graffiti'},
'Location Type': {2869: 'Street/Sidewalk',
23571: 'Park/Playground',
41625: 'Street',
44331: 'Street',
46913: 'Street',
47459: 'Street',
48465: 'Street/Sidewalk',
51837: 'Park/Playground',
51848: 'Park/Playground',
54089: 'Park/Playground',
54343: 'Street',
55140: 'Street/Sidewalk',
57789: 'Street',
63119: 'Street',
66242: 'Street',
66758: 'Park/Playground',
66786: 'Street',
66809: 'Street/Sidewalk',
67465: 'Street/Sidewalk',
72424: 'Street',
75531: 'Park/Playground',
77918: 'Park/Playground',
78048: 'Street',
78352: 'Street',
78383: 'Street/Sidewalk',
79078: 'Park/Playground',
84489: 'Park/Playground',
84518: 'Park/Playground',
84688: 'Park/Playground',
84695: 'Park/Playground',
88812: 'Street',
89205: 'Park/Playground',
89382: 'Sidewalk',
89734: 'Park/Playground',
93990: 'Street',
99407: 'Street',
99847: 'Street',
100073: 'Street',
101013: 'Street',
104020: 'Street',
106118: 'Park/Playground',
106499: 'Sidewalk'},
'Incident Zip': {2869: '11217.0',
23571: '10000',
41625: '11430',
44331: '11430',
46913: '11215',
47459: '10031',
48465: '11434',
51837: '10031.0',
51848: '10031.0',
54089: '10000.0',
54343: '10003.0',
55140: '11368.0',
57789: '11369.0',
63119: '11430.0',
66242: '11369',
66758: '10036',
66786: '10003',
66809: '11430',
67465: '11367',
72424: '11217',
75531: '10000',
77918: '10000',
78048: '11369',
78352: '11217',
78383: '11368',
79078: '10011',
84489: '11215',
84518: '11215',
84688: '11215',
84695: '11215',
88812: '11430',
89205: '10000',
89382: '11238',
89734: '10036',
93990: '10003',
99407: '11430.0',
99847: '10036.0',
100073: '10024.0',
101013: '10017.0',
104020: '11430.0',
106118: '10000.0',
106499: '11369.0'},
'Address Type': {2869: 'PLACENAME',
23571: 'PLACENAME',
41625: 'PLACENAME',
44331: 'PLACENAME',
46913: 'PLACENAME',
47459: 'PLACENAME',
48465: 'PLACENAME',
51837: 'PLACENAME',
51848: 'PLACENAME',
54089: 'PLACENAME',
54343: 'PLACENAME',
55140: 'PLACENAME',
57789: 'PLACENAME',
63119: 'PLACENAME',
66242: 'PLACENAME',
66758: 'PLACENAME',
66786: 'PLACENAME',
66809: 'PLACENAME',
67465: 'PLACENAME',
72424: 'PLACENAME',
75531: 'PLACENAME',
77918: 'PLACENAME',
78048: 'PLACENAME',
78352: 'PLACENAME',
78383: 'PLACENAME',
79078: 'PLACENAME',
84489: 'PLACENAME',
84518: 'PLACENAME',
84688: 'PLACENAME',
84695: 'PLACENAME',
88812: 'PLACENAME',
89205: 'PLACENAME',
89382: 'PLACENAME',
89734: 'PLACENAME',
93990: 'PLACENAME',
99407: 'PLACENAME',
99847: 'PLACENAME',
100073: 'PLACENAME',
101013: 'PLACENAME',
104020: 'PLACENAME',
106118: 'PLACENAME',
106499: 'PLACENAME'},
'City': {2869: 'BROOKLYN',
23571: 'NEW YORK',
41625: 'JAMAICA',
44331: 'JAMAICA',
46913: 'BROOKLYN',
47459: 'NEW YORK',
48465: 'JAMAICA',
51837: 'NEW YORK',
51848: 'NEW YORK',
54089: 'NEW YORK',
54343: 'NEW YORK',
55140: 'CORONA',
57789: 'EAST ELMHURST',
63119: 'JAMAICA',
66242: 'EAST ELMHURST',
66758: 'NEW YORK',
66786: 'NEW YORK',
66809: 'JAMAICA',
67465: 'FLUSHING',
72424: 'BROOKLYN',
75531: 'NEW YORK',
77918: 'NEW YORK',
78048: 'EAST ELMHURST',
78352: 'BROOKLYN',
78383: 'CORONA',
79078: 'NEW YORK',
84489: 'BROOKLYN',
84518: 'BROOKLYN',
84688: 'BROOKLYN',
84695: 'BROOKLYN',
88812: 'JAMAICA',
89205: 'NEW YORK',
89382: 'BROOKLYN',
89734: 'NEW YORK',
93990: 'NEW YORK',
99407: 'JAMAICA',
99847: 'NEW YORK',
100073: 'NEW YORK',
101013: 'NEW YORK',
104020: 'JAMAICA',
106118: 'NEW YORK',
106499: 'EAST ELMHURST'},
'Landmark': {2869: 'BARCLAYS CENTER',
23571: 'CENTRAL PARK',
41625: 'J F K AIRPORT',
44331: 'J F K AIRPORT',
46913: 'BARTEL PRITCHARD SQUARE',
47459: 'CITY COLLEGE',
48465: 'PS 37',
51837: 'JACKIE ROBINSON PARK',
51848: 'JACKIE ROBINSON PARK',
54089: 'CENTRAL PARK',
54343: 'UNION SQUARE PARK',
55140: 'WORLDS FAIR MARINA',
57789: 'LA GUARDIA AIRPORT',
63119: 'J F K AIRPORT',
66242: 'LA GUARDIA AIRPORT',
66758: 'BRYANT PARK',
66786: 'BETH ISRAEL MED CENTER',
66809: 'J F K AIRPORT',
67465: 'QUEENS COLLEGE',
72424: 'BARCLAYS CENTER',
75531: 'CENTRAL PARK',
77918: 'CENTRAL PARK',
78048: 'LA GUARDIA AIRPORT',
78352: 'BARCLAYS CENTER',
78383: 'WORLDS FAIR MARINA',
79078: 'WASHINGTON SQUARE PARK',
84489: 'PROSPECT PARK',
84518: 'PROSPECT PARK',
84688: 'PROSPECT PARK',
84695: 'PROSPECT PARK',
88812: 'J F K AIRPORT',
89205: 'CENTRAL PARK',
89382: 'GRAND ARMY PLAZA',
89734: 'BRYANT PARK',
93990: 'BETH ISRAEL MED CENTER',
99407: 'J F K AIRPORT',
99847: 'PORT AUTH 42 STREET',
100073: 'MUSEUM NATURAL HIST',
101013: 'GRAND CENTRAL TERM',
104020: 'JFK',
106118: 'CENTRAL PARK',
106499: 'LA GUARDIA AIRPORT'},
'Status': {2869: 'Closed',
23571: 'Closed',
41625: 'Closed',
44331: 'Closed',
46913: 'Closed',
47459: 'Closed',
48465: 'Closed',
51837: 'Closed',
51848: 'Closed',
54089: 'Closed',
54343: 'Closed',
55140: 'Closed',
57789: 'Closed',
63119: 'Closed',
66242: 'Closed',
66758: 'Closed',
66786: 'Closed',
66809: 'Closed',
67465: 'Closed',
72424: 'Closed',
75531: 'Closed',
77918: 'Closed',
78048: 'Closed',
78352: 'Closed',
78383: 'Closed',
79078: 'Closed',
84489: 'Closed',
84518: 'Closed',
84688: 'Closed',
84695: 'Closed',
88812: 'Closed',
89205: 'Closed',
89382: 'Closed',
89734: 'Closed',
93990: 'Closed',
99407: 'Closed',
99847: 'Closed',
100073: 'Closed',
101013: 'Closed',
104020: 'Closed',
106118: 'Closed',
106499: 'Closed'},
'Borough': {2869: 'BROOKLYN',
23571: 'MANHATTAN',
41625: 'QUEENS',
44331: 'QUEENS',
46913: 'BROOKLYN',
47459: 'MANHATTAN',
48465: 'QUEENS',
51837: 'MANHATTAN',
51848: 'MANHATTAN',
54089: 'MANHATTAN',
54343: 'MANHATTAN',
55140: 'QUEENS',
57789: 'QUEENS',
63119: 'QUEENS',
66242: 'QUEENS',
66758: 'MANHATTAN',
66786: 'MANHATTAN',
66809: 'QUEENS',
67465: 'QUEENS',
72424: 'BROOKLYN',
75531: 'MANHATTAN',
77918: 'MANHATTAN',
78048: 'QUEENS',
78352: 'BROOKLYN',
78383: 'QUEENS',
79078: 'MANHATTAN',
84489: 'BROOKLYN',
84518: 'BROOKLYN',
84688: 'BROOKLYN',
84695: 'BROOKLYN',
88812: 'QUEENS',
89205: 'MANHATTAN',
89382: 'BROOKLYN',
89734: 'MANHATTAN',
93990: 'MANHATTAN',
99407: 'QUEENS',
99847: 'MANHATTAN',
100073: 'MANHATTAN',
101013: 'MANHATTAN',
104020: 'QUEENS',
106118: 'MANHATTAN',
106499: 'QUEENS'}}
CodePudding user response：
You can first start by converting your date columns to datetime type using pd.to_datetime():
for c in ['Created Date', 'Closed Date']:
df[c] = pd.to_datetime(df[c])
#df[c '_date'] = df[c].dt.date # to extract the date (for created closed)
#df[c '_time'] = df[c].dt.time # to extract the time (for created closed)
Then you can calculate the difference in time between the two as a new column (as hours) .astype('timedelta64[h]'), and then calculate a grouped mean:
df['difference in time'] = (df['Closed Date'] - df['Created Date']).astype('timedelta64[h]')
print(df.groupby('Complaint Type').agg({'difference in time':'mean'}))
returns:
difference in time
Complaint Type
Dead Tree 40.000000
For Hire Vehicle Complaint 48.333333
Illegal Parking 0.500000
Noise - Park 1.916667
Noise - Vehicle 0.500000
Overgrown Tree/Branches 15.000000
Public Toilet 125.500000
Street Condition 211.000000
Taxi Complaint 125.461538
Traffic 1.500000
Vending 3.666667