How to explode (or pop) array of JSON twice

Question

I'm trying to pop MongoDB documents that consist array of JSON by using pop, usually it works well if MongoDB documents is only JSON, in array of JSON it produce too much column, so I can't pop to non-JSON format easily. for more detailed question I give explanation below

Here's my data

Id locations
1   [{'timestamp': 2018-05-28 15:00:00, 'lat': 0.0..
2   [{'timestamp': 2018-05-28 15:00:00, 'lat': 0.0..

What I try to pop

df = df.join(pd.DataFrame(df.pop('locations').values.tolist(), index=df.index))

The output is

Id   0                              1                            ...      136
1   {'timestamp': 2018-05-28...    {'timestamp': 2018-05-28...           {'timestamp': 2018-05-28... 
2   {'timestamp': 2018-05-28...    {'timestamp': 2018-05-28...           None

The output that I expected is

Id   0                             
1   {'timestamp': 2018-05-28... 
1.  {'timestamp': 2018-05-28... 
    ...
    {'timestamp': 2018-05-28... 
2   {'timestamp': 2018-05-28...
    ...
    {'timestamp': 2018-05-28...

So, I can pop again

jezrael · Accepted Answer · 2018-06-25 08:53:20Z

1

I think need melt:

df2 = df.join(pd.DataFrame(df.pop('locations').values.tolist(), index=df.index)).melt('Id')

Or stack:

s = (pd.DataFrame(df.pop('locations').values.tolist(), index=df.index)
       .stack()
       .reset_index(level=1, drop=True))

df2 = df.join(s.rename('new'))

Or numpy solution with repeat Id values and flatenning nested lists:

df2 = pd.DataFrame({
        "Id": np.repeat(df.Id.values, df.locations.str.len()),
        "new": list(chain.from_iterable(df.locations))})
print (df2)
   Id                                               new
0   1  {'timestamp': '2018-05-28 15:00:00', 'lat': 0.0}
1   1  {'timestamp': '2018-05-28 16:00:00', 'lat': 0.0}
2   2  {'timestamp': '2018-05-28 10:00:00', 'lat': 0.0}
3   2  {'timestamp': '2018-05-28 17:00:00', 'lat': 0.0}
4   2  {'timestamp': '2018-05-28 18:00:00', 'lat': 0.0}

Setup:

df = pd.DataFrame({'Id':[1,2], 
                   'locations':[[{'timestamp': '2018-05-28 15:00:00', 'lat': 0.0}, {'timestamp': '2018-05-28 16:00:00', 'lat': 0.0}],
                                [{'timestamp': '2018-05-28 10:00:00', 'lat': 0.0}, {'timestamp': '2018-05-28 17:00:00', 'lat': 0.0}, {'timestamp': '2018-05-28 18:00:00', 'lat': 0.0}]]})
print (df)


   Id                                          locations
0   1  [{'timestamp': '2018-05-28 15:00:00', 'lat': 0...
1   2  [{'timestamp': '2018-05-28 10:00:00', 'lat': 0...

edited Jun 25, 2018 at 8:53

answered Jun 25, 2018 at 8:40

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

Nabih Bawazir Over a year ago

works, but not as expected, still debugging, probably mistake in my side

Nabih Bawazir Over a year ago

the first is give different output that I expected, the third AttributeError: 'DataFrame' object has no attribute 'locations' but the second works very well

Collectives™ on Stack Overflow

How to explode (or pop) array of JSON twice

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related