coverting a mongodb nested record into a dataframe using pandas

Question

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

user11717481 · Accepted Answer · 2022-03-10 01:53:57Z

1

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.'))

edited Mar 10, 2022 at 1:53

answered Mar 9, 2022 at 23:42

user11717481

1,5919 gold badges18 silver badges31 bronze badges

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

3 Comments

Maddy Over a year ago

thanks a lot. I have the same approach in my mind. I would like know whether there are another approach for this

user11717481 Over a year ago

okay it seems good another approach more 'direct' because maybe the names of the columns can vary, I would recommend you to read the rename method if you want to change the name of the columns it is very simple, for now I only have doubts in the last 3 definitions in the columns, I don't know have no problems or IndexingErrors?

user11717481 Over a year ago

currently it should not present errors, the values are decompressed properly, I hope it works correctly.

Collectives™ on Stack Overflow

coverting a mongodb nested record into a dataframe using pandas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related