I need to convert a nested array of dictionary to new Dataframe in pandas python

Question

My_dict = \
{0: '[{"accountType": "Consumer Loan", "currentBalance": "9,250", "paymentHistory1": "000    000    000    000    000    000    ", "dateofLastPayment": "02/09/2017", "ownershipIndicator": "Individual", "dateOpened_Disbursed": "14/04/2017", "paymentHistoryEndDate": "01/04/2017", "paymentHistoryStartDate": "01/09/2017", "dateReportedandCertified": "30/09/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "18,500"}, {"dateClosed": "11/07/2017", "accountType": "Business Loan – General", "accountNumber": "LK0000010410", "currentBalance": "0", "paymentHistory1": "000    000    000    000    ", "paymentFrequency": "Monthly", "ownershipIndicator": "Individual", "dateOpened_Disbursed": "10/04/2017", "paymentHistoryEndDate": "01/04/2017", "paymentHistoryStartDate": "01/07/2017", "dateReportedandCertified": "31/07/2017", "reportingMemberShortName": "AADRILTD", "highCredit_SanctionedAmount": "1,00,000"}, {"accountType": "Credit Card", "currentBalance": "26,841", "paymentHistory1": "000    000    000    000    000    000    000    000    000    000    000    XXX    000    000    ", "dateofLastPayment": "15/09/2017", "ownershipIndicator": "Individual", "dateOpened_Disbursed": "03/07/2016", "paymentHistoryEndDate": "01/08/2016", "paymentHistoryStartDate": "01/09/2017", "dateReportedandCertified": "22/09/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "75,102"}, {"accountType": "Credit Card", "creditLimit": "27,000", "currentBalance": "12,561", "paymentHistory1": "000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    XXX    ", "paymentHistory2": "000    XXX    000    000    000    000    000    000    000    000    ", "paymentFrequency": "Monthly", "dateofLastPayment": "30/09/2017", "ownershipIndicator": "Individual", "actualPaymentAmount": "9,000", "dateOpened_Disbursed": "25/06/2015", "paymentHistoryEndDate": "01/06/2015", "paymentHistoryStartDate": "01/09/2017", "dateReportedandCertified": "30/09/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "28,409"}, {"emiAmount": "16,883", "accountType": "Auto Loan (Personal)", "currentBalance": "4,89,730", "rateOfInterest": "9.17", "paymentHistory1": "000    000    XXX    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ", "paymentHistory2": "019    021    000    000    000    000    XXX    000    000    000    ", "repaymentTenure": "60", "paymentFrequency": "Monthly", "dateofLastPayment": "11/08/2017", "ownershipIndicator": "Joint", "actualPaymentAmount": "16,883", "dateOpened_Disbursed": "20/05/2015", "paymentHistoryEndDate": "01/05/2015", "paymentHistoryStartDate": "01/08/2017", "dateReportedandCertified": "31/08/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "8,10,000"}, {"dateClosed": "05/12/2013", "accountType": "Credit Card", "currentBalance": "0", "paymentHistory1": "000    000    341    312    306    277    246    216    185    155    124    069    038    000    000    XXX    XXX    XXX    ", "paymentHistory2": "XXX    000    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    ", "ownershipIndicator": "Authorised User", "dateOpened_Disbursed": "13/08/2006", "paymentHistoryEndDate": "01/01/2011", "paymentHistoryStartDate": "01/12/2013", "dateReportedandCertified": "24/04/2014", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "3,710"}]',
 1: '[{"cashLimit": "3,000", "accountType": "Credit Card", "creditLimit": "30,000", "amountOverdue": "1,331", "currentBalance": "4,336", "paymentHistory1": "093    063    033    003    003    000    003    003    003    003    003    000    000    003    000    003    003    003    ", "paymentHistory2": "003    000    000    003    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ", "paymentFrequency": "Monthly", "dateofLastPayment": "07/07/2017", "ownershipIndicator": "Individual", "dateOpened_Disbursed": "27/08/2013", "paymentHistoryEndDate": "01/12/2014", "paymentHistoryStartDate": "01/11/2017", "dateReportedandCertified": "03/11/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "34,051"}, {"emiAmount": "11,288", "accountType": "Personal Loan", "amountOverdue": "31,728", "currentBalance": "3,92,459", "rateOfInterest": "12.00", "paymentHistory1": "089    029    STD    STD    STD    STD    000    000    000    000    000    000    000    000    000    000    000    000    ", "paymentHistory2": "000    000    000    ", "repaymentTenure": "60", "paymentFrequency": "Monthly", "dateofLastPayment": "12/04/2017", "ownershipIndicator": "Individual", "dateOpened_Disbursed": "12/01/2016", "paymentHistoryEndDate": "01/02/2016", "paymentHistoryStartDate": "01/10/2017", "dateReportedandCertified": "31/10/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "5,00,000"}, {"dateClosed": "11/07/2017", "accountType": "Business Loan – General", "accountNumber": "LK0000010410", "currentBalance": "0", "paymentHistory1": "000    000    000    000    ", "paymentFrequency": "Monthly", "ownershipIndicator": "Individual", "dateOpened_Disbursed": "10/04/2017", "paymentHistoryEndDate": "01/04/2017", "paymentHistoryStartDate": "01/07/2017", "dateReportedandCertified": "31/07/2017", "reportingMemberShortName": "AADRILTD", "highCredit_SanctionedAmount": "1,00,000"}, {"accountType": "Auto Loan (Personal)", "currentBalance": "10,65,245", "paymentHistory1": "000    000    000    000    000    000    000    000    000    000    000    000    000    000    ", "dateofLastPayment": "12/09/2017", "ownershipIndicator": "Guarantor", "dateOpened_Disbursed": "25/08/2016", "paymentHistoryEndDate": "01/08/2016", "paymentHistoryStartDate": "01/09/2017", "dateReportedandCertified": "30/09/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "14,00,000"}, {"accountType": "Auto Loan (Personal)", "currentBalance": "3,74,330", "paymentHistory1": "000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ", "paymentHistory2": "000    ", "dateofLastPayment": "12/09/2017", "ownershipIndicator": "Joint", "dateOpened_Disbursed": "21/03/2016", "paymentHistoryEndDate": "01/03/2016", "paymentHistoryStartDate": "01/09/2017", "dateReportedandCertified": "30/09/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "7,00,000"}, {"accountType": "Credit Card", "creditLimit": "1,25,000", "currentBalance": "71,670", "paymentHistory1": "000    005    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ", "paymentHistory2": "XXX    000    XXX    000    000    000    000    ", "paymentFrequency": "Monthly", "dateofLastPayment": "02/10/2017", "ownershipIndicator": "Individual", "actualPaymentAmount": "6,884", "dateOpened_Disbursed": "30/10/2015", "paymentHistoryEndDate": "01/10/2015", "paymentHistoryStartDate": "01/10/2017", "dateReportedandCertified": "31/10/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "1,14,344"}, {"accountType": "Credit Card", "currentBalance": "11,036", "paymentHistory1": "000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ", "paymentHistory2": "000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ", "dateofLastPayment": "02/10/2017", "ownershipIndicator": "Individual", "dateOpened_Disbursed": "26/10/2014", "paymentHistoryEndDate": "01/11/2014", "paymentHistoryStartDate": "01/10/2017", "dateReportedandCertified": "13/10/2017", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "26,102"}, {"dateClosed": "03/11/2016", "accountType": "Auto Loan (Personal)", "currentBalance": "0", "paymentHistory1": "000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ", "paymentHistory2": "000    000    000    000    000    XXX    000    000    000    000    000    000    ", "dateofLastPayment": "28/10/2016", "ownershipIndicator": "Guarantor", "dateOpened_Disbursed": "25/06/2014", "paymentHistoryEndDate": "01/06/2014", "paymentHistoryStartDate": "01/11/2016", "dateReportedandCertified": "30/11/2016", "reportingMemberShortName": "NOT DISCLOSED", "highCredit_SanctionedAmount": "10,27,000"}]'
}

note that outer key of dictionary is integer starting from 0,1,2.. inner values is array of key value pairs but all are in string format even numbers

show sample output dataframe. It will good to see your code attempt as well. — pyeR_biz
– pyeR_biz, Commented Aug 4, 2021 at 7:12

mozway · Accepted Answer · 2021-08-04 07:13:18Z

Use pandas.read_json and pandas.concat:

pd.concat({k: pd.read_json(v) for k,v in My_dict.items()})

output:

                 accountType currentBalance                                                                                                                 paymentHistory1 dateofLastPayment ownershipIndicator dateOpened_Disbursed paymentHistoryEndDate paymentHistoryStartDate dateReportedandCertified reportingMemberShortName highCredit_SanctionedAmount  dateClosed accountNumber paymentFrequency creditLimit                                                                                                                 paymentHistory2 actualPaymentAmount emiAmount  rateOfInterest  repaymentTenure cashLimit amountOverdue
0 0            Consumer Loan          9,250                                                                                      000    000    000    000    000    000            02/09/2017         Individual           14/04/2017            01/04/2017              01/09/2017               30/09/2017            NOT DISCLOSED                      18,500         NaN           NaN              NaN         NaN                                                                                                                             NaN                 NaN       NaN             NaN              NaN       NaN           NaN
  1  Business Loan – General              0                                                                                                    000    000    000    000                   NaN         Individual           10/04/2017            01/04/2017              01/07/2017               31/07/2017                 AADRILTD                    1,00,000  11/07/2017  LK0000010410          Monthly         NaN                                                                                                                             NaN                 NaN       NaN             NaN              NaN       NaN           NaN
  2              Credit Card         26,841                              000    000    000    000    000    000    000    000    000    000    000    XXX    000    000            15/09/2017         Individual           03/07/2016            01/08/2016              01/09/2017               22/09/2017            NOT DISCLOSED                      75,102         NaN           NaN              NaN         NaN                                                                                                                             NaN                 NaN       NaN             NaN              NaN       NaN           NaN
  3              Credit Card         12,561  000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    XXX            30/09/2017         Individual           25/06/2015            01/06/2015              01/09/2017               30/09/2017            NOT DISCLOSED                      28,409         NaN           NaN          Monthly      27,000                                                          000    XXX    000    000    000    000    000    000    000    000                   9,000       NaN             NaN              NaN       NaN           NaN
  4     Auto Loan (Personal)       4,89,730  000    000    XXX    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000            11/08/2017              Joint           20/05/2015            01/05/2015              01/08/2017               31/08/2017            NOT DISCLOSED                    8,10,000         NaN           NaN          Monthly         NaN                                                          019    021    000    000    000    000    XXX    000    000    000                  16,883    16,883            9.17             60.0       NaN           NaN
  5              Credit Card              0  000    000    341    312    306    277    246    216    185    155    124    069    038    000    000    XXX    XXX    XXX                   NaN    Authorised User           13/08/2006            01/01/2011              01/12/2013               24/04/2014            NOT DISCLOSED                       3,710  05/12/2013           NaN              NaN         NaN  XXX    000    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX    XXX                     NaN       NaN             NaN              NaN       NaN           NaN
1 0              Credit Card          4,336  093    063    033    003    003    000    003    003    003    003    003    000    000    003    000    003    003    003            07/07/2017         Individual           27/08/2013            01/12/2014              01/11/2017               03/11/2017            NOT DISCLOSED                      34,051         NaN           NaN          Monthly      30,000  003    000    000    003    000    000    000    000    000    000    000    000    000    000    000    000    000    000                     NaN       NaN             NaN              NaN     3,000         1,331
  1            Personal Loan       3,92,459  089    029    STD    STD    STD    STD    000    000    000    000    000    000    000    000    000    000    000    000            12/04/2017         Individual           12/01/2016            01/02/2016              01/10/2017               31/10/2017            NOT DISCLOSED                    5,00,000         NaN           NaN          Monthly         NaN                                                                                                           000    000    000                     NaN    11,288           12.00             60.0       NaN        31,728
  2  Business Loan – General              0                                                                                                    000    000    000    000                   NaN         Individual           10/04/2017            01/04/2017              01/07/2017               31/07/2017                 AADRILTD                    1,00,000  11/07/2017  LK0000010410          Monthly         NaN                                                                                                                             NaN                 NaN       NaN             NaN              NaN       NaN           NaN
  3     Auto Loan (Personal)      10,65,245                              000    000    000    000    000    000    000    000    000    000    000    000    000    000            12/09/2017          Guarantor           25/08/2016            01/08/2016              01/09/2017               30/09/2017            NOT DISCLOSED                   14,00,000         NaN           NaN              NaN         NaN                                                                                                                             NaN                 NaN       NaN             NaN              NaN       NaN           NaN
  4     Auto Loan (Personal)       3,74,330  000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000            12/09/2017              Joint           21/03/2016            01/03/2016              01/09/2017               30/09/2017            NOT DISCLOSED                    7,00,000         NaN           NaN              NaN         NaN                                                                                                                         000                     NaN       NaN             NaN              NaN       NaN           NaN
  5              Credit Card         71,670  000    005    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000            02/10/2017         Individual           30/10/2015            01/10/2015              01/10/2017               31/10/2017            NOT DISCLOSED                    1,14,344         NaN           NaN          Monthly    1,25,000                                                                               XXX    000    XXX    000    000    000    000                   6,884       NaN             NaN              NaN       NaN           NaN
  6              Credit Card         11,036  000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000            02/10/2017         Individual           26/10/2014            01/11/2014              01/10/2017               13/10/2017            NOT DISCLOSED                      26,102         NaN           NaN              NaN         NaN  000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000                     NaN       NaN             NaN              NaN       NaN           NaN
  7     Auto Loan (Personal)              0  000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000            28/10/2016          Guarantor           25/06/2014            01/06/2014              01/11/2016               30/11/2016            NOT DISCLOSED                   10,27,000  03/11/2016           NaN              NaN         NaN                                            000    000    000    000    000    XXX    000    000    000    000    000    000                     NaN       NaN             NaN              NaN       NaN           NaN

NB. check that your json is correct, especially regarding the separator

It throws error when i run on complete dictionary. error : Invalid file path or buffer object type: <class 'float'>

Collectives™ on Stack Overflow

I need to convert a nested array of dictionary to new Dataframe in pandas python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related