Nested .json to pandas dataframe

Question

I have a json file that I want to convert to a Pandas Dataframe. I have tried the previous solutions but could not get it to work.

Here is a sample from the file:

    {"dateTime": "2022-08-18",
    "minutes": [
      {
        "value": 94.5,
        "minute": "2022-08-18T05:01:00"
      },
      {
        "value": 94.4,
        "minute": "2022-08-18T05:02:00"
      },

I dont' need the "dateTime" column, and I want to get to:

value   minute
94.5   2022-08-18 05:01:00
94.4   2022-08-18 05:02:00

I am unable to extract the "value" and "minute" to seperate columns. Any help would be appreciated.

it gives: "TypeError: list indices must be integers or slices, not str" — prof31
– prof31, Commented Aug 30, 2022 at 3:15
I did read the other posts with this error code but wasnt able to get past it — prof31
– prof31, Commented Aug 30, 2022 at 3:17
You may want to write a minimum reproducible example. Without knowing your data fully it is not possible to comment. — SomeDude
– SomeDude, Commented Aug 30, 2022 at 3:20
You should share a better sample, as @SomeDude suggests, that code makes the magic. — Luis Alejandro Vargas Ramos
– Luis Alejandro Vargas Ramos, Commented Aug 30, 2022 at 3:21

JubJub · Accepted Answer · 2022-08-30 04:35:28Z

1

I might be off here, as others have stated you haven't given code but can you try:

import json
f = open("your PATH")
js = json.load(f)
df = pd.json_normalize(js, 'minutes', ['dateTime'])

This will signal to pandas that "minutes" has nested values and it will dig into them.

edited Aug 30, 2022 at 4:35

answered Aug 30, 2022 at 4:34

JubJub

455 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested .json to pandas dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related