Thank you, Singapore. See the photos from our Agentic Patterns workshop at GovTech STACK 2026.

Financial Data Analysis · Financial Data Analysis, part 2

2. Transforming deeply nested JSON from APIs into Pandas DataFrame

Convert JSON to a pandas DataFrame in Python. We flatten deeply nested API responses with json_normalize and explode, using real IDX bank valuation data.

SC
Samuel Chan
Head of Supertype Incubator3 min read

In one of the past workshops we did for Bank Rakyat Indonesia (BBRI), we provided a clean dataset that participants use to construct their analysis on. This article expands on how we transformed the raw JSON data from the API into a Pandas DataFrame.

Quick answer

The pandas call you need depends on the shape of your JSON:

import io
import pandas as pd

df = pd.DataFrame(records)            # a list of flat dicts
df = pd.json_normalize(data)          # nested dicts
df = pd.read_json(io.StringIO(text))  # a JSON string

Since pandas 3, read_json treats a plain string as a file path and raises, so wrap JSON text in io.StringIO. The rest of this article works through a nested API response, where json_normalize and explode do most of the work.

For context, here is what the data looks like in tabular form:

symbol,company_name,year,pe,pb,ps
BBRI.JK,PT Bank Rakyat Indonesia (Persero) Tbk,2020,27.296475059198,2.24404134373024,3.90812024342799
BBRI.JK,PT Bank Rakyat Indonesia (Persero) Tbk,2021,19.8501979232225,2.13579246128274,4.02599153256836
BBRI.JK,PT Bank Rakyat Indonesia (Persero) Tbk,2022,14.4852351105461,2.47654136988394,4.40613876244184
BBRI.JK,PT Bank Rakyat Indonesia (Persero) Tbk,2023,14.3700683214413,2.77373225214342,5.18776985111159
BBRI.JK,PT Bank Rakyat Indonesia (Persero) Tbk,2024,11.8598506535486,2.42373070867358,3.96923623135049
BMRI.JK,PT Bank Mandiri (Persero) Tbk,2020,17.3942521554938,1.54302128039226,3.05224667300517
BMRI.JK,PT Bank Mandiri (Persero) Tbk,2021,11.5796062923157,1.58561909266445,2.91227616355148
BMRI.JK,PT Bank Mandiri (Persero) Tbk,2022,11.1374278712277,1.99641859827664,3.62397928851202
BMRI.JK,PT Bank Mandiri (Persero) Tbk,2023,11.6603200287564,2.46122688658584,5.20968178447345
...

By comparing the historical P/E ratios of different banks to their peers and their own historical averages, an analyst can get a sense of whether a bank is trading at a premium or discount to its intrinsic value, and whether it is a good value investment relative to its historical norm.

The data, of course, is sourced from Sectors Financial Data API, which is a product incubated by Supertype and powering thousands of financial workflows for the leading financial institutions in Indonesia. In the following sections, we will walk through the process of transforming the raw JSON data from the API into a Pandas DataFrame.

requests on a Financial API endpoint

We build fetch_data from the get_info function in the previous chapter. It takes a URL, which you construct from the corresponding API documentation. For example, to fetch the historical P/E ratios of BBRI:

import os
import requests
from dotenv import load_dotenv
import pandas as pd

load_dotenv()

SECTORS_API_KEY = os.getenv("SECTORS_API_KEY")

headers = {"Authorization": SECTORS_API_KEY}

banks = ["BBRI", "BMRI", "BBCA", "BBNI", "BRIS"]
base_url = "https://api.sectors.app/v2/company/report/"


def fetch_data(url):
    """
    Fetches the data from the specified URL and returns it as a JSON object.
    We created this from the get_info function in the previous chapter (API programming)
    """
    try:
        response = requests.get(url, headers=headers)
        response.raise_for_status()  # Raise an exception for HTTP errors
        return response.json()
    except Exception as e:
        print(f"An error occurred: {e}")
        return {"error": "An error occurred while fetching the data."}

def get_bbri_as_dataframe():
    """
    Fetches the historical P/E ratios of BBRI and returns it as a DataFrame
    """
    url = f"{base_url}BBRI/?sections=valuation"
    # formed url is: https://api.sectors.app/v2/company/report/BBRI/?sections=valuation
    response = fetch_data(url)

    return pd.json_normalize(response['valuation']).T


bbri = get_bbri_as_dataframe()

pd.json_normalize and explode method

The one line that you should pay attention to from the section above is pd.json_normalize(response['valuation']).T.

This line of code is responsible for transforming the deeply nested JSON data into a Pandas DataFrame, and then transposing it.

If we were to print bbri, we would get the following output:

print(bbri)

Output:                                                                0
last_close_price                                                    4530
latest_close_date                                             2024-08-05
daily_close_change                                             -0.038217
forward_pe                                                     10.036112
price_cash_flow                                                 5.830954
enterprise_to_revenue                                               None
enterprise_to_ebitda                                                 0.0
peg_ratio                                                       0.353977
intrinsic_value                                              5389.036722
historical_valuation   [{'pb': 2.24404134373024, 'pe': 27.29647505919...}]

An interesting observation here is that with the json_normalize function, each key in the JSON object is flattened into a column in the DataFrame, but the historical_valuation key is still a list of dictionaries.

To give each dictionary in that list its own row, we can use the explode method in Pandas.

Let's see how .explode() works in action:

bbri.loc['historical_valuation'].explode().head()

# Output:
# 0    {'pb': 2.24404134373024, 'pe': 27.296475059198...}
# 0    {'pb': 2.13579246128274, 'pe': 19.850197923222...}
# 0    {'pb': 2.47654136988394, 'pe': 14.485235110546...}
# 0    {'pb': 2.77373225214342, 'pe': 14.370068321441...}
# 0    {'pb': 2.33110392317047, 'pe': 11.406607296663...}
# Name: historical_valuation, dtype: object

explode puts each list item on its own row, then json_normalize turns its keys into columns. Each row keeps the original index, so all of them are labelled 0, and we reset it to get 0 to 4. Together, these two calls turn even deeply nested JSON data into a Pandas DataFrame:

pd.json_normalize(bbri.loc['historical_valuation'].explode()).reset_index(drop=True)

# Output:
#          pb         pe        ps  year  pb_peer_avg  pe_peer_avg  ps_peer_avg
# 0  2.244041  27.296475  3.908120  2020     1.262425    27.296475     6.378919
# 1  2.135792  19.850198  4.025992  2021     1.466306    15.014559     6.374056
# 2  2.476541  14.485235  4.406139  2022     1.008524    15.129255     4.343578
# 3  2.773732  14.370068  5.187770  2023     0.832330    14.072334     3.899448
# 4  2.331104  11.406607  3.817545  2024     0.781849    13.367830     3.457184

Putting it all together

As a quick recap, whenever an API service (like the one from Sectors Financial API) returns deeply nested JSON data, you can call on these two tools in Pandas to transform it into a flattened, tabular DataFrame:

  • json_normalize to flatten the JSON data into a DataFrame
  • explode to put each item of a list on its own row, so json_normalize can turn its keys into columns

To arrive at the full code used for the workshop at BBRI, one would just have to perform the above iteratively for each bank in a defined banks list before concatenating them into a single DataFrame. Inside the loop, json_normalize can do both steps in one call: record_path points at the nested list, and meta copies symbol and company_name onto every row.

import os

banks = ["BBRI", "BMRI", "BBCA", "BBNI", "BRIS"]


def create_df(banks, save_to_csv=False, file_name="indonesia_banks.csv"):
    """
    Creates a DataFrame by fetching data from the API for each bank in the list.

    Parameters:
    - banks: List of bank identifiers.
    - save_to_csv: Boolean flag to indicate if the DataFrame should be saved to a CSV file.
    - file_name: Path of the CSV file to save the DataFrame (default is "indonesia_banks.csv").

    Returns:
    - DataFrame with valuation data from banks of Indonesia
    """
    df_list = []

    for bank in banks:
        url = f"{base_url}{bank}/?sections=valuation"
        response = fetch_data(url)

        if "error" not in response:
            historical_df = pd.json_normalize(
                response,
                record_path=["valuation", "historical_valuation"],
                meta=["symbol", "company_name"],
            )
            df_list.append(
                historical_df[["symbol", "company_name", "year", "pe", "pb", "ps"]]
            )
        else:
            print(f"Error fetching data for {bank}")

    if not df_list:
        joined = pd.DataFrame()
    else:
        joined = pd.concat(df_list, ignore_index=True)

    if save_to_csv:
        os.makedirs(os.path.dirname(file_name) or ".", exist_ok=True)
        joined.to_csv(file_name, index=False)
        print(f"Data saved to {file_name}")
    return joined


df = create_df(banks, save_to_csv=True, file_name="datasets/indonesia_banks_050824.csv")
Taggedapipythondata analysispandas
Share
Sectors Snapshot: Indonesia Banks (May '25)
Sectors research

Like this piece? Take home Sectors Snapshot: Indonesia Banks (May '25).

We packed the full picture into this report by Evelyn Ong, with the numbers, charts, and context laid out end to end.

Building agents that build your business
Learn it live with our engineers

Building agents that build your business

A two-evening workshop on designing and shipping AI agents that do real work for your business: from the agentic patterns that keep them reliable, to a working multi-agent system that researches, drafts and reports on your behalf.

December 9, 2026Zoom Conferencing (Online)

Save your seatSeats from Rp490.000 (about $27)

Keep reading

1. API Programming with Python
Financial Data Analysis
Financial Data Analysis · Part 1

1. API Programming with Python

Everything to get you started with creating programs using Financial APIs in Python.

SC
Samuel Chan
12 min read

Data Science in Industry

Data Science in Industry

Data Science for Digital Advertising

How I use data analytics to deliver value to my advertising clients; A case study on creative decay analysis, retention rate analysis, and exploratory data analysis (python code provided)

GB
Gerald Bryan
8 min read

Field notes, straight to your inbox

We publish what we learn building analytics and AI systems for some of the largest enterprises in the region. Subscribe and we will send the next one your way.

The latest in AI and Enterprise Analytics

We do not resell your data. We respect your privacy and trust.