Python: How to handle missing values in a CSV?

Question

I have a given CSV sample as follows:

ID,ID_TYPE,OB_DATE,VERSION_NUM,MET_DOMAIN_NAME,OB_END_CTIME,OB_DAY_CNT,SRC_ID,REC_ST_IND,PRCP_AMT,OB_DAY_CNT_Q,PRCP_AMT_Q,METO_STMP_TIME,MIDAS_STMP_ETIME,PRCP_AMT_J
90, RAIN, 2006-01-01 00:00,1, WADRAIN,900,1,24109,1011,0,0,6, 2006-01-17 09:04,0,
150, RAIN, 2006-01-01 00:00,1, DLY3208,900,1,30747,1011,0,0,6, 2006-01-09 13:21,3,
174, RAIN, 2006-01-01 00:00,1, WADRAIN,900,1,24775,1011,0.2,0,6, 2006-01-17 09:04,0,

I would like to determine the weekday of each given date in my CSV. My code which achieves that looks as follows:

import csv
from datetime import datetime as dt


csv_file = open('raindata.csv')
csv_reader = csv.DictReader(csv_file)
field_names = list(csv_reader.fieldnames)
if 'WEEKDAY' in field_names:
    print "data has error"

elif 'RECWEEKDAY' in field_names:
    print "data has error"

else:
    field_names.insert(field_names.index('OB_DATE') + 1, 'WEEKDAY')
    field_names.insert(field_names.index('METO_STMP_TIME') + 1, 'RECWEEKDAY')

    def get_weekday(ob_date):
        return dt.strptime(ob_date, ' %Y-%m-%d %H:%M').strftime('%A')

    output = open('raindata.csv','w')
    csv_writer = csv.DictWriter(output, field_names)
    csv_writer.writeheader()
    for row in csv_reader:
        row['WEEKDAY'] = get_weekday(row['OB_DATE'])
        row['RECWEEKDAY'] = get_weekday(row['METO_STMP_TIME'])
        csv_writer.writerow(row)

My script runs fine and gives the correct result but it fails where the Date values are missing from OB_DATE column and METO_STMP_TIME column.

How do I change the existing code, so that for a blank Date value the corresponding Weekday value is also blank?

@Rahul : ValueError: time data ' ' does not match format ' %Y-%m-%d %H:%M' — desmond.carros
– desmond.carros, Commented Mar 18, 2016 at 6:17
@Rahul i know that it undertakes a string of the particular format but i want that for a blank it should give a blank. — desmond.carros
– desmond.carros, Commented Mar 18, 2016 at 6:18

lguiel · Accepted Answer · 2016-03-18 06:34:54Z

2

For other alternative, you can modify your get_weekday function to handle a blank date.

def get_weekday(ob_date):
    return dt.strptime(ob_date, ' %Y-%m-%d %H:%M').strftime('%A') if ob_date.strip() else ""

answered Mar 18, 2016 at 6:34

lguiel

3367 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jsfan · Accepted Answer · 2016-03-18 06:15:37Z

2

Just catch the exception that is thrown when the date/time string is missing or invalid and then set the value to an empty string.

try:
    row['WEEKDAY'] = get_weekday(row['OB_DATE'])
except ValueError:
    row['WEEKDAY'] = ''

answered Mar 18, 2016 at 6:15

jsfan

1,38310 silver badges22 bronze badges

11 Comments

Tadhg McDonald-Jensen Over a year ago

could also use if row["OB_DATE"]: ... else:... instead of try:... except:... but it is really up to preference.

desmond.carros Over a year ago

The output is nothing. the CSV was blank

jsfan Over a year ago

@TadhgMcDonald-Jensen: The try:...except:... will also work for invalid dates (or wrongly formatted ones). So, it's probably a better solution than specifically checking for a falsy value.

Tadhg McDonald-Jensen Over a year ago

@jsfan, if the date was malformed I would want the program to fail so like I said: it is really up to preference.

jsfan Over a year ago

@desmond.carros: If you have enough memory, you can read the whole file into memory first and then write straight back to the file.

|

Collectives™ on Stack Overflow

Python: How to handle missing values in a CSV?

2 Answers 2

Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related