Importing csv into array

[rexmorgan]

I could reallly use some help. I am a complete newbie in python. What I would like to do is import a csv file (originally an excel file). Have the user pick a beginning month and an ending month and add the columns of the months between. For example I have the following headers

Station Name,Lat,Long,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
Test 1,45.125478,-105.623154,3.12,0.15,0.08,0.61,0.67,1.24,2.32,1.06,0.64,0.07,0.32,1.02
Test 2,42.854123,-106.321587,0.09,3.15,1.61,0.03,0.84,1.62,3.01,1.51,0.81,0.02,0.23,1.09
Test 3,43.974532,-105.896214,2.65,2.01,0.05,3.02,1.05,0.08,0.08,1.06,0.43,0.65,0.12,1.06

If the user selected April as the beginning month and Jul as the ending month I would like output like

Station Name,Lat,Long,Sum
Test 1,45.125478,-105.623154,4.84
Test 2,42.854123,-106.321587,5.50
Test 3,43.974532,-105.896214,4.23

Any help would be greatly appreciated.

[Ene Uran]

This might help:

# sum some data of a csv file

raw_data = '''\
Station Name,Lat,Long,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
Test 1,45.125478,-105.623154,3.12,0.15,0.08,0.61,0.67,1.24,2.32,1.06,0.64,0.07,0.32,1.02
Test 2,42.854123,-106.321587,0.09,3.15,1.61,0.03,0.84,1.62,3.01,1.51,0.81,0.02,0.23,1.09
Test 3,43.974532,-105.896214,2.65,2.01,0.05,3.02,1.05,0.08,0.08,1.06,0.43,0.65,0.12,1.06
'''

# create the test data file
fname = "mydata.csv"
with open(fname, "w") as fout:
    fout.write(raw_data)

# 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
    if ix > 2:
        mdict[item] = ix

print(mdict)  # test
print('-'*70)

month_start = 'Apr'
month_end = 'Jul'
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]]
    print(plist) # test
    mysum = sum(plist)
    new_list.append([station, lat, long, mysum])

print('-'*70)
print("Result:")
for item in new_list:
    print(item)

[rexmorgan]

Thanks for the help so far. It worked nicely. What I need to do now is import the existing csv instead of hardcoding it. The csv file is located on my desktop and is named test.csv. When I attempted to modify the above code I am getting something that is not what I need.

I have tried replacing the raw_data with the name of my csv file and get unwanted results.

Any additional help or a point in the right direction is greatly appreciated. I need to get some sleep now. I will be awake and at my computer again in about 10 hours.

[rexmorgan]

OK I was really tired when I made my last post. Sleep deprivation does not help with coding. After some sleep this is what I have

#This code is working as of 9-6-2012 at 630AM


    # sum some data of a csv file
    import time


    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
        time.sleep(1)
        if ix > 8:
            mdict[item] = ix
    print(mdict)  # test prints out the month positions

    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')
    month2=raw_input('Input the 3 letter ID of ending month')
    #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]] #having trouble with blanks
        print(plist) # test prints out the values prior to adding them
        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)

[Ene Uran]

I am glad you could make it work! Nice job and interesting project!

[vegaseat]

Using a months dictionary makes this a nicely thought out solution. Well done!

[rexmorgan]

This is possibly slightly off the original topic but related to the above project. I am having some trouble dealing with blanks in the data. For instance, some weather stations did not report for a particular month and when that happens I get problems. I have attached a clip of the output. Just wondering if anyone could point me in the right direction for dealing with blanks in csv data.

[TrustyTony]

plist = [float(x) for x in item[start : end]] #having trouble with blanks

You could add default value like

plist = [(float(x) if x else default) for x in item[start:end]]

[rexmorgan]

Thanks for the reply pyTony! That code does work, however it still does not really solve my delima. What I would like to have done is ignore the row if the value for selected month is null but continue to process all rows of data. Any suggestions?

I will share the completed code in a post so that any interested parties can make minor adjustments to suit their needs. The complete project takes the excel file, which is record of weather station precip measurements by months and creates an ArcGIS map then exports that map to a predefined directory as a .png file for use elsewhere.

[TrustyTony]

How about then

plist = [float(x) for x in item[start:end] if x]
print(plist) # test prints out the values prior to adding them
if plist:
    mysum = sum(plist)
    new_list.append([station, lat, long, mysum])

[rexmorgan]

Brilliant! Thanks again for your help pyTony. Now I just need to integrate that part of code into my other code that makes the map.

[rexmorgan]

Link to the ArcGIS code This is the code promised earlier. I still have to get these 2 pieces working together but that should not be too difficult. Thanks again for all of your help pyTony and Ene Uran.