Pandas: access values inside .json file

Question

I am trying to create a loop that let's me access value's inside a .json file (google location file). I just started learning Python last week, so barre with me and all the help is really appreciated!

First my import list:

import pandas as pd
import requests
import json

Than I open up the .json file:

with open('data_may.json', 'r') as fh:
    raw = json.loads(fh.read())
    data = raw['timelineObjects']
data

Last I am trying to create a loop to print out the value's of latitudeE7:

for locations in data[0:5]:
    print(locations['placeVisit']['location']['latitudeE7'])

This is the error message that I get:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-99-a8c46eea2476> in <module>
      1 for locations in data[0:5]:
----> 2     print(locations['placeVisit']['location']['latitudeE7'])

KeyError: 'placeVisit'

My .json file is constructed like this:

{
  "timelineObjects" : [ {
    "activitySegment" : {
      "startLocation" : {
        "latitudeE7" : 520950336,
        "longitudeE7" : 51250455,
        "sourceInfo" : {
          "deviceTag" : -1547871288
        }
      },
      "endLocation" : {
        "latitudeE7" : 520969498,
        "longitudeE7" : 51162451,
        "sourceInfo" : {
          "deviceTag" : -1547871288
        }
      },
      "duration" : {
        "startTimestampMs" : "1556693508999",
        "endTimestampMs" : "1556693710203"
      },
      "distance" : 652,
      "activityType" : "CYCLING",
      "confidence" : "HIGH",
      "activities" : [ {
        "activityType" : "CYCLING",
        "probability" : 99.11152749237161
      }, {
        "activityType" : "WALKING",
        "probability" : 0.6369730834565008
      }, {
        "activityType" : "RUNNING",
        "probability" : 0.10388160016829592
      } ],
      "waypointPath" : {
        "waypoints" : [ {
          "latE7" : 520950508,
          "lngE7" : 51250495
        }, {
          "latE7" : 520971412,
          "lngE7" : 51164069
        } ]
      }
    }
  }, {
    "placeVisit" : {
      "location" : {
        "latitudeE7" : 520967891,
        "longitudeE7" : 51159244,
        "placeId" : "ChIJOYpLXEFvxkcRtSdOEoh1Lns",
        "address" : "Bemuurde Weerd Oostzijde 1\n3514 AN Utrecht\nNederland",
        "name" : "LSR Landelijk Studenten Rechtsbureau",
        "sourceInfo" : {
          "deviceTag" : -1547871288
        },
        "locationConfidence" : 37.736637
      },
      "duration" : {
        "startTimestampMs" : "1556693710203",
        "endTimestampMs" : "1556713681618"
      },
      "placeConfidence" : "MEDIUM_CONFIDENCE",
      "centerLatE7" : 520969163,
      "centerLngE7" : 51162406,
      "visitConfidence" : 93,
      "otherCandidateLocations" : [ {
        "latitudeE7" : 520967890,
        "longitudeE7" : 51159240,
        "placeId" : "ChIJOYpLXEFvxkcRV2Hp03ASVuI",
        "locationConfidence" : 33.198143
      }, {
        "latitudeE7" : 520968971,
        "longitudeE7" : 51161709,
        "placeId" : "ChIJbeiKCEFvxkcRgiZQno9hikQ",
        "semanticType" : "TYPE_WORK",
        "locationConfidence" : 23.059763
      }, {
        "latitudeE7" : 520968160,
        "longitudeE7" : 51158805,
        "placeId" : "ChIJ-SXmBkVvxkcRtpNT_vTdQEE",
        "locationConfidence" : 1.7593758
      }, {
        "latitudeE7" : 520971045,
        "longitudeE7" : 51162728,
        "placeId" : "ChIJd66UREFvxkcRZJCaM9z3baw",
        "semanticType" : "TYPE_SEARCHED_ADDRESS",
        "locationConfidence" : 0.6827666
      } ],
      "editConfirmationStatus" : "NOT_CONFIRMED"
    }
  }, {
    "activitySegment" : {
      "startLocation" : {
        "latitudeE7" : 520967891,
        "longitudeE7" : 51159244,
        "sourceInfo" : {
          "deviceTag" : -1547871288
        }
      },
      "endLocation" : {
        "latitudeE7" : 520942097,
        "longitudeE7" : 51258010,
        "sourceInfo" : {
          "deviceTag" : -1547871288
        }
      },
      "duration" : {
        "startTimestampMs" : "1556713681618",
        "endTimestampMs" : "1556713939630"
      },
      "distance" : 816,
      "activityType" : "CYCLING",
      "confidence" : "HIGH",
      "activities" : [ {
        "activityType" : "CYCLING",
        "probability" : 99.41448992772163
      }, {
        "activityType" : "WALKING",
        "probability" : 0.43749986739765867
      }, {
        "activityType" : "IN_PASSENGER_VEHICLE",
        "probability" : 0.08513907766062832
      } ],
      "waypointPath" : {
        "waypoints" : [ {
          "latE7" : 520967674,
          "lngE7" : 51158652
        }, {
          "latE7" : 520942306,
          "lngE7" : 51260013
        } ]
      }
    }
  }, {
    "placeVisit" : {
      "location" : {
        "latitudeE7" : 520941602,
        "longitudeE7" : 51258926,
        "placeId" : "ChIJ46vtEE9vxkcRyu8VVfUro2Q",
        "address" : "Lucasbolwerk 18\n3512 EH Utrecht\nNederland",
        "name" : "Lucasbolwerk 18",
        "semanticType" : "TYPE_HOME",
        "sourceInfo" : {
          "deviceTag" : -1547871288
        },
        "locationConfidence" : 62.083218
      },
      "duration" : {
        "startTimestampMs" : "1556713939630",
        "endTimestampMs" : "1556731681066"
      },
      "placeConfidence" : "HIGH_CONFIDENCE",
      "centerLatE7" : 520942021,
      "centerLngE7" : 51257989,
      "visitConfidence" : 95,
      "otherCandidateLocations" : [ {
        "latitudeE7" : 520940870,
        "longitudeE7" : 51259290,
        "placeId" : "ChIJu8CAEE9vxkcRRXzQvpFYiIk",
        "locationConfidence" : 28.85588
      }, {
        "latitudeE7" : 520940508,
        "longitudeE7" : 51258612,
        "placeId" : "ChIJFXCOGk9vxkcRvLD0dAfMEwE",
        "locationConfidence" : 0.79878336
      }, {
        "latitudeE7" : 520943848,
        "longitudeE7" : 51258475,
        "placeId" : "ChIJC9dtF09vxkcR9zOEsPmnHcQ",
        "locationConfidence" : 0.35787553
      }, {
        "latitudeE7" : 520940508,
        "longitudeE7" : 51258612,
        "placeId" : "ChIJx2ucGk9vxkcRF_deiIhd7_k",
        "locationConfidence" : 0.25281402
      } ],
      "editConfirmationStatus" : "NOT_CONFIRMED"
    }
  }, {

As you can see, for now I am only trying to access the value's after the placeVisit.

Thank you in advance!

Maurice Meyer · Accepted Answer · 2020-02-11 08:08:20Z

2

placeVisit is just in every second timelineObjects item, you need to check if placeVisit exists:

import json
with open('data_may.json', 'r') as fh:
    raw = json.loads(fh.read())
    data = raw['timelineObjects']


for locations in data[0:5]:
    if 'placeVisit' in locations:
        print(locations['placeVisit']['location']['latitudeE7'])
    else:
        print("Found activitySegment instead of placeVisit!")

Output:

Found activitySegment instead of placeVisit!
520967891
Found activitySegment instead of placeVisit!
520941602

answered Feb 11, 2020 at 8:08

Maurice Meyer

18.2k4 gold badges36 silver badges55 bronze badges

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

1 Comment

Timo Vossen Over a year ago

Thank you, that makes sense. I got it working. Helped me a lot!

rednafi · Accepted Answer · 2020-02-11 08:25:56Z

Let's start with how many upper-level keys are present in the JSON:

data = <your-json-dictionary>

# print a list of your top-level keys 
print(list(data.keys()))

This shows,

>> ['timelineObjects']

Now let's see the keys of the elements of data['timelineObject']

for timelineObject in a['timelineObjects']:
    print(list(timelineObject.keys()))

>> ['activitySegment']
   ['placeVisit']
   ['activitySegment']
   ['placeVisit']
   ...

However, there are some nested dictionaries where the key 'placeVisit' does not exist. Python is raising keyError when it encounters these cases. You should use python's dict.get(key, default_when_key_is_missing) method to return a default value when the key is not found in the dictionary.

for timelineObject in a['timelineObjects']:
    print(timelineObject.get('placeVisit', None))

This will return your desired output.

Collectives™ on Stack Overflow

Pandas: access values inside .json file

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related