Exporting a Data Frame to custom formatted Excel

Introduction

You might already be familiar with Pandas. Using Pandas, it is quite easy to export a data frame to an excel file. However, this exported file is very simple in terms of look and feel.

In this article we will try to make some changes in the formatting and try to make it more interesting visually.

For this job we will mostly use Pandas. And the data-set i’m going to use for this is some random credit card data. You can find the same here. I’m going to add some new columns to the following data-set for better understanding.

Let’s get started by importing pandas and numpy.

import pandas as pd
import numpy as np

Reading the file:

“loc ”variable contains name of the folder where data-set file is present. You can directly put data-set file name along with the location in a single variable. I personally like concatenation. As you can see below, currently the file has 100 rows and 11 columns

loc = “Y:\\test\\”
df = pd.read_excel(loc+”test_records.xlsx”)
df.shape
#(100, 11)

Let’s add three more columns- “Balance”, “Credit Used” and “Credit Used Rate". For generating Balance we will use random.randint from numpy. For rest of the two columns, we can use simple math formulas.

df["Balance"]= np.random.randint(500,15000,df.shape[0])
df["Credit Used"]= df["Credit Limit"] - df["Balance"]
df["Credit Used Rate"] = (df["Credit Used"]/ df["Credit Limit"])
df.head()

If you notice, Credit Used Rate is in numbers but it should be in percentage format. We will change it afterwards. Now let’s save the above data frame to an excel file without changing any format. ExcelWriter is a class for writing Data-Frame into excel sheets. You can find more information about it here.

writer_1 = pd.ExcelWriter(loc+”test_Records_modified.xlsx”,engine = ‘xlsxwriter’)
df.to_excel(writer_1 , index=False, sheet_name= “Report”)
writer_1.save()

test_Records_modified.xlsx

Let’s create another writer to save the formatted output file. Here we are using to_excel function for creating workbook

writer_2= pd.ExcelWriter(loc+”test_Records_modified_2.xlsx”, engine= “xlsxwriter”)
df.to_excel(writer_2, index=False, sheet_name= “Report”)
workbook_2 = writer_2.book
worksheet_2 = writer_2.sheets[“Report”]

Now, let’s start the formatting. We will be making all the changes to the worksheet we created above.

Firstly, we will define the format variables, so that we can use them in the code where ever required. And, for this task we will use add_format.

# Adding simple number format. 
fmt_number = workbook_2.add_format({
 “num_format” : “0”
})
# Adding currency format
fmt_currency = workbook_2.add_format({
 “num_format” : “$#,##0.00” ,”bold” :False
 })
# Adding percentage format.
fmt_rate = workbook_2.add_format({
 “num_format” : “%0.0” , “bold” : False
})
# Adding formats for header row.
fmt_header = workbook_2.add_format({
 ‘bold’: True,
 ‘text_wrap’: True,
 ‘valign’: ‘top’,
 ‘fg_color’: ‘#5DADE2’,
 ‘font_color’: ‘#FFFFFF’,
 ‘border’: 1})
#Setting the zoom
worksheet_2.set_zoom(80)

Now, we will use above defined format variables in the worksheet using set_column.

worksheet_2.set_column("A:N", 20)
worksheet_2.set_column("K:M", 10, fmt_currency)
worksheet_2.set_column("D:D",20,fmt_number)
worksheet_2.set_column("N:N",10,fmt_rate)

Time to change header format. Zero in the below code implies 0th row which is basically the topmost row(Header Row).

for col , value in enumerate(df.columns.values):
 worksheet_2.write(0, col, value, fmt_header)

lets show sum of certain numeric columns like “Balance”.

for col in (“Balance”, “Credit Limit”, “Credit Used”):
 position_tot = pd.Index(list(df)).get_loc(col)
 worksheet_2.write(df.shape[0]+1,position_tot,df[col].sum(axis=0))

Saving the writer

writer_2.save()

The final output file would look like below file. You can see that format of some columns is changed. Header formatting is also differenct look different too

formatted

Now, You can change the formats on your own.