Making a dataframe from a dictionary in python

Question

Trying to make a database from the following dict:

    sales = { 
   "clients": [
       {"ID_client": "241341", 
       "purchases": [
            "Item 101",
            "Item 202",
            "Item 324",
        ],
        "payment": [
            "visa", "master", "visa"
        ]           
       },
       {"ID_client": "24356", 
       "purchases": [
            "Item 2320",
            "Item 2342",
            "Item 5604",
        ],
        "payment": [
            "diners", "cash", "diners"
        ]           
       },    
       {"ID_client": "5534", 
       "purchases": [
            "Item 50563",
            "Item 52878",
            "Item 54233",
        ],
        "payment": [
            "diners", "master", "visa"
        ]           
       }       
   ]

}

So far I have:

    all=[]
row_purchase=[]
for p1 in sales['clients'] :
    for p2,p3 in zip(p1['purchases'],p1['payment']):
        row_p1=p1['ID_client']+","+p2+","+p3
        row_purchase.append(row_p1)
all.append(row_purchase)

df = pd.DataFrame(np.array(all), columns = ['ID_client','purchase','payment'])

And I have:

[['241341,Item 101,visa', '241341,Item 202,master', '241341,Item 324,visa', '24356,Item 2320,diners', '24356,Item 2342,cash', '24356,Item 5604,diners', '5534,Item 50563,diners', '5534,Item 52878,master', '5534,Item 54233,visa']]

And an error creating the df.

I need to create the following df:

Any help will be very welcome. Thanks in advance.

I've tested, but I don't get the dataframe the way I need it. — user9910379
– user9910379, Commented Oct 3, 2018 at 19:48

Alexander · Accepted Answer · 2018-10-03 20:12:21Z

2

Assuming that the number of payments for a given client equals the number of purchases, you can use a list comprehension together with zip.

>>> pd.DataFrame(
        [(record['ID_client'], purchase, payment)
         for record in sales['clients']
         for purchase, payment in zip(record['purchases'], record['payment'])],
        columns=['ID_client', 'Purchase', 'Payment'])
  ID_client    Purchase Payment
0    241341    Item 101    visa
1    241341    Item 202  master
2    241341    Item 324    visa
3     24356   Item 2320  diners
4     24356   Item 2342    cash
5     24356   Item 5604  diners
6      5534  Item 50563  diners
7      5534  Item 52878  master
8      5534  Item 54233    visa

Please refer to this related question: How to explode a list inside a Dataframe cell into separate rows

edited Oct 3, 2018 at 20:12

answered Oct 3, 2018 at 20:06

Alexander

111k32 gold badges212 silver badges208 bronze badges

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

1 Comment

user9910379 Over a year ago

Worked just fine @Alexander! Thanks a lot!

Collectives™ on Stack Overflow

Making a dataframe from a dictionary in python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related