Pandas read_csv() in Python with Example

Import CSV in Pandas

During the TensorFlow tutorial, you will use the adult dataset, which is often used for classification tasks. The file used below is the raw adult.data export from the UCI Machine Learning Repository, and the dataset page also offers a zipped download and the ucimlrepo package if the direct path ever stops resolving.

The data is stored in CSV format. The dataset includes 8 categorical variables:

• workclass
• education
• marital
• occupation
• relationship
• race
• sex
• native_country

And 6 continuous variables:

• age
• fnlwgt
• education_num
• capital_gain
• capital_loss
• hours_week

Together with the income label column, that makes the 15 column names you will pass to Pandas in the next section.

Pandas read_csv() Method

To import a CSV dataset, you can use the object pd.read_csv(). The basic signature is:

Pandas read_csv() Syntax

pandas.read_csv(filepath_or_buffer, sep=',', names=None, index_col=None, skipinitialspace=False)

• filepath_or_buffer: path, URL, or file-like object holding the data
• sep=’,’: the delimiter to use
• names=None: name the columns. If the dataset has ten columns, you need to pass ten names
• index_col=None: which column to use as the row index. Pass False to force a fresh integer index
• skipinitialspace=False: skip spaces after the delimiter

For the full list of arguments, check the official pandas.read_csv() documentation.

Pandas read_csv() Example

The snippet below names all 15 columns, points at the UCI file, and strips the space that follows every comma in this particular dataset.

## Import csv
import pandas as pd
## Define path data
COLUMNS = ['age','workclass', 'fnlwgt', 'education', 'education_num', 'marital',
           'occupation', 'relationship', 'race', 'sex', 'capital_gain', 'capital_loss',
           'hours_week', 'native_country', 'label']
PATH = "https://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.data"
df_train = pd.read_csv(PATH,
                       skipinitialspace=True,
                       names = COLUMNS,
                       index_col=False)
df_train.shape

Output:

(32561, 15)

The shape confirms 32,561 rows and 15 columns, so every name in COLUMNS was matched to a field in the file.

Key Pandas read_csv() Parameters

read_csv() accepts more than fifty arguments, but a small group covers almost every real import. The defaults below are the ones documented in the current pandas API reference.

Two of these deserve a note. index_col=False is not the same as leaving it unset: it explicitly tells Pandas not to promote the first column, which is what you want when a file ends every line with a stray delimiter. And names silently overwrites whatever the file declares, so pass header=0 alongside it when the CSV really does carry a header row.

Pandas groupby() Method

An easy way to see the data is to use the groupby method. This method can help you summarize the data by group. Below is a list of aggregations available with the groupby() method:

• count: count
• min: min
• max: max
• mean: mean
• median: median
• standard deviation: std
• and others

Inside groupby(), you name the column you want to group on before applying the aggregation.

Let us have a look at a single grouping with the adult dataset. You will get the mean of all the continuous variables by type of revenue, that is above 50k or below 50k:

df_train.groupby(['label']).mean()

You can get the minimum age by type of household:

df_train.groupby(['label'])['age'].min()
label
<=50K    17
>50K     19
Name: age, dtype: int64

You can also group by multiple columns. For instance, you can get the maximum capital gain according to the household type and marital status.

df_train.groupby(['label', 'marital'])['capital_gain'].max()
label  marital
<=50K  Divorced                 34095
       Married-AF-spouse         2653
       Married-civ-spouse       41310
       Married-spouse-absent     6849
       Never-married            34095
       Separated                 7443
       Widowed                   6849
>50K   Divorced                 99999
       Married-AF-spouse         7298
       Married-civ-spouse       99999
       Married-spouse-absent    99999
       Never-married            99999
       Separated                99999
       Widowed                  99999
Name: capital_gain, dtype: int64

You can create a plot after a groupby. One way to do it is to plot the grouped result directly.

To create a clearer plot, apply unstack() after mean() so the marital status moves from the index into the columns. The chart then has two groups, one per income label, instead of the fourteen (2*7) bars a flat multilevel index would produce.

If you use a Jupyter Notebook, remember to add %matplotlib inline, otherwise no plot will be displayed.

%matplotlib inline
df_plot = df_train.groupby(['label', 'marital'])['capital_gain'].mean().unstack()
df_plot

Plotting that unstacked frame as a bar chart gives the figure below.

How to Read Large CSV Files with Pandas

The adult dataset is small, but the same call can stall on a multi-gigabyte export. Three arguments do most of the work.

• usecols loads only the columns you actually need. The pandas documentation notes that this results in much faster parsing and lower memory usage.
• dtype pins each column to a type, so Pandas skips inference and does not widen integers to 64-bit by default.
• chunksize returns a TextFileReader instead of a DataFrame, letting you loop over blocks of N rows and aggregate as you go.

import pandas as pd

# read only the columns you need, with explicit types
cols  = ['age', 'education', 'capital_gain', 'label']
types = {'age': 'int32', 'capital_gain': 'int32'}
df = pd.read_csv(PATH, names=COLUMNS, usecols=cols, dtype=types,
                 skipinitialspace=True, index_col=False)

# stream the file in fixed-size chunks instead of loading it whole
total = 0
for chunk in pd.read_csv(PATH, names=COLUMNS, chunksize=10000,
                         skipinitialspace=True, index_col=False):
    total = total + len(chunk)
print(total)

Two further options help once the basics are in place. engine='pyarrow' switches to the multithreaded Arrow parser, and dtype_backend='pyarrow' keeps the result in Arrow-backed columns. low_memory is True by default, which parses the file internally in pieces, but it can produce mixed types in a column; set an explicit dtype rather than relying on it.

Finally, compression='infer' means a path ending in .gz, .zip, .bz2, .xz or .zst is decompressed on the fly, so there is no need to unpack an archive before reading it.

Common Pandas read_csv() Errors and How to Fix Them

Most read_csv() failures come from four causes, and each has a documented argument that resolves it.

The adult dataset shows the missing-value case directly: unknown workclass, occupation and native_country entries are stored as a literal question mark, so they arrive as the string ‘?’ unless you declare them.

# tolerate rows with too many fields instead of raising
df = pd.read_csv(PATH, names=COLUMNS, on_bad_lines='skip')

# read a file that is not UTF-8
df = pd.read_csv('sales.csv', encoding='latin-1')

# treat the dataset's '?' placeholder as a missing value
df = pd.read_csv(PATH, names=COLUMNS, na_values='?', skipinitialspace=True)

FAQs