how to store pandas data frame into json file, while having first column name as key to access json

Question

I am trying to convert pandas data frame into json, with the help of for loop. I want custom formatting of my data frame in json. I have data in csv, and want to convert it into json file like below.

i tried to convert data frame to_json, then tried to replace index number with account number, but insted of replacing key only, my for loop replaced all "zeros" present in my code to account number.

{"12312312313": {
    account_no: 12312312313,  # To save acc number
    account_type: 'saving'  # account type
    branch: BARB,  #branch
    branch_address:  #address
    ifsc: BARB000000,  #ifsc
    uidai: 123412341234,   #aadhar_id
    mobile_no: 8888888888,  #communication medium
    email: '[email protected]',  #email
    fname: "Raj",
    mname: "Ramesh",
    sname: "Rampal",
    acc_balance: 132156465413513,
    dob: "20/12/1999",
    pan: "QWER123QER",
    occupation: "Student",
    address: "Mumbai-412321",
    ac_open_date: 12/12/2012,
    blood_group: "a-",
    history:{
        dd_mm_yyyy_hh_mm_ss:{
            transition_id: 132132323213563513,
            added: 13213,
            withdrawn: 1231,
        }
    },
    cards_allocated: {
        credit:{
            credit_card_type: "platinum",
            credit_card_no: 1323213,
            name_on_card: "raj ramesh rampal",
            card_limit: 20000,
            amount_used: 10000,
            amount_to_pay: 0000.0,
            card_issue_date: 12/12/2012,
            card_exp_date: 11/12/2025,
            cvv: 123123,
            credit_card_password: 1234,
            monthly_emi: 4225,
            history:{
                dd_mm_yyyy_hh_mm_ss:{
                    transition_id: 132132323213563513,
                    added: 13213,
                    withdrawn: 1231,
                }
            }
        },
        debit:{
            debit_card_type: "visa",
            debit_card_no: 1323213,
            name_on_card: "raj ramesh rampal",
            daily_debit_limit: 20000,
            card_issue_date: 12/12/2012,
            card_exp_date: 11/12/2025,
            cvv: 123123,
            debit_card_password: 1234,
            history:{
                dd_mm_yyyy_hh_mm_ss:{
                    transition_id: 132132323213563513,
                    added: 13213,
                    withdrawn: 1231,
                }
            },
        }
    }
Also i tried to add Transition history column but its not working.

**I am using this data to create ATM Machine Simulator using TKinter or PyQT5.**

Answer 1

Here is an example, try the 'Split' option.

sample2=pd.DataFrame({'Client':['Bob','Sally', 'Bob', 'Doug', 'Sally'],
                'Result':['Expired', 'Expired','Expired', 'Not Expired', 'Not Expired'],
                'Account_Type':['Savings', 'Savings', 'Savings', 'Savings', 'Checking'],
                'Occupation':['Student', 'Engineer', 'Doctor', 'Student', 'Engineer']})


df_json = sample2.set_index('Client').to_json(orient='split')

print(df_json)

{"columns":["Result","Account_Type","Occupation"],"index":["Bob","Sally","Bob","Doug","Sally"],"data":[["Expired","Savings","Student"],["Expired","Savings","Engineer"],["Expired","Savings","Doctor"],["Not Expired","Savings","Student"],["Not Expired","Checking","Engineer"]]}