Python JSON file to sorted pandas dataframe

Question

I have a JSON file in the following format:

[
  {
    "positionmessage": {
      "name": "ship-1",
      "sog": 0,
      "latitude": 51.82284,
      "eni": "02010000",
      "mmsi": "100000"
    }
  },
  {
    "positionmessage": {
      "name": "ship-2",
      "sog": 0,
      "latitude": 51.81966,
      "eni": "02020000",
      "mmsi": "200000"
    }
  },
  {
    "positionmessage": {
      "name": "ship-2",
      "sog": 0,
      "latitude": 51.8196,
      "eni": "02020000",
      "mmsi": "200000"
    }
  },
  {
    "positionmessage": {
      "name": "ship-3",
      "sog": 0,
      "latitude": 51.8225,
      "eni": "02030000",
      "mmsi": "300000"
    }
  },
  {
    "positionmessage": {
      "name": "ship-4",
      "sog": 0,
      "latitude": 51.8291,
      "eni": "02040000",
      "mmsi": "400000"
    }
  }
]

I would like to get a pandas dataframe with the variables on the columns:

column 1: name
column 2: SOG
column 3: latitude, etc.

On the rows should be the content of all columns. How do I do this?

I validated and shortened the JSON for you, see duckduckgo's prettifier — B--rian
– B--rian, Commented Feb 19, 2020 at 11:03

jezrael · Accepted Answer · 2020-02-19 09:35:27Z

1

Use read_json with lines parameter, convert to list and then to DataFrame:

L = pd.read_json('file.json', lines=True)['positionmessage'].tolist()
df = pd.DataFrame(L)
print (df)
       name   sog  latitude       eni    mmsi
0    ship-1   0.0  51.82284  02010000  100000
1    ship-2   0.0  51.81966  02020000  200000
2    ship-2   0.0  51.81960  02020000  200000
3    ship-3   0.0  51.82250  02030000  300000
4    ship-4   0.0  51.82910  02040000  400000
5    ship-5   8.0  51.82384  02050000  500000
6    ship-6   0.0  51.82470  02060000  600000
7    ship-7   0.0  51.81920  02070000  700000
8    ship-6   0.0  51.82470  02060000  600000
9    ship-8   9.0  51.82870  02080000  800000
10   ship-9   0.0  51.82710  02090000  900000
11  ship-10   9.0  51.82620  02010000  100000
12  ship-11   0.0  51.81930  02011000  110000
13  ship-12  10.0  51.82390  02012000  120000

answered Feb 19, 2020 at 9:35

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Jeroen Over a year ago

perfect, exactly what I want

Collectives™ on Stack Overflow

Python JSON file to sorted pandas dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related