Where Do CSV Files Come From?

CSV files are normally created by programs that handle large amounts of data. They are a convenient way to export data from spreadsheets and databases as well as import or use it in other programs. For example, you might export the results of a data mining program to a CSV file and then import that into a spreadsheet to analyze the data, generate graphs for a presentation, or prepare a report for publication.

CSV files are very easy to work with programmatically. Any language that supports text file input and string manipulation (like Python) can work with CSV files directly.

Reading CSV Files Withcsv

Reading from a CSV file is done using thereader object. The CSV file is opened as a text file with Python’s built-inopen() function, which returns a file object. This is then passed to thereader, which does the heavy lifting.

Here’s theemployee_birthday.csv file:

name,department,birthday monthJohn Smith,Accounting,NovemberErica Meyers,IT,March

Here’s code to read it:

importcsvwithopen('employee_birthday.csv')ascsv_file:csv_reader=csv.reader(csv_file,delimiter=',')line_count=0forrowincsv_reader:ifline_count==0:print(f'Column names are{", ".join(row)}')line_count+=1else:print(f'\t{row[0]} works in the{row[1]} department, and was born in{row[2]}.')line_count+=1print(f'Processed{line_count} lines.')

This results in the following output:

Column names are name, department, birthday month    John Smith works in the Accounting department, and was born in November.    Erica Meyers works in the IT department, and was born in March.Processed 3 lines.

Each row returned by thereader is a list ofString elements containing the data found by removing the delimiters. The first row returned contains the column names, which is handled in a special way.

Reading CSV Files Into a Dictionary Withcsv

Rather than deal with a list of individualString elements, you can read CSV data directly into a dictionary (technically, anOrdered Dictionary) as well.

Again, our input file,employee_birthday.csv is as follows:

name,department,birthday monthJohn Smith,Accounting,NovemberErica Meyers,IT,March

Here’s the code to read it in as adictionary this time:

importcsvwithopen('employee_birthday.csv',mode='r')ascsv_file:csv_reader=csv.DictReader(csv_file)line_count=0forrowincsv_reader:ifline_count==0:print(f'Column names are{", ".join(row)}')line_count+=1print(f'\t{row["name"]} works in the{row["department"]} department, and was born in{row["birthday month"]}.')line_count+=1print(f'Processed{line_count} lines.')

This results in the same output as before:

Column names are name, department, birthday month    John Smith works in the Accounting department, and was born in November.    Erica Meyers works in the IT department, and was born in March.Processed 3 lines.

Where did the dictionary keys come from? The first line of the CSV file is assumed to contain the keys to use to build the dictionary. If you don’t have these in your CSV file, you should specify your own keys by setting thefieldnames optional parameter to a list containing them.

Optional Python CSVreader Parameters

Thereader object can handle different styles of CSV files by specifyingadditional parameters, some of which are shown below:

• delimiter specifies the character used to separate each field. The default is the comma (',').

• quotechar specifies the character used to surround fields that contain the delimiter character. The default is a double quote (' " ').

• escapechar specifies the character used to escape the delimiter character, in case quotes aren’t used. The default is no escape character.

These parameters deserve some more explanation. Suppose you’re working with the followingemployee_addresses.csv file:

name,address,date joinedjohn smith,1132 Anywhere Lane Hoboken NJ, 07030,Jan 4erica meyers,1234 Smith Lane Hoboken NJ, 07030,March 2

This CSV file contains three fields:name,address, anddate joined, which are delimited by commas. The problem is that the data for theaddress field also contains a comma to signify the zip code.

There are three different ways to handle this situation:

• Use a different delimiter
  That way, the comma can safely be used in the data itself. You use thedelimiter optional parameter to specify the new delimiter.

• Wrap the data in quotes
  The special nature of your chosen delimiter is ignored in quoted strings. Therefore, you can specify the character used for quoting with thequotechar optional parameter. As long as that character also doesn’t appear in the data, you’re fine.

• Escape the delimiter characters in the data
  Escape characters work just as they do in format strings, nullifying the interpretation of the character being escaped (in this case, the delimiter). If an escape character is used, it must be specified using theescapechar optional parameter.

Writing CSV Files Withcsv

You can also write to a CSV file using awriter object and the.write_row() method:

importcsvwithopen('employee_file.csv',mode='w')asemployee_file:employee_writer=csv.writer(employee_file,delimiter=',',quotechar='"',quoting=csv.QUOTE_MINIMAL)employee_writer.writerow(['John Smith','Accounting','November'])employee_writer.writerow(['Erica Meyers','IT','March'])

Thequotechar optional parameter tells thewriter which character to use to quote fields when writing. Whether quoting is used or not, however, is determined by thequoting optional parameter:

• Ifquoting is set tocsv.QUOTE_MINIMAL, then.writerow() will quote fields only if they contain thedelimiter or thequotechar. This is the default case.
• Ifquoting is set tocsv.QUOTE_ALL, then.writerow() will quote all fields.
• Ifquoting is set tocsv.QUOTE_NONNUMERIC, then.writerow() will quote all fields containing text data and convert all numeric fields to thefloat data type.
• Ifquoting is set tocsv.QUOTE_NONE, then.writerow() will escape delimiters instead of quoting them. In this case, you also must provide a value for theescapechar optional parameter.

Reading the file back in plain text shows that the file is created as follows:

John Smith,Accounting,NovemberErica Meyers,IT,March

Writing CSV File From a Dictionary Withcsv

Since you can read our data into a dictionary, it’s only fair that you should be able to write it out from a dictionary as well:

importcsvwithopen('employee_file2.csv',mode='w')ascsv_file:fieldnames=['emp_name','dept','birth_month']writer=csv.DictWriter(csv_file,fieldnames=fieldnames)writer.writeheader()writer.writerow({'emp_name':'John Smith','dept':'Accounting','birth_month':'November'})writer.writerow({'emp_name':'Erica Meyers','dept':'IT','birth_month':'March'})

UnlikeDictReader, thefieldnames parameter is required when writing a dictionary. This makes sense, when you think about it: without a list offieldnames, theDictWriter can’t know which keys to use to retrieve values from your dictionaries. It also uses the keys infieldnames to write out the first row as column names.

The code above generates the following output file:

emp_name,dept,birth_monthJohn Smith,Accounting,NovemberErica Meyers,IT,March

Reading CSV Files Withpandas

To show some of the power ofpandas CSV capabilities, I’ve created a slightly more complicated file to read, calledhrdata.csv. It contains data on company employees:

Name,Hire Date,Salary,Sick Days remainingGraham Chapman,03/15/14,50000.00,10John Cleese,06/01/15,65000.00,8Eric Idle,05/12/14,45000.00,10Terry Jones,11/01/13,70000.00,3Terry Gilliam,08/12/14,48000.00,7Michael Palin,05/23/13,66000.00,8

Reading the CSV into apandasDataFrame is quick and straightforward:

importpandasdf=pandas.read_csv('hrdata.csv')print(df)

That’s it: three lines of code, and only one of them is doing the actual work.pandas.read_csv() opens, analyzes, and reads the CSV file provided, and stores the data in aDataFrame. Printing theDataFrame results in the following output:

             Name Hire Date   Salary  Sick Days remaining0  Graham Chapman  03/15/14  50000.0                   101     John Cleese  06/01/15  65000.0                    82       Eric Idle  05/12/14  45000.0                   103     Terry Jones  11/01/13  70000.0                    34   Terry Gilliam  08/12/14  48000.0                    75   Michael Palin  05/23/13  66000.0                    8

Here are a few points worth noting:

• First,pandas recognized that the first line of the CSV contained column names, and used them automatically. I call this Goodness.
• However,pandas is also using zero-based integer indices in theDataFrame. That’s because we didn’t tell it what our index should be.

• Further, if you look at the data types of our columns , you’ll seepandas has properly converted theSalary andSick Days remaining columns to numbers, but theHire Date column is still aString. This is easily confirmed in interactive mode:

  Python

  >>>print(type(df['Hire Date'][0]))<class 'str'>

Let’s tackle these issues one at a time. To use a different column as theDataFrame index, add theindex_col optional parameter:

importpandasdf=pandas.read_csv('hrdata.csv',index_col='Name')print(df)

Now theName field is ourDataFrame index:

               Hire Date   Salary  Sick Days remainingNameGraham Chapman  03/15/14  50000.0                   10John Cleese     06/01/15  65000.0                    8Eric Idle       05/12/14  45000.0                   10Terry Jones     11/01/13  70000.0                    3Terry Gilliam   08/12/14  48000.0                    7Michael Palin   05/23/13  66000.0                    8

Next, let’s fix the data type of theHire Date field. You can forcepandas to read data as a date with theparse_dates optional parameter, which is defined as a list of column names to treat as dates:

importpandasdf=pandas.read_csv('hrdata.csv',index_col='Name',parse_dates=['Hire Date'])print(df)

Notice the difference in the output:

                Hire Date   Salary  Sick Days remainingNameGraham Chapman 2014-03-15  50000.0                   10John Cleese    2015-06-01  65000.0                    8Eric Idle      2014-05-12  45000.0                   10Terry Jones    2013-11-01  70000.0                    3Terry Gilliam  2014-08-12  48000.0                    7Michael Palin  2013-05-23  66000.0                    8

The date is now formatted properly, which is easily confirmed in interactive mode:

>>>print(type(df['Hire Date'][0]))<class 'pandas._libs.tslibs.timestamps.Timestamp'>

If your CSV files doesn’t have column names in the first line, you can use thenames optional parameter to provide a list of column names. You can also use this if you want to override the column names provided in the first line. In this case, you must also tellpandas.read_csv() to ignore existing column names using theheader=0 optional parameter:

importpandasdf=pandas.read_csv('hrdata.csv',index_col='Employee',parse_dates=['Hired'],header=0,names=['Employee','Hired','Salary','Sick Days'])print(df)

Notice that, since the column names changed, the columns specified in theindex_col andparse_dates optional parameters must also be changed. This now results in the following output:

                    Hired   Salary  Sick DaysEmployeeGraham Chapman 2014-03-15  50000.0         10John Cleese    2015-06-01  65000.0          8Eric Idle      2014-05-12  45000.0         10Terry Jones    2013-11-01  70000.0          3Terry Gilliam  2014-08-12  48000.0          7Michael Palin  2013-05-23  66000.0          8

Writing CSV Files Withpandas

Of course, if you can’t get your data out ofpandas again, it doesn’t do you much good. Writing aDataFrame to a CSV file is just as easy as reading one in. Let’s write the data with the new column names to a new CSV file:

importpandasdf=pandas.read_csv('hrdata.csv',index_col='Employee',parse_dates=['Hired'],header=0,names=['Employee','Hired','Salary','Sick Days'])df.to_csv('hrdata_modified.csv')

The only difference between this code and the reading code above is that theprint(df) call was replaced withdf.to_csv(), providing the file name. The new CSV file looks like this:

Employee,Hired,Salary,Sick DaysGraham Chapman,2014-03-15,50000.0,10John Cleese,2015-06-01,65000.0,8Eric Idle,2014-05-12,45000.0,10Terry Jones,2013-11-01,70000.0,3Terry Gilliam,2014-08-12,48000.0,7Michael Palin,2013-05-23,66000.0,8