Convert text to float in redshift with same output

My view returns text field output since I concatenate my field with '%' symbol. When I push this data into excel using to_excel, it inserts as text and not numbers. Is there a way to fix this problem so that my excel sheets has numbers instead of text?

redshift code:

select concat(((("replace"((myfield)::text, '%'::text, ''::text))::numeric(10,2))::character varying)::text, '%'::text) AS myfield_formatted
from myview

Python code to push data into excel:

 df3 = df3.apply(pd.to_numeric, errors='ignore')
 df3.to_excel(writer, sheet_name=comp,startrow=0, startcol= 0, index=False, float_format='%.2f')

Should i fix thsi problem at redshift level or pandas dataframes level?

> Is there a way to fix this problem so that my excel sheets has numbers instead of text?

The way to do that is to convert the text to numbers in pandas prior to exporting the dataframe to Excel. It looks like you are trying to do that but I think the % that is added to the strings is preventing a numeric conversion.

Here is an example of converting some string data to percentages using the following steps:

1. Convert the string to a number
2. Divide by 100 to get a percentage
3. Format the number with an Excel percentage format

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('%', '') # Add this if required.
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.close()

Output:

Could you try to set the column format at the XlsxWriter level?