0

How do I convert this JSON data (https://api.llama.fi/summary/fees/lyra?dataType=dailyFees) to a pandas dataframe? https://data.page/json/csv does this extremely well when uploading a file, how can I modify my script to accomplish this?

# Initialize an empty list to store the exploded and normalized dataframes
df_list = []
# Iterate over the columns of the dataframe
for col in df.columns:
  # Check if the column contains lists
  if df[col].apply(type).eq(list).any():
    # Explode the column
    df1 = df.explode(col, ignore_index=True)
    # Normalize the dataframe
    df2 = pd.json_normalize(json.loads(df1.to_json(orient="records")))
    df_list.append(df2)   
    result = pd.concat(df_list) 
  else:
    result = df_list.append(df) 
    result = pd.concat(df_list)
# Check if the dataframe contains any columns with dicts
if result.applymap(type).eq(list).any().any():
# Get the labels of the columns with dicts
  list_columns = result.applymap(type).eq(list).any().index[result.applymap(type).eq(list).any()].tolist()
  result = result.drop(list_columns, axis=1)   
else:
   result
result
Anthony Loya
  • 1
  • 2
  • 1
    are you expecting 1 row and 23 columns? If so, does this answer your question? [Pandas read nested json](https://stackoverflow.com/questions/40588852/pandas-read-nested-json) – JonSG Jan 04 '23 at 21:03
  • Take a look at [`pandas.json_normalize()`](https://pandas.pydata.org/docs/reference/api/pandas.json_normalize.html#pandas-json-normalize). If it doesn't produce the output you want, you'll need to write your own formatter. – Michael Ruth Jan 04 '23 at 21:05
  • Please don't use links to code, data, etc, use the editor to include sample data inline in your question. Then, also include the results you want from the sample data. – MatBailie Jan 04 '23 at 23:18
  • Why do none of your previous questions have accepted answers? – MatBailie Jan 04 '23 at 23:22

1 Answers1

1

Using json.normalize():

import json

import pandas as pd
import requests


url = "https://api.llama.fi/summary/fees/lyra?dataType=dailyFees"

with requests.Session() as request:
    response = request.get(url)
if response.status_code != 200:
    print(response.raise_for_status())

data = json.loads(response.text)

df = (pd
      .json_normalize(data=data)
      .explode("totalDataChart")
      .explode("totalDataChartBreakdown")
      .explode("totalDataChartBreakdown")
      .explode("audit_links")
      .explode("totalDataChart")
      .explode("chains")
      ).reset_index(drop=True)
df = df.join(pd.json_normalize(df["totalDataChartBreakdown"])).drop("totalDataChartBreakdown", axis=1)
df.columns = df.columns.str.split(".").str[-1]
print(df)
Jason Baker
  • 3,170
  • 2
  • 12
  • 15