Title: Problem based on 311 service request from nyc open data
Tags: python;pandas
Question: Here is small portion of my data in dictionary format.
{'Unnamed: 0': {0: 0,
1: 1,
2: 2,
3: 3,
4: 4,
5: 5,
6: 6,
7: 7,
8: 8,
9: 9,
10: 10,
11: 11,
12: 12,
13: 13,
14: 14,
15: 15,
16: 16,
17: 17,
18: 18,
19: 19,
20: 20,
21: 21,
22: 22,
23: 23,
24: 24,
25: 25,
26: 26,
27: 27,
28: 28,
29: 29,
30: 30,
31: 31,
32: 32,
33: 33,
34: 34,
35: 35,
36: 36,
37: 37,
38: 38,
39: 39,
40: 40,
41: 41,
42: 42,
43: 43,
44: 44,
45: 45,
46: 46,
47: 47,
48: 48,
49: 49,
50: 50,
51: 51,
52: 52,
53: 53,
54: 54,
55: 55,
56: 56,
57: 57,
58: 58,
59: 59,
60: 60,
61: 61,
62: 62,
63: 63,
64: 64,
65: 65,
66: 66,
67: 67,
68: 68,
69: 69},
'Created Date': {0: '10/31/2013 02:08:41 AM',
1: '10/31/2013 02:01:04 AM',
2: '10/31/2013 02:00:24 AM',
3: '10/31/2013 01:56:23 AM',
4: '10/31/2013 01:53:44 AM',
5: '10/31/2013 01:46:52 AM',
6: '10/31/2013 01:46:40 AM',
7: '10/31/2013 01:44:19 AM',
8: '10/31/2013 01:44:14 AM',
9: '10/31/2013 01:34:41 AM',
10: '10/31/2013 01:25:12 AM',
11: '10/31/2013 01:24:14 AM',
12: '10/31/2013 01:20:57 AM',
13: '10/31/2013 01:20:13 AM',
14: '10/31/2013 01:19:54 AM',
15: '10/31/2013 01:14:02 AM',
16: '10/31/2013 12:54:03 AM',
17: '10/31/2013 12:52:46 AM',
18: '10/31/2013 12:51:00 AM',
19: '10/31/2013 12:46:27 AM',
20: '10/31/2013 12:43:47 AM',
21: '10/31/2013 12:41:17 AM',
22: '10/31/2013 12:39:55 AM',
23: '10/31/2013 12:38:00 AM',
24: '10/31/2013 12:37:16 AM',
25: '10/31/2013 12:35:18 AM',
26: '10/31/2013 12:33:00 AM',
27: '10/31/2013 12:32:44 AM',
28: '10/31/2013 12:32:08 AM',
29: '10/31/2013 12:32:00 AM',
30: '10/31/2013 12:31:17 AM',
31: '10/31/2013 12:30:36 AM',
32: '10/31/2013 12:30:31 AM',
33: '10/31/2013 12:29:47 AM',
34: '10/31/2013 12:28:30 AM',
35: '10/31/2013 12:23:24 AM',
36: '10/31/2013 12:23:00 AM',
37: '10/31/2013 12:20:44 AM',
38: '10/31/2013 12:20:00 AM',
39: '10/31/2013 12:19:48 AM',
40: '10/31/2013 12:18:05 AM',
41: '10/31/2013 12:16:25 AM',
42: '10/31/2013 12:15:06 AM',
43: '10/31/2013 12:14:42 AM',
44: '10/31/2013 12:12:08 AM',
45: '10/31/2013 12:11:58 AM',
46: '10/31/2013 12:09:07 AM',
47: '10/31/2013 12:08:47 AM',
48: '10/31/2013 12:07:45 AM',
49: '10/31/2013 12:05:10 AM',
50: '10/31/2013 12:04:50 AM',
51: '10/31/2013 12:03:27 AM',
52: '10/31/2013 12:02:01 AM',
53: '10/31/2013 12:01:47 AM',
54: '10/31/2013 12:01:45 AM',
55: '10/31/2013 12:01:34 AM',
56: '10/31/2013 12:01:00 AM',
57: '10/31/2013 12:00:00 AM',
58: '10/31/2013 12:00:00 AM',
59: '10/31/2013 12:00:00 AM',
60: '10/31/2013 12:00:00 AM',
61: '10/31/2013 12:00:00 AM',
62: '10/31/2013 12:00:00 AM',
63: '10/31/2013 12:00:00 AM',
64: '10/31/2013 12:00:00 AM',
65: '10/31/2013 12:00:00 AM',
66: '10/31/2013 12:00:00 AM',
67: '10/30/2013 11:58:43 PM',
68: '10/30/2013 11:57:57 PM',
69: '10/30/2013 11:57:00 PM'},
'Closed Date': {0: nan,
1: nan,
2: '10/31/2013 02:40:32 AM',
3: '10/31/2013 02:21:48 AM',
4: nan,
5: nan,
6: nan,
7: '10/31/2013 01:58:49 AM',
8: '10/31/2013 02:28:04 AM',
9: '10/31/2013 02:23:51 AM',
10: nan,
11: '10/31/2013 01:54:39 AM',
12: '10/31/2013 02:12:31 AM',
13: nan,
14: nan,
15: '10/31/2013 01:30:34 AM',
16: '10/31/2013 02:16:39 AM',
17: nan,
18: nan,
19: nan,
20: '10/31/2013 12:56:20 AM',
21: '10/31/2013 01:04:37 AM',
22: nan,
23: nan,
24: nan,
25: '10/31/2013 02:41:35 AM',
26: nan,
27: nan,
28: nan,
29: nan,
30: '10/31/2013 02:41:36 AM',
31: nan,
32: nan,
33: '10/31/2013 12:38:29 AM',
34: '10/31/2013 02:06:11 AM',
35: '10/31/2013 01:05:41 AM',
36: nan,
37: '10/31/2013 02:10:24 AM',
38: nan,
39: nan,
40: '10/31/2013 01:26:15 AM',
41: nan,
42: '10/31/2013 03:00:20 AM',
43: '10/31/2013 01:39:00 AM',
44: '10/31/2013 01:13:45 AM',
45: '10/31/2013 01:54:38 AM',
46: '10/31/2013 12:53:12 AM',
47: nan,
48: '10/31/2013 01:43:11 AM',
49: '10/31/2013 02:43:43 AM',
50: '10/31/2013 01:09:38 AM',
51: nan,
52: '10/31/2013 01:02:28 AM',
53: '10/31/2013 12:39:31 AM',
54: '10/31/2013 12:02:37 AM',
55: '10/31/2013 01:32:43 AM',
56: nan,
57: nan,
58: nan,
59: nan,
60: nan,
61: nan,
62: nan,
63: nan,
64: nan,
65: nan,
66: nan,
67: nan,
68: nan,
69: '10/31/2013 01:09:39 AM'},
'Agency': {0: 'NYPD',
1: 'NYPD',
2: 'NYPD',
3: 'NYPD',
4: 'DOHMH',
5: 'NYPD',
6: 'NYPD',
7: 'NYPD',
8: 'NYPD',
9: 'NYPD',
10: 'NYPD',
11: 'NYPD',
12: 'NYPD',
13: 'NYPD',
14: 'DOHMH',
15: 'NYPD',
16: 'NYPD',
17: 'NYPD',
18: 'DOT',
19: 'NYPD',
20: 'NYPD',
21: 'NYPD',
22: 'NYPD',
23: 'NYPD',
24: 'NYPD',
25: 'NYPD',
26: 'DOT',
27: 'DOHMH',
28: 'NYPD',
29: 'DOT',
30: 'NYPD',
31: 'NYPD',
32: 'TLC',
33: 'NYPD',
34: 'NYPD',
35: 'NYPD',
36: 'DOT',
37: 'NYPD',
38: 'DOT',
39: 'NYPD',
40: 'NYPD',
41: 'DOHMH',
42: 'NYPD',
43: 'NYPD',
44: 'NYPD',
45: 'NYPD',
46: 'NYPD',
47: 'TLC',
48: 'NYPD',
49: 'NYPD',
50: 'NYPD',
51: 'DOT',
52: 'NYPD',
53: 'NYPD',
54: 'HRA',
55: 'NYPD',
56: 'DSNY',
57: 'HPD',
58: 'HPD',
59: 'HPD',
60: 'HPD',
61: 'HPD',
62: 'HPD',
63: 'HPD',
64: 'HPD',
65: 'HPD',
66: 'HPD',
67: 'DOT',
68: 'DCA',
69: 'NYPD'},
'Agency Name': {0: 'New York City Police Department',
1: 'New York City Police Department',
2: 'New York City Police Department',
3: 'New York City Police Department',
4: 'Department of Health and Mental Hygiene',
5: 'New York City Police Department',
6: 'New York City Police Department',
7: 'New York City Police Department',
8: 'New York City Police Department',
9: 'New York City Police Department',
10: 'New York City Police Department',
11: 'New York City Police Department',
12: 'New York City Police Department',
13: 'New York City Police Department',
14: 'Department of Health and Mental Hygiene',
15: 'New York City Police Department',
16: 'New York City Police Department',
17: 'New York City Police Department',
18: 'Department of Transportation',
19: 'New York City Police Department',
20: 'New York City Police Department',
21: 'New York City Police Department',
22: 'New York City Police Department',
23: 'New York City Police Department',
24: 'New York City Police Department',
25: 'New York City Police Department',
26: 'Department of Transportation',
27: 'Department of Health and Mental Hygiene',
28: 'New York City Police Department',
29: 'Department of Transportation',
30: 'New York City Police Department',
31: 'New York City Police Department',
32: 'Taxi and Limousine Commission',
33: 'New York City Police Department',
34: 'New York City Police Department',
35: 'New York City Police Department',
36: 'Department of Transportation',
37: 'New York City Police Department',
38: 'Department of Transportation',
39: 'New York City Police Department',
40: 'New York City Police Department',
41: 'Department of Health and Mental Hygiene',
42: 'New York City Police Department',
43: 'New York City Police Department',
44: 'New York City Police Department',
45: 'New York City Police Department',
46: 'New York City Police Department',
47: 'Taxi and Limousine Commission',
48: 'New York City Police Department',
49: 'New York City Police Department',
50: 'New York City Police Department',
51: 'Department of Transportation',
52: 'New York City Police Department',
53: 'New York City Police Department',
54: 'HRA Benefit Card Replacement',
55: 'New York City Police Department',
56: 'BCC - Brooklyn South',
57: 'Department of Housing Preservation and Development',
58: 'Department of Housing Preservation and Development',
59: 'Department of Housing Preservation and Development',
60: 'Department of Housing Preservation and Development',
61: 'Department of Housing Preservation and Development',
62: 'Department of Housing Preservation and Development',
63: 'Department of Housing Preservation and Development',
64: 'Department of Housing Preservation and Development',
65: 'Department of Housing Preservation and Development',
66: 'Department of Housing Preservation and Development',
67: 'Department of Transportation',
68: 'Department of Consumer Affairs',
69: 'New York City Police Department'},
'Complaint Type': {0: 'Noise - Street/Sidewalk',
1: 'Illegal Parking',
2: 'Noise - Commercial',
3: 'Noise - Vehicle',
4: 'Rodent',
5: 'Noise - Commercial',
6: 'Blocked Driveway',
7: 'Noise - Commercial',
8: 'Noise - Commercial',
9: 'Noise - Commercial',
10: 'Noise - House of Worship',
11: 'Noise - Commercial',
12: 'Illegal Parking',
13: 'Noise - Vehicle',
14: 'Rodent',
15: 'Noise - House of Worship',
16: 'Noise - Street/Sidewalk',
17: 'Illegal Parking',
18: 'Street Light Condition',
19: 'Noise - Commercial',
20: 'Noise - House of Worship',
21: 'Noise - Commercial',
22: 'Noise - Vehicle',
23: 'Noise - Commercial',
24: 'Blocked Driveway',
25: 'Noise - Street/Sidewalk',
26: 'Street Light Condition',
27: 'Harboring Bees/Wasps',
28: 'Noise - Street/Sidewalk',
29: 'Street Light Condition',
30: 'Blocked Driveway',
31: 'Noise - Street/Sidewalk',
32: 'Taxi Complaint',
33: 'Noise - House of Worship',
34: 'Homeless Encampment',
35: 'Blocked Driveway',
36: 'Traffic Signal Condition',
37: 'Noise - Commercial',
38: 'Traffic Signal Condition',
39: 'Blocked Driveway',
40: 'Noise - Commercial',
41: 'Food Establishment',
42: 'Noise - Commercial',
43: 'Noise - Commercial',
44: 'Noise - Park',
45: 'Noise - Street/Sidewalk',
46: 'Noise - Commercial',
47: 'Taxi Complaint',
48: 'Noise - Commercial',
49: 'Noise - Street/Sidewalk',
50: 'Noise - Commercial',
51: 'Broken Muni Meter',
52: 'Blocked Driveway',
53: 'Noise - Commercial',
54: 'Benefit Card Replacement',
55: 'Noise - Commercial',
56: 'Sanitation Condition',
57: 'ELECTRIC',
58: 'PLUMBING',
59: 'HEATING',
60: 'ELECTRIC',
61: 'HEATING',
62: 'HEATING',
63: 'GENERAL CONSTRUCTION',
64: 'HEATING',
65: 'ELECTRIC',
66: 'GENERAL CONSTRUCTION',
67: 'Street Condition',
68: 'Consumer Complaint',
69: 'Blocked Driveway'},
'Descriptor': {0: 'Loud Talking',
1: 'Commercial Overnight Parking',
2: 'Loud Music/Party',
3: 'Car/Truck Horn',
4: 'Condition Attracting Rodents',
5: 'Banging/Pounding',
6: 'No Access',
7: 'Loud Music/Party',
8: 'Loud Talking',
9: 'Loud Music/Party',
10: 'Loud Music/Party',
11: 'Loud Music/Party',
12: 'Double Parked Blocking Vehicle',
13: 'Engine Idling',
14: 'Rat Sighting',
15: 'Loud Music/Party',
16: 'Loud Music/Party',
17: 'Posted Parking Sign Violation',
18: 'Street Light Out',
19: 'Loud Music/Party',
20: 'Loud Music/Party',
21: 'Banging/Pounding',
22: 'Car/Truck Music',
23: 'Loud Music/Party',
24: 'Partial Access',
25: 'Loud Talking',
26: 'Street Light Out',
27: 'Bees/Wasps - Not a beekeper',
28: 'Loud Talking',
29: 'Street Light Out',
30: 'No Access',
31: 'Loud Music/Party',
32: 'Driver Complaint',
33: 'Banging/Pounding',
34: nan,
35: 'No Access',
36: 'Controller',
37: 'Loud Music/Party',
38: 'Controller',
39: 'No Access',
40: 'Loud Music/Party',
41: 'Rodents/Insects/Garbage',
42: 'Loud Music/Party',
43: 'Loud Music/Party',
44: 'Loud Talking',
45: 'Loud Music/Party',
46: 'Loud Music/Party',
47: 'Driver Complaint',
48: 'Loud Music/Party',
49: 'Loud Talking',
50: 'Loud Music/Party',
51: 'No Receipt',
52: 'No Access',
53: 'Loud Music/Party',
54: 'Medicaid',
55: 'Loud Music/Party',
56: '15 Street Cond/Dump-Out/Drop-Off',
57: 'ELECTRIC-SUPPLY',
58: 'BASIN/SINK',
59: 'HEAT',
60: 'BELL-BUZZER/INTERCOM',
61: 'HEAT',
62: 'HEAT',
63: 'MAIL-BOX',
64: 'HEAT',
65: 'ELECTRIC-SUPPLY',
66: 'LOCKS',
67: 'Cave-in',
68: 'Exchange/Refund/Return',
69: 'Partial Access'},
'Location Type': {0: 'Street/Sidewalk',
1: 'Street/Sidewalk',
2: 'Club/Bar/Restaurant',
3: 'Street/Sidewalk',
4: 'Vacant Lot',
5: 'Club/Bar/Restaurant',
6: 'Street/Sidewalk',
7: 'Club/Bar/Restaurant',
8: 'Club/Bar/Restaurant',
9: 'Club/Bar/Restaurant',
10: 'House of Worship',
11: 'Club/Bar/Restaurant',
12: 'Street/Sidewalk',
13: 'Street/Sidewalk',
14: '1-2 Family Mixed Use Building',
15: 'House of Worship',
16: 'Street/Sidewalk',
17: 'Street/Sidewalk',
18: nan,
19: 'Club/Bar/Restaurant',
20: 'House of Worship',
21: 'Store/Commercial',
22: 'Street/Sidewalk',
23: 'Club/Bar/Restaurant',
24: 'Street/Sidewalk',
25: 'Street/Sidewalk',
26: nan,
27: '3+ Family Mixed Use Building',
28: 'Street/Sidewalk',
29: nan,
30: 'Street/Sidewalk',
31: 'Street/Sidewalk',
32: nan,
33: 'House of Worship',
34: 'Residential Building/House',
35: 'Street/Sidewalk',
36: nan,
37: 'Club/Bar/Restaurant',
38: nan,
39: 'Street/Sidewalk',
40: 'Club/Bar/Restaurant',
41: 'Restaurant/Bar/Deli/Bakery',
42: 'Store/Commercial',
43: 'Club/Bar/Restaurant',
44: 'Park/Playground',
45: 'Street/Sidewalk',
46: 'Club/Bar/Restaurant',
47: nan,
48: 'Club/Bar/Restaurant',
49: 'Street/Sidewalk',
50: 'Store/Commercial',
51: 'Street',
52: 'Street/Sidewalk',
53: 'Club/Bar/Restaurant',
54: 'NYC Street Address',
55: 'Store/Commercial',
56: 'Street',
57: 'RESIDENTIAL BUILDING',
58: 'RESIDENTIAL BUILDING',
59: 'RESIDENTIAL BUILDING',
60: 'RESIDENTIAL BUILDING',
61: 'RESIDENTIAL BUILDING',
62: 'RESIDENTIAL BUILDING',
63: 'RESIDENTIAL BUILDING',
64: 'RESIDENTIAL BUILDING',
65: 'RESIDENTIAL BUILDING',
66: 'RESIDENTIAL BUILDING',
67: 'Street',
68: nan,
69: 'Street/Sidewalk'},
'Incident Zip': {0: '11432.0',
1: '11378.0',
2: '10032.0',
3: '10023.0',
4: '10027.0',
5: '11372.0',
6: '11419.0',
7: '11417.0',
8: '10011.0',
9: '11225.0',
10: '11218.0',
11: '10003.0',
12: '10029.0',
13: '10466.0',
14: '11219.0',
15: '10025.0',
16: '10310.0',
17: '11236.0',
18: nan,
19: '10033.0',
20: '11216.0',
21: '10016.0',
22: '11419.0',
23: '11216.0',
24: '10305.0',
25: '10312.0',
26: nan,
27: '10025.0',
28: '10026.0',
29: '10309.0',
30: '10312.0',
31: '11236.0',
32: '10036.0',
33: '10025.0',
34: '10025.0',
35: '11433.0',
36: '11235.0',
37: '11216.0',
38: '11213.0',
39: '11379.0',
40: '11101.0',
41: '10014.0',
42: '11231.0',
43: '11234.0',
44: '10457.0',
45: '10459.0',
46: '10465.0',
47: '10036.0',
48: '10014.0',
49: '11225.0',
50: '10011.0',
51: '10003.0',
52: '11207.0',
53: '10002.0',
54: nan,
55: '10034.0',
56: '11231.0',
57: '11233.0',
58: '11233.0',
59: '10453.0',
60: '11233.0',
61: '10456.0',
62: '10469.0',
63: '11233.0',
64: '10032.0',
65: '11374.0',
66: '10033.0',
67: '11218.0',
68: '11221.0',
69: '11421.0'},
'Address Type': {0: 'ADDRESS',
1: 'BLOCKFACE',
2: 'ADDRESS',
3: 'BLOCKFACE',
4: 'BLOCKFACE',
5: 'BLOCKFACE',
6: 'ADDRESS',
7: 'ADDRESS',
8: 'ADDRESS',
9: 'ADDRESS',
10: 'ADDRESS',
11: 'ADDRESS',
12: 'ADDRESS',
13: 'INTERSECTION',
14: 'BLOCKFACE',
15: 'INTERSECTION',
16: 'ADDRESS',
17: 'INTERSECTION',
18: 'ADDRESS',
19: 'INTERSECTION',
20: 'ADDRESS',
21: 'LATLONG',
22: 'INTERSECTION',
23: 'ADDRESS',
24: 'ADDRESS',
25: 'ADDRESS',
26: 'BLOCKFACE',
27: 'ADDRESS',
28: 'ADDRESS',
29: 'ADDRESS',
30: 'ADDRESS',
31: 'BLOCKFACE',
32: 'ADDRESS',
33: 'INTERSECTION',
34: 'ADDRESS',
35: 'ADDRESS',
36: 'INTERSECTION',
37: 'ADDRESS',
38: 'INTERSECTION',
39: 'ADDRESS',
40: 'ADDRESS',
41: 'ADDRESS',
42: 'LATLONG',
43: 'ADDRESS',
44: 'BLOCKFACE',
45: 'ADDRESS',
46: 'ADDRESS',
47: 'INTERSECTION',
48: 'ADDRESS',
49: 'ADDRESS',
50: 'LATLONG',
51: 'INTERSECTION',
52: 'ADDRESS',
53: 'ADDRESS',
54: nan,
55: 'LATLONG',
56: 'ADDRESS',
57: 'ADDRESS',
58: 'ADDRESS',
59: 'ADDRESS',
60: 'ADDRESS',
61: 'ADDRESS',
62: 'ADDRESS',
63: 'ADDRESS',
64: 'ADDRESS',
65: 'ADDRESS',
66: 'ADDRESS',
67: 'ADDRESS',
68: 'ADDRESS',
69: 'ADDRESS'},
'City': {0: 'JAMAICA',
1: 'MASPETH',
2: 'NEW YORK',
3: 'NEW YORK',
4: 'NEW YORK',
5: 'JACKSON HEIGHTS',
6: 'SOUTH RICHMOND HILL',
7: 'OZONE PARK',
8: 'NEW YORK',
9: 'BROOKLYN',
10: 'BROOKLYN',
11: 'NEW YORK',
12: 'NEW YORK',
13: 'BRONX',
14: 'BROOKLYN',
15: 'NEW YORK',
16: 'STATEN ISLAND',
17: 'BROOKLYN',
18: nan,
19: 'NEW YORK',
20: 'BROOKLYN',
21: 'NEW YORK',
22: 'SOUTH RICHMOND HILL',
23: 'BROOKLYN',
24: 'STATEN ISLAND',
25: 'STATEN ISLAND',
26: nan,
27: 'NEW YORK',
28: 'NEW YORK',
29: 'STATEN ISLAND',
30: 'STATEN ISLAND',
31: 'BROOKLYN',
32: 'NEW YORK',
33: 'NEW YORK',
34: 'NEW YORK',
35: 'JAMAICA',
36: 'BROOKLYN',
37: 'BROOKLYN',
38: 'BROOKLYN',
39: 'MIDDLE VILLAGE',
40: 'LONG ISLAND CITY',
41: 'NEW YORK',
42: 'BROOKLYN',
43: 'BROOKLYN',
44: 'BRONX',
45: 'BRONX',
46: 'BRONX',
47: 'NEW YORK',
48: 'NEW YORK',
49: 'BROOKLYN',
50: 'NEW YORK',
51: 'NEW YORK',
52: 'BROOKLYN',
53: 'NEW YORK',
54: nan,
55: 'NEW YORK',
56: 'BROOKLYN',
57: 'BROOKLYN',
58: 'BROOKLYN',
59: 'BRONX',
60: 'BROOKLYN',
61: 'BRONX',
62: 'BRONX',
63: 'BROOKLYN',
64: 'NEW YORK',
65: 'Rego Park',
66: 'NEW YORK',
67: 'BROOKLYN',
68: 'BROOKLYN',
69: 'WOODHAVEN'},
'Landmark': {0: nan,
1: nan,
2: nan,
3: nan,
4: nan,
5: nan,
6: nan,
7: nan,
8: nan,
9: nan,
10: nan,
11: nan,
12: nan,
13: nan,
14: nan,
15: nan,
16: nan,
17: nan,
18: nan,
19: nan,
20: nan,
21: nan,
22: nan,
23: nan,
24: nan,
25: nan,
26: nan,
27: nan,
28: nan,
29: nan,
30: nan,
31: nan,
32: nan,
33: nan,
34: nan,
35: nan,
36: nan,
37: nan,
38: nan,
39: nan,
40: nan,
41: nan,
42: nan,
43: nan,
44: nan,
45: nan,
46: nan,
47: nan,
48: nan,
49: nan,
50: nan,
51: nan,
52: nan,
53: nan,
54: nan,
55: nan,
56: nan,
57: nan,
58: nan,
59: nan,
60: nan,
61: nan,
62: nan,
63: nan,
64: nan,
65: nan,
66: nan,
67: nan,
68: nan,
69: nan},
'Status': {0: 'Assigned',
1: 'Open',
2: 'Closed',
3: 'Closed',
4: 'Pending',
5: 'Open',
6: 'Assigned',
7: 'Closed',
8: 'Closed',
9: 'Closed',
10: 'Open',
11: 'Closed',
12: 'Closed',
13: 'Open',
14: 'Pending',
15: 'Closed',
16: 'Closed',
17: 'Open',
18: 'Open',
19: 'Assigned',
20: 'Closed',
21: 'Closed',
22: 'Open',
23: 'Assigned',
24: 'Assigned',
25: 'Closed',
26: 'Open',
27: 'Open',
28: 'Assigned',
29: 'Open',
30: 'Closed',
31: 'Open',
32: 'Open',
33: 'Closed',
34: 'Closed',
35: 'Closed',
36: 'Open',
37: 'Closed',
38: 'Open',
39: 'Open',
40: 'Closed',
41: 'Open',
42: 'Closed',
43: 'Closed',
44: 'Closed',
45: 'Closed',
46: 'Closed',
47: 'Open',
48: 'Closed',
49: 'Closed',
50: 'Closed',
51: 'Open',
52: 'Closed',
53: 'Closed',
54: 'Closed',
55: 'Closed',
56: 'Open',
57: 'Open',
58: 'Open',
59: 'Open',
60: 'Open',
61: 'Open',
62: 'Open',
63: 'Open',
64: 'Open',
65: 'Open',
66: 'Open',
67: 'Open',
68: 'Open',
69: 'Closed'},
'Borough': {0: 'QUEENS',
1: 'QUEENS',
2: 'MANHATTAN',
3: 'MANHATTAN',
4: 'MANHATTAN',
5: 'QUEENS',
6: 'QUEENS',
7: 'QUEENS',
8: 'MANHATTAN',
9: 'BROOKLYN',
10: 'BROOKLYN',
11: 'MANHATTAN',
12: 'MANHATTAN',
13: 'BRONX',
14: 'BROOKLYN',
15: 'MANHATTAN',
16: 'STATEN ISLAND',
17: 'BROOKLYN',
18: 'BROOKLYN',
19: 'MANHATTAN',
20: 'BROOKLYN',
21: 'MANHATTAN',
22: 'QUEENS',
23: 'BROOKLYN',
24: 'STATEN ISLAND',
25: 'STATEN ISLAND',
26: 'BROOKLYN',
27: 'MANHATTAN',
28: 'MANHATTAN',
29: 'STATEN ISLAND',
30: 'STATEN ISLAND',
31: 'BROOKLYN',
32: 'MANHATTAN',
33: 'MANHATTAN',
34: 'MANHATTAN',
35: 'QUEENS',
36: 'BROOKLYN',
37: 'BROOKLYN',
38: 'BROOKLYN',
39: 'QUEENS',
40: 'QUEENS',
41: 'MANHATTAN',
42: 'BROOKLYN',
43: 'BROOKLYN',
44: 'BRONX',
45: 'BRONX',
46: 'BRONX',
47: 'MANHATTAN',
48: 'MANHATTAN',
49: 'BROOKLYN',
50: 'MANHATTAN',
51: 'MANHATTAN',
52: 'BROOKLYN',
53: 'MANHATTAN',
54: 'Unspecified',
55: 'MANHATTAN',
56: 'BROOKLYN',
57: 'BROOKLYN',
58: 'BROOKLYN',
59: 'BRONX',
60: 'BROOKLYN',
61: 'BRONX',
62: 'BRONX',
63: 'BROOKLYN',
64: 'MANHATTAN',
65: 'QUEENS',
66: 'MANHATTAN',
67: 'BROOKLYN',
68: 'BROOKLYN',
69: 'QUEENS'}}
I want to find the day that has the highest number of complaints. The output should be like this:
October-24-Thursday
Produce a table for the number complaint type in each day of the week. Do this analysis only
for the top-10 most frequent complaints.
Complaint Type  Blocked   DOF Literature    General       Heating  Illegal    street
day             Driveway  Request          Constructions           packing    condition
Friday          613       1035            963             2384     593        1357
Monday          720       1120            1425            1621     736        1432
Tuesday         628       170             475             1596     836        624
Wednesday       689       74              310             1316     846        623
thursday        689       830             1121            436      252        975
saturday        556       1288            1600            243      1412       145
Sunday          670       1280            1577            245      1234       1612
can anyone help me with this. I am new to pandas and python.
Unnamed: 0  Created Date    Closed Date Agency  Agency Name Complaint Type  Descriptor  Location Type   Incident Zip    Address Type    City    Landmark    Status  Borough
0   0   10/31/2013 02:08:41 AM  NaN NYPD    New York City Police Department Noise - Street/Sidewalk Loud Talking    Street/Sidewalk 11432.0 ADDRESS JAMAICA NaN Assigned    QUEENS
1   1   10/31/2013 02:01:04 AM  NaN NYPD    New York City Police Department Illegal Parking Commercial Overnight Parking    Street/Sidewalk 11378.0 BLOCKFACE   MASPETH NaN Open    QUEENS
2   2   10/31/2013 02:00:24 AM  10/31/2013 02:40:32 AM  NYPD    New York City Police Department Noise - Commercial  Loud Music/Party    Club/Bar/Restaurant 10032.0 ADDRESS NEW YORK    NaN Closed  MANHATTAN
3   3   10/31/2013 01:56:23 AM  10/31/2013 02:21:48 AM  NYPD    New York City Police Department Noise - Vehicle Car/Truck Horn  Street/Sidewalk 10023.0 BLOCKFACE   NEW YORK    NaN Closed  MANHATTAN
4   4   10/31/2013 01:53:44 AM  NaN DOHMH   Department of Health and Mental Hygiene Rodent  Condition Attracting Rodents    Vacant Lot  10027.0 BLOCKFACE   NEW YORK    NaN Pending MANHATTAN
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
65  65  10/31/2013 12:00:00 AM  NaN HPD Department of Housing Preservation and Develop...   ELECTRIC    ELECTRIC-SUPPLY RESIDENTIAL BUILDING    11374.0 ADDRESS Rego Park   NaN Open    QUEENS
Comment: there is column status with opened and closed value

Comment: should i mail you the csv file?

Comment: Can you more explain day of highest complaints? It is count only from `Created Date` column? Or it is between `Created Date` and `Closed Date` ? If `Closed Date` is `NaN` it means complaints are still until today?

Comment: Unfortunately question is really unclear.

Comment: no, please create 10 rows sample data in question and get expected output from it. It should help. check [how to provide a great pandas example](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples)

Comment: Can yo uadd expected output from last paragraph data? I think exactly what date and how is count. Not some general date like `October-24-Thursday`

Here is the accepted answer: The day that has the highest number of complaints can be find like this.
def problem5a(path):
df=pd.read_csv('311-service-requests.csv')
df['Created Date'] = pd.to_datetime(df['Created Date'])
df['Day of Week'] = df['Created Date'].dt.day_name()
df['Month of Year'] = df['Created Date'].dt.month_name()
a=df['Created Date'].value_counts().idxmax()
most_complaints_data = df[df['Created Date']==a]
day = most_complaints_data['Day of Week'].unique()
month = most_complaints_data['Month of Year'].unique()
return  month+' '+str(a.day)+' '+day
part 2 of the question can be done like this
def problem5b(path):
df=pd.read_csv('311-service-requests.csv')
df['Created Date'] = pd.to_datetime(df['Created Date'])
df['Day of Week'] = df['Created Date'].dt.day_name()
df['Complaint Type'].value_counts()[:10]
b=df['Complaint Type'].value_counts()[:10].index.tolist()
top_10 = df[(df['Complaint Type']==b[0]) | (df['Complaint Type']==b[1]) |(df['Complaint Type']==b[2]) |(df['Complaint Type']==b[3]) |
(df['Complaint Type']==b[4]) | (df['Complaint Type']==b[5]) | (df['Complaint Type']==b[6]) | (df['Complaint Type']==b[7]) |
(df['Complaint Type']==b[8]) | (df['Complaint Type']==b[9])]
top_10['Complaint Type'].unique()
list1 = []
for i in top_10.columns:
list1.append((i,top_10[i].unique()))
week_days = ['Monday','Tuesday','Wednusday','Thursday','Friday','Saturday','Sunday']
new_df = df[['Complaint Type','Day of Week']]
list_comp = df['Complaint Type'].unique()
list_day = df['Day of Week'].unique()
df_1 = pd.DataFrame(new_df[(new_df['Day of Week']==list_day[0]) &amp; (new_df['Complaint Type']== list_comp [0])])
complaint_list=['Blocked Driveway','DOF Literature Request','GENERAL CONSTRUCTION','HEATING','Illegal Parking','NONCONST','PAINT - PLASTER','PLUMBING','Street Condition','Street Light Condition']
comp_day = top_10.groupby(['Day of Week','Complaint Type']).size().unstack()
comp_day = comp_day[complaint_list]
return comp_day


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 6, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2021:00:12/23/21', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<python><pandas>', 'Title': 'Problem based on 311 service request from nyc open data', 'ViewCount': 72, 'id': 70457988, 'user_AboutMe': None, 'user_AccountId': 23689444.0, 'user_DisplayName': 'Hussnain Amjad', 'user_Reputation': 79.0, 'user_Views': 15.0, 'user_id': 17713955.0}}