How to generate Reports with Python (3 Formats/4 Tools)

 As Excel, HTML, PDF

In this tutorial, we’ll show you how to generate reports with Python.

Reporting is one of the essential tasks for anyone who works with data information. It is critical but also tedious. To free our hands and minds, we can make a program to automate the report generation process. Besides data analysis, Python is also convenient for automating routine tasks such as reporting.

Following this guide, you’ll use tools with Python to generate reports, as the below common formats:

• Excel
• HTML (with template)
• HTML to PDF
• PDF directly

If you are looking to generate reports automatically with Python, this tutorial is a great starting point. You’ll know where to start generating your next report!

We are going to show you popular and easy-to-use Python tools, with examples. The example report will include data tables and a chart, the two most common elements within reports.

Let’s get started!

To follow this tutorial, you’ll need to know:

• Python basics, which you can learn with our FREE Python crash course: breaking into Data Science.
• Python pandas basics, which you can learn with our course Python for Data Analysis with projects.
• HTML basics, which you can get a quick overview with HTML Introduction from W3 Schools.

Overview of Python reporting

Before we start, let’s look at an overview of reporting with Python.

The standard formats of reports are Excel, HTML, and PDF. The good news is, Python can generate reports in all these formats. So you can choose any of these formats, depending on the needs of the report’s users.

Below is a summary of what we’ll cover in this tutorial. We’ll need pandas for all the reports, since we need to manipulate and analyze data when building reports.

Create the example report

To show examples of the above, we’ll use stock market data. We’ll generate simple reports based on the historical data of the S&P 500 index from Yahoo! finance.

Below is the code to pull the data for the reports. In summary, we grab the historic data of the S&P 500 and make a summary statistics table based on it.

At the end, you should have two pandas DataFrames sp500_history and sp500_history_summary for reporting:

• sp500_history: the 10 years historical data of S&P 500
  Your result will look different than below, since the date changes according to the date when you run the code.

2514 rows × 6 columns

• sp500_history_summary: a simple summary statistics of the above data

Besides these two DataFrames, we’ll also create a line chart showing the series of Close and Close_200ma.

The code below builds the line chart using the matplotlib and seaborn libraries, and saves it as a PNG file on your local computer. This file chart.png will be used in the reports below.

Now we have everything needed (sp500_history, sp500_history_summary, chart.png) to generate reports.

Further learning: Please note we generated the above simple report quickly for demonstration only.
To learn about using Python for data analysis, please check out our course Python for Data Analysis with projects.
The course covers the pandas, seaborn libraries. You’ll learn how to manipulate data, create data visualizations, etc., which is essential to create reports in Python.

Let’s dive into each specific example!

Excel

We’ll start with the classic: Excel. Excel is a widely used, powerful data analysis and visualization tool. In many scenarios, you may want to store the report within Excel and share it with others. Others can easily open the spreadsheet, examine the report, and even use it for further analysis in Excel, Python, or other programs.

While there are different tools to save reports as Excel, we’ll use the foundation one: ExcelWriter and the to_excel method in pandas. Here is the summary of the steps. You can read it together with the code below.

1. Set up an ExcelWriter with engine ‘openpyxl’
   You can also use the engine ‘xlsxwriter’. The general procedure is the same, but the syntax will be different. We are using ‘openpyxl’ since it’s the default engine for xlsx files.
2. Export the data from Python to Excel
   We export DataFrames sp500_history and sp500_history_summary to two separate sheets/tabs.
3. Add a line chart in Excel showing the data of Close and Close_200ma
   Please check the comments in the code below for details.
   Since Excel is a data visualization tool, we are adding charts directly in Excel. But this is essentially the same chart as the chart.png file we’ve created earlier.

The final saved Excel file has two tabs that look like below:

There is much more styling you can accomplish with this method. Please check out openpyxl documentation.

HTML

HTML (Hyper Text Markup Language) is the standard markup language for creating web pages. We can embed an HTML format report easily on a web page, or an email. So it is also popular for different use cases.

We’ll cover two main methods of generating HTML reports in Python. One is the basic one, and the other is to generate one with templates using the library called Jinja2.

Let’s start with the basic one. We can define HTML code as a Python string, and write/save it as an HTML file.

Here are the general steps of the procedure. You can read it together with the code below.

1. Set up multiple variables to store the titles, text within the report
2. Combine them as a long f-string of code
   Within the f-string, we define the HTML document, including:
   • the head: contains meta information about the HTML page, including the title
   • the body: a container for all the visible contents, such as h1, p, img, table
     Within the img element, we include the chart.png file saved on your computer.
     To include the DataFrame as HTML tables, we use the to_html method. For simplicity, we only render the last 3 rows of the DataFrame sp500_history.
3. Write the html string as an HTML file

You can open the final HTML report in any modern browser. You can try to match each of the HTML elements to the final report below.

We’ll leave the report as it is. If you want to style this HTML report more, please learn about CSS (Cascading Style Sheets). With CSS, you can control almost everything, including the color of text, font, spacing between elements, background color, and so on.

HTML with template

Besides the basic approach to generate HTML reports, we can also use a templating engine like Jinja2. With Jinja2, we can put special placeholders in the template to allow writing code similar to Python syntax; then the template is passed data to render the final document.

So we can easily create an HTML template, then use it with Python-like syntax. This is especially useful when you are using complicated templates for reporting over and over again.

Below are the general steps to use Jinja2 to generate HTML reports:

1. Create an HTML template with variables
   I named it ‘report_template.html’, and saved it under a folder in the current working directory called ‘templates’.

Within such a file, I put in the code below.
We are creating the same HTML report as the previous example, but the syntax of Jinja‘s template is different from Python strings. You can read more about its syntax here.
For example, two curly brackets {{...}} are used as placeholders for expressions like variables. We can specify such variable’s value later within this template, which you’ll see soon.

2. Create a template Environment object, which will be used to load templates
   loader=FileSystemLoader('templates') tells Jinja to look for templates stored within a file/folder called ‘templates’. You can use other loaders to load templates in other ways or from other locations, please check the doc for details.
3. Load the template from the Environment
   We load the template file set up earlier called ‘report_template.html’.
4. Render the template with variables
   As you can see, the variables within {{...}} in ‘report_template.html’ are set as specific values. Jinja will embed them within the template.
5. Write the template to an HTML file
   You can open the HTML file in any modern web browser. It should look the same as the previous example of HTML report.

You might wonder, Jinja2 doesn’t seem to be very impressive in this example? It seems similar to the basic approach, except that we use some Jinja2 methods versus the f-string. But imagine if you have a much more complicated report, and you want to reuse it, then Jinja would make it much easier.

HTML to PDF

Now you have the report in HTML format, what if you also want PDF documents to share with a broader audience? We can use a package called WeasyPrint to convert HTML to PDF.

Using the below code, we convert the HTML file ‘html_report_jinja.html’ to a PDF file called ‘weasyprint_pdf_report.pdf’, with an inline CSS stylesheet. Within the stylesheet, we specified the page size, margin, and the table header and cell border.

The final result is a nice looking PDF below.

PDF directly

What if you’re not familiar with HTML, you just want the PDF format report? Python also has a solution for that. We’ll use this Python package called FPDF.

I would strongly suggest you read the short minimal example tutorial on the FPDF website before using the package.

The challenge to generate our report with FPDF is to show the tables of data. So before using FPDF, we define a function below to loop through the columns and rows of a DataFrame to display it on cells in the PDF.

Then, we can use FPDF to display the report. The general procedure is:

1. Set up basic setting for a PDF page with FPDF
2. Lay out each item we want to see in the report
   Please see the details in the comments below. It is helpful to match the code with the final PDF.
3. Output the PDF file

The final PDF report looks like below.

You might have heard about another popular tool called ReportLab. We picked FPDF since it seems to be easier to learn.

In summary, you’ve learned examples of using Python to generate reports as Excel, HTML (with template), and PDF.

Each of them is good for different scenarios:

• If you want an Excel file, then do Excel
• If you want to embed HTML to web pages, or are just good at HTML, use HTML. You can also convert HTML to PDF after
• If you only want PDF, you can go with PDF directly too

Each of these methods/packages has a lot more techniques. Hope you got the basics within this tutorial, and are ready to explore details on your own!

After generating reports using Python, it is also convenient to automatically send emails to share the reports. Please check out How to Send Emails using Python: Tutorial with examples.

Further learning: if you want an interactive web-based report/dashboard, we highly recommend plotly Dash.
You can either take our comprehensive introductory course: Python Interactive Dashboards with Plotly Dash, or read our article with an example: 6 Steps to Interactive Python Dashboards with Plotly Dash.

We’d love to hear from you. Leave a comment for any questions you may have or anything else.