coverting a mongodb nested record into a dataframe using pandas

user_17019173 0
Viewed 60

I was read a record from mongodb using python and the end result was not as expected.

MongoDb record

_id:objectID("4624689264826482")
verison:2
name:"matt"
code:"57532"
status:"active"
address:object
   address1:"4638, 14th cross"
   city:"london"
   state:"london"
   date:"2021-10-25T00:19:56:000+00:00"
floordetails:object
   floorname:"2"
   room:"5"
metadata:object
   extid:"3303"
   ctype:"6384"



_id:objectID("20889689264826482")
verison:3
name:"rick"
code:"96597"
status:"active"
address:object
   address1:"34, 12th street"
   city:"london"
   state:"london"
   date:"2021-10-25T00:19:56:000+00:00"
floordetails:object
   floorname:"4"
   room:"234"
metadata:object
   extid:"26403"
   ctype:"4724"

I tried converting the record to a dataframe( all nested key with in the object to be a column name)

expected result:

_id                          |verison|name  |code   |status  |address1          |city    |state |date                             |floorname |room|extid |ctype
objectID("4624689264826482") |2      |"matt"|"57532"|"active"|"4638, 14th cross"|"london"|"london"|"2021-10-25T00:19:56:000+00:00"|"2"       |"5" |"3303"|"6384"
objectID("20889689264826482")|3      |"rick"|"96597"|"active"|"34, 12th street" |"london"|"london"|"2021-10-25T00:19:56:000+00:00"|"4"       |"234"|"26403"|"4724"

but the final result appears as below

id                          |verison|name  |code   |status  |address                                                                                        |floordetails              |metadata
objectID("4624689264826482") |2      |"matt"|"57532"|"active"|{address1:"4638, 14th cross",city:"london",state:"london",date:"2021-10-25T00:19:56:000+00:00"}|{floorname:"2",room:"5"}  |{extid:"3303",ctype:"6384"}
objectID("20889689264826482")|3      |"rick"|"96597"|"active"|{address1:"34, 12th street",city:"london",state:"london",date:"2021-10-25T00:19:56:000+00:00"} |{floorname:"4",room:"234"}|{extid:"26403",ctype:"4724"}

please advise me on this

pandas mongodb python
1 Answers

Supposing you're loading data by:

DataFrame(list(db.collection_name.find({}))

I think that there is no a direct form to "unpack" your values separately, otherwise if your JSON/Record or like dict data are string type you need to write this to convert properly a dictionary to be processed with pandas.DataFrame:

import ast

df['address'] = df['address'].map(ast.literal_eval)
df['floordetails'] = df['floordetails'].map(ast.literal_eval)
df['metadata'] = df['metadata'].map(ast.literal_eval)

Now I use Pandas.DataFrame.join() each time is added new nested dict values to a new dataframe

import pandas as pd

newdf = df[['_id','verison','name','code','status']]
newdf = newdf[['_id','verison','name','code','status']].join(pd.DataFrame(df['address'].tolist(), index=df.index).add_prefix('address.'))
newdf = newdf[['_id','verison','name','code','status','address.city','address.state','address.date']].join(pd.DataFrame(df['floordetails'].tolist(), index=df.index).add_prefix('floordetails.'))
newdf = newdf[['_id','verison','name','code','status','address.city','address.state','address.date','floordetails.floorname', 'floordetails.room']].join(pd.DataFrame(df['metadata'].tolist(), index=df.index).add_prefix('metadata.'))
user_117174810
Related