Python Pandas nested MongoDB

Question

I try to read get a nested mongodb result into a pandas dataframe.

The data looks like this.

{
"_id" : ObjectId("5911b9cebb56c016794d45a4"),
"crawlat" : "2017-05-09 14:45",
"traffic" : [ 
    {
        "timestamp" : "1494338401",
        "organic" : 53
    }, 
    {
        "timestamp" : "1494342001",
        "organic" : 64
    }, 
    {
        "timestamp" : "1494345601",
        "organic" : 74
    }, 
    {
        "timestamp" : "1494349201",
        "organic" : 78
    }, 
    {
        "timestamp" : "1494352801",
        "organic" : 80
    }, 
    {
        "timestamp" : "1494356401",
        "organic" : 88
    }, 
    {
        "timestamp" : "1494360001",
        "organic" : 91
    }, 
    {
        "timestamp" : "1494363601",
        "organic" : 92
    }, 
    {
        "timestamp" : "1494367201",
        "organic" : 94
    }
]

}

The traffic array contains 48 entries for every result.

Im just interested in the values of "organic" ordered in the order of the array.

I start with

con = pymongo.MongoClient(['...:27017'])
collsitemap = con.sitemap.newssitemap
sitemapsdata = collsitemap.find({'traffic':{'$size':48}})

I did some cleanup used json_normalize and

dfsitemap = dfsitemap['traffic'].apply(pd.Series)

Now the result look like this

But i need a table with just the organic values. How can i clean this up?

What are the two dimensions in your data frame?

oLas
– oLas

2017-05-19 09:42:16 +00:00
Commented May 19, 2017 at 9:42 — oLas
– oLas, Commented May 19, 2017 at 9:42

oLas · Accepted Answer · 2017-05-19 13:28:20Z

1

You could create your data frame with the from_records constructor, which allows you to specify columns to include or exclude:

pd.DataFrame.from_records(sitemapsdata['traffic'], exclude=['timestamp'])

which gives:

edited May 19, 2017 at 13:28

answered May 19, 2017 at 9:38

oLas

1,2711 gold badge11 silver badges17 bronze badges

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

3 Comments

Tobi Over a year ago

OK thanks i get an error: TypeError: Argument 'rows' has incorrect type (expected list, got Series) Traffic is an array as you can see above.

oLas Over a year ago

Sorry that's a variable typo. The argument to from_records is a list of dicts (in your case the value of traffic). I've updated the answer.

Tobi Over a year ago

Now i get: TypeError: index 'traffic' cannot be applied to Cursor instances The find will return multiple rows.

Collectives™ on Stack Overflow

Python Pandas nested MongoDB

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