Open excel pivot table in python

Question

I have pivot table in excel pivot table

I'd like to open this table in python( f.e. pandas or something like that) then remove period filters(using python). How can i do this? I tried df = pd.read_excel(path) but just opening in pandas like this does not help (because of filters)

Answer 1

If I understand your question correctly, you want to read in a pivot table from Excel which has data filtered out. Then, once you get the data into pandas, you want to remove the filter. I don't believe that precise workflow is possible. In order to remove the filter in pandas, the pandas dataframe would need to contain the entire dataset that exists in Excel. Because pandas cannot interpret Excel's filter operations into Pandas code, it is unable to both hold the filtered pivot table as well as the original data -- its just not how pd.read_excel(path) works.

So I think your best option is to actually do the filtering in pandas itself. To import your data, convert the raw data file to a csv and then use:

import pandas as pd
df = pd.read_csv(path)

Then, once you have your data loaded into a dataframe, use the pandas pivot_table function

pivot_table = AMTRAK_Stations_2015_csv.pivot_table(
     index=['id'],
     values=['date'],
     aggfunc={'date': 'sum'}
)

Then lastly, you can sort your data to be in ascending date order

df = df.sort_values(by='date', ascending=True, na_position='first')

Hopefully this is helpful! Full disclosure: I'm the creator of Mito, a spreadsheet that automatically generates Python code from your analysis.