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