Python making combined bar and line plot with secondary y-axis

Question

I am trying to plot some csv data. I would like to plot some csv data. The data is shown below. I'm trying to plot columns 1-11 as a bar plot and column 12 as a line. I can make both plots separately using the following code, but how can I combine the plots and also have a secondary y-axis?

Here is a sample of the data I am plotting

DateTime    e1      e2       e3      e4      e5     e6      e7      e8      e9       e10      e11    p12
11/1/2014   1772    1926    1852    1513    1713    1568    1721    1822    1665    1449    1874    347
11/2/2014   19884   20365   19799   18017   18394   19383   20089   19929   20277   19522   19882   3710
11/3/2014   28697   29692   28881   25031   26731   28207   29095   29109   29577   28714   28926   5614
11/4/2014   24906   26061   25174   21745   23623   24126   24954   25344   25679   24406   25288   4990
11/5/2014   9059    9821    9116    7546    8742    8530    8910    9372    9214    8227    9366    1734
11/6/2014   1396    1691    1569    1176    1353    1223    1347    1541    1355    1044    1580    282
11/7/2014   10039   10416   9902    8223    9667    9511    9877    10106   10180   9524    10138   1857
11/8/2014   26746   27694   27128   23694   25520   26351   27176   27155   27704   26979   26995   5155
11/9/2014   14797   15567   14818   13556   14499   14244   14899   14979   15225   14171   14929   2846
11/10/2014  26059   27443   26573   22844   24655   25538   26658   26690   27303   26094   26471   5304

Here is the code I am using to plot them separately

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv("data.csv", index_col="DateTime", parse_dates=True)
df.iloc[:,[0,1,2,3,4,5,6,7,8,9,10]].plot(kind='bar')
df.iloc[:,11].plot(linestyle='-', marker='o')
plt.show()

Answer 1

Unfortunately it seems impossible to plot a bar plot and a lineplot to the same axes in pandas if the x axis is a dates axis.

A workaround is to use a matplotlib barplot instead

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv("data.csv", index_col="DateTime", parse_dates=True, delim_whitespace=True)

fig, ax= plt.subplots()

ax.plot_date(df.index, df.iloc[:,11], '-')
for i in range(10):
    diff = df.index[1]-df.index[0]
    spacing = diff/(1.3*len(df.columns))
    ax.bar(df.index+(-5+i)*spacing, df.iloc[:,i], 
           width=spacing/diff, label=df.columns[i]) 
plt.legend()
plt.gcf().autofmt_xdate()
plt.show()

Edit:

It will be possible to plot the bar plot and line plot in the same axes, if we neglect the fact that the points are dates. In the following code mind that we do not read the first column as index.

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv("data.csv", parse_dates=True, delim_whitespace=True)

ax = df.iloc[:,[0,1,2,3,4,5,6,7,8,9,10,11]].plot(kind='bar')
df.iloc[:,12].plot(linestyle='-', marker='o', ax = ax)

ax.set_xticklabels(df.DateTime, rotation=40) 
plt.show()

So this method will produce a graph where the bars and linepoints are simply ordered by their index (which is not the date). This may be acceptable or not depending on whether the dates are equally spaced.

If we e.g. change the input file to skip a date (11/6/2014 is not present), the code will produce

where the bars and line points are still equally spaced, although the dates in reality are not.

Plotting the same data with one day skipped with the matplotlib code from the start of the answer we get

where indeed the 11/6/2014 is missing.

Answer 2

You just need to plot them on the same axis

ax = df.iloc[:,[0,1,2,3,4,5,6,7,8,9,10]].plot(kind='bar')
df.iloc[:,12].plot(linestyle='-', marker='o', ax = ax)

ax.set_xticklabels(df.DateTime, rotation=40) #set the x-ticks to datetime column and rotate

is the code I used to plot both the graphs on same plot