CODEWITHSUNDEEP
pythonexcelpandasdataframexlsxwriter
Heng
Heng

Reputation: 63

How to keep the value with signs as number from dataframe after write into Excel?

I am currently using XlsxWriter to output the dataframe to Excel files. My dataframe is as follows.

Num     Des     price   percentage
321     pencil  $23     24%
452     pen     $12     29%
444     key     $32     33%
111     eraser  $49     14%

And the dollar and percent signs I used are by the codes:

df['price'] = df['price'].apply(lambda x: format(x, '.0%')) 
df['percentage'] = df['percentage'].apply(lambda x: format(x, "${:20,.0f}"))

But after I output the dataframe into Excel by XlsxWriter, the values with signs turn into strings.

Is there a way that I can keep the number type?

Upvotes: 2

Views: 307

Answers (1)

Dimitris Thomas
Dimitris Thomas

Reputation: 1393

Do not use pandas to format the file, use them for what they 're best at, pandas for the data manipulation and xlsxwriter for the format.

So your code should be something like this:

import pandas as pd

# Create your dataframe
df = pd.DataFrame({'Num': [321,452,444,111],
                    'Des': ['pencil','pen','key','eraser'],
                    'price': [23,12,32,49],
                    'percentage': [24,29,33,14]})

# Divide by 100 the column with the percentages
df['percentage'] = df['percentage'] / 100

# Pass the df into the xlsxwriter
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Define the formats
cell_format1 = workbook.add_format({'num_format': '$#,##0'})
cell_format2 = workbook.add_format({'num_format': '0%'})

# Set the columns width and format
worksheet.set_column('C:C', 12, cell_format1)
worksheet.set_column('D:D', 12, cell_format2)

# Write the file
writer.save()

Output:

enter image description here

For more info about xlsxwriter's format class have a look here, it really has everything you need.

Upvotes: 2

Related Questions