Create a Pandas table with nested document of MongoDB

Question

I need create a table using Pandas with nested documents in MongoDB.

This is my json:

{
"CNPJ" : "65206503000163",
"CNAE" : [ 
        {
            "codigoCNAE" : 7911200,
            "dataInicioCNAE" : 20000101,
        },
        {
            "codigoCNAE" : 9999999,
            "dataInicioCNAE" : 2018101,
        }
        ]
}

I need a simple table:

    CNPJ             codigoCNAE     dataInicioCNAE 
0   65206503000163   7911200        20000101      
1   65206503000163   9999999        2018101

Thanks

Bimal Gangawal · Accepted Answer · 2019-02-07 06:08:52Z

1

Assuming that you have only one such document you can use the following code.

dict1 = { "CNPJ" : "65206503000163", "CNAE" : [{ "codigoCNAE" : 7911200, "dataInicioCNAE" : 20000101, }, { "codigoCNAE" : 9999999, "dataInicioCNAE" : 2018101, } ] }

df = pd.DataFrame(dict1['CNAE'])
df['CNPJ'] = dict1['CNPJ']

OUTPUT :

print(df)

   codigoCNAE dataInicioCNAE     CNPJ
0   7911200   20000101          65206503000163
1   9999999   2018101           65206503000163

For multiple documents you can iterate through each document and use pd.concat to combine each df

answered Feb 7, 2019 at 6:08

Bimal Gangawal

4051 gold badge3 silver badges17 bronze badges

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

Comments

jezrael · Accepted Answer · 2019-02-07 06:26:24Z

1

Use json_normalize:

from pandas.io.json import json_normalize

dict1 = { "CNPJ" : "65206503000163", 
          "CNAE" : [{ "codigoCNAE" : 7911200, 
                     "dataInicioCNAE" : 20000101, }, 
                      { "codigoCNAE" : 9999999, 
                        "dataInicioCNAE" : 2018101, } ] }

df = json_normalize(dict1, ['CNAE'],'CNPJ')
print (df)
   codigoCNAE  dataInicioCNAE            CNPJ
0     7911200        20000101  65206503000163
1     9999999         2018101  65206503000163

answered Feb 7, 2019 at 6:26

jezrael

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

Comments

Sociopath · Accepted Answer · 2019-02-07 06:10:37Z

You need:

import pandas as pd 

x = {
"CNPJ" : "65206503000163",
"CNAE" : [ 
        {
            "codigoCNAE" : 7911200,
            "dataInicioCNAE" : 20000101,
        },
        {
            "codigoCNAE" : 9999999,
            "dataInicioCNAE" : 2018101,
        }
        ]
}

df = pd.DataFrame.from_dict(x, orient='columns')
df = pd.concat([df['CNAE'].apply(pd.Series), df['CNPJ']], axis=1)
print(df)

Output:

codigoCNAE        dataInicioCNAE            CNPJ                                                                                         
0     7911200        20000101       65206503000163                                                                                         
1     9999999         2018101       65206503000163

Sreeram TP · Accepted Answer · 2019-02-07 06:15:56Z

Simply make a dataframe from the dict you have, seperate the dataframe to 2 parts. Make the CNAE part as a Series and concat with the other part on axis 1.

 x = {
    "CNPJ" : "65206503000163",
    "CNAE" : [ 
                {
                    "codigoCNAE" : 7911200,
                    "dataInicioCNAE" : 20000101,
                },
                {
                    "codigoCNAE" : 9999999,
                    "dataInicioCNAE" : 2018101,
                }
            ]
}

x_df = pd.DataFrame(x)

a_df = x_df['CNAE'].apply(pd.Series)
b_df = x_df['CNPJ']

df = pd.concat([b_df, a_df], axis=1)

df

#Output

CNPJ    codigoCNAE  dataInicio    CNAE
0   65206503000163  7911200     20000101
1   65206503000163  9999999     2018101

U13-Forward · Accepted Answer · 2019-02-07 06:23:56Z

0

Use concat:

>>> df=pd.DataFrame(d)
>>> pd.concat([df[['CNPJ']],pd.DataFrame(d['CNAE'])],axis=1)
             CNPJ  codigoCNAE  dataInicioCNAE
0  65206503000163     7911200        20000101
1  65206503000163     9999999         2018101
>>>

answered Feb 7, 2019 at 6:23

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

Create a Pandas table with nested document of MongoDB

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related