6.10 Numbers and strings

This page covers a common problem when loading data into Pandas — when Pandas gets confused about whether values in a column are text or numbers.

An example

import numpy as np
import pandas as pd
pd.set_option('mode.chained_assignment','raise')

We return to the example data file that you may have seen in the text encoding page.

You can download the data file from imdblet_latin.csv.

films = pd.read_csv('imdblet_latin.csv', encoding='latin1')
films.head()

Now imagine we are interested in the average rating across these films:

ratings = films['Rating'].copy()
ratings.mean()

ValueError                                Traceback (most recent call last)
   ...
TypeError: Could not convert 8.68.1N/K8.38.08.18.08.48.28.08.08.08.68.68.08.28.48.48.18.38.48.28.58.08.28.18.48.18.68.48.18.78.1 to numeric

The problem

The problem is that we were expecting our ratings to be numbers, but in fact, they are strings.

We can see what type of thing Pandas has stored by looking at the dtype attribute of a Series, or the dtypes attribute of a data frame.

films.dtypes

Votes     object
Rating    object
Title     object
Year       int64
Decade     int64
dtype: object

ratings.dtype

dtype('O')

In fact both these bits of information say the same thing – that the ‘Rating’ column stores things in the “object” or “O” type. This is a general type that can store any Python value. It is the standard type that Pandas uses when storing text.

Why does Pandas use text for the ‘Rating’ column?

A quick look at the first rows gives the answer:

ratings.head()

0    8.6
1    8.1
2    N/K
3    8.3
4    8.0
Name: Rating, dtype: object

The film “Paris, Texas (1984)” has a value “N/K” for the rating. This can’t be a number, so Pandas stored this column in a format that allows it to store “N/K” as text.

If that wasn’t obvious, another way of checking where the problem value is, to apply the function float to the column values.

When we apply a function to a Series, it does this:

• For each value in the Series it:
  • Calls the function, with the value as the single argument.
  • Collects the new value returned from the function, and appends it to a new Series.
• Returns the new Series.

The result is a Series that is the same length as the original series, but where each value in the new series is the result of calling the function on the original value.

Recall that the float function converts the thing you pass into a floating point value:

v = float('3.14')
v

3.14

type(v)

float

Now we try applying float to the problematic column:

ratings.apply(float)

ValueError                                Traceback (most recent call last)
   ...
ValueError: could not convert string to float: 'N/K'

One way of dealing with this problem is to make a recoding function.

A recoding function is a function that we will apply to a Series. That means that we call the function for every value in the Series. The function argument is the value from the series. The function returns the new value, for a new Series.

def recode_ratings(v):
    if v == 'N/K':  # Return missing value for 'N/K'
        return np.nan
    # Otherwise make text value into a float
    return float(v)

We test our function:

recode_ratings('8.3')

8.3

recode_ratings('N/K')

nan

We make a new Series by calling the recode function:

new_ratings = ratings.apply(recode_ratings)
new_ratings.head()

0    8.6
1    8.1
2    NaN
3    8.3
4    8.0
Name: Rating, dtype: float64

We can insert this back into a copy of the original data frame:

films_fixed = films.copy()
films_fixed.loc[:, 'Rating'] = new_ratings
films_fixed.head()