Good Day, I have a text file in the .csv format. The file originates as a xlsx (excel) file type. The file looks something like the following.

Name Number Lat Long Jan Feb Mar Apr

Brockton 24-1670-06 38.145236 145.854921 0.15 0.62 1.25 2.14
Westby 24-7432-04 35.846125 132.743652 0.25 0.94 1.14 2.01
Zortman 24-9910-06 38.418461 125.541525 0.31 0.75 1.51 1.84

Now what I would like to do is select a starting month and an ending month and add them together. For example using the above info, selecting a starting month of Feb and an ending month of Apr add the data and create something like the following.

Name Number Lat Long Sum

Brockton 24-1670-06 38.145236 145.854921 4.01
Westby 24-7432-04 35.846125 132.743652 4.09
Zortman 24-9910-06 38.418461 125.541525 4.10

What I have so far is the following code. The problem that I have is that I am not sure how to write the results to a new csv or xlsx (excel) for further use in the remainding code.

#This code is working as of 9-7-2012 at 550AM
# sum some data of a csv file
import time
import csv

fname = 'Z:\\Desktop\\COOP_rainfall.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

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 = []
for item in data_list[1:]:
    #print(item) # test
    station = item[0]
    lat = item[1]
    long = item[2]
    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:")
for item in new_list:
    print(item)
time.sleep(45)

I am still kinda new to python. Also there is a restriction of not using anything beyond python 2.7. The reason for this is that the code above is a small snippett from a larger work. The idea is that I will export my precipitation data from the excel file into a csv file read in the file add up the appropriate months and send that info to ArcGIS 10.1 to plot the data on a map that is appropriate. Thanks in advance for any help.

Rex

Recommended Answers

All 6 Replies

Your data is in a 2-d list, data_list. The first step is finding the indices of the 2 months you want. That will accomplished on data_list[0].
index1=data_list[0].index(month1), where month1 is the starting month. Likewise,
index2=data_list[0].index(month2).
Now you'll loop through the elements from 1 to the end, convert the string rainfall amounts to numbers and add them, output the first 4 elements of each row and the new sum to a new list:

newlist=[]
for row in data_list[1:]:
    s=sum([float(i) for i in row[index1:index2+1]])
    newlist.append(row[:4].append(s))

Thanks for the reply. The file that I read in initially is in the 6th line of code

fname = 'Z:\\Desktop\\COOP_rainfall.csv'

I need to be able to take the list (new_list) that I created, between lines of code # 32 and 49, and write that list to a file in a csv format which looks very much like the file that I read in above. That file is simply a comma seperated file with a carriage return at the end of each row. For example:

Station Name, StaNum, Lat, Long, Precip
Brockton, 24-1670-06, 38.145236, 145.854921, 4.01
Westby, 24-7432-04, 35.846125, 132.743652, 4.09
Zortman, 24-9910-06, 38.418461, 125.541525, 4.10

I do not have a problem adding the precip values that part is taken care of. Essentially the problem that I am having is writing the list to a csv file that can be read-in in another module of the larger program.

The whole idea is that I take the columnar data from a spreadsheet that gets produced select the months that I am interested in add them together and plot the values in ArcGIS. Thanks for any help. I will post the entire code for any interested parties once I have accomplished this.

I hope this explains what I am trying to do. Thanks again.

I tried to upload the file for an example but it will not let me. It says that the file type is not allowed. It was simply a text file (txt) and a (csv) text file.

Have you tried using CSVed? CSVed is an easy and powerful CSV file editor, you can manipulate any CSV file, separated with any separator, including combining columns. Afterall it's free. You can download it here: http://csved.sjfrancke.nl/

some of the features include:

edit, add, insert and delete items;
import;
change separator;
add, delete columns;
rearrange column order;
save selection of rows and columns;
save filtered items;
save with double quotes;
search and replace;
filter items;
remove duplicates;
append and merge files;
install and uninstall
help in English
add prefix and suffix
split and join columns
export to HTML, Excel, Word, XML and more
proper words and strings
column search
column sort and global sort

Leo

To write any 2-d list as a csv formatted file, simply "join" the elements of the list with a comma and write as text:

f=open(<output file>, 'w')
for row in listname:
    f.write(','.join(map(str,row))+'\n')

Thank you rrashkin, that is exactly what I needed to do. It worked brilliantly! Thanks for all the other replies also. Now to get that incorporated into the rest.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.