Python, plotting Pandas' pivot_table from long data
<p>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. </p>

<p>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 </p>

<pre><code>ValueError: could not convert string to float: 'ZAF'
</code></pre>

<p>(where 'ZAF' is the label of one country)</p>

<p>What's the problem?</p>

<p>This is the code:</p>

<pre><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)
</code></pre>

<p>The xls file with raw data looks like:
<a href="http://i.stack.imgur.com/T1bom.png" rel="nofollow">raw data Excel view</a></p>

<p><a href="http://i.stack.imgur.com/5shnZ.png" rel="nofollow"><img src="http://i.stack.imgur.com/5shnZ.png" alt="enter image description here"></a></p>

<p>This is what I get from data_CO2PROD.info()</p>

<pre><code>&lt;class 'pandas.core.frame.DataFrame'&gt;
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
</code></pre>

<p>I think you need add parameter <code>values</code> to <code>pivot_table</code>:</p>

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

data_CO2PROD.plot()
plt.show()
</code></pre>

python pandas matplotlib panel-data