How to convert dataframe column which has % sign into number format while exporting data into excel-CodePudding

100.00% it gives string format.But i want to copy in excel using to_excel() using xlsxwriter into number format with % sign

CodePudding user response：

df['col']=df['col'].str.replace('%','').astype(float)
df['col']=df['col'].str.rstrip('%').astype(float)

CodePudding user response：

To create a percentage in Excel the data must be a number, must be divided by 100 and must have a percentage number format applied.

Here is a simple example of converting some string percentage data in a Pandas dataframe to percentage numbers in an xlsx file using XlsxWriter as the Pandas excel engine:

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Names': ['Anna', 'Arek', 'Arun'],
                   'Grade': ['100%', '70%',  '85%']})

# Convert the percentage strings to percentage numbers.
df['Grade'] = df['Grade'].str.replace('%', '')
df['Grade'] = df['Grade'].astype(float)
df['Grade'] = df['Grade'].div(100)

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_percent.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a percent number format.
percent_format = workbook.add_format({'num_format': '0%'})

# Apply the number format to Grade column.
worksheet.set_column(2, 2, None, percent_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output: