How to convert excel data to json in python?

Question

My data is below

food ID	name	ingredients	ingredient ID	amount	unit
1	rice	red	R1	10	g
1	soup	blue	B1	20	g
1	soup	yellow	Y1	30	g

and I want to convert it like this


{
  'data': [
    {
      'name': 'rice',
      'ingredients': [
        {
          'name': 'red',
          'ingredient_id':'R1',
          'amount': 10,
          'unit': 'g',
        }
      ]
    },
    {
      'name': 'soup',
      'ingredients': [
        {
          'name': 'blue',
          'ingredient_id':'B1',
          'amount': 20,
          'unit': 'g',
        },
        {
          'name': 'yellow',
          'ingredient_id':'Y1',
          'amount': 30,
          'unit': 'g',
        }
      ]
    }
  ]
}

How can I do it? Do I need to use the same library as pandas?

NIKUNJ KOTHIYA · Accepted Answer · 2023-02-06 07:41:46Z

1

Yes you can modify your data by using custom code function inside python.

For your required format you need to use this code for format your data into json.

import pandas as pd

data = [[1, 'rice', 'red', 'R1', 10, 'g'],
    [1, 'soup', 'blue', 'B1', 20, 'g'],
    [1, 'soup', 'yellow', 'Y1', 30, 'g'],
    [1, 'apple', 'yellow', 'Y1', 30, 'g']]

df = pd.DataFrame(data, columns=['food ID', 'name', 'ingredients', 'ingredient ID', 'amount', 'unit'])

def convert_data_group(group):
    ingredients = [{'name': row['ingredients'], 'ingredient_id': row['ingredient ID'], 'amount': row['amount'], 'unit': row['unit']} for _, row in group.iterrows()]
    return {'name': group.iloc[0]['name'], 'ingredients': ingredients}

unique_names = df['name'].unique().tolist()
result = []
for name in unique_names:
    group = df[df['name'] == name]
    result.append(convert_data_group(group))

final_result = {'datas': result}
print(final_result)

Your final result will be:

{'datas': [{'name': 'rice', 'ingredients': [{'name': 'red', 'ingredient_id': 'R1', 'amount': 10, 'unit': 'g'}]}, {'name': 'soup', 'ingredients': [{'name': 'blue', 'ingredient_id': 'B1', 'amount': 20, 'unit': 'g'}, {'name': 'yellow', 'ingredient_id': 'Y1', 'amount': 30, 'unit': 'g'}]}, {'name': 'apple', 'ingredients': [{'name': 'yellow', 'ingredient_id': 'Y1', 'amount': 30, 'unit': 'g'}]}]}

edited Feb 6, 2023 at 7:41

answered Feb 6, 2023 at 5:43

NIKUNJ KOTHIYA

2,2061 gold badge10 silver badges26 bronze badges

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

13 Comments

studyhard Over a year ago

sir, how can I do sort data not rice , soup but soup, rice? I have many data, so when I adjust your solution, ingredient data order is twisted.

NIKUNJ KOTHIYA Over a year ago

Sorry I can not understand your question. Explain what you want to from result.

studyhard Over a year ago

If you look at the order of the text, it is printed in order of rice and soup, but if you assume that apple was added last in the table I have, I executed your code. Then, json data (name) is not output in the order of rice, soup, and apple, but in the order of apple, rice, and soup (abc). Do you understand? I'm sorry for my lack of explanation.

NIKUNJ KOTHIYA Over a year ago

Yes. This code will give you result in asc order like apple, rice and soup.

studyhard Over a year ago

Thank you sir. It is what I wanted. I'm sorry to bother you.

|

Collectives™ on Stack Overflow

How to convert excel data to json in python?

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related