Displaying pandas pivot_table in streamlit

[rimano]

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

[rimano]

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.

[Caroline]

Hey @rimano,

Sorry for the delayed reply on this!

I wanted to share that you might have better luck implementing a pivot table with the streamlit-aggrid component – in fact, we have a blog post written by Pablo Fonseca, the creator of the streamlit-aggrid component, about building a pivot table with streamlit-aggrid.

[rajendra_dharanikota]

hello @rimano , In you above image I see that the pivot table has an extra roe with Month under Year how did you display the data frame with all in a single row. by removing year. Please advice. Thanks.

[rimano]

Hi @rajendra_dharanikota,

Sorry for the delay

The answer to your question is I didn’t. In fact, that is what this topic is all about (Guess you weren’t very attentive there ).

This is a default streamlit behaviour and I have been trying to find a workaround. However, if that’s the behaviour you are expecting the code snippet I provided before can help you make it look a bit better. Otherwise, you may look into the solution proposed by @Caroline.