时间戳

首页 > 解决方案 > 如何使用 DataFrame 将 csv 文件转换为嵌套的 json 格式

python-3.x - 如何使用 DataFrame 将 csv 文件转换为嵌套的 json 格式

问题描述

我正在使用 pandas 库来读取.csv文件并将文件转换csv为json使用json库，但在我的csv文件中，一列包含一个列表，我也必须将其转换为列表，json array list但它正在转换为string format

这是我的 csv 内容

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"}]

这是我的代码

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)

输出：

[
  {
     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"}]"
    }, 
 ]

预期输出：

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

请帮助我

标签： python-3.xpandasdataframe

解决方案

  1. 您提供的示例数据看起来不正确。它不是 CSV，它是管道和空格分隔的混合体。修改为管道分隔
  2. 已命名路线json而不是index
  3. ingreients2是一个字符串，需要是json才能返回为json
  4. jsonify()从 Flask 路由返回 JSON更简单。
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)

推荐阅读