Extract csv file specific columns to list in Python

Question

What I'm trying to do is plot the latitude and longitude values of specific storms on a map using matplotlib,basemap,python, etc. My problem is that I'm trying to extract the latitude, longitude, and name of the storms on map but I keep getting errors between lines 41-44 where I try to extract the columns into the list.

Here is what the file looks like:

1957,AUDREY,HU, 21.6N, 93.3W
1957,AUDREY,HU,22.0N,  93.4W
1957,AUDREY,HU,22.6N,  93.5W
1957,AUDREY,HU,23.2N,  93.6W

I want the list to look like the following:

latitude = [21.6N,22.0N,23.4N]
longitude = [93.3W, 93.5W,93.8W]
name = ["Audrey","Audrey"]

Here's what I have so far:

data = np.loadtxt('louisianastormb.csv',dtype=np.str,delimiter=',',skiprows=1)
'''print data'''

data = np.loadtxt('louisianastormb.csv',dtype=np.str,delimiter=',',skiprows=0)

f= open('louisianastormb.csv', 'rb')
reader = csv.reader(f, delimiter=',')
header = reader.next()
zipped = zip(*reader)

latitude = zipped[3]
longitude = zipped[4]
names = zipped[1]
x, y = m(longitude, latitude)

Here's the last error message/traceback I received:

Traceback (most recent call last):
File "/home/darealmzd/lstorms.py", line 42, in

header = reader.next()
_csv.Error: new-line character seen in unquoted field - do you need to open the file in universal-newline mode?

I think the problem is in your csv files not your code. Your code runs as expected with the provided sample csv input. This post may provided some insight. stackoverflow.com/questions/6726953/… — RMcG
– RMcG, Commented Oct 21, 2013 at 5:45

chthonicdaemon · Accepted Answer · 2018-04-15 14:57:50Z

76

This looks like a problem with line endings in your code. If you're going to be using all these other scientific packages, you may as well use Pandas for the CSV reading part, which is both more robust and more useful than just the csv module:

import pandas
colnames = ['year', 'name', 'city', 'latitude', 'longitude']
data = pandas.read_csv('test.csv', names=colnames)

If you want your lists as in the question, you can now do:

names = data.name.tolist()
latitude = data.latitude.tolist()
longitude = data.longitude.tolist()

edited Apr 15, 2018 at 14:57

answered Oct 21, 2013 at 5:42

chthonicdaemon

19.9k2 gold badges55 silver badges70 bronze badges

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

7 Comments

Jona Over a year ago

this is great. thanks, i hadn't heard of pandas before. one small thing, if you run this on columns with different lengths, the shorter columns will populate with nulls to match the longest column. you know a fix for this?

chthonicdaemon Over a year ago

I'm not sure how you would have columns with different lengths in the CSV? But you can get rid of the na values with data.dropna()

Jona Over a year ago

yeah it's a bit of a hack i'm doing for a small script. thanks =]

Euler_Salter Over a year ago

@chthonicdaemon I tried using your code for a similar example, however I think pands doesn't find my file, so it gives me the error " File text.csv does not exist" where instead of text there is the name of my file. Should I add the path of the file? How do I do that?

chthonicdaemon Over a year ago

@Euler_Salter I think that warrants a new question rather than a comment on an old one.

|

Ben Southgate · Accepted Answer · 2015-03-16 17:11:09Z

54

A standard-lib version (no pandas)

This assumes that the first row of the csv is the headers

import csv

# open the file in universal line ending mode 
with open('test.csv', 'rU') as infile:
  # read the file as a dictionary for each row ({header : value})
  reader = csv.DictReader(infile)
  data = {}
  for row in reader:
    for header, value in row.items():
      try:
        data[header].append(value)
      except KeyError:
        data[header] = [value]

# extract the variables you want
names = data['name']
latitude = data['latitude']
longitude = data['longitude']

answered Mar 16, 2015 at 17:11

Ben Southgate

3,8783 gold badges25 silver badges31 bronze badges

3 Comments

freethebees Over a year ago

Instead of the try/except, you could just use data.setdefault(header, list()).append(value). That will create an empty list the first time you access the key.

nogjam Over a year ago

try/except is faster, however, in case the file is large.

sqp_125 Over a year ago

I get: for header, value in row.items(): ^ TabError: inconsistent use of tabs and spaces in indentation

person the human · Accepted Answer · 2020-05-26 19:34:01Z

1

import csv
from sys import argv

d = open("mydata.csv", "r")

db = []

for line in csv.reader(d):
    db.append(line)

# the rest of your code with 'db' filled with your list of lists as rows and columbs of your csv file.

answered May 26, 2020 at 19:34

person the human

4154 silver badges16 bronze badges

Collectives™ on Stack Overflow

Extract csv file specific columns to list in Python

3 Answers 3

7 Comments

A standard-lib version (no pandas)

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

A standard-lib version (no pandas)

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related