Processing change over time with pandas

Sometimes data is easy to graph and communicate, and sometimes it takes a few extra steps. Let’s take a look at visas-by-continent.csv in the data/ folder.

Open it without moving it.

%matplotlib inline
import pandas as pd

df = pd.read_csv("data/visas-by-continent.csv")
df.head()

Checking our data

• How many columns and rows does it have?
• What is the data type of each column?

df.dtypes

year               int64
Africa           float64
Asia             float64
Europe           float64
North America    float64
Oceania          float64
South America    float64
Unknown          float64
dtype: object

df['year'] = pd.to_datetime(df['year'], format="%Y")
df.head()

df.dtypes

year             datetime64[ns]
Africa                  float64
Asia                    float64
Europe                  float64
North America           float64
Oceania                 float64
South America           float64
Unknown                 float64
dtype: object

df.set_index('year', inplace=True)

df.head()

Making a simple sample plot

I worked on this data a bit for you, so it’s almost very easy to graph. Just take the dataframe and .plot() it.

df.plot()

<matplotlib.axes._subplots.AxesSubplot at 0x10835ada0>

That x axis looks terrible, and “year” isn’t really a country. Let’s fix that.

Improving our data to improve our plot

We can improve the x axis and get rid of the ‘year’ column if we switch to a datetime index. This requires the following steps:

• Convert the year into a datetime column,
• Set it as our index,
• and Graph it again

Be sure to specify the format when converting to a date.

How can we compare the change in visas between different continents when their populations are so much different?

Tough comparisons made easy with percent change

If we’re looking at one set of values that’s in the billions and another in the thousands, it’s very tough to see on a graphic.

Instead of looking at raw values, a better way of thinking about this is percent change. If one thing goes up 10% and the other goes up 50%, we can make a simple comparison!

You can do this in pandas by selecting a column and asking for .pct_change().

df['Oceania'].pct_change()

year
1997-01-01          NaN
1998-01-01     1.387662
1999-01-01    11.714450
2000-01-01     0.513650
2001-01-01     5.064824
2002-01-01   -15.431176
2003-01-01    -2.867185
2004-01-01     7.973859
2005-01-01    -3.170509
2006-01-01     1.843772
2007-01-01     2.986427
2008-01-01     6.171630
2009-01-01   -12.906628
2010-01-01     3.324178
2011-01-01     2.945117
2012-01-01     0.919054
2013-01-01     6.650817
2014-01-01     6.919938
2015-01-01     7.708889
2016-01-01    13.681102
Name: Oceania, dtype: float64

df['Oceania'].pct_change().plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1086f63c8>

It’s missing the first value because it wasn’t changing from anything before! You can also easily plot the results.

import matplotlib.pyplot as plt
plt.style.use('ggplot')

df['Oceania'].pct_change().plot()
df['Asia'].pct_change().plot()

<matplotlib.axes._subplots.AxesSubplot at 0x108901668>

df.pct_change().head()

df.pct_change().plot(figsize=(20, 10))

<matplotlib.axes._subplots.AxesSubplot at 0x1090a99e8>

Friendly, friendly data

If we look at our data, every column is a category we’d like to calculate percent change on. We can actually use df.pct_change() to do it for every single column.

Let’s graph it, and use figsize to make it big enough that the legend doesn’t get in the way of everything.

Long vs. wide data

Using pct_change is easy when we have our set of data as a column. This makes a lot of other calculations more difficult, though. Different analysis and graphing approaches require different formats of data.

The current format - where a column is a list of observations - is called wide format. The more subjects we have, the more columns we have.

wide_df = pd.read_csv("data/visas-by-continent.csv")
wide_df.set_index('year', inplace=True)
wide_df.head()

df.sum(axis=1)

year
1997-01-01    17826183.00
1998-01-01    17442459.00
1999-01-01    18577434.00
2000-01-01    21424908.00
2001-01-01    22766334.00
2002-01-01    17308311.00
2003-01-01    14644902.00
2004-01-01    15147297.00
2005-01-01    16166853.00
2006-01-01    17510190.00
2007-01-01    19332855.00
2008-01-01    19809228.00
2009-01-01    17412546.00
2010-01-01    19268253.00
2011-01-01    22523817.00
2012-01-01    26781270.00
2013-01-01    27493047.00
2014-01-01    29797440.00
2015-01-01    32675235.72
2016-01-01    31144473.00
dtype: float64

The other option is called long data, where every row is an observation with a category variable.

long_df = pd.read_csv("data/visas-by-continent-long.csv")
long_df.head()

Converting long data to wide data

You can convert from long data to wide data using .pivot

# Reminder of what long data looks like
long_df.head(5)

# Turn the year into an index
# And make columns out of all of the values in 'continent'
# and turn the visas column into the cells
long_df.pivot(index='year', columns='continent', values='visas').head()

Converting wide data to long data

You can convert from wide data to long data using .melt

# Let's remind ourselves what it looks like
wide_df.head(2)

# Bring the year out as a real column again
wide_df.head(2).reset_index()

# convert the column into the variable "CONTINENT"
# and the value in the cell to the column "VISAS"
# and separate them based on the "year"
wide_df.reset_index().melt(var_name='continent', value_name='visas', id_vars=['year'])