Import CSV into Python using Pandas

One of the features I like about R is when you read in a CSV file into a data frame you can access columns using names from the header file. The Python Data Analysis Library (pandas) aims to provide a similar data frame structure to Python and also has a function to read a CSV. Once pandas has been installed a CSV file can be read using:

import pandas
data_df = pandas.read_csv('in_data.csv')

To get the names of the columns use:

print(data_df.columns)

And to access columns use:

colHH = data_df['colHH']

Or if the column name is a valid Python variable name:

colHH = data_df.colHH

This is only a tiny part of pandas, there are lots of features available (which I’m just getting into). One interesting one is the ability to create pivot table reports from a data frame, similar to Excel.

Join two CSV files based on a common field

Sometimes it’s necessary to join two CSV files together using a common field. I wrote a python script a while ago to perform this task using the CSV python library (JoinTablesCSV.py). The script is run from the command line and takes the input and output CSV files and the numbers of the column to match (starting at 0).

For example to join attributes from inMatchFileName.csv to inRefFileName.csv, by matching the first column you can use:

python JoinTables.py inRefFileName.csv \
    inMatchFileName.csv outFileName.csv 0 0

Note this assumes there is only one record for each ID, if there are multiple (as in a relational database) this simple aproach won’t work and actually loading the data into a relational database would be better. My recomendation for this is SQLite, a self-contained database (i.e., stored in a single file and doesn’t require setting up a server), that can be accessed from Python and R.