How to plot data from snowflake into grouped bars overlaid with a line plot

Question

The requirement to have two bar graphs displayed either through sql or python library based on TYPE

Below is the data from the table

with data as (
select 'DIRECT' as type , '2023-04-30' as report_month , 148 as returns_per_head , 30.00 as filing_count ,52.25 as total_count
union 
select 'INDIRECT' as type , '2023-04-30' as report_month , 2876 as returns_per_head , 22.3 as filing_count ,29.25 as total_count
 )
 select * from data

	TYPE	REPORT_MONTH	FILING_COUNT	RETURNS_PER_HEAD	TOTAL_COUNT
0	DIRECT	2023-04-30	30	148	52.25
1	INDIRECT	2023-04-30	22.3	2876	29.25

I need output as below

	TYPE	REPORT_MONTH	Metric_HC	HC
0	DIRECT	2023-04-30	FILING_COUNT	30
1	DIRECT	2023-04-30	TOTAL_COUNT	52.25
2	DIRECT	2023-04-30	RETURNS_PER_HEAD	148
3	INDIRECT	2023-04-30	FILING_COUNT	22.3
4	INDIRECT	2023-04-30	TOTAL_COUNT	29.25
5	INDIRECT	2023-04-30	RETURNS_PER_HEAD	2876

The reason is I need to display in report as below, if it can be achieved, either via python library, or sql

Below is for example INDIRECT type alone alone

I am using HEX as new visualization

Trenton McKinney · Accepted Answer · 2023-06-02T21:09:55.903

Tested in python 3.11.3, pandas 2.0.1, matplotlib 3.7.1

import pandas as pd

# load data table from sql/hex into a pandas.DataFrame
df = pd.DataFrame(data)

# if REPORT_MONTH isn't a datetime, then convert it
df.REPORT_MONTH = pd.to_datetime(df.REPORT_MONTH)

# melt df into the requested shape
dfm = df.melt(id_vars=['TYPE', 'REPORT_MONTH'], var_name='Metric HC', value_name='HC')

# create the axes and figure
fig, axes = plt.subplots(2, 1, tight_layout=True, figsize=(10, 8), sharey=False, sharex=False)

# flatten the numpy array of axes for easy usage
axes = axes.flat

# iterate through axes and df groups
for ax, (t, data) in zip(axes, df.groupby('TYPE')):

    # reset the index, because the line plot must be plotted agains the index, from 0, to match the bar tick locations
    data = data.reset_index(drop=True)
    
    # conver the to the correct Year Month label
    data['Year Month'] = data.REPORT_MONTH.dt.strftime('%Y %B')

    # plots the bars
    data.plot(kind='bar', x='Year Month', y=['TOTAL_COUNT', 'FILING_COUNT'], title=t, color=['skyblue', 'darkblue'], rot=0, xlabel='', ax=ax)
    
    # get the left legend information
    hl, ll = ax.get_legend_handles_labels()
    # remove the legend
    ax.get_legend().remove()

    # plot the line on the rigth side
    ax_right = ax.twinx()
    data.plot(y='RETURNS_PER_HEAD', color='orange', ax=ax_right, marker='.')

    # annotate the line markers
    for i, text in data.iterrows():
        text = text['RETURNS_PER_HEAD']
        ax_right.text(x=i + 0.02, y=text, s=text, color='black', bbox=dict(facecolor='w', alpha=0.5, edgecolor='none', boxstyle='round'))
    
    # get the right legend information
    hr, lr = ax_right.get_legend_handles_labels()
    # remove the legend
    ax_right.get_legend().remove()
    
    # customize the right legend label
    lr = [v + ' (right)' for v in lr]
    
    # combine the handles and labels into a single figure legend
    fig.legend(hl + hr, ll + lr, bbox_to_anchor=(0.5, -0.05), loc='lower center', frameon=False, ncols=3)
    
    # annotate the bars
    for c in ax.containers:
        ax.bar_label(c)
    
    # add extra space between the top of the bars and the top spine
    ax.margins(y=0.1)

Sample `data` for answer

data = {'TYPE': ['DIRECT', 'INDIRECT', 'DIRECT', 'INDIRECT'],
        'REPORT_MONTH': ['2023-04-30', '2023-04-30', '2023-05-30', '2023-05-30'],
        'FILING_COUNT': [30.0, 22.3, 40.0, 32.3],
        'RETURNS_PER_HEAD': [148.0, 2876.0, 248.0, 2976.0],
        'TOTAL_COUNT': [52.25, 29.25, 62.25, 39.25]}

`df`

       TYPE REPORT_MONTH  FILING_COUNT  RETURNS_PER_HEAD  TOTAL_COUNT
0    DIRECT   2023-04-30          30.0             148.0        52.25
1  INDIRECT   2023-04-30          22.3            2876.0        29.25
2    DIRECT   2023-05-30          40.0             248.0        62.25
3  INDIRECT   2023-05-30          32.3            2976.0        39.25

`dfm`

        TYPE REPORT_MONTH         Metric HC       HC
0     DIRECT   2023-04-30      FILING_COUNT    30.00
1   INDIRECT   2023-04-30      FILING_COUNT    22.30
2     DIRECT   2023-05-30      FILING_COUNT    40.00
3   INDIRECT   2023-05-30      FILING_COUNT    32.30
4     DIRECT   2023-04-30  RETURNS_PER_HEAD   148.00
5   INDIRECT   2023-04-30  RETURNS_PER_HEAD  2876.00
6     DIRECT   2023-05-30  RETURNS_PER_HEAD   248.00
7   INDIRECT   2023-05-30  RETURNS_PER_HEAD  2976.00
8     DIRECT   2023-04-30       TOTAL_COUNT    52.25
9   INDIRECT   2023-04-30       TOTAL_COUNT    29.25
10    DIRECT   2023-05-30       TOTAL_COUNT    62.25
11  INDIRECT   2023-05-30       TOTAL_COUNT    39.25

How to plot data from snowflake into grouped bars overlaid with a line plot

1 Answers1

Sample data for answer

df

dfm

Sample `data` for answer

`df`

`dfm`