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?

Answer 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'}]}]}