CODEWITHSUNDEEP
pythonjsonpandasdictionaryapply
Raphadasilva
Raphadasilva

Reputation: 617

dict of dict displays a list after apply

I've got one DataFrame, like this one :

import pandas pd

df = pd.DataFrame.from_dict({"name":["Spot1","Spot2"],2020:[{"product1":20.3,"product2":.93},{"product1":67,"product2":678}],2019:[{"product1":28,"product2":1003},{"product1":267,"product2":28}]})

    name                    2020                                  2019
0   Spot1   {'product1': 20.3, 'product2': 0.93}    {'product1': 28, 'product2': 1003}
1   Spot2   {'product1': 67, 'product2': 678}   {'product1': 267, 'product2': 28}

My goal is to concatenate the columns refering to a year to have this type of structure in a json :

    name                                        chronology
0   Spot1   {'2020':{'product1': 20.3, 'product2': 0.93}, '2019':{'product1': 28, 'product2': 1003}}
1   Spot2   {'2020':{'product1': 67, 'product2': 678},  '2019':{'product1': 267, 'product2': 28}}

So I wrote a function :

def all_timeline(x):
    dic = {str(i):x[i] for i in range(2020,2018,-1)}
    return dic

And then used it with an apply after a groupby :

after_gb=df.groupby(by="name").apply(all_timeline).reset_index()
after_gb.rename(columns={0:"chronology"}, inplace=True)

name    chronology
0   Spot1   {'2020': [{'product1': 20.3, 'product2': 0.93}...
1   Spot2   {'2020': [{'product1': 67, 'product2': 678}], .

Instead of dict inside a dict, I've got a dict with lists... I saw this error after a to_json export, that displays :

{
"name":{"0":"Spot1",
        "1":"Spot2"},
"chronology":{
    "0":{
        "2020":{"1":{"product1":20.3,"product2":0.93}},
        "2019":{"1":{"product1":28,"product2":1003}}},
    "1":{
        "2020":{"1":{"product1":67,"product2":678}},
        "2019":{"1":{"product1":267,"product2":28}}}}
}

What did I miss ?

Upvotes: 1

Views: 50

Answers (1)

Nk03
Nk03

Reputation: 14949

One Way:

df = df.set_index('name')
df = df.apply(lambda x: {f'{col}':x[col] for col in df.columns}, 1).reset_index(name = 'chronology')

Upvotes: 2

Related Questions