how to form correct JSON by reading csv data and use in API payload

Question

I am using the below code to form a JSON, by reading data from csv

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

and want to achieve below

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

however, what I am getting is below

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

can anyone help here. thanks in advance.

jusstol · Accepted Answer · 2021-03-01 13:08:33Z

0

I think what you need is to use pandas.DataFrame.to_dict() and pandas.DataFrame.to_json().

Right after you read your csv file, you can create a new column loan_info that will format all the fields you want to a Python dictionary :

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)

Then drop the columns we just used :

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

This is what we have so far :

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...

Now you can convert the whole dataframe to 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}}

edited Mar 1, 2021 at 13:08

answered Mar 1, 2021 at 10:49

jusstol

1264 bronze badges

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

4 Comments

Devesh Joshi Over a year ago

thanks a lot :), this works now how can I remove \ from the start of the node name

jusstol Over a year ago

@DeveshJoshi I edited my answer. I now recommand you to first convert the loan_info subset to Python dictionaries, and then the whole dataframe to JSON.

jusstol Over a year ago

@DeveshJoshi If this answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this.

Devesh Joshi Over a year ago

oh not aware of this, as I am new here, now done the needful, thanks for the help :)

Collectives™ on Stack Overflow

how to form correct JSON by reading csv data and use in API payload

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related