Updating a list inside a dictionary in each iteration

user_8942968 0
Viewed 98

From my db query in mongo, i get a <class 'pymongo.cursor.Cursor'> Object as lets say query_result where data is collection of dictionary such as

{
 id:'1', 
 locations:
           [
           {lat:1.36, long:-21.99}, 
           {lat:13.08, long:12.13}, 
           {lat:33.08, long: 12.13}
           ]
}   
{
 id:'2', 
 locations:
          [
          {lat:1.42, long:-21.31}, 
          {lat:2.36, long:11.12}
          ]
}

The lat long dict can be from 1 to many.

I need to convert the above dict into dictionary of list as

res = {
       id: ['1','1','1','2','2'],  
       lat: [1.36, 13.08, 33.08, 1.42, 2.36], 
       long: [-21.99, 12.13, 12.13, -21.31, 11.12] 
      }

I tried using the following code:

res= dict(id=[], lat=[],long=[])
for result in query_result:`
    for location in result['locations']:
        res.update(id=res['id'] + result['id'], 
                  lat=res['lat'] + location['lat'],
                  long=res['long'] + location['long'],
               )

Only the ID field is in string while lat and long are Double.

The error i received was: TypeError: can only concatenate list (not "str") to list on line 4.

pandas mongodb list dictionary python
3 Answers

IIUC, you can try -> json_normalize:

data = [{
    'id': '1',
    'locations':
    [
        {'lat': 1.36, 'long': -21.99},
        {'lat': 13.08, 'long': 12.13},
        {'lat': 33.08, 'long': 12.13}
    ]
},
    {
    'id': '2',
    'locations':
    [
        {'lat': 1.42, 'long': -21.31},
        {'lat': 2.36, 'long': 11.12}
    ]
}]

result = pd.json_normalize(
    data, record_path='locations', meta='id').to_dict('list')

OUTPUT:

{'lat': [1.36, 13.08, 33.08, 1.42, 2.36],
 'long': [-21.99, 12.13, 12.13, -21.31, 11.12],
 'id': ['1', '1', '1', '2', '2']}
user_154380330

you don't need Pandas as a dependency if you don't want it. Just change this

        res.update(id=res['id'] + result['id'], 
                  lat=res['lat'] + location['lat'],
                  long=res['long'] + location['long'],
               )

to this

        res['id'].append(result['id'])
        res['lat'].append(location['lat'])
        res['long'].append(location['long'])
user_114611200

This can be done within your mongo query using an aggregate pipeline as follows:

db.collection.aggregate([
    # previous pipeline query that produces the result to be transformed below 
    { '$group': {
       '_id': 0,
       'id': { '$push': '$id' },
       'lat': { '$push': '$locations.lat' },
       'long':{ '$push': '$locations.long' }
    } },
    { '$addFields': {
       'lat': {
          '$reduce': {
            'input': '$lat',
            'initialValue': [],
            'in': { '$concatArrays': ['$$value', '$$this'] }
          }
        },
       'long': {
          '$reduce': {
            'input': '$long',
            'initialValue': [],
            'in': { '$concatArrays': ['$$value', '$$this'] }
          }
        } 
    } }
])
user_1220050
Related