I’m trying to parse through a csv file and extract the data from only specific columns.

Example csv:

ID | Name | Address | City | State | Zip | Phone | OPEID | IPEDS |
10 | C... | 130 W.. | Mo.. | AL... | 3.. | 334.. | 01023 | 10063 |

I’m trying to capture only specific columns, say ID, Name, Zip and Phone.

Code I’ve looked at has led me to believe I can call the specific column by its corresponding number, so ie: Name would correspond to 2 and iterating through each row using row[2] would produce all the items in column 2. Only it doesn’t.

Here’s what I’ve done so far:

import sys, argparse, csv
from settings import *

# command arguments
parser = argparse.ArgumentParser(description='csv to postgres',\
 fromfile_prefix_chars="@" )
parser.add_argument('file', help='csv file to import', action='store')
args = parser.parse_args()
csv_file = args.file

# open csv file
with open(csv_file, 'rb') as csvfile:

    # get number of columns
    for line in csvfile.readlines():
        array = line.split(',')
        first_item = array[0]

    num_columns = len(array)
    csvfile.seek(0)

    reader = csv.reader(csvfile, delimiter=' ')
        included_cols = [1, 2, 6, 7]

    for row in reader:
            content = list(row[i] for i in included_cols)
            print content

and I’m expecting that this will print out only the specific columns I want for each row except it doesn’t, I get the last column only.

The only way you would be getting the last column from this code is if you don’t include your print statement in your for loop.

This is most likely the end of your code:

for row in reader:
    content = list(row[i] for i in included_cols)
print content

You want it to be this:

for row in reader:
        content = list(row[i] for i in included_cols)
        print content

Now that we have covered your mistake, I would like to take this time to introduce you to the pandas module.

Pandas is spectacular for dealing with csv files, and the following code would be all you need to read a csv and save an entire column into a variable:

import pandas as pd
df = pd.read_csv(csv_file)
saved_column = df.column_name #you can also use df['column_name']

so if you wanted to save all of the info in your column Names into a variable, this is all you need to do:

names = df.Names

It’s a great module and I suggest you look into it. If for some reason your print statement was in for loop and it was still only printing out the last column, which shouldn’t happen, but let me know if my assumption was wrong. Your posted code has a lot of indentation errors so it was hard to know what was supposed to be where. Hope this was helpful!

import csv
from collections import defaultdict

columns = defaultdict(list) # each value in each column is appended to a list

with open('file.txt') as f:
    reader = csv.DictReader(f) # read rows into a dictionary format
    for row in reader: # read a row as {column1: value1, column2: value2,...}
        for (k,v) in row.items(): # go over each column name and value 
            columns[k].append(v) # append the value into the appropriate list
                                 # based on column name k

print(columns['name'])
print(columns['phone'])
print(columns['street'])

With a file like

name,phone,street
Bob,0893,32 Silly
James,000,400 McHilly
Smithers,4442,23 Looped St.

Will output

>>> 
['Bob', 'James', 'Smithers']
['0893', '000', '4442']
['32 Silly', '400 McHilly', '23 Looped St.']

Or alternatively if you want numerical indexing for the columns:

with open('file.txt') as f:
    reader = csv.reader(f)
    reader.next()
    for row in reader:
        for (i,v) in enumerate(row):
            columns[i].append(v)
print(columns[0])

>>> 
['Bob', 'James', 'Smithers']

To change the deliminator add delimiter=" " to the appropriate instantiation, i.e reader = csv.reader(f,delimiter=" ")

Use pandas:

import pandas as pd
my_csv = pd.read_csv(filename)
column = my_csv.column_name
# you can also use my_csv['column_name']

Discard unneeded columns at parse time:

my_filtered_csv = pd.read_csv(filename, usecols=['col1', 'col3', 'col7'])

P.S. I’m just aggregating what other’s have said in a simple manner. Actual answers are taken from here and here.

With pandas you can use read_csv with usecols parameter:

df = pd.read_csv(filename, usecols=['col1', 'col3', 'col7'])

Example:

import pandas as pd
import io

s = '''
total_bill,tip,sex,smoker,day,time,size
16.99,1.01,Female,No,Sun,Dinner,2
10.34,1.66,Male,No,Sun,Dinner,3
21.01,3.5,Male,No,Sun,Dinner,3
'''

df = pd.read_csv(io.StringIO(s), usecols=['total_bill', 'day', 'size'])
print(df)

   total_bill  day  size
0       16.99  Sun     2
1       10.34  Sun     3
2       21.01  Sun     3

You can use numpy.loadtext(filename). For example if this is your database .csv:

ID | Name | Address | City | State | Zip | Phone | OPEID | IPEDS |
10 | Adam | 130 W.. | Mo.. | AL... | 3.. | 334.. | 01023 | 10063 |
10 | Carl | 130 W.. | Mo.. | AL... | 3.. | 334.. | 01023 | 10063 |
10 | Adolf | 130 W.. | Mo.. | AL... | 3.. | 334.. | 01023 | 10063 |
10 | Den | 130 W.. | Mo.. | AL... | 3.. | 334.. | 01023 | 10063 |

And you want the Name column:

import numpy as np 
b=np.loadtxt(r'filepath\name.csv',dtype=str,delimiter='|',skiprows=1,usecols=(1,))

>>> b
array([' Adam ', ' Carl ', ' Adolf ', ' Den '], 
      dtype='|S7')

More easily you can use genfromtext:

b = np.genfromtxt(r'filepath\name.csv', delimiter='|', names=True,dtype=None)
>>> b['Name']
array([' Adam ', ' Carl ', ' Adolf ', ' Den '], 
      dtype='|S7')

Context: For this type of work you should use the amazing python petl library. That will save you a lot of work and potential frustration from doing things ‘manually’ with the standard csv module. AFAIK, the only people who still use the csv module are those who have not yet discovered better tools for working with tabular data (pandas, petl, etc.), which is fine, but if you plan to work with a lot of data in your career from various strange sources, learning something like petl is one of the best investments you can make. To get started should only take 30 minutes after you’ve done pip install petl. The documentation is excellent.

Answer: Let’s say you have the first table in a csv file (you can also load directly from the database using petl). Then you would simply load it and do the following.

from petl import fromcsv, look, cut, tocsv 

#Load the table
table1 = fromcsv('table1.csv')
# Alter the colums
table2 = cut(table1, 'Song_Name','Artist_ID')
#have a quick look to make sure things are ok. Prints a nicely formatted table to your console
print look(table2)
# Save to new file
tocsv(table2, 'new.csv')

I think there is an easier way

import pandas as pd

dataset = pd.read_csv('table1.csv')
ftCol = dataset.iloc[:, 0].values

So in here iloc[:, 0], : means all values, 0 means the position of the column. in the example below ID will be selected

ID | Name | Address | City | State | Zip | Phone | OPEID | IPEDS |
10 | C... | 130 W.. | Mo.. | AL... | 3.. | 334.. | 01023 | 10063 |

import pandas as pd 
csv_file = pd.read_csv("file.csv") 
column_val_list = csv_file.column_name._ndarray_values

Thanks to the way you can index and subset a pandas dataframe, a very easy way to extract a single column from a csv file into a variable is:

myVar = pd.read_csv('YourPath', sep = ",")['ColumnName']

A few things to consider:

The snippet above will produce a pandas Series and not dataframe. The suggestion from ayhan with usecols will also be faster if speed is an issue. Testing the two different approaches using %timeit on a 2122 KB sized csv file yields 22.8 ms for the usecols approach and 53 ms for my suggested approach.

And don’t forget import pandas as pd

If you need to process the columns separately, I like to destructure the columns with the zip(*iterable) pattern (effectively “unzip”). So for your example:

ids, names, zips, phones = zip(*(
  (row[1], row[2], row[6], row[7])
  for row in reader
))

To fetch column name, instead of using readlines() better use readline() to avoid loop & reading the complete file & storing it in the array.

with open(csv_file, 'rb') as csvfile:

    # get number of columns

    line = csvfile.readline()

    first_item = line.split(',')

Assume I have a csv.DictReader object and I want to write it out as a CSV file. How can I do this?

I know that I can write the rows of data like this:

dr = csv.DictReader(open(f), delimiter='\t')
# process my dr object
# ...
# write out object
output = csv.DictWriter(open(f2, 'w'), delimiter='\t')
for item in dr:
    output.writerow(item)

But how can I include the fieldnames?

Edit:
In 2.7 / 3.2 there is a new writeheader() method. Also, John Machin’s answer provides a simpler method of writing the header row.
Simple example of using the writeheader() method now available in 2.7 / 3.2:

from collections import OrderedDict
ordered_fieldnames = OrderedDict([('field1',None),('field2',None)])
with open(outfile,'wb') as fou:
    dw = csv.DictWriter(fou, delimiter='\t', fieldnames=ordered_fieldnames)
    dw.writeheader()
    # continue on to write data

Instantiating DictWriter requires a fieldnames argument.
From the documentation:

The fieldnames parameter identifies the order in which values in the dictionary passed to the writerow() method are written to the csvfile.

Put another way: The Fieldnames argument is required because Python dicts are inherently unordered.
Below is an example of how you’d write the header and data to a file.
Note: with statement was added in 2.6. If using 2.5: from __future__ import with_statement

with open(infile,'rb') as fin:
    dr = csv.DictReader(fin, delimiter='\t')

# dr.fieldnames contains values from first row of `f`.
with open(outfile,'wb') as fou:
    dw = csv.DictWriter(fou, delimiter='\t', fieldnames=dr.fieldnames)
    headers = {} 
    for n in dw.fieldnames:
        headers[n] = n
    dw.writerow(headers)
    for row in dr:
        dw.writerow(row)

As @FM mentions in a comment, you can condense header-writing to a one-liner, e.g.:

with open(outfile,'wb') as fou:
    dw = csv.DictWriter(fou, delimiter='\t', fieldnames=dr.fieldnames)
    dw.writerow(dict((fn,fn) for fn in dr.fieldnames))
    for row in dr:
        dw.writerow(row)

A few options:

(1) Laboriously make an identity-mapping (i.e. do-nothing) dict out of your fieldnames so that csv.DictWriter can convert it back to a list and pass it to a csv.writer instance.

(2) The documentation mentions “the underlying writer instance” … so just use it (example at the end).

dw.writer.writerow(dw.fieldnames)

(3) Avoid the csv.Dictwriter overhead and do it yourself with csv.writer

Writing data:

w.writerow([d[k] for k in fieldnames])

or

w.writerow([d.get(k, restval) for k in fieldnames])

Instead of the extrasaction “functionality”, I’d prefer to code it myself; that way you can report ALL “extras” with the keys and values, not just the first extra key. What is a real nuisance with DictWriter is that if you’ve verified the keys yourself as each dict was being built, you need to remember to use extrasaction=’ignore’ otherwise it’s going to SLOWLY (fieldnames is a list) repeat the check:

wrong_fields = [k for k in rowdict if k not in self.fieldnames]

============

>>> f = open('csvtest.csv', 'wb')
>>> import csv
>>> fns = 'foo bar zot'.split()
>>> dw = csv.DictWriter(f, fns, restval='Huh?')
# dw.writefieldnames(fns) -- no such animal
>>> dw.writerow(fns) # no such luck, it can't imagine what to do with a list
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\python26\lib\csv.py", line 144, in writerow
    return self.writer.writerow(self._dict_to_list(rowdict))
  File "C:\python26\lib\csv.py", line 141, in _dict_to_list
    return [rowdict.get(key, self.restval) for key in self.fieldnames]
AttributeError: 'list' object has no attribute 'get'
>>> dir(dw)
['__doc__', '__init__', '__module__', '_dict_to_list', 'extrasaction', 'fieldnam
es', 'restval', 'writer', 'writerow', 'writerows']
# eureka
>>> dw.writer.writerow(dw.fieldnames)
>>> dw.writerow({'foo':'oof'})
>>> f.close()
>>> open('csvtest.csv', 'rb').read()
'foo,bar,zot\r\noof,Huh?,Huh?\r\n'
>>>

Another way to do this would be to add before adding lines in your output, the following line :

output.writerow(dict(zip(dr.fieldnames, dr.fieldnames)))

The zip would return a list of doublet containing the same value. This list could be used to initiate a dictionary.

I am asking Python to print the minimum number from a column of CSV data, but the top row is the column number, and I don’t want Python to take the top row into account. How can I make sure Python ignores the first line?

This is the code so far:

import csv

with open('all16.csv', 'rb') as inf:
    incsv = csv.reader(inf)
    column = 1                
    datatype = float          
    data = (datatype(column) for row in incsv)   
    least_value = min(data)

print least_value

Could you also explain what you are doing, not just give the code? I am very very new to Python and would like to make sure I understand everything.

You could use an instance of the csv module’s Sniffer class to deduce the format of a CSV file and detect whether a header row is present along with the built-in next() function to skip over the first row only when necessary:

import csv

with open('all16.csv', 'r', newline='') as file:
    has_header = csv.Sniffer().has_header(file.read(1024))
    file.seek(0)  # Rewind.
    reader = csv.reader(file)
    if has_header:
        next(reader)  # Skip header row.
    column = 1
    datatype = float
    data = (datatype(row[column]) for row in reader)
    least_value = min(data)

print(least_value)

Since datatype and column are hardcoded in your example, it would be slightly faster to process the row like this:

    data = (float(row[1]) for row in reader)

Note: the code above is for Python 3.x. For Python 2.x use the following line to open the file instead of what is shown:

with open('all16.csv', 'rb') as file:

To skip the first line just call:

next(inf)

Files in Python are iterators over lines.

In a similar use case I had to skip annoying lines before the line with my actual column names. This solution worked nicely. Read the file first, then pass the list to csv.DictReader.

with open('all16.csv') as tmp:
    # Skip first line (if any)
    next(tmp, None)

    # {line_num: row}
    data = dict(enumerate(csv.DictReader(tmp)))

Borrowed from python cookbook,
A more concise template code might look like this:

import csv
with open('stocks.csv') as f:
    f_csv = csv.reader(f) 
    headers = next(f_csv) 
    for row in f_csv:
        # Process row ...

You would normally use next(incsv) which advances the iterator one row, so you skip the header. The other (say you wanted to skip 30 rows) would be:

from itertools import islice
for row in islice(incsv, 30, None):
    # process

use csv.DictReader instead of csv.Reader. If the fieldnames parameter is omitted, the values in the first row of the csvfile will be used as field names. you would then be able to access field values using row[“1”] etc

The new ‘pandas’ package might be more relevant than ‘csv’. The code below will read a CSV file, by default interpreting the first line as the column header and find the minimum across columns.

import pandas as pd

data = pd.read_csv('all16.csv')
data.min()

Well, my mini wrapper library would do the job as well.

>>> import pyexcel as pe
>>> data = pe.load('all16.csv', name_columns_by_row=0)
>>> min(data.column[1])

Meanwhile, if you know what header column index one is, for example “Column 1”, you can do this instead:

>>> min(data.column["Column 1"])

For me the easiest way to go is to use range.

import csv

with open('files/filename.csv') as I:
    reader = csv.reader(I)
    fulllist = list(reader)

# Starting with data skipping header
for item in range(1, len(fulllist)): 
    # Print each row using "item" as the index value
    print (fulllist[item])  

Because this is related to something I was doing, I’ll share here.

What if we’re not sure if there’s a header and you also don’t feel like importing sniffer and other things?

If your task is basic, such as printing or appending to a list or array, you could just use an if statement:

# Let's say there's 4 columns
with open('file.csv') as csvfile:
     csvreader = csv.reader(csvfile)
# read first line
     first_line = next(csvreader)
# My headers were just text. You can use any suitable conditional here
     if len(first_line) == 4:
          array.append(first_line)
# Now we'll just iterate over everything else as usual:
     for row in csvreader:
          array.append(row)

The documentation for the Python 3 CSV module provides this example:

with open('example.csv', newline='') as csvfile:
    dialect = csv.Sniffer().sniff(csvfile.read(1024))
    csvfile.seek(0)
    reader = csv.reader(csvfile, dialect)
    # ... process CSV file contents here ...

The Sniffer will try to auto-detect many things about the CSV file. You need to explicitly call its has_header() method to determine whether the file has a header line. If it does, then skip the first row when iterating the CSV rows. You can do it like this:

if sniffer.has_header():
    for header_row in reader:
        break
for data_row in reader:
    # do something with the row

I would use tail to get rid of the unwanted first line:

tail -n +2 $INFIL | whatever_script.py 

just add [1:]

example below:

data = pd.read_csv("/Users/xyz/Desktop/xyxData/xyz.csv", sep=',', header=None)**[1:]**

that works for me in iPython

Python 3.X

Handles UTF8 BOM + HEADER

It was quite frustrating that the csv module could not easily get the header, there is also a bug with the UTF-8 BOM (first char in file). This works for me using only the csv module:

import csv

def read_csv(self, csv_path, delimiter):
    with open(csv_path, newline='', encoding='utf-8') as f:
        # https://bugs.python.org/issue7185
        # Remove UTF8 BOM.
        txt = f.read()[1:]

    # Remove header line.
    header = txt.splitlines()[:1]
    lines = txt.splitlines()[1:]

    # Convert to list.
    csv_rows = list(csv.reader(lines, delimiter=delimiter))

    for row in csv_rows:
        value = row[INDEX_HERE]

I would convert csvreader to list, then pop the first element

import csv        

with open(fileName, 'r') as csvfile:
        csvreader = csv.reader(csvfile)
        data = list(csvreader)               # Convert to list
        data.pop(0)                          # Removes the first row

        for row in data:
            print(row)

Python 2.x

csvreader.next()

Return the next row of the reader’s iterable object as a list, parsed according to the current dialect.

csv_data = csv.reader(open('sample.csv'))
csv_data.next() # skip first row
for row in csv_data:
    print(row) # should print second row

Python 3.x

csvreader.__next__()

Return the next row of the reader’s iterable object as a list (if the object was returned from reader()) or a dict (if it is a DictReader instance), parsed according to the current dialect. Usually you should call this as next(reader).

csv_data = csv.reader(open('sample.csv'))
csv_data.__next__() # skip first row
for row in csv_data:
    print(row) # should print second row

This code opens the url and appends the /names at the end and opens the page and prints the string to test1.csv:

import urllib2
import re
import csv

url = ("http://www.example.com")
bios = [u'/name1', u'/name2', u'/name3']
csvwriter = csv.writer(open("/test1.csv", "a"))

for l in bios:
    OpenThisLink = url + l
    response = urllib2.urlopen(OpenThisLink)
    html = response.read()
    item = re.search('(JD)(.*?)(\d+)', html)
    if item:
        JD = item.group()
        csvwriter.writerow(JD)
    else:
        NoJD = "NoJD"
        csvwriter.writerow(NoJD)

But I get this result:

J,D,",", ,C,o,l,u,m,b,i,a, ,L,a,w, ,S,c,h,o,o,l,....

If I change the string to (“JD”, “Columbia Law School” ….) then I get

JD, Columbia Law School...)

I couldn’t find in the documentation how to specify the delimeter.

If I try to use delimenter I get this error:

TypeError: 'delimeter' is an invalid keyword argument for this function

Thanks for the help.

It expects a sequence (eg: a list or tuple) of strings. You’re giving it a single string. A string happens to be a sequence of strings too, but it’s a sequence of 1 character strings, which isn’t what you want.

If you just want one string per row you could do something like this:

csvwriter.writerow([JD])

This wraps JD (a string) with a list.

The csv.writer class takes an iterable as it’s argument to writerow; as strings in Python are iterable by character, they are an acceptable argument to writerow, but you get the above output.

To correct this, you could split the value based on whitespace (I’m assuming that’s what you want)

csvwriter.writerow(JD.split())

This happens, because when group() method of a MatchObject instance returns only a single value, it returns it as a string. When there are multiple values, they are returned as a tuple of strings.

If you are writing a row, I guess, csv.writer iterates over the object you pass to it. If you pass a single string (which is an iterable), it iterates over its characters, producing the result you are observing. If you pass a tuple of strings, it gets an actual string, not a single character on every iteration.