python csv DictReader Import columns that have dates with slashes in the names

Question

I have a csv file that has dates with slashes in the Column names -- 4/10/2020. It imports fine with Python csv.reader but the file adds a column each day. I have no control over the csv file.

Using csv.reader I have to guess at the column number. I'd like to map it with csv.DictReader and just plug in the date to sum the column. With the slashes in the column names I get

KeyError: '4/10/2020'

I have tried escaping it with \, double quotes and ticks. I've searched and tried for a while now. I know there are dozens here that can answer this in a few keystrokes. Thanks in advance.

the csv abbreviated commas added in edit:

Lat,    Long_,  place_name,             1/22/2020,  1/23/2020
-14.271,    -170.132,   American Samoa, 0,           0
13.4443,    144.7937,   Guam,            1,            0

I can import it fine with:

import csv
 dir = 'home/me/'
 file = 'time_series.csv'
 with open(dir + file) as file:
   reader = csv.reader(file)
   for row in reader:
       print(row[0], row[1], row[2], row[3], row[3])

**Edit:**This is the output from above:

me@hills:~/tmp/c19$ python cSum.py
Lat  Long_  place_name  1/22/2020  1/23/2020
-14.271  -170.132  American Samoa  0  0
13.4443  144.7937  Guam  0  0
15.0979  145.6739  Northern Mariana Islands  0  0
18.2208  -66.5901  Puerto Rico  0  0

What I'm trying to accomplish:

import csv
dir = 'home/me/'
file = 'time_series.csv'
#cr = csv.reader(open(dir + file))
cr = csv.DictReader(open(dir + file))
next(cr) #  skip the header

total = 0
for row in cr:
total += int(row['1/22/2020'])

print (total)

Edit output from above:

me@hills:~/tmp/c19$ python c1.py
Traceback (most recent call last):
File "c1.py", line 10, in <module>
total += int(row['1/22/2020'])
KeyError: '1/22/2020'

don't worry about the directory structure -- obfuscated.

May I see the output of print(row). I guess you don't use a proper delimiter=, looks like \t. See repl.it — stovfl
@stovfl I see what you are thinking. but I mistakenly copied the csv sample from a scratch file. it is indeed csv - 14.271, -170.132, American Samoa, 0, 0 thanks — grantiago
Unfortunately you don't show print(row) from the failing DictReader. My given reply.it link shows that a key like '1/22/2020' is working. — stovfl

Anikel31 Anikel31 · Accepted Answer · 2020-04-12T19:19:11

I strongly recommend using DictReader and not reader for the way you want to use it. Moreover, for just reading how many lines there are just use reader.line_num and it'll give you how many lines there are. Here's an example for parsing CSV file with DictReader :).

import csv


def main():
    file_path = r"yourfilepath"

    # Opening the file for reading.
    with open(file_path, "r") as csv_file:
        reader = csv.DictReader(csv_file)

        # Getting the csv data as dictionary.
        values = reader.next()

    # Printing the key and its values.
    for key, value in values.items():
        print "{}: {}".format(key, value)


if __name__ == "__main__":
    main()

python csv DictReader Import columns that have dates with slashes in the names

1 Answers