Search code examples
pythonpandasmatplotlibpanel-data

Plotting Pandas' pivot_table from long data

I have a xls file with data organized in long format. I have four columns: the variable name, the country name, the year and the value.

After importing the data in Python with pandas.read_excel, I want to plot the time series of one variable for different countries. To do so, I create a pivot table that transforms the data in wide format. When I try to plot with matplotlib, I get an error 

ValueError: could not convert string to float: 'ZAF'

(where 'ZAF' is the label of one country)

What's the problem?

This is the code:

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

data = pd.read_excel('raw_emissions_energy.xls','raw data', index_col = None, thousands='.',parse_cols="A,C,F,M")

data['Year'] = data['Year'].astype(str)
data['COU'] = data['COU'].astype(str)

# generate sub-datasets for specific VARs

data_CO2PROD = pd.pivot_table(data[(data['VAR']=='CO2_PBPROD')], index='COU', columns='Year')

plt.plot(data_CO2PROD)

The xls file with raw data looks like: raw data Excel view

enter image description here

This is what I get from data_CO2PROD.info()

<class 'pandas.core.frame.DataFrame'>
Index: 105 entries, ARE to ZAF
Data columns (total 16 columns):
(Value, 1990)    104 non-null float64
(Value, 1995)    105 non-null float64
(Value, 2000)    105 non-null float64
(Value, 2001)    105 non-null float64
(Value, 2002)    105 non-null float64
(Value, 2003)    105 non-null float64
(Value, 2004)    105 non-null float64
(Value, 2005)    105 non-null float64
(Value, 2006)    105 non-null float64
(Value, 2007)    105 non-null float64
(Value, 2008)    105 non-null float64
(Value, 2009)    105 non-null float64
(Value, 2010)    105 non-null float64
(Value, 2011)    105 non-null float64
(Value, 2012)    105 non-null float64
(Value, 2013)    105 non-null float64
dtypes: float64(16)
memory usage: 13.9+ KB
None
Solution

  • I think you need add parameter values to pivot_table:

    data_CO2PROD = pd.pivot_table(data=data[(data['VAR']=='CC')], 
                              index='COU', 
                              columns='Year', 
                              values='Value')

data_CO2PROD.plot()
plt.show()