Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I am working in python trying to parse an excel file but also add two things that are not there. 1 being dayOfWeek and the

I am working in python trying to parse an excel file but also add two things that are not there. 1 being dayOfWeek and the other being latLong. Here is my code that I have tried moving around several times. It will parse the data but will not add dayOfWeek or latLong. Not all of the entries have a date and the dayOfWeek code is supposed to skip those but still provide latLong in the parsed output.

import csv import datetime

# Put the full path to your CSV/Excel file here MY_FILE = "c:\\TestData\\houston_crime_data_jan17_clearLake.csv"

def parse(raw_file, delimiter): """Parses a raw CSV file to a JSON-like object""" # Setup an empty list parsed_data = []

# Open CSV file, and safely close it when we're done with open(raw_file, newline='') as opened_file: # Read the CSV data csv_data = csv.reader(opened_file, delimiter=delimiter) # Skip over the first line of the file for the headers field_labels = next(csv_data) #print(', '.join(field_labels)) # Iterate over each row of the csv file, zip together field -> value for row in csv_data: parsed_data.append(dict(zip(field_labels, row)))

return parsed_data

def dayOfWeek(parsed_data): for record in parsed_data: date = record['Date'] year = date[-4:] month = date[:date.index('/')] day = date[date.index('/')+1:-5] d = datetime.date(int(year),int(month),int(day)) dayOfWeek = d.weekday() dayList = ['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'] record['DayOfWeek']=dayList[dayOfWeek]

return daylist

def latLong(parsed_data): from geopy.geocoders import Nominatim geolocator = Nominatim() for record in parsed_data: street = record['StreetName']+' '+record['Type'] location = geolocator.geocode(street + " HOUSTON TX") if location != None: record['Y'] = location.latitude record['X'] = location.longitude else: # set coordinates to 0,0 and then make sure to ignore those record['Y'] = '0' record['X'] = '0' return X, Y

def main(): # Call our parse function and give it the needed parameters new_data = parse(MY_FILE, ",")

# Let's see what the data looks like! print (new_data)

if __name__ == "__main__": main()

Here is the data that I am using from the excel file.

Date Hour Offense Type Beat Premise BlockRange StreetName Type Suffix # offenses
######## 18 Theft 12D70 Department or Discount Store 100-199 EL DORADO BLVD W 1
######## 17 Auto Theft 12D70 Mall Parking Lot 500-599 BAYBROOK MALL - - 1
######## 17 Theft 12D70 Department or Discount Store 100-199 EL DORADO BLVD W 1
######## 18 Theft 12D70 Department or Discount Store 500-599 BAYBROOK MALL - - 1
######## 12 Theft 12D70 Miscellaneous Business (Non-Specific) 1300-1399 BAY AREA BLVD - 1
######## 12 Theft 12D70 Apartment Parking Lot 15900-15999 GALVESTON RD - 1
######## 19 Theft 12D70 Car Wash 300-399 EL DORADO BLVD - 1
######## 7 Theft 12D70 Apartment Parking Lot 600-699 BARRINGER LN - 1
######## 5 Theft 12D70 Apartment Parking Lot 200-299 EL DORADO BLVD - 1
######## 1 Burglary 12D70 Apartment 15800-15899 GALVESTON RD - 1
######## 17 Theft 12D70 Department or Discount Store 1400-1499 BAY AREA BLVD W 1
######## 19 Theft 12D70 Mall Parking Lot 500-599 BAYBROOK MALL - - 1
######## 20 Auto Theft 12D70 Other Parking Lot 1500-1599 BAY AREA BLVD W 1
######## 14 Robbery 12D70 Jewelry Stores 500-599 BAYBROOK MALL - - 1
######## 10 Theft 12D70 Mall Common Area 500-599 BAYBROOK MALL - - 1
######## 11 Theft 12D70 Specialty Store (Non-Specific) 500-599 EL DORADO BLVD - 1
######## 13 Theft 12D70 Clothing Store 1400-1499 BAY AREA BLVD W 1
######## 15 Theft 12D70 Department or Discount Store 600-699 BAYBROOK MALL - - 1
######## 5 Auto Theft 12D70 Other Parking Lot 18200-18299 GULF FWY - 1
######## 10 Theft 12D70 Miscellaneous Business (Non-Specific) 15500-15599 GALVESTON RD - 1
######## 17 Theft 12D70 Grocery Store or Supermarket 100-199 EL DORADO BLVD W 1
######## 17 Theft 12D70 Department or Discount Store 200-299 BAYBROOK MALL - - 1
######## 17 Theft 12D70 Mall Common Area 500-599 BAYBROOK MALL - - 1
######## 23 Robbery 12D70 Apartment Parking Lot 400-499 EL DORADO BLVD - 1
######## 14 Theft 12D70 Department or Discount Store 18100-18199 GULF FWY - 1
######## 7 Auto Theft 12D70 Apartment Parking Lot 400-499 TRESVANT DR - 1
######## 15 Theft 12D70 Other Unknown or Not Listed 200-299 BAYBROOK MALL - - 1
######## 16 Theft 12D70 Jewelry Stores 500-599 BAYBROOK MALL - - 1
######## 20 Theft 12D70 Grocery Store or Supermarket Parking Lot 100-199 EL DORADO BLVD W 1
######## 20 Theft 12D70 Other Parking Lot 18100-18199 GULF FWY - 1
######## 19 Theft 12D70 Mall Common Area 500-599 BAYBROOK MALL - - 1
1/9/2017 5 Burglary 12D70 Commercial Building 1500-1599 BAY AREA BLVD W 1
1/9/2017 13 Theft 12D70 Mall Parking Lot 200-299 BAYBROOK MALL - - 1
1/9/2017 15 Robbery 12D70 Specialty Store (Non-Specific) 15200-15299 GALVESTON RD - 1
1/9/2017 19 Theft 12D70 Restaurant or Cafeteria Parking Lot 1400-1499 BAY AREA BLVD W 1
1/9/2017 21 Theft 12D70 Mall Parking Lot 500-599 BAYBROOK MALL - - 1
1/6/2017 12 Auto Theft 12D70 Mall Parking Lot 500-599 BAYBROOK MALL - - 1
1/6/2017 14 Theft 12D70 Jewelry Stores 500-599 BAYBROOK MALL - - 1
1/5/2017 9 Theft 12D70 Mall Parking Lot 500-599 BAYBROOK MALL - - 1
1/5/2017 8 Auto Theft 12D70 Apartment Parking Lot 600-699 PINELOCH DR - 1
1/5/2017 15 Auto Theft 12D70 Apartment Parking Lot 500-599 EL DORADO BLVD - 1
1/5/2017 14 Theft 12D70 Mall Common Area 500-599 BAYBROOK MALL - - 1
1/8/2017 16 Theft 12D70 Grocery Store or Supermarket 100-199 EL DORADO BLVD W 1
1/8/2017 14 Theft 12D70 Apartment Parking Lot 500-599 EL DORADO BLVD - 1
1/8/2017 6 Burglary 12D70 Commercial Building 18300-18399 GULF FWY - 1
1/7/2017 20 Robbery 12D70 Mall Common Area 1000-1099 BAYBROOK MALL - - 1
1/7/2017 20 Theft 12D70 Mall Common Area 500-599 BAYBROOK MALL - - 1
1/7/2017 16 Theft 12D70 Department or Discount Store 200-299 BAYBROOK MALL - - 1
1/7/2017 12 Theft 12D70 Specialty Store (Non-Specific) 1100-1199 BAYBROOK MALL - - 1
1/7/2017 15 Theft 12D70 Mall Common Area 500-599 BAYBROOK MALL - - 1
1/3/2017 15 Theft 12D70 Convenience Store 14800-14899 GALVESTON RD - 1
1/3/2017 13 Theft 12D70 Specialty Store (Non-Specific) 700-799 BAYBROOK MALL - - 1
1/4/2017 20 Theft 12D70 Specialty Store (Non-Specific) 500-599 BAYBROOK MALL - - 1
1/4/2017 14 Theft 12D70 Department or Discount Store 18100-18199 GULF FWY - 1
1/4/2017 15 Burglary 12D70 Residence or House 400-499 SEAFOAM RD - 1
1/4/2017 5 Theft 12D70 Vacant Grocery Store or Supermarket 100-199 EL DORADO BLVD W 1
1/4/2017 8 Theft 12D70 Apartment Parking Lot 600-699 BARRINGER LN - 1
1/2/2017 18 Robbery 12D70 Mall Parking Lot 500-599 BAYBROOK MALL - - 1
1/2/2016 11 Theft 12D70 Other Parking Lot 14500-14599 GALVESTON RD - 1
1/1/2017 7 Auto Theft 12D70 Apartment Parking Lot 200-299 EL DORADO BLVD - 1
1/1/2017 10 Theft 12D70 Other Parking Lot 100-199 EL DORADO BLVD W 1
1/1/2017 19 Auto Theft 12D70 Mall Parking Lot 600-699 BAYBROOK MALL - - 1
######## 14 Theft 12D70 Grocery Store or Supermarket 100-199 EL DORADO BLVD W 1
######## 12 Theft 12D70 Mall Parking Lot 700-799 BAYBROOK MALL - - 1
######## 13 Theft 12D70 Other Parking Lot 18600-18699 GULF FWY - 1
######## 13 Theft 12D70 Mall Common Area 500-599 BAYBROOK MALL - - 1
######## 17 Theft 12D70 Restaurant or Cafeteria 700-799 BAYBROOK MALL - - 1
######## 16 Theft 12D70 Mall Parking Lot 18600-18699 GULF FWY - 1
######## 9 Theft 12D70 Restaurant or Cafeteria Parking Lot 19000-19099 GULF FWY - 1
######## 11 Theft 12D70 Drug Store or Medical Supply 900-999 CLEAR LAKE CITY BLVD - 1
######## 18 Auto Theft 12D70 Mall Parking Lot 600-699 BAYBROOK MALL - - 1
######## 14 Aggravated Assault 12D70 Bar or Night Club Parking Lot 100-199 EL DORADO BLVD W 1
######## 18 Burglary 12D70 Apartment 200-299 EL DORADO BLVD - 1
######## 17 Theft 12D70 Restaurant or Cafeteria Parking Lot 18100-18199 GULF FWY - 1
######## 16 Theft 12D70 Other Parking Lot 18100-18199 GULF FWY - 1
######## 16 Theft 12D70 Apartment Parking Lot 15800-15899 GALVESTON RD - 1
######## 11 Theft 12D70 Department or Discount Store 100-199 EL DORADO BLVD W 1
######## 17 Theft 12D70 Mall Common Area 1100-1199 BAYBROOK MALL - - 1
######## 3 Aggravated Assault 12D70 Apartment 200-299 EL DORADO BLVD - 1
######## 19 Robbery 12D70 Other Unknown or Not Listed 200-299 EL DORADO BLVD - 1
######## 12 Theft 12D70 Apartment Parking Lot 600-699 BARRINGER LN - 1
######## 7 Theft 12D70 Apartment Parking Lot 600-699 BARRINGER LN - 1
######## 8 Burglary 12D70 Pawn Resale Shop or Flea Market 500-599 EL DORADO BLVD - 1
######## 8 Theft 12D70 Apartment Parking Lot 400-499 EL DORADO BLVD - 1
######## 13 Burglary 12D70 Apartment 15800-15899 GALVESTON RD - 1
######## 15 Theft 12D70 Gym Recreat Club House Indoor Pool Spa 14600-14699 GALVESTON RD - 1
######## 11 Theft 12D70 Auto Repair 18100-18199 GULF FWY - 1
######## 8 Robbery 12D70 Department or Discount Store 100-199 EL DORADO BLVD W 1
######## 11 Theft 12D70 Department or Discount Store 100-199 BAYBROOK MALL - - 1
######## 16 Theft 12D70 Strip Business Center Parking Lot 200-299 EL DORADO BLVD W 1
######## 1 Burglary 12D70 19400-19499 GULF FWY - 1
######## 13 Burglary 12D70 Rental Storage Facility 15500-15599 GALVESTON RD - 1

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Real Time Database Systems Architecture And Techniques

Authors: Kam-Yiu Lam ,Tei-Wei Kuo

1st Edition

1475784023, 978-1475784022

More Books

Students also viewed these Databases questions

Question

What is conservative approach ?

Answered: 1 week ago

Question

What are the basic financial decisions ?

Answered: 1 week ago