I'm using pandas data frames to create a pivot table and then exporting to Excel format. I've tried some of the examples in the docs, but it doesn't create the filter drop down.
I would like to recreate how Excel does the pivot table when you add a Filter, by adding a drop down to filter the view.
I found a few articles that called the PageRange, but seems only available in pywin32. Can Xlsxwriter do something similar or can it be done by filtering the column. Can we add an autofilter to a cell?
import numpy as np
import pandas as pd
from pandas import ExcelWriter
writer = pd.ExcelWriter('test1.xlsx')
df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
"bar", "bar", "bar", "bar"],
"B": ["one", "one", "one", "two", "two",
"one", "one", "two", "two"],
"C": ["small", "large", "large", "small",
"small", "large", "small", "small",
"large"],
"D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
"E": [2, 4, 5, 5, 6, 6, 8, 9, 9]})
table = pd.pivot_table(df,values=['D','E'],index=['A','C'],aggfunc={'D':np.sum,'E':np.mean})
table.to_excel(writer, sheet_name="Sheet1", index=True)
writer.save()
Output:
> table
D E
A C
bar large 11 7.500000
small 11 8.500000
foo large 4 4.500000
small 7 4.333333
The drop down would filter on A, default to All, and the A column would be hidden. In the drop down you would see 'foo' and 'bar', selecting either of those would then only show the related data.
