How to Read/Write CSV files with Python


CSV files are a great way to store data. They are easy to read and write, and they can be opened in any text editor. However, if you want to use Python to read/write CSV files, you need to import the CSV module. In this blog post, we will show you how to do just that!

Reading CSV Files with the reader Function

In order to read CSV files with Python, you need to import the CSV module. You can do this by typing the following into your terminal:

import csv
Code language: JavaScript (javascript)

Once you have imported the CSV module, you can use the reader function to read your CSV file. The reader function takes two arguments:

  • csvfile – this is the file object that contains your CSV data.
  • delimiter – this is the character that separates each field in your CSV file. The default delimiter is a comma (“,”).

Here is an example of how to use the reader function:

with open('my_data.csv', 'r') as csvfile: reader = csv.reader(csvfile, delimiter=',') for row in reader: print(row)
Code language: JavaScript (javascript)

Python Script:

Take the following file as an example. Here, it is simply read and displayed as a text file for the purpose of explaining the contents.

sample.csv File contains:

with open('users/david/sample.csv) as f: print(f.read()) # 11,12,13,14 # 21,22,23,24 # 31,32,33,34

get csv file with csv.reader() function

import csv import pprint # Specify the file object opened in the first argument of the constructor with open('users/david/sample.csv', 'delimiter=',') as f: reader = csv.reader(f) for row in reader: print(row) # ['11', '12', '13', '14'] # ['21', '22', '23', '24'] # ['31', '32', '33', '34'] #If you want to get it as a two-dimensional array (list of lists), use list comprehension. with open('users/david/sample.csv', delimiter=',') as f: reader = csv.reader(f) l = [row for row in reader] print(l) # [['11', '12', '13', '14'], ['21', '22', '23', '24'], ['31', '32', '33', '34']]
Code language: Python (python)

csv.reader() function treats the delimiter as a comma by default. Any character string can be specified as a delimiter in the argument.

csv file contains: # 11 12 13 14 # 21 22 23 24 # 31 32 33 34 #Read it with open('data/src/sample.txt') as f: reader = csv.reader(f, delimiter=' ') l = [row for row in reader] print(l) # [['11', '12', '13', '14'], ['21', '22', '23', '24'], ['31', '32', '33', '34']]
Code language: Python (python)

How to get the row/column in a list?

Getting the row/column

# getting the row by specifying the row number starting with 0 as an index print(l[1]) # ['21', '22', '23', '24'] #getting the element by specifying the column number starting with 0 by index print(l[1][1]) # 22 #If you want to get a column, there is a method of transposing (swapping rows and columns) and then [] specifying it by index. l_T = [list(x) for x in zip(*l)] print(l_T) # [['11', '21', '31'], ['12', '22', '32'], ['13', '23', '33'], ['14', '24', '34']] print(l_T[1]) # ['12', '22', '32']
Code language: Python (python)

How to convert a string to a number?

You can easily convert string type to number string with some method below:

#convert string to number r = l[0] print(r) # ['11', '12', '13', '14'] print([int(v) for v in r]) # [11, 12, 13, 14] # It is also possible to convert a two-dimensional array (list of lists) at once. print([[int(v) for v in row] for row in l]) # [[11, 12, 13, 14], [21, 22, 23, 24], [31, 32, 33, 34]]
Code language: Python (python)

Treatment of quotation marks

I have the Dataset below

# 1,3,"5" # "a,b,c",x,y
Code language: PHP (php)

By default, the quotation marks themselves are not part of the element value, and delimiters inside the quotation marks are also not taken into account. This should not be a problem in most cases.

with open('users/data/sample_quote.csv') as f: reader = csv.reader(f) for row in reader: print(row) # ['1', '3', '5'] # ['a,b,c', 'x', 'y']
Code language: Python (python)

If you set argument quoting = csv.QUOTE_NONE, no special treatment is given to quotes. Delimiters within quotation marks are also treated as element delimiters.

with open('users/david/sample_quote.csv') as f: reader = csv.reader(f, quoting=csv.QUOTE_NONE) for row in reader: print(row) # ['1', '3', '"5"'] # ['"a', 'b', 'c"', 'x', 'y']
Code language: Python (python)

Read CSV file includes heading rows and heading columns

Dataset

# ,H1,H2,H3,H3 # ONE,11,12,13,14 # TWO,21,22,23,24 # THREE,31,32,33,34
Code language: PHP (php)
with open('users/david/sample_header_index.csv') as f: reader = csv.reader(f) l = [row for row in reader] pprint.pprint(l) # [['', 'H1', 'H2', 'H3', 'H4'], # ['ONE', '11', '12', '13', '14'], # ['TWO', '21', '22', '23', '24'], # ['THREE', '31', '32', '33', '34']] print([row[1:] for row in l[1:]]) # [['11', '12', '13', '14'], ['21', '22', '23', '24'], ['31', '32', '33', '34']] print([[int(v) for v in row[1:]] for row in l[1:]]) # [[11, 12, 13, 14], [21, 22, 23, 24], [31, 32, 33, 34]]
Code language: Python (python)

Read as dictionary: csv.DictReader

CSV file contains:

# H1,H2,H3,H3 # 11,12,13,14 # 21,22,23,24 # 31,32,33,34
Code language: PHP (php)

By default, the values ​​in the first row are used as field names, which are keys in the dictionary.

with open('users/marry/sample_header.csv') as csvfile: read_data = csv.DictReader(csvfile) l = [row for row in read_data] pprint.pprint(l) # [OrderedDict([('H1', '11'), ('H2', '12'), ('H3', '13'), ('H4', '14')]), # OrderedDict([('H1', '21'), ('H2', '22'), ('H3', '23'), ('H4', '24')]), # OrderedDict([('H1', '31'), ('H2', '32'), ('H3', '33'), ('H4', '34')])]
Code language: Python (python)

You can get the value by specifying the column with the field name as follows.

print(l[1]) # OrderedDict([('H1', '21'), ('H2', '22'), ('H3', '23'), ('H4', '24')]) print(l[1]['H2']) # 22
Code language: Python (python)

If there is no header (heading line) in the first line, specify it as an argument – fieldnames.

#CSV file contains: # 11,12,13,14 # 21,22,23,24 # 31,32,33,34 with open('users/marry/sample.csv') as csvfile: read_data = csv.DictReader(csvfile, fieldnames=['H1', 'H2', 'H3', 'H4'], ) for row in read_data: print(row) # OrderedDict([('H1', '11'), ('H2', '12'), ('H3', '13'), ('H4', '14')]) # OrderedDict([('H1', '21'), ('H2', '22'), ('H3', '23'), ('H4', '24')]) # OrderedDict([('H1', '31'), ('H2', '32'), ('H3', '33'), ('H4', '34')])
Code language: Python (python)

if you want to exclude column “One, Two, Three” as extra information, using pop() function.

# ,H1,H2,H3,H4 # One,11,12,13,14 # Two,21,22,23,24 # Three,31,32,33,34 with open('data/src/sample_header_index.csv') as f: reader = csv.DictReader(f) l = [row for row in reader] pprint.pprint(l, width=100) # [OrderedDict([('', 'one'), ('H1', '11'), ('H2', '12'), ('H3', '13'), ('H4', '14')]), # OrderedDict([('', 'Two'), ('H1', '21'), ('H2', '22'), ('H3', '23'), ('H4', '24')]), # OrderedDict([('', 'Three'), ('H1', '31'), ('H2', '32'), ('H3', '33'), ('H4', '34')])] print([od.pop('') for od in l]) # ['one', 'Two', 'Three'] pprint.pprint(l) # [OrderedDict([('H1', '11'), ('H2', '12'), ('H3', '13'), ('H4', '14')]), # OrderedDict([('H1', '21'), ('H2', '22'), ('H3', '23'), ('H4', '24')]), # OrderedDict([('H1', '31'), ('H2', '32'), ('H3', '33'), ('H4', '34')])]
Code language: Python (python)

Writing CSV Files with the writer Function

Once you have imported the CSV module, you can use the reader function to read your CSV file. The reader function takes two arguments:

  • csvfile – this is the file object that contains your CSV data.
  • delimiter – this is the character that separates each field in your CSV file. The default delimiter is a comma (“,”).

Here is an example of how to use the reader function:

with open('my_data.csv', 'w') as csvfile: writer = csv.writer(csvfile, delimiter=',') for row in my_data: writer.writerow(row)
Code language: JavaScript (javascript)

The CSV module is a great way to read and write CSV files with Python!

Example 1: Write line by line, using writerow() function with Arguments are lists.

with open('users/david/sample_writer_row.csv', 'w') as csvfile: writer = csv.writer(csvfile) writer.writerow([1, 3, 5]) writer.writerow(['x', 'y', 'z']) #output to csv file # 1,3,5 # x,y,z
Code language: Python (python)

Example 2: Write a two-dimensional array (list of lists) at once with writerow() function

list_two_dim = [[11, 12, 13, 14], [21, 22, 23, 24], [31, 32, 33, 34]] print(list_two_dim) # [[11, 12, 13, 14], [21, 22, 23, 24], [31, 32, 33, 34]] with open('users/david/sample_writer.csv', 'w') as csvfile: writer = csv.writer(csvfile) writer.writerows(list_two_dim) with open('users/david/sample_writer.csv') as csvfile: print(csvfile.read()) # 11,12,13,14 # 21,22,23,24 # 31,32,33,34
Code language: Python (python)

Example 3: Add to an already existing CSV file

If you want to append to an already existing CSV file , open the file in append mode ‘a’ with .open() function. Writing itself is the same as creating a new file. Appended at the end.

with open('users/david/sample_writer_row.csv', 'a') as csvfile: writer = csv.writer(csvfile) writer.writerow(['a', 'b', 'c']) with open('users/david/sample_writer_row.csv') as csvfile: print(csvfile.read()) # 1,3,5 # x,y,z # a,b,c
Code language: Python (python)

Example 4: Specify any delimiter to CSV file you want with delimiter argument

As in the previous examples, the default output is a comma-separated file. However, You can set any delimiter by delimiter argument.

list_two_dim = [[11, 12, 13, 14], [21, 22, 23, 24], [31, 32, 33, 34]] print(list_two_dim) # [[11, 12, 13, 14], [21, 22, 23, 24], [31, 32, 33, 34]] with open('users/david/sample_writer.tsv', 'w') as tsvfile: writer = csv.writer(tsvfile, delimiter='\t') writer.writerows(list_two_dim) with open('users/david/sample_writer.tsv') as tsvfile: print(tsvfile.read()) # 11 12 13 14 # 21 22 23 24 # 31 32 33 34
Code language: Python (python)

Example 5: Treatment of quotation marks

list_two_dim = [[1, 3, 5], ['a,b,c', 'x', 'y']] #By default, elements including delimiters are enclosed in quotes when written. with open('users/david/sample_writer_quote.csv', 'w') as csvfile: writer = csv.writer(csvfile) writer.writerows(list_two_dim) #CSV File Output # 1,3,5 # "a,b,c",x,y # Setting quoting=csv.QUOTE_ALL will enclose all elements in quotation marks. with open('users/david/sample_writer_quote_all.csv', 'w') as csvfile: writer = csv.writer(csvfile, quoting=csv.QUOTE_ALL) writer.writerows(list_two_dim) #CSV File Output # "1","3","5" # "a,b,c","x","y" #Setting quoting=csv.QUOTE_NONNUMERIC will quote non-numeric elements. #CSV File Output # 1,3,5 # "a,b,c","x","y"
Code language: Python (python)

Example 6: Adding headers

list_two_dim = [[11, 12, 13, 14], [21, 22, 23, 24], [31, 32, 33, 34]] print(list_two_dim) # [[11, 12, 13, 14], [21, 22, 23, 24], [31, 32, 33, 34]] header = ['', 'H1', 'H2', 'H3', 'H4'] index = ['ONE', 'TWO', 'THREE'] with open('users/david/sample_writer_header_index.csv', 'w') as csvfile: writer = csv.writer(csvfile) writer.writerow(header) for i, row in zip(index, list_two_dim): writer.writerow([i] + row) #Output CSV file # ,H1,H2,H3,H4 # ONE,11,12,13,14 # TWO,21,22,23,24 # THREE,31,32,33,34
Code language: PHP (php)

Example 7: Write a dictionary with csv.DictWriter method

Specify the list of dictionary keys to be written in the second argument (fieldnames) of the csv.DictWriter() function.

d1 = {'H1': 1, 'H2': 2, 'H3': 3} d2 = {'H1': 10, 'H3': 30} with open('users/temp/sample_dictwriter.csv', 'w') as csvfile: writer = csv.DictWriter(csvfile, ['H1', 'H2', 'H3']) writer.writeheader() writer.writerow(d1) writer.writerow(d2) #You also can use a list of dictionaries to write to CSV file. #writer.writerows([d1, d2]) # Output to CSV file # H1,H2,H3 # 1,2,3 # 10,,30
Code language: PHP (php)

if you write a dictionary with no key, the element is skipped (nothing is written and it becomes a missing value) using extrasaction'ignore' to ignore it.

d1 = {'H1': 1, 'H2': 2, 'H3': 3} d2 = {'H1': 10, 'H3': 30} with open('users/temp/sample_dictwriter_ignore.csv', 'w') as csvfile: writer = csv.DictWriter(f, ['H1', 'H3'], extrasaction='ignore') writer.writeheader() writer.writerows([d1, d2]) #Output # H1,H3 # 1,3 # 10,30
Code language: PHP (php)

Read more:

Andy Avery

I really enjoy helping people with their tech problems to make life easier, ​and that’s what I’ve been doing professionally for the past decade.

Recent Posts