CODEWITHSUNDEEP
pythoncsvtext-files
Geogrammer
Geogrammer

Reputation: 137

Converting a CSV file for Hydrologic Model

I'm working with a utility within the calibration software PEST that is only compatible with a Sample Site File type. I would like to use this utility to calibrate my model, but my model's output is in a very different format (CSV). SSF is tab delimited and only contains 4 key columns of data: Sample Site ID, Date (MM/DD/YYYY), Time, and Streamflow.

Example of SSF file:

134 01/01/1980 00:00:00 34
134 01/02/1980 00:00:00 30
134 01/03/1980 00:00:00 28
134 01/04/1980 00:00:00 38

The output from my hydrologic model is written to CSV files that contain the Date (YYYYMMDD), Simulated Flow (Qsim), Observed Flow (Qobs), Temperature, Precipitation, Actual Evaporation, Potential Evaporation, and Snow-Water Equivalent.

Example of model output:

134
Date, Qsim, Qobs, Temp, Precip, AET, PET, SWE
19800101, 34, 31, 11, 21, 3.4, 4.0, 0
19800102, 30, 30, 11, 15, 3.0, 4.4, 0
19800103, 28, 25, 12, 0, 3.1, 4.0, 0
19800104, 38, 45, 8, 30, 0.5, 3.8, 0

So I've determined that in order to make the conversion I will to:

  1. add a column 0 that contains the site ID (row 0 in csv file)
  2. delete rows 0 and 1 in the csv file
  3. delete rows 2-7 in the csv file
  4. add a column for the time (in this case, I just need "00:00:00" in the col 2 for each row)
  5. reformat the dates from YYYYMMDD to MM/DD/YYYY
  6. write the file tab delimited

I am definitely a beginner with python, and this is the code I've been able to write so far:

import csv
HBVout = csv.reader(open('C:\\ENVpest\\Output\\Results.csv', 'rb'))
HBVout.next()
newSSF = csv.writer(open('SSF1.txt', 'wb+'), delimiter='\t')

for cline in HBVout:
    new_line = [val for col, val in enumerate(cline) if col not in (2,3,4,5,6,7)]
    newSSF.writerow(new_line)

I'm able to skip the first row, delete the unnecessary columns, and rewrite as a tab delimited file. I would greatly appreciate any help with adding columns to a file and with reformating the dates! I've scoured the other questions on SO and other sites, but haven't found any luck yet.

Upvotes: 1

Views: 297

Answers (2)

martineau
martineau

Reputation: 123491

Here's how I would do it. Your code was a little confusing at first because it's reading the output of another program which you're calling HBVout. I also didn't understand what you meant in #3 about deleting rows 2-7 in the csv file so have ignored it. Although the datetime module could have been used to convert the format of the date, it was so trivial, it's just done manually.

One notable difference is the data is read in using a csv.DictReader because that make accessing the various field more readable since each line read becomes a dictionary of values.

import csv

with open('Results.csv', 'rb') as inf, open('SSF1.txt', 'wb') as outf:
    site_id = inf.next().rstrip()  # read past site id on first line
    HBVout = csv.DictReader(inf, skipinitialspace=True)  # will read header line
    ssf = csv.writer(outf, delimiter='\t')

    for data in HBVout:
        date = data['Date']  # convert date from YYYYMMDD to MM/DD/YYYY 
        date = '/'.join((date[4:6], date[6:8], date[0:4]))
        ssf.writerow([site_id, date, '00:00:00', data['Qsim']])

Contents of SSF file created from model output example:

134\t01/01/1980\t00:00:00\t34\n
134\t01/02/1980\t00:00:00\t30\n
134\t01/03/1980\t00:00:00\t28\n
134\t01/04/1980\t00:00:00\t38\n

Upvotes: 1

Nurbldoff
Nurbldoff

Reputation: 321

Reformatting the dates can quite easily be done using the builtin datetime module, something like this:

from datetime import datetime
d = datetime.strptime("19800508", "%Y%m%d")  # convert your string to a 
                                             # datetime object
s = d.strftime("%d/%m/%Y")  # gives the string "08/05/1980"

Admittedly this module is a bit confusing to use - I always have to look up the documentation before using it - but it is pretty powerful. Dealing with date conversions is not something one wants to have to do manually.

I don't have much experience with the csv module but I think you're on the right track.

Upvotes: 1

Related Questions