Question 109554 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How do I access specific data in a nested JSON file with Python and Pandas

I am still a newbie with Python and working on my first REST API. I have a JSON file that has a few levels. When I create the data frame with pandas, no matter what I try I cannot access the level I need.

The API is built with Flask and has the correct parameters for the book, chapter and verse.

Below is a small example of the JSON data.

{
  "book": "Python",
  "chapters": [
    {
      "chapter": "1",
      "verses": [
        {
          "verse": "1",
          "text": "Testing"
        },
        {
          "verse": "2",
          "text": "Testing 2"
        }
      ]
    }
  ]
}

Here is my code:

@app.route("/api/v1/<book>/<chapter>/<verse>/")
def api(book, chapter, verse):
    book = book.replace(" ", "").title()

    df = pd.read_json(f"Python/{book}.json")

    filt = (df['chapters']['chapter'] == chapter) & (df['chapters']['verses']['verse'] == verse)
    text = df.loc[filt].to_json()

    result_dictionary = {'Book': book, 'Chapter': chapter, "Verse": verse, "Text": text}
    return result_dictionary

Here is the error I am getting:

KeyError
KeyError: 'chapter'

I have tried normalizing the data, using df.loc to filter and just trying to access the data directly.

Expecting that the API endpoint will allow the user to supply the book, chapter and verse as arguments and then it returns the text for the given position based on those parameters supplied.

Tags:

3

Answers


  1. 0

    You are trying to access a list in a dict with a dict key ?

    filt = (df[‘chapters’][0][‘chapter’] == "chapter") & (df[‘chapters’][0][‘verses’][0][‘verse’] == "verse")

    Will get a result.
    But df.loc[filt] requires a list with (boolean) filters and above only gerenerates one false or true, so you can’t filter with that.

    You can filter like:

    df.from_dict(df['chapters'][0]['verses']).query("verse =='1'")
    Login or Signup to reply.
  2. 0

    You can first create a dataframe of the JSON and then query it.

    import json
import pandas as pd

def api(book, chapter, verse):
    # Read the JSON file
    with open(f"Python/{book}.json", "r") as f:
        data = json.load(f)

    # Convert it into a DataFrame
    df = pd.json_normalize(data, record_path=["chapters", "verses"], meta=["book", ["chapters", "chapter"]])
    df.columns = ["Verse", "Text", "Book", "Chapter"]  # rename columns

    # Query the required content
    query = f"Book == '{book}' and Chapter == '{chapter}' and Verse == '{verse}'"
    result = df.query(query).to_dict(orient="records")[0]

    return result

    Here df would look like this after json_normalize:

      Verse       Text    Book Chapter
0     1    Testing  Python       1
1     2  Testing 2  Python       1
2     1    Testing  Python       2
3     2  Testing 2  Python       2

    And result is:

    {'Verse': '2', 'Text': 'Testing 2', 'Book': 'Python', 'Chapter': '1'}
    Login or Signup to reply.
  3. 0

    One of the issues here is that "chapters" is a list

    "chapters": [

    This is why ["chapters"]["chapter"] wont work as you intend.

    If you’re new to this, it may be helpful to "normalize" the data yourself:

    import json

with open("book.json") as f:
    book = json.load(f)

for chapter in book["chapters"]:
    for verse in chapter["verses"]:
        row = book["book"], chapter["chapter"], verse["verse"], verse["text"]
        print(repr(row))

    ('Python', '1', '1', 'Testing')
('Python', '1', '2', 'Testing 2')

    It is possible to pass this to pd.DataFrame()

    df = pd.DataFrame(
    ([book["book"], chapter["chapter"], verse["verse"], verse["text"]]
        for verse in chapter["verses"] 
        for chapter in book["chapters"]),
    columns=["Book", "Chapter", "Verse", "Text"]
)

         Book Chapter Verse       Text
0  Python       1     1    Testing
1  Python       1     2  Testing 2

    Although it’s not clear if you need a dataframe here at all.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search