Datetime Stacked Bar Chart with Matplotlib from dataframe

Question

Background:

I am stuck. 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:

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)

Quang Hoang · Accepted Answer · 2020-06-04 00:29:18Z

4

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:

edited Jun 4, 2020 at 0:29

answered Jun 2, 2020 at 13:18

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

3kstc Over a year ago

with the daily one, how can I make it so everyday is shown in the x-axis, ie define the x limits plt.xlim(datetime.date(2020, 5, 1), datetime.date(2020, 5, 31)) also, with this graph, my dates are appearing to be 00:00

Quang Hoang Over a year ago

You can use DayLocator.

3kstc Over a year ago

I'm struggling :( Any chance you could kindly update your answer and write the whole code? Where one can define the title, x and y axis [custom] labels? please?

3kstc Over a year ago

what about custom range like datetime.date(2020, 5, 1), datetime.date(2020, 5, 31)) mentioned in my first comment

Quang Hoang Over a year ago

Replace mindate with ''2020-05-01'` and maxdate with '2020-05-31' in the definition of all_dates.

|

Collectives™ on Stack Overflow

Datetime Stacked Bar Chart with Matplotlib from dataframe

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related