How to convert csv file into nested json format using DataFrame

Question

I am using pandas library to read .csv file and to convert csv file to json using json library but in my csv file one column contains a list which I have to also convert into json array list but it is converting into a string format

here is my csv content

recipe_id | recipe_name | ingredients2 |
 
   240488    Pork Loin     [{"title":"basmati rice","quantity":"1 cup"}, 
                           {"title":"mint  leaves","quantity":"1/2teaspoon"}, 
                           {"title":"salt","quantity":"as required"}]
   
   218939     Foolproof       [{"title":"Meat","quantity":"1 kg"}, 
              Rosemary             {"title":"Water","quantity":"1 Glass"}, 
                           {"title":"Mirch powder","quantity":"5 -10 Teaspoon"}]

this is my code

import numpy as np
import pandas as pd
import json
from flask import Flask, render_template

app = Flask(__name__)

df = pd.read_csv('sample.csv')


  @app.route("/index")
  def index():


     data = pd.DataFrame(df[['recipe_id', 'recipe_name', 'ingredients2']])

     return_data = data.to_json(orient="records")
     prse = json.loads(return_data)

    response = app.response_class(
    response=json.dumps(prse, indent=4),
    status=200,
    mimetype='application/json'
   )

   return response



if __name__ == '__main__':
     app.run(debug=True)

output:

[
  {
     recipe_id: 240488,
     recipe_name: "Pork Loin, Apples, and Sauerkraut",
     ingredients2: "[{"title":"basmati rice","quantity":"1 cup"},{"title":"mint 
                     leaves","quantity":"1/2 teaspoon"},{"title":"salt","quantity":"as required"}]"
   },
   {
       recipe_id: 218939,
       recipe_name: "Foolproof Rosemary Chicken Wings",
       ingredients2: "[{"title":"Meat","quantity":"1 kg"},{"title":"Water","quantity":"1 Glass"}, 
                      {"title":"Mirch powder","quantity":"5 -10 Teaspoon"}]"
    }, 
 ]

expected output :

[
   {
      title: "Biryani",
      description: "Chicken Biryani",
      ingredients2: [
                       {
                          title: "basmati rice",
                          quantity: "1 cup"
                        },
                        {
                           title: "mint leaves",
                           quantity: "1/2 teaspoon"
                        },
                        {
                            title: "salt",
                            quantity: "as required"
                        }
                 ]
     }
]

pleas help me for this

from the input example I can see there's no description column, so where do you intend on getting that? — maverick6912
– maverick6912, Commented Jan 3, 2021 at 9:34
I have updated please take look in my csv file the ingredients2 column contains the key values and pair so i want to get that data in json array list as above i have mentioned please any help for this — Prototype
– Prototype, Commented Jan 3, 2021 at 10:45

Rob Raymond · Accepted Answer · 2021-01-03 14:52:02Z

sample data you have provided does not look correct. It's not CSV, it's a mix of pipe and space delimited. Modified to be pipe delimited
have named route json rather than index
ingreients2 is a string, needs to be json to return as json
it's simpler to use jsonify() to return JSON from a Flask route.

import pandas as pd, json, io
from flask import Flask, render_template, Response, jsonify

app = Flask(__name__)

@app.route('/json')
def json_so():
    df = pd.read_csv(io.StringIO("""recipe_id|recipe_name|ingredients2
       240488  |  Pork Loin  |   [{"title":"basmati rice","quantity":"1 cup"}, {"title":"mint  leaves","quantity":"1/2teaspoon"}, {"title":"salt","quantity":"as required"}]
       218939  |   Foolproof Rosemary   |    [{"title":"Meat","quantity":"1 kg"}, {"title":"Water","quantity":"1 Glass"}, {"title":"Mirch powder","quantity":"5 -10 Teaspoon"}]
     """), sep="|")
    # key step convert string to actual JSON so when given back as response it's not a string
    df["ingredients2"] = df["ingredients2"].apply(lambda x: json.loads(x))
    # just use jsonify() it's simpler
    return jsonify(df[['recipe_id', 'recipe_name', 'ingredients2']].to_dict(orient="records"))


if __name__ == '__main__':
    app.run(debug=True)

Collectives™ on Stack Overflow

How to convert csv file into nested json format using DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related