Displaying pandas pivot_table in streamlit

Summary

I am trying to display a pivot_table in streamlit but the dataframe is not properly displayed (See pictures below).

Note: The data used here is junk data I created to add some clarity to this example, as nobody replied to this issue after more than 24 hours.

Steps to reproduce

Code snippet:

import streamlit as st
import pandas as pd
import calendar
import sys


def read_csv(PATH: str) -> pd.DataFrame:
    try:
        df = pd.read_csv(PATH)
    except:
        sys.exit('Unable to read the data, kindly verify the source and try again')

    abbr = dict(enumerate(calendar.month_abbr))
    abbr.pop(0)
    df['MONTH'] = pd.Categorical(df['MONTH'], categories=list(abbr.values()), ordered=True)

    return df


with st.sidebar:
    st.info('This is my first web appliaction with streamlit')

PATH = 'https://raw.githubusercontent.com/Lamy237/hello-world/main/pay.csv'

df = read_csv(PATH)
monthly_pay_df = df.pivot_table(values='PAY', index='MONTH', columns='YEAR')

st.dataframe(monthly_pay_df, use_container_width=True)

Expected behavior:

I want the dataframe to be displayed the following way:

Actual behavior:

However, this is what I get on streamlit.

As you can see, not only the cell containing YEAR was removed but I also get several warnings in the MONTH column.

Debug info

  • Streamlit version: latest
  • Python version: 3.11.1
  • OS version: Windows 10

Since I havenโ€™t received any response for almost 2 days now, I guess this is a feature that is not yet supported in streamlit.

I found a workaround that could do the trick, although itโ€™s not really what I wanted. It consists of changing the pivot_table back into a regular dataframe.

Code snippet

import streamlit as st
import pandas as pd
import calendar
import sys


def read_csv(PATH: str) -> pd.DataFrame:
    try:
        df = pd.read_csv(PATH)
    except:
        sys.exit('Unable to read the data, kindly verify the source and try again')

    abbr = dict(enumerate(calendar.month_abbr))
    abbr.pop(0)
    df['MONTH'] = pd.Categorical(
        df['MONTH'], categories=list(abbr.values()), ordered=True)

    return df


with st.sidebar:
    st.info('This is my first web application with streamlit')


PATH = 'https://raw.githubusercontent.com/Lamy237/hello-world/main/pay.csv'

df = read_csv(PATH)
monthly_pay_df = df.pivot_table(values='PAY', index='MONTH', columns='YEAR')

# changing back to regular df
monthly_pay_df = monthly_pay_df.reset_index().rename_axis(None, axis='columns')

st.dataframe(monthly_pay_df, use_container_width=True)

Output

Note: I still get the following warning tho:

UserWarning: The DataFrame has column names of mixed type. They will be converted 
to strings and not roundtrip correctly.