CODEWITHSUNDEEP
pythonexcelpandas
John
John

Reputation: 503

Specific Number format Python to Excel

I am using a custom function and I am trying to apply a specific formatting to the output file that is sent to Excel. I want positive/negative integers = 1234.34 to look like 1,234/(1,234). The function I have works, but the output is a string and i would like it to show up as an integer/float/number in excel:

def formatter(x):
    if x > 0:
        return '{0:,.0f}'.format(x)
    else:
        return '({0:,.0f})'.format(abs(x))

for col in DFstats2.columns[1:]:
    DFstats2[col] = DFstats2[col].apply(formatter)

Currently for Excel to treat these as numbers, I have to right select 'convert to number' in order to do any kind of operations on them.

Any guidance would be appreciated, thank you!

Upvotes: 1

Views: 8559

Answers (1)

T. Ray
T. Ray

Reputation: 641

If you want Excel to recognize your strings as formatted numbers, you will have to use pd.ExcelWriter formatting methods. Python string formatting can only produce, well, strings. Here's a trivial example:

import pandas as pd
from string import ascii_uppercase

# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Positive': [1024.34, 2024.34, 3024.34, 4024.34],
                   'Negative': [-1024.34, -2024.34, -3024.34, -4024.34],
                   'Mixed': [1024.34, -2024.34, 3024.34, -4024.34]})

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

# Connect dataframe to Excel worksheet
df.to_excel(writer, sheet_name='Formatted')

# Make handles for workbook/sheet
workbook  = writer.book
worksheet = writer.sheets['Formatted']

# Create positive/negative cell format
pos_neg_fmt = workbook.add_format({'num_format': '#,##0;(#,##0)'})

# Define the worksheet range to apply number format
cols = dict(zip(range(26), ascii_uppercase))
row = len(df)
format_range = '{}{}:{}{}'.format(cols[0], row, cols[len(df.columns)-1], row)

# Apply number formats to specified range
worksheet.set_column(format_range, None, pos_neg_fmt)

writer.save()

The ExcelWriter methods give you tremendous flexibility to format your data in an Excel-specific way. You can even add conditional formatting, if you like. And you never have to touch the workbook, per your requirements.

Upvotes: 3

Related Questions