Datetime Stacked Bar Chart with Matplotlib from dataframe

Question

Background:

I am stuck. Or just having a blonde moment. I'm like a deer looking at headlights.

I've queried the following dataframe from MySQL table.

date          operation
2020-05-07        A
2020-05-08        B
2020-05-08        A
2020-05-12        A
2020-05-12        A
2020-05-12        B
2020-05-13        C
2020-05-13        A
2020-05-13        B
2020-05-14        A
2020-05-19        B
2020-05-21        A
2020-05-25        A
2020-05-26        B
2020-05-26        C
2020-05-26        A
2020-05-26        A
2020-05-29        A

I have no idea how to make it a stacked bar chart with matplotlib.

Research:

1. Grouped Bar-Chart with customized DateTime Index using pandas and Matplotlib
2. Stacked bar plot using Matplotlib

Question:

How can I generate a stack-bar-chart with matplot lib with the above sample data?

Code snippet:

import datetime as dt
import mysql.connector
import os
import pandas as pd
# import numpy as np
import matplotlib.pyplot as plt
import datetime

def generate_monthly_graph():
    query = "SELECT [...]`"
    mycursor.execute(query)
    mycursor.execute(query)
    data = mycursor.fetchall()

    df = pd.DataFrame(data, columns=['date', 'operation'])

    df = df.set_index('date')

    df.index = pd.to_datetime(df.index)

Answer 1

You can use crosstab then plot.bar:

# convert to date, no need for set_index
df.date = pd.to_datetime(df.date)

pd.crosstab(df.date,df.operation).plot.bar(stacked=True)

Output:

If you want monthly, you can use dt.to_period('M') to get monthly dates and crosstab:

pd.crosstab(df.date.dt.to_period('M'),df.operation).plot.bar(stacked=True)

Output (for the sample data):

---

Update to have all the dates on the x-axis:

df['date'] = pd.to_datetime(df.date)

mindate, maxdate = df['date'].agg(['min','max'])

all_dates = pd.date_range(mindate, maxdate, freq='D').date

(pd.crosstab(df.date,df.operation)
   .reindex(all_dates)
   .plot.bar(stacked=True)
)

Output: