Export JSON to HTML Table using Pandas in Python

In this tutorial, you’ll learn how to transform JSON data into an HTML table using Python Pandas.

By the end, you will understand how to handle standard and nested JSON structures, export large JSON files through chunking, and convert your data into HTML tables.

 

 

Reading JSON Data with Pandas

Pandas read_json(), function allows you to read your JSON data into a Pandas DataFrame.

In this example, we’ll use sample data in JSON. The data includes fields such as customer ID, plan type, and usage details.

Here’s the code to read the JSON data:

import pandas as pd
json_data = """
[
    {"customer_id": "12345", "plan": "Basic", "data_usage": 2.5},
    {"customer_id": "67890", "plan": "Premium", "data_usage": 5.0},
    {"customer_id": "13579", "plan": "Standard", "data_usage": 3.5}
]
"""
dataframe = pd.read_json(json_data)
print(dataframe)

After running this code, the output will be:

  customer_id      plan  data_usage
0       12345     Basic         2.5
1       67890   Premium         5.0
2       13579  Standard         3.5

 

Converting JSON to HTML

After successfully loading the JSON data into a Pandas DataFrame, the next step is to convert this data into an HTML table.

The to_html() function converts the DataFrame into HTML format complete with standard table tags.

Let’s use the DataFrame you created in the previous step and convert it to an HTML table.

# Convert the DataFrame to HTML
html_data = dataframe.to_html()
print(html_data)

Executing this code will produce the following output:

<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>customer_id</th>
      <th>plan</th>
      <th>data_usage</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>12345</td>
      <td>Basic</td>
      <td>2.5</td>
    </tr>
    <tr>
      <th>1</th>
      <td>67890</td>
      <td>Premium</td>
      <td>5.0</td>
    </tr>
    <tr>
      <th>2</th>
      <td>13579</td>
      <td>Standard</td>
      <td>3.5</td>
    </tr>
  </tbody>
</table>

 

Handling Nested JSON Structures

The json_normalize function allows you to flatten nested JSON structures.

Let’s consider a slightly more complex JSON structure with nested elements:

import json
from pandas import json_normalize
nested_json_data = """
{
  "telecom_data": [
    {"customer_id": "12345", "plan": "Basic", "usage_details": {"data": 2.5, "calls": 150}},
    {"customer_id": "67890", "plan": "Premium", "usage_details": {"data": 5.0, "calls": 300}},
    {"customer_id": "13579", "plan": "Standard", "usage_details": {"data": 3.5, "calls": 200}}
  ]
}
"""
parsed_json = json.loads(nested_json_data)

# Flatten the nested JSON data
flattened_dataframe = json_normalize(parsed_json, record_path=['telecom_data'])
print(flattened_dataframe)

After running this script, you will see the following output:

  customer_id      plan  usage_details.data  usage_details.calls
0       12345     Basic                 2.5                  150
1       67890   Premium                 5.0                  300
2       13579  Standard                 3.5                  200

This output demonstrates how the nested JSON data is successfully flattened into a DataFrame.

It flattens the nested structure, making each nested key a separate column in the DataFrame.

 

Export Large JSON File

Here, we’ll demonstrate how to read a large JSON file in chunks and then convert each chunk into an HTML table:

import pandas as pd
chunk_size = 1000
html_output = ""
for chunk in pd.read_json('path_to_large_json_file.json', lines=True, chunksize=chunk_size):
    # Convert each chunk to HTML and append to the output string
    html_output += chunk.to_html() + "<br>"  # Adding a line break for separation
with open('output.html', 'w') as file:
    file.write(html_output)
print(html_output[:1000])

The code above reads a large JSON file in chunks. Each chunk is then converted into an HTML table and appended tohtml_output string.

This method is efficient as it processes the file in segments, thereby reducing memory usage significantly.