Working with csv and headers

[Anonymous User]

Original User: rexmorgan72

Good day.  I have some code that I will post below.

import time import csv import array from time import gmtime, strftime print strftime("%Y-%m-%d %H:%M:%S", gmtime())   print ' ' print '                     ARCGIS Climate Data Mapping Program' print ' '  # # This is an attempt to rectify the removal of headers in the csv file. ##   fname = 'Z:\\Desktop\\COOP_rainfall2.csv'  # read the data file data_list = [] for line in open(fname):     line = line.rstrip()     # create a list     line_list = line.split(',')     data_list.append(line_list) print(line, data_list)  ### #### Printed the file out but it started with Zortman for some reason which fouled up the header issue.  time.sleep(10)   # create a months dictionary with month:index pairs mdict = {} for ix, item in enumerate(data_list[0]):     print(ix, item)  # test prints out all headers and data positions     if ix > 8:         mdict[item] = ix time.sleep(3) print('-'*70) #prints a row of dashes this helps seperate data  #Gathering input from user  month1=raw_input('Input the 3 letter ID of beginning month')#getting input data from user month2=raw_input('Input the 3 letter ID of ending month')#getting input data from user  #assigning beginning and ending month here  month_start = month1 month_end = month2  #Create a new list new_list = [] outgroup = file("test.csv", 'w') #this file is getting put in the Rex.Morgan(\\204.228.188.2\HOME) (Z:) location   for item in data_list[1:]:     #print(item) # test     station = item[0]     lat = item[2] #Remembe to change this value if altering the structure of the input file Z:\\Desktop\\COOP_rainfall2.csv     long = item[3]     start = mdict[month_start]     end = mdict[month_end]+1     plist = [float(x) for x in item[start : end] if x] #having trouble with blanks     print(plist) # test prints out the values prior to adding them     if plist:       mysum = sum(plist)     new_list.append([station, lat, long, mysum]) print('-'*70) #prints another row of dashes further seperating data print(item, new_list) #this still prints the data with the Zortman line first print('~'*70) print(item) # this only prints the Zortman row of data; I am very confused here. print('^'*70)  for item in new_list:     print(item) # this print statement seems to produce a different set of data than the print statement in line 65  #print('~'*70) # prints a row of 70 ~ signs     #outgroup.write("%s \n " % item)     outgroup.write(str(item)) #this does produce a file with the info contained therein but not quite what I want     #new_file.write("%s\n" % item)  #print (item) f= open("test.csv", 'w') # This file is ending up in the Rex.Morgan(\\204.228.188.2\HOME) (Z:) location #f= open("test2.csv", 'w') for row in new_list:     f.write(','.join(map(str,row))+'\n')

         
One of the problems that I am having is getting the resultant file 'test.csv' to not strip off the headers.  Most of the code above has been pieced together from other bits.  Ultimately what I am trying to do is import a csv file add a few fields together and feed that data to ArcGIS 10.1.  I have everything working except for the part where I need to feed it to ArcGIS. For some reason (probably a very simple one) I cannot seems to get the file to write without removing the header row.

The info contained in the csv file is Weather info. Station Name, Station Number, Lat, Long, Precip.  I want to plot the precip values by having the user input a couple of variables 'month1' and 'month2'.  I am going to attach the python file and the csv file.

[RexMorgan · Accepted solution]

OK. I got this part solved. It is rather ugly and I am open to suggestions but this does work.

#This code is working as of 2-17-2013 at 950 AM # sum some data of a csv file #This code now seems to be working as expected.   import time import csv fname = 'Z:\\Desktop\\COOP_rainfall2.csv'  # read the data file data_list = [] for line in open(fname):     # remove trailing newline char     line = line.rstrip()     # create a list     line_list = line.split(',')     data_list.append(line_list)   # create a months dictionary with month:index pairs mdict = {} for ix, item in enumerate(data_list[0]):     print(ix, item)  # test prints out all headers and data positions     if ix > 8:         mdict[item] = ix time.sleep(3) print('-'*70) #prints a row of dashes this helps seperate data  #Gathering input from user  month1=raw_input('Input the 3 letter ID of beginning month')#getting input data from user month2=raw_input('Input the 3 letter ID of ending month')#getting input data from user  #assigning beginning and ending month here  month_start = month1 month_end = month2  #Create a new list new_list = [] outgroup = file("Z:\\Desktop\\test.csv", 'w') #this file is initially in the wrong format. for item in data_list[1:]:     #print(item) # test     station = item[0]     lat = item[2] #Remembe to change this value if altering the structure of the input file Z:\\Desktop\\COOP_rainfall2.csv     long = item[3]     start = mdict[month_start]     end = mdict[month_end]+1     plist = [float(x) for x in item[start : end] if x] #having trouble with blanks     print(plist) # test prints out the values prior to adding them     if plist:       mysum = sum(plist)     new_list.append([station, lat, long, mysum]) print('-'*70) #prints another row of dashes further seperating data print("Result:")  #### Adding these next two lines for a test #print([new_list]) #added the brackets to see if this makes a difference 2-17-2013 908 AM #print('~'*80)  for item in new_list:     #print(item)     #outgroup.write("%s \n " % item)     outgroup.write(str(item)) #this does produce a file with the info contained therein but not quite what I want     #new_file.write("%s\n" % item) f= open("Z:\\Desktop\\test2.csv", 'w') # This file needs to be different than the one in line 35 of code.  #f= open("test2.csv", 'w') for row in new_list:     f.write(','.join(map(str,row))+'\n')   #Another test  #### new_list.insert(0,['Station', 'lat', 'long', 'mysum']) # this ran without errors and did add the values to the beginning of list ## this line added at 2-17-2013 933 AM  ##### Adding these same lines of code found in lines  60, 62 and 63 f= open("Z:\\Desktop\\test3.csv", 'w') for row in new_list:     f.write(','.join(map(str,row))+'\n') #The above code was added 2-17-2013 939 AM this is a test #I am not sure what is going on now. Before I would get 3 differenct csv files which is what i was expecting # now I only get this one. This is is correct but not sure what to do about it.      # the line below is producing an error  #new_list.append(('Station', 'lat', 'long', 'mysum')) #this is a new line for testing 2-17-2013 911 AM #this does seems to append the Station, lat, long, mysum but it puts it at the end not the beginning.  #print([new_list]) #added the brackets to see if this makes a difference 2-17-2013 914 AM #print('~'*80)  time.sleep(15) 

Like I said, I know it's ugly. Now I seems to be having another set of issues.  I got a decent output file. I will attach it to this post. The problem now is getting ArcGIS to recognize the new csv file in the arcpy.MakeXYEventLayer_management.  I have the file that I attached test3.csv I am hoping to read that data into ArcGIS and plot accordingly.  Any suggestions on this part would be greatly appreciated.

The error is in line 151 arcpy.MakeXYEventLayer_management(Rex, "Long", "Lat", Var_layer, spRef, "")
ERROR 000728: Field Long does not exist within table
ERROR 000728: Field Lat does not exist within table
Failed to execute (MakeXYEventLayer).

The code follows in a separate post : Sorry about the long post.