时间戳

首页 > 解决方案 > 如何通过读取 csv 数据并在 API 有效负载中使用来形成正确的 JSON

python - 如何通过读取 csv 数据并在 API 有效负载中使用来形成正确的 JSON

问题描述

我正在使用以下代码通过从 csv 读取数据来形成 JSON

df = pd.read_csv('/testdata.csv', dtype={

 "debt_type": str,
 "debt_amount": int,
 "interest_rate": float,
 "total_monthly_payment": int,
 "remaining_term,interest_payable": int})

finalList = []
finalDict = {}
grouped = df.groupby(['debt_type'])
for key, value in grouped:
    dictionary = {}

    j = grouped.get_group(key).reset_index(drop=True)
    dictionary['debt_type'] = j.at[0, 'debt_type']

    dictList = []
    anotherDict = {}
    for i in j.index:
        anotherDict['debt_amount'] = j.at[i, 'debt_amount']
        anotherDict['interest_rate'] = j.at[i, 'interest_rate']
        anotherDict['total_monthly_payment'] = j.at[i, 'total_monthly_payment']
        anotherDict['remaining_term'] = j.at[i, 'remaining_term']
        anotherDict['interest_payable'] = j.at[i, 'interest_payable']

        dictList.append(anotherDict)
        dictionary['loan_info'] = dictList
        finalList.append(dictionary)
        finalDict = finalList

并想实现以下

{"loan_info":{"debt_amount":9000,"interest_rate":23,"total_monthly_payment":189,"remaining_term":129,"interest_payable":15356},"debt_type":"credit_card"}

但是，我得到的是下面

[{'debt_type': 'credit_card', 'loan_info': [{'debt_amount': 9000, 'interest_rate': 12.2, 'total_monthly_payment': 189, 'remaining_term': 129, 'interest_payable': 15256}]}]

任何人都可以在这里帮忙。提前致谢。

标签： pythonlistapidictionary

解决方案

我认为您需要使用pandas.DataFrame.to_dict()和pandas.DataFrame.to_json()。

阅读完 csv 文件后，您可以创建一个新列loan_info，将您想要的所有字段格式化为 Python 字典：

loan_info_cols = ['debt_amount', 'interest_rate', 'total_monthly_payment', 'remaining_term', 'interest_payable']
df['loan_info'] = df[loan_info_cols].apply(lambda x: x.to_dict(), axis=1)

然后删除我们刚刚使用的列：

df = df.drop(loan_info_cols, axis=1)

这是我们迄今为止所拥有的：

print(df)

     debt_type                                          loan_info
0  credit_card  {u'total_monthly_payment': 189.0, u'interest_p...
1   debit_card  {u'total_monthly_payment': 165.0, u'interest_p...

现在您可以将整个数据帧转换为 JSON ：

df_json = df.to_json(orient='records', lines=True)
print(df_json)

{"debt_type":"credit_card","loan_info":{"total_monthly_payment":189.0,"interest_payable":15356.0,"interest_rate":23.0,"debt_amount":9000.0,"remaining_term":129.0}}
{"debt_type":"debit_card","loan_info":{"total_monthly_payment":165.0,"interest_payable":21354.0,"interest_rate":24.0,"debt_amount":8000.0,"remaining_term":167.0}}

推荐阅读