0

Dataframe

import pandas as pd
import json
data = {'store_id' : ['1', '1','1','2','2'],
        'item_name' : ['apples', 'oranges', 'pears', 'persimmons', 'bananas'],
        '2022-01-01': [2.33, 1.99, 2.33, 2.33, 4.21],
        '2022-01-02': [2.38, 1.96, 2.38, 2.37, 4.34],
        '2022-01-03': [2.45, 1.78, 2.45, 2.45, 4.13]}
        
df = pd.DataFrame(data)

  store_id   item_name  2022-01-01  2022-01-02  2022-01-03
0        1      apples        2.33        2.38        2.45
1        1     oranges        1.99        1.96        1.78
2        1       pears        2.33        2.38        2.45
3        2  persimmons        2.33        2.37        2.45
4        2     bananas        4.21        4.34        4.13

My expected output

{
    "apples": {
        "2022-01-01": 2.33,
        "2022-01-02": 2.38,
        "2022-01-03": 2.45
    },
    "bananas": {
        "2022-01-01": 4.21,
        "2022-01-02": 4.34,
        "2022-01-03": 4.13
    },
    "oranges": {
        "2022-01-01": 1.99,
        "2022-01-02": 1.96,
        "2022-01-03": 1.78
    },
    "pears": {
        "2022-01-01": 2.33,
        "2022-01-02": 2.38,
        "2022-01-03": 2.45
    },
    "persimmons": {
        "2022-01-01": 2.33,
        "2022-01-02": 2.37,
        "2022-01-03": 2.45
    }
}

My approach in the following

df2 = df.melt(id_vars=["store_id", "item_name"], var_name="date", value_name="value")
df3 = df2.groupby(["store_id", "item_name","date"]).first()
d = {level:{level1:1 for level1 in df3.index.levels[2]} for level in df3.index.levels[1]} 
k = json.dumps(d)

Above I have set the value is equal to 1, it should be some function to extract the corresponding of each level

eg. 2.33 for 'item_name'=='apple' on '2022-01-01'

I would like to know how to achieve the expected output.

Alana
  • 77
  • 6

0 Answers0