Ak Suhail
Reputation: 11
I need to convert a nested array of dictionary to new Dataframe in pandas python
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
Upvotes: 0
Views: 68
Answers (1)
mozway
Reputation: 260690
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
Upvotes: 1
Related Questions
- How to create pandas dataframe from nested json with dictionary
- Convert json nested dictionary to pandas dataframe
- Data Frame to nested dictionary: Python
- how to convert nested dict to dataframe using python?
- How to convert pandas dataframe into nested dictionary or json?
- How to convert a nested dict into dataframe
- Pandas convert Dataframe to Nested Json
- How to convert dataframe to nested dictionary?
- Converting pandas dataframe to nested dictionary/json format
- How to convert nested dictionaries to a pandas DataFrame?