Cannot Export to Excel

[apkant0neze]

Hey Henry,

I'm experiencing the following error: AttributeError: 'PrettyPandas' object has no attribute 'to_excel'

I also tried to shove the results in a list, and spit the list back out as a dataframe, but that totally failed as well. Would you be able to implement the to_excel function any time soon?

Thank you so much!!!!

[HHammond]

I hadn't built this with a to_excel function in mind but I think it could be useful.

If could you give me a few more details of your use case that would help me understand what the solution to this issue is.

[apkant0neze]

Hey Henry,

Thanks for the quick response!

Use case: I'm automating a Python script that pulls aggregated numbers from SQL server and I'd like to surface the percentages of a few columns for business report. I can certainly export the numbers using the to_excel function in Pandas and manually handle the percentages in Excel, but the very fact that I found PrettyPandas in the first place is to automate that manual nuance

Thanks again!

[HHammond]

Have you tried using the to_frame method yet? Something like the following should work:

PrettyPandas(df).total().to_frame().to_excel(...)

One issue that I'm not sure is solvable is that you will lose styling if you add percentages. I think a function that does the styling and converts all your data to strings would work for this use case but I probably won't be able to add that until this weekend at the earliest.

[apkant0neze]

Hey man!

Even adding it this weekend would be really nice! And yup, I'm aware of the formatting going away if I do convert it to with to_frame()

For now, I'll wait for the changes and complete the script after the weekend or whenever you get a chance to code in the to_excel!

Thank you so much for your response, I actually didn't expect any response when I made the comment. Happy to have contributors like you around!

[HHammond]

I did some digging and it looks like this is actually a pandas bug, but I'll try to make the API for pretty pandas easier to work with.

The Styler class has a to_excel function but doesn't actually keep applied formatting when creating the xlsx document:

https://pandas.pydata.org/pandas-docs/stable/style.html#Export-to-Excel

As an example this code will correctly colour the spreadsheet cells but won't format them as a percentage with two significant digits, even though it's happy to display the correct table in a Jupiter Notebook.

df = pd.DataFrame({'a': [1, 2], 'b': [2, 3]})
df.style.background_gradient().format("{:.2%}").to_excel('test.xlsx')

Since the styling in PrettyPandas primarily uses the Styler.format function this will need to be fixed in Pandas and then a fix added to PrettyPandas.

[HHammond]

Pandas issue tracking the Styler.to_excel behaviour https://github.com/pandas-dev/pandas/issues/21221

[apkant0neze]

Hey man, thanks for investigating the styling issue! You're the best!
I'll circle back here after a fix is deployed in Pandas then!!