16

I have extracted xlsx data into pandas dataframe and used style.format to format particular columns into percentages and dollars. So now my dataframe is converted to styler object, because I need to parse this data into csv. I have to convert this object into dataframe please help.

below is the code and output:

import pandas as pd
import numpy as np

file_path = "./sample_data.xlsx"

df = pd.read_excel(file_path, sheet_name = "Channel",skiprows=10, header = 
[0,1,2])

dollar_cols = ['SalesTY', 'SalesLY','InStoreTY', 'InStoreLY','eCommTY']

dollar_dict = {}
for dollar_col in dollar_cols: 
    formatdict[dollar_col] = "${:,.0f}"
    final_df = df.style.format(formatdict)

Here final_df has the columns converted to dollars but I am unable to convert this to csv or into a data frame. It's a styler object now, I need to convert this into a data frame again. Any help is appreciated. Thanks.

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
Roohi
  • 181
  • 1
  • 1
  • 7

1 Answers1

16

You can retrieve the original dataframe from the styler object using the "data" attribute.

In your example:

df = final_df.data

type(df) yields

pandas.core.frame.DataFrame

tomanizer
  • 851
  • 6
  • 16
  • 5
    This method works but you lose the formatting in the process. – Gabriel Mar 26 '20 at 13:55
  • 2
    Yes. The question was to get the original data frame back. The formatting is of course in the styling object the questioner already has. – tomanizer Mar 28 '20 at 10:00
  • 3
    And how do I get a new dataframe with the correct new format? – Thadeu Melo Mar 31 '21 at 06:23
  • you want a dataframe with formattng which is not a styler or using styles?if you actually want to see $ symbols in the exported csv you will need to replace the numbers with strings, e.g. replace 1000 with $1,000. are you sure that's what you want? why would you want that? – tomanizer Apr 14 '21 at 10:06
  • 1
    I would be fine with "losing the formatting" if the formatting was still stored in the new data frame's `style` attribute. `style.data.style` should be an identity. Alas, it is not. – Attila the Fun Apr 12 '23 at 16:56