In this notebook we first generate count data per businessday at several locations over two years. Then we merge the two years into one dataframe. We resample the data to monthly means by end of month frequencies. After that we display the data to compare the average month count of the two years.
from bokeh.io import output_file, show
from bokeh.io import output_notebook
import pandas as pd
import numpy as np
output_notebook()
Loading BokehJS ...
Pandas datetime
pandas stores timestamps using NumPy’s datetime64 data type at the nanosecond resolution. We can demonstrate this by creating a pandas series object with a data range in nanosecond frequency
We see that the pandas series object is of the dtype datetime64[ns] (ns = nanoseconds)
Create date_range
With date_range we can create all sorts of time intervals For example, if you wanted a date index containing the last business day of each month, you would pass the 'BM' frequency (business end of month)
dates = pd.date_range('1/1/2021', periods = 3, freq='BM')
pd.Series(dates)
In the https://pandas.pydata.org/docs/reference/offset_frequency.html you can find more about frequencies and in the documentation https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html you can read all the methods of this datetime object.
Create time related count data
Let us create some time related data. The data represent counts per business days at several locations
N = 250 #there are about 250 business days in a year
locations=['Assen', 'Groningen', 'Hoogeveen']
dates20 = pd.date_range('1/1/2019', periods=N, freq='B')
count20 = pd.DataFrame(np.random.randint(7,33 ,size=(N, len(locations))), index=dates20, columns = locations)
dates21 = pd.date_range('1/1/2020', periods=N, freq='B')
count21 = pd.DataFrame(np.random.randint(10,40 ,size=(N, len(locations))), index=dates21, columns = locations)
#concatenate the two dataframes
counts = pd.concat([count20, count21])
We can plot the data just by calling pandas.DataFrame.plot()
counts.hist()
#plot the counts of Assen
counts['Assen'].plot()
Resample
This data is not readable. We should consider Resampling. Resampling is necessary when you’re given a data set recorded in some time interval and you want to change the time interval to something else. For example, aggregate daily numbers into monthly numbers. The syntax
<DataFrame or Series>.resample(arguments).<aggregate function>
month_counts = counts.resample('M').sum() #create a dataframe with the total sales per month
month_counts.tail()
We can also resample to evaluate a part of the dataset. For instance we could get the mean value of Assen en Hoogeveen combined
# get mean of Drenthe counts
month_counts_drenthe = counts.resample('M').mean().eval('Assen+Hoogeveen')
print(month_counts_drenthe.head())
#create a dataframe from the series
msd = pd.DataFrame({'date':month_counts_drenthe.index, 'average':month_counts_drenthe.values}).set_index('date')
print(msd.head())
2019-01-31 38.434783
2019-02-28 42.750000
2019-03-31 38.571429
2019-04-30 37.318182
2019-05-31 35.739130
Freq: M, dtype: float64
average
date
2019-01-31 38.434783
2019-02-28 42.750000
2019-03-31 38.571429
2019-04-30 37.318182
2019-05-31 35.739130
msd.plot(kind = 'bar')
Locators and Formatters using matplotib
This is not the kind of plot we want. Remember we can access the objects of the figure. The two relevant classes are Locators and Formatters. Locators determine where the ticks are, and formatters control the formatting of tick labels.
import matplotlib.pyplot as plt
from matplotlib.dates import MonthLocator, YearLocator, DateFormatter
plt.figure(figsize=(8, 6))
#ax = plt.gca(), figure = plt.gcf()
#adjust titles and labels
plt.title('counts in Drenthe')
plt.ylabel('average counts')
#adjust thick labels
plt.gca().xaxis.set_minor_formatter(DateFormatter('%B')) #display name of month
plt.gca().xaxis.set_minor_locator(MonthLocator(interval=1, bymonthday=-1)) #every months end of month
plt.gca().xaxis.set_major_formatter(DateFormatter('\n\n\n\n\n%Y')) #display year
plt.gca().xaxis.set_major_locator(YearLocator())
#plot
plt.bar(x = msd.index, height = msd['average'], width = 15, color = 'grey')
# adjust limit
plt.gca().set_xlim(pd.Timestamp('2019-01-01'), pd.Timestamp('2020-12-31'))
# auto rotate
plt.gcf().autofmt_xdate(rotation=90, which = 'minor', ha="center")
plt.show()
However, we want to compare the different dates over the year per year. Let's pivot the table
#change numbers to month name
def format_months(i):
l = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
return l[i-1]
df['month'] = df['month'].apply(format_months)
print(df.head(3))
year month 2019 2020
0 Jan 38.434783 44.260870
1 Feb 42.750000 51.300000
2 Mar 38.571429 49.954545
df.plot(x = 'month',
y=[2019, 2020],
kind = 'bar',
color = ['lightgrey', 'grey'],
ylabel='average counts',
title='counts in Drenthe',
figsize=(8, 6))
The bokeh way
The example above is an example of grouped bar chart. Bokeh can handle up to three levels of nested (hierarchical) categories, and will automatically group output according to the outermost level. To specify neted categorical coordinates, the columns of the data source should contain tuples, for example:
Furthermore we need some styling https://docs.bokeh.org/en/latest/docs/user_guide/styling.html
from bokeh.models import FactorRange # needed for grouped data
from bokeh.transform import factor_cmap #import colormap for factors
from bokeh.models import Legend, LegendItem #import legend object
from bokeh.models import ColumnDataSource #import columndatasource for plotting data
from bokeh.plotting import figure #import plotting figure canvas
#preprocces data to columndatsource in the grouped format
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
years = ['2019', '2020']
#create a tuple format for the group display
x = [ (month, year) for month in months for year in years ] # this creates [ ("jan", "2019"), ("jan", "2020"),...]
counts = sum(zip(df[2019].tolist(), df[2020].tolist()), ())
source = ColumnDataSource(data=dict(x=x, counts=counts))
#define color palette to be used
palette = ['lightgrey', 'grey'] #to color the bars
#create the figure area
p = figure(x_range=FactorRange(*x),
y_range= df[[2019,2020]].max() + 10,
tools = "",
toolbar_location=None,
height = 350,
title="sales by Year")
p.title.align = "center" #align the title
#add the barchart
b = p.vbar(x='x', #x -axis values
top='counts', # y-axis values
width=1,
source=source,
fill_color=factor_cmap('x', palette=palette, factors=years, start =1, end = 2), #fill color of the bar
line_color = 'white') #white line of the bars
#set the labels with normal font
p.xaxis.axis_label = "month"
p.xaxis.axis_label_text_font_style = "normal"
p.yaxis.axis_label = "count"
p.yaxis.axis_label_text_font_style = "normal"
#add a custom legend
legend = Legend(items=[
LegendItem(label="2019", renderers=[b], index=0),
LegendItem(label="2020", renderers=[b], index=1),
])
p.add_layout(legend)
p.legend.location = "top_right" # display legend in top right corner
p.legend.title = "years" # add a title to your legend
p.legend.title_text_font_style = "normal" #font style
p.legend.background_fill_alpha = 0.5 #make legend transparant
#ticks setup. First we disable the major tick (2019 and 2020 tick)
#then we make sure that the group label (month) is dsplayed vertical
p.xaxis.major_label_text_font_size = '0pt' # turn off x-axis tick labels
p.xaxis.major_tick_line_color = None # turn off x-axis major ticks
p.yaxis.minor_tick_line_color = None # turn off y-axis minor ticks
p.xaxis.group_label_orientation = "vertical"
#set the display margins
p.y_range.start = 0 #start directly with bar from the horizontal x-axis
p.x_range.range_padding = 0.05 #some margin from the y-axis
#disable grid
p.xgrid.grid_line_color = None #do not display grid
show(p)
see also https://docs.bokeh.org/en/latest/docs/user_guide/interaction/legends.html
Make it interactive using panel
We can make this plot interactive using panel. we need to reformat our print function to a general function that can handle year 2019, 2020 or both and we need to create a widget to select the years. We will make a grid layout to display the widget, the plot and the table. First we make sure we can run the panel form the notebook by panel.extension()
Important! If you use matplotlib instead of bokeh or holov you must make sure to return an interactive plot using pn.pane.Matplotlib(fig)
import panel as pn
pn.extension()
#generalize function
def plot_b(location = " Drenthe", years = ['2020']):
#preprocces data to columndatsource in the grouped format
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
#create a tuple format for the group display
x = [ (month, year) for month in months for year in years ] # this creates [ ("jan", "2019"), ("jan", "2020"),...]
if len(years) > 1:
counts = sum(zip(df[2019].tolist(), df[2020].tolist()), ())
else:
counts = tuple(df[int(years[0])].tolist())
source = ColumnDataSource(data=dict(x=x, counts=counts))
#define color palette to be used
palette = ['lightgrey', 'grey'] #to color the bars
start_color = {'2019':1,'2020':2}
#create the figure area
p = figure(x_range=FactorRange(*x),
y_range= df[[2019,2020]].max() + 10,
tools = "",
toolbar_location=None,
height = 350,
title=f"counts by Year in {location}")
p.title.align = "center" #align the title
start = start_color[years[0]]
#add the barchart
b = p.vbar(x='x', #x -axis values
top='counts', # y-axis values
width=1,
source=source,
fill_color=factor_cmap('x', palette=palette, factors=years, start=start), #fill color of the bar
line_color = 'white') #white line of the bars
#set the labels with normal font
p.xaxis.axis_label = "month"
p.xaxis.axis_label_text_font_style = "normal"
p.yaxis.axis_label = "count"
p.yaxis.axis_label_text_font_style = "normal"
#add a custom legend
legend = Legend(items=
[LegendItem(label=year, renderers=[b], index=i) for i, year in enumerate(years)])
p.add_layout(legend)
p.legend.location = "top_right" # display legend in top right corner
p.legend.title = "years" # add a title to your legend
p.legend.title_text_font_style = "normal" #font style
p.legend.background_fill_alpha = 0.5 #make legend transparant
#ticks setup. First we disable the major tick (2019 and 2020 tick)
#then we make sure that the group label (month) is dsplayed vertical
p.xaxis.major_label_text_font_size = '0pt' # turn off x-axis tick labels
p.xaxis.major_tick_line_color = None # turn off x-axis major ticks
p.yaxis.minor_tick_line_color = None # turn off y-axis minor ticks
p.xaxis.group_label_orientation = "vertical"
#set the display margins
p.y_range.start = 0 #start directly with bar from the horizontal x-axis
p.x_range.range_padding = 0.05 #some margin from the y-axis
#disable grid
p.xgrid.grid_line_color = None #do not display grid
return p
