CODEWITHSUNDEEP
pythonexcelpandaspivot-table
zdl
zdl

Reputation: 1

Is it possible to write an Excel Pivot Table object from pandas?

I'm replacing a lot of my Excel workload with pandas. I need to generate a standard Excel Pivot Table object (with the interactive UI such that users can define filters as they see fit). I'm not too well versed in pandas.pivot_table(), but before digging in want to know if this is possible. For example, given this DataFrame:

   Name Birth Year  Favorite Color
George  1987    Red
Maria   1987    Orange
Andrew  1992    Blue
Wyatt   1994    Red
Celeste 1987    Blue
Peter   1992    Blue

Is it possible to write an XLSX such as in this screenshot:

Excel pivot table

Thanks in advance!

Upvotes: 0

Views: 689

Answers (1)

Jakub
Jakub

Reputation: 549

A user interface could easily be built for a pivot table.

import pandas as pd

data = {'name': ['George', 'Maria', 'Andrew', 'Wyatt', 'Celeste', 'Peter'],
        'year': [1987, 1987, 1992, 1994, 1987, 1992],
        'color': ['Red', 'Orange', 'Blue', 'Red', 'Blue', 'Blue']}
df = pd.DataFrame(data)

# Simple interactive UI variable selection:
yr_selected = 'All'
if yr_selected != 'All':
    df = df.loc[df['year'] == yr_selected]
lbl_selected = 'color'

pt = df.pivot_table(index=[lbl_selected], values=['name'], aggfunc='count', margins=True)

print(pt)

Output:

        name
color
Blue       3
Orange     1
Red        2
All        6

Upvotes: 1

Related Questions