Date does not get displayed in the desired format in line chart

Question

I have a plot_graph() function that plots pandas dataframe as a line chart.

def plot_graph(df):
 ax = plt.gca()
 #df["Date"].dt.strftime("%m/%d/%y")
 #df["date"] = df["date"].astype('datetime64[ns]')
 print(df['date'])
 df.plot(kind='line', x='date', y='Actual', ax=ax)
 df.plot(kind='line', x='date', y='Expected', color='red', ax=ax)
 ax.xaxis.set_major_locator(plt.MaxNLocator(3))
 plt.savefig("fig1.png")

I pass pandas dataframe in this format

 date       actual   expected
 2019-11    20       65
 2019-12    35       65

When I plot the line chart, x axis labels does not get displayed correctly as in (yyyy-mm) format. I believe it is with the date format. So I tried converting it to date. I tried with all the options(commented in the code), nothing seems to work. Any suggestions would be appreicated.

Use Matplotlib's [DateFormatter](https://matplotlib.org/3.2.1/api/dates_api.html#matplotlib.dates.DateFormatter)? — Quang Hoang, Jul 16 '20 at 20:07
Your `ax.xaxis.set_major_locator(plt.MaxNLocator(3))` looks really dangerous :-). — Quang Hoang, Jul 16 '20 at 20:15
@QuangHoang: ok, it was one of my tries, it did not work as well — user3447653, Jul 16 '20 at 20:16

Tom · Accepted Answer · 2020-07-16T20:36:26.627

Try this:

import pandas as pd
import matplotlib.dates as mdates

def plot_graph(df):
    ax = plt.gca()
    df['date'] = pd.to_datetime(df['date']).dt.date
    df.plot(kind='line', x='date', y='actual', ax=ax)
    df.plot(kind='line', x='date', y='expected', color='red', ax=ax)
    ax.xaxis.set_major_locator(mdates.MonthLocator())
    # ax.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m')) #to explicitly set format
    
plot_graph(df)

I think using matplotlib.dates is the best thing here, but it seems like df.plot() needs dates to be date and not datetime (or string). If you instead plot directly through matplotlib you don't need to do this. More here.

Trenton McKinney · Answer 2 · 2020-07-16T20:56:38.653

Reference Matplotlib: Date tick labels & Formatting date ticks using ConciseDateFormatter
Note the index column is in a datetime format. To transform your column to datetime, use df.date = pd.to_datetime(df.date)
df.plot() has tick locs like array([13136, 13152, 13174, 13175], dtype=int64). I don't actually know how those numbers are derived, but they cause an issue with some of the matplotlib axis and date formatting methods, which is why I changed the plots away from df.plot.
sns.lineplot and plt.plot have tick locs that are the ordinal representation of the datetime, array([737553., 737560., 737567., 737577., 737584., 737591., 737598., 737607.].

import pandas as pd
import numpy as np  # for test data
from datetime import datetime  # for test data
import matplotlib.dates as mdates
import matplotlib.pyplot as plt

# synthetic data with date as a datetime
np.random.seed(365)
length = 700
df = pd.DataFrame(np.random.rand(length, 2) * 10, columns=['Actual', 'Expected'], index=pd.bdate_range(datetime.today(), freq='d', periods=length).tolist()).reset_index()

# display(df.head())
       index    Actual  Expected
0 2020-07-16  9.414557  6.416027
1 2020-07-17  6.846105  5.885621
2 2020-07-18  5.438872  3.680709
3 2020-07-19  7.666258  3.050124
4 2020-07-20  4.420860  1.104433


# function
def plot_graph(df):
    
    #  df.date = pd.to_datetime(df.date)  # if needed and date is the column name

    fig, ax = plt.subplots()
    
    months = mdates.MonthLocator()  # every month
    months_fmt = mdates.DateFormatter('%Y-%m')   # format

    ax.plot('index', 'Actual', data=df)
    ax.plot('index', 'Expected', data=df, color='red')

    # format the ticks
    ax.xaxis.set_major_locator(months)
    ax.xaxis.set_major_formatter(months_fmt)
    
    plt.xticks(rotation=90)
    
    plt.legend()

    plt.show()


plot_graph(df)

Date does not get displayed in the desired format in line chart

2 Answers2