Use A Chart-Level Filter For Your Y Axis - Plot.ly

Want to create a chart-level filter, allowing you to change your Y axis? Say hello to your new favorite library plot.ly! Sisense for Cloud Data Teams' Python and R integration allows you to use Plot.ly (as well as a host of other libraries), some of which we will leverage in this example.
Here, in your SQL output, be sure that your fields for the X axis are preceded with an 'x' character, and all your fields for the Y axis options are preceded by the 'y' character. If the y axis should be formatted as a dollar or percent, ensure the prefix is 'y$' or 'y%'
Python 3.6 Code
# SQL output should have these columns:
# x value prefixed with 'x'
# y value(s) prefixed with 'y' -- if $ or %, prefix with 'y$' or 'y%'
# series value(s) prefixed with 's'
import pandas as pd
import plotly.plotly as py
import plotly.graph_objs as go
import datetime
from datetime import timedelta
import numpy as np
community_post = ''
dummy_df = pd.DataFrame()
dummy_df['x_date'] = pd.date_range(start='1/1/2018', end='1/1/2019')
dummy_df['row_num'] = range(1, dummy_df.shape[0] + 1)
dummy_df['multiplier'] = np.random.randint(10,50, dummy_df.shape[0])
dummy_df['y$_revenue'] = dummy_df['row_num'] * dummy_df['multiplier']
dummy_df['y_purchases'] = np.random.randint(100, 1000, dummy_df.shape[0])
# Helper Function that removes underscores
def column_name(column):
return column.split('_', 1)[1].replace('_',' ').title()
# Helper function that formats values as $ or %
def format(column):
if column.startswith('Y$'):
return '$.3s'
elif column.startswith('Y%'):
return '.0%'
else:
return '.3s'
# Helper function that returns unique column values
def unique_vals(df, column):
return df.groupby(column).size().reset_index()[column]
# Get the x, y, and series columns
def get_columns(df):
x_column = [c for c in df.columns if c.startswith('X')][0]
y_columns = [c for c in df.columns if c.startswith('Y')]
series_columns = [c for c in df.columns if c.startswith('S_')]
unique_series = unique_vals(df, series_columns) if len(series_columns) > 0 else None
return x_column, y_columns, series_columns, unique_series
def button(y_col, y_columns, unique_series = None):
return {
'label': column_name(y_col),
'method': 'update',
'args': [
{
'visible': [c==y_col for c in y_columns for i in range(0, 1 if unique_series is None else len(unique_series))]
},
{
'yaxis': {
'tickformat': format(y_col),
'hoverformat': format(y_col)
}
}
]
}
def style_link(text, link, **settings):
style = ';'.join([f'{key.replace("_","-")}:{settings[key]}' for key in settings])
return f'<a href="{link}" style="{style}">{text}</a>'
def plot(df, annotation=None):
# Force consistent casing for columns
df.columns = [c.upper() for c in df.columns]
x_column, y_columns, series_columns, unique_series = get_columns(df)
has_series = unique_series is not None
showlegend = has_series
data = []
buttons = []
for idx, y_col in enumerate(y_columns):
buttons.append(button(y_col, y_columns, unique_series=unique_series))
# if no series -- create the traces for each y value and only display the first one
if not has_series:
trace = go.Scatter(
x=df[x_column],
y=df[y_col],
name=column_name(y_col),
visible=(idx==0)
)
data.append(trace)
# if series -- create the traces for each series for each y value, still only displaying series for the first y value
else:
for idx_series, series in unique_series.iterrows():
query = ' & '.join(f'{col} == "{series[{col}].iloc[0]}"' for col in series_columns)
df_series = df.query(query)
trace = go.Scatter(
x=df_series[x_column],
y=df_series[y_col],
name=f'{", ".join([series[{col}].iloc[0] for col in series_columns])}',
visible=(idx == 0)
)
data.append(trace)
updatemenus = list([
{
'active': 0,
'buttons': buttons,
'x': -.1,
'y': 1.25,
'xanchor': 'left',
'yanchor': 'top',
'bgcolor': '#FFFFFF'
}
])
first_y = y_columns[0]
xaxis = {'title': column_name(x_column)}
# If x value is a date, then add the quick-filter options for dates
if isinstance(df[x_column].iloc[0], datetime.date):
duration = (df[x_column].max() - df[x_column].min()).days
month_buttons = [dict(count=x, label=str(x)+'m', step='month', stepmode='backward') for x in [1,3,6] if x * 30 <= duration]
xaxis['rangeselector'] = {
'buttons': list(month_buttons + [{'step': 'all'}]) if len(month_buttons) > 0 else None,
'xanchor': 'right',
'yanchor': 'top',
'x': 1,
'y': 1.2
}
layout = {
'showlegend': showlegend,
'yaxis': {
'tickformat': format(first_y),
'hoverformat': format(first_y)
},
'xaxis': xaxis,
'margin': {
't': 20,
'b': 50,
'l': 60,
'r': 10
}
}
if annotation is not None:
layout['annotations'] = [annotation]
if len(y_columns) > 1:
layout['updatemenus'] = updatemenus
else:
layout['yaxis']['title'] = column_name(y_columns[0])
fig = dict(data=data, layout=layout)
# Use Periscope to visualize a dataframe by passing the data to periscope.output()
periscope.plotly(fig)
try:
plot(df)
except Exception as e:
print(e)
annotation = {
'x': 0.5,
'y': 0.5,
'ax': 0,
'ay': 0,
'xref': 'paper',
'yref': 'paper',
'text': style_link('DUMMY<br><br><br><br>DATA<br><br><br><br>EXAMPLE', community_post, font_size='60px', font_weight='bold', color='rgba(0, 0, 0, .25)'),
'showarrow': False,
'textangle': -25
}
plot(dummy_df, annotation=annotation)
Updated 03-02-2023
intapiuser
Admin
Joined December 15, 2022