-1

I have got data in the following format

Date Store_Id Data
08-10-2020 1 {"Product_1":{"Size_1":2},"Product_2":{"Size_3":2,"Size_4":10}}
08-10-2020 2 {"Product_3":{"Size_2":2}}
09-10-2020 1 {"Product_1":{"Size_1":2},"Product_3":{"Size_1":2,"Size_2":4,"Size_3":5}}
09-10-2020 3 {"Product_5":{"Size_1":2},"Product_3":{"Size_3":2,"Size_4":10}}

and want the output such there is a separate column for products, another for their size and one for the quantity at Date - Store_Id level similar to the table shown below:

Date Store_Id Product Size Quantity
08-10-2020 1 Product_1 Size_1 2
08-10-2020 1 Product_2 Size_3 2
08-10-2020 1 Product_2 Size_4 10
08-10-2020 2 Product_3 Size_2 2
09-10-2020 1 Product_1 Size_1 2
09-10-2020 1 Product_3 Size_1 2
09-10-2020 1 Product_3 Size_2 4
09-10-2020 1 Product_3 Size_3 5
09-10-2020 3 Product_5 Size_1 2
09-10-2020 3 Product_3 Size_3 2
09-10-2020 3 Product_3 Size_4 10

Is there any way to do the above using python?

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
Aaryan Singh
  • 21
  • 5
  • What is the input format ? – Hirusha Fernando Dec 12 '20 at 14:40
  • You can do it using python, if that is the question. On the how would you do it, that depends on your current data structure. You might want to first undo the nesting of your dictionary, for example [here](https://stackoverflow.com/questions/6027558/flatten-nested-dictionaries-compressing-keys) or [here](https://stackoverflow.com/questions/65203641/how-to-convert-a-nested-json-to-the-following-format-in-python3) and then loop over the keys. – Thymen Dec 12 '20 at 14:40

1 Answers1

2

Assuming that your data is formatted as a list like the following one:

items = [
        ["08-10-2020", 1, {"Product_1": {"Size_1": 2},"Product_2": {"Size_3": 2,"Size_4": 10}}],
        ["08-10-2020", 2, {"Product_3" : {"Size_2": 2}}],
        ["09-10-2020", 1, {"Product_1":{"Size_1": 2},"Product_3":{"Size_1": 2,"Size_2": 4,"Size_3": 5}}],
        ["09-10-2020", 3, {"Product_5":{"Size_1": 2},"Product_3":{"Size_3": 2, "Size_4": 10}}]
]

You can create another list with the updated items with some for loops:

updated = []

for item in items:
    for product, list_products in item[2].items():
        for size, quantity in list_products.items():
            aux = [item[0], item[1], product, size, quantity]
            updated.append(aux)

Now, your data is in the format that you are looking for:

>>> print(updated)
['08-10-2020', 1, 'Product_1', 'Size_1', 2]
['08-10-2020', 1, 'Product_2', 'Size_3', 2]
['08-10-2020', 1, 'Product_2', 'Size_4', 10]
['08-10-2020', 2, 'Product_3', 'Size_2', 2]
['09-10-2020', 1, 'Product_1', 'Size_1', 2]
['09-10-2020', 1, 'Product_3', 'Size_1', 2]
['09-10-2020', 1, 'Product_3', 'Size_2', 4]
['09-10-2020', 1, 'Product_3', 'Size_3', 5]
['09-10-2020', 3, 'Product_5', 'Size_1', 2]
['09-10-2020', 3, 'Product_3', 'Size_3', 2]
['09-10-2020', 3, 'Product_3', 'Size_4', 10]
lassault
  • 48
  • 1
  • 5