I want to read json file into pandas and make a data frame for further analysis like reject inferencing.
{
"loans": [
{
"loanId": "11111111111111111",
"TransactionStatus": {
"ResponseCode": "0",
"ResponseMessage": "Success",
"ExtendedErrorDetails": {
"messages": {
"code": "0",
"message": "Success"
}
}
},
"DCResponse": {
"Status": "Success",
"Authentication": {
"Status": "Success",
"Token": "222222222222222222222222222222222"
},
"ResponseInfo": {
"ApplicationId": "123456789",
"SolutionSetInstanceId": "3333333333333333333333333333338"
},
"ContextData": {
"Applicants": {
"Applicant": {
"Accounts": {
"Account": null
},
"Addresses": {
"Address": [
{
"StateCode": "23",
"PinCode": "xxxxxxxxx",
"City": "xxxx",
"AddressType": "02",
"AddressLine3": "xx",
"AddressLine2": "xxx",
"AddressLine1": "xxxx"
},
null
]
},
"Telephones": {
"Telephone": [
{
"TelephoneType": "01",
"TelephoneNumber": "xxxxxxxxxx"
},
{
"TelephoneType": "02"
}
]
},
"Identifiers": {
"Identifier": [
{
"IdType": "01",
"IdNumber": "xxxxxxxx"
},
{
"IdType": "02"
}
]
},
"EmailAddress": "xxxxxxxxx",
"Gender": "Male",
"DateOfBirth": "000000000",
"ApplicantLastName": "xxxxx",
"ApplicantFirstName": "xxxxxx",
"ApplicantType": "xxxxxxxx",
"NodeIdentifier": "0",
"DsCibilBureau": {
"DsCibilBureauData": {
"Request": {
"Request": {
"ConsumerDetails": {
"CreditReportInquiry": {
"Header": {
"SegmentTag": " TUEF ",
"Version": " 12 ",
"ReferenceNumber": "234444444444",
"MemberCode": "333333333333333333",
"Password": null,
"Purpose": "05",
"Amount": "100",
"ScoreType": "08",
"OutputFormat": "01",
"ResponseSize": "1",
"MediaType": "CC",
"AuthenticationMethod": "L"
},
"Names": {
"Name": {
"ConsumerName1": "xxxxx",
"ConsumerName3": "xxxxxx",
"DateOfBirth": "000000000",
"Gender": "xxxxxx"
}
},
"Identifications": {
"Identification": {
"PanNo": "xxxxxxxxxxxx",
"PassportNumber": null,
"DLNo": null,
"VoterId": null,
"UId": null,
"RationCardNo": null,
"AdditionalID1": null,
"AdditionalID2": null
}
},
"Telephones": {
"Telephone": {
"TelephoneNumber": "xxxxxxxxxx",
"TelephoneExtension": null,
"TelephoneType": "01"
}
},
"Addresses": {
"Address": {
"AddressLine1": "xxxxxxxx",
"AddressLine2": "d,,xxxxx",
"AddressLine3": "Default",
"AddressLine4": null,
"AddressLine5": "xxxxxx",
"StateCode": "23",
"PinCode": "1111111",
"AddressCategory": "02",
"ResidenceCode": null
}
}
}
}
}
}
},
"DsCibilBureauStatus": null,
"Response": {
"CibilBureauResponse": {
"BureauResponseRaw": "xxxxxxxxxx**",
"BureauResponseXml": {
"FailureResponse": null,
"SucessResponse": {
"Header": [
{
"SegmentTag": "TUEF",
"Version": "12",
"ReferenceNumber": "353867102",
"MemberCode": "333333333333333333",
"SubjectReturnCode": "1",
"EnquiryControlNumber": "dddddddddddd",
"DateProcessed": "01032023",
"TimeProcessed": "022959"
}
],
"NameSegment": [
{
"SegmentTag": "N01",
"Length": "03",
"ConsumerName1FieldLength": "05",
"ConsumerName1": "xxxx",
"ConsumerName2FieldLength": "06",
"ConsumerName2": "xxxxx",
"DateOfBirthFieldLength": "08",
"DateOfBirth": "00000000",
"GenderFieldLength": "01",
"Gender": "2"
}
],
"IDSegment": [
{
"SegmentTag": "I01",
"Length": "03",
"IDType": "01",
"IDNumberFieldLength": "10",
"IDNumber": "xxxxxxx"
}
],
"TelephoneSegment": [
{
"SegmentTag": "T01",
"Length": "03",
"TelephoneNumberFieldLength": "10",
"TelephoneNumber": "000000000000",
"TelephoneType": "01"
},
{
"SegmentTag": "T02",
"Length": "03",
"TelephoneNumberFieldLength": "10",
"TelephoneNumber": "0000000000",
"TelephoneType": "01"
}
],
"ScoreSegment": [
{
"Length": "10",
"ScoreName": "CIBILTUSC3",
"ScoreCardName": "08",
"ScoreCardVersion": "10",
"ScoreDate": "01032023",
"Score": "00719",
"ReasonCode1FieldLength": "02",
"ReasonCode1": "27",
"ReasonCode2FieldLength": "02",
"ReasonCode2": "25",
"ReasonCode3FieldLength": "02",
"ReasonCode3": "28",
"ReasonCode4FieldLength": "02",
"ReasonCode4": "15",
"ReasonCode5FieldLength": null,
"ReasonCode5": null
}
],
"Address": [
{
"AddressSegmentTag": "PA",
"Length": "03",
"AddressLine1FieldLength": "18",
"AddressLine1": "xxxxx",
"AddressLine2FieldLength": "18",
"AddressLine2": "xxxxxxxxR",
"StateCode": "23",
"PinCodeFieldLength": "06",
"PinCode": "452001",
"AddressCategory": "02",
"DateReported": "31052022"
},
{
"AddressSegmentTag": "PA",
"Length": "03",
"AddressLine1FieldLength": "40",
"AddressLine1": "xxxxxxx",
"AddressLine2FieldLength": "23",
"AddressLine2": "xxxxxxxE",
"StateCode": "23",
"PinCodeFieldLength": "06",
"PinCode": "452009",
"AddressCategory": "02",
"DateReported": "19042019"
}
],
"Account": [
{
"Length": "04",
"SegmentTag": "T001",
"Account_Summary_Segment_Fields": [
{
"ReportingMemberShortNameFieldLength": "13"
}
],
"Account_NonSummary_Segment_Fields": [
{
"ReportingMemberShortNameFieldLength": "13",
"ReportingMemberShortName": "NOT DISCLOSED",
"AccountType": "02",
"OwenershipIndicator": "4",
"DateOpenedOrDisbursed": "30012023",
"DateReportedAndCertified": "31012023",
"HighCreditOrSanctionedAmountFieldLength": "07",
"HighCreditOrSanctionedAmount": "1500000",
"CurrentBalanceFieldLength": "07",
"CurrentBalance": "1200565",
"PaymentHistory1FieldLength": "03",
"PaymentHistory1": "STD",
"PaymentHistoryStartDate": "01012023",
"PaymentHistoryEndDate": "01012023",
"ValueOfCollateralFieldLength": "07",
"ValueOfCollateral": "3850000",
"TypeOfCollateralFieldLength": "02",
"TypeOfCollateral": "01",
"RateOfInterestFieldLength": "04",
"RateOfInterest": "9.00",
"RepaymentTenureFieldLength": "03",
"RepaymentTenure": "106",
"EmiAmountFieldLength": "05",
"EmiAmount": "16199",
"PaymentFrequency": "03"
}
]
},
{
"Length": "04",
"SegmentTag": "T002",
"Account_Summary_Segment_Fields": [
{
"ReportingMemberShortNameFieldLength": "13"
}
],
"Account_NonSummary_Segment_Fields": [
{
"ReportingMemberShortNameFieldLength": "13",
"ReportingMemberShortName": "NOT DISCLOSED",
"AccountType": "00",
"OwenershipIndicator": "4",
"DateOpenedOrDisbursed": "30012023",
"DateReportedAndCertified": "30012023",
"HighCreditOrSanctionedAmountFieldLength": "07",
"HighCreditOrSanctionedAmount": "1500000",
"CurrentBalanceFieldLength": "07",
"CurrentBalance": "1200000",
"PaymentHistory1FieldLength": "03",
"PaymentHistory1": "STD",
"PaymentHistoryStartDate": "01012023",
"PaymentHistoryEndDate": "01012023",
"ValueOfCollateralFieldLength": "07",
"ValueOfCollateral": "3850000",
"TypeOfCollateralFieldLength": "02",
"TypeOfCollateral": "01",
"RateOfInterestFieldLength": "04",
"RateOfInterest": "9.00",
"RepaymentTenureFieldLength": "03",
"RepaymentTenure": "106",
"EmiAmountFieldLength": "05",
"EmiAmount": "20249",
"PaymentFrequency": "03"
}
]
},
{
"Length": "04",
"SegmentTag": "T003",
"Account_Summary_Segment_Fields": [
{
"ReportingMemberShortNameFieldLength": "13"
}
],
"Account_NonSummary_Segment_Fields": [
{
"ReportingMemberShortNameFieldLength": "13",
"ReportingMemberShortName": "NOT DISCLOSED",
"AccountType": "12",
"OwenershipIndicator": "1",
"DateOpenedOrDisbursed": "30052022",
"DateOfLastPayment": "30052022",
"DateReportedAndCertified": "30062022",
"CurrentBalanceFieldLength": "01",
"CurrentBalance": "0",
"PaymentHistory1FieldLength": "06",
"PaymentHistory1": "000000",
"PaymentHistoryStartDate": "01062022",
"PaymentHistoryEndDate": "01052022"
}
]
},
{
"Length": "04",
"SegmentTag": "T004",
"Account_Summary_Segment_Fields": [
{
"ReportingMemberShortNameFieldLength": "13"
}
],
"Account_NonSummary_Segment_Fields": [
{
"ReportingMemberShortNameFieldLength": "13",
"ReportingMemberShortName": "NOT DISCLOSED",
"AccountType": "00",
"OwenershipIndicator": "1",
"DateOpenedOrDisbursed": "27042022",
"DateOfLastPayment": "02012023",
"DateReportedAndCertified": "31012023",
"HighCreditOrSanctionedAmountFieldLength": "04",
"HighCreditOrSanctionedAmount": "4000",
"CurrentBalanceFieldLength": "01",
"CurrentBalance": "0",
"PaymentHistory1FieldLength": "30",
"PaymentHistory1": "000000000000000000000000000000",
"PaymentHistoryStartDate": "01012023",
"PaymentHistoryEndDate": "01042022"
}
]
}
],
"Enquiry": [
{
"Length": "04",
"SegmentTag": "I001",
"DateOfEnquiryFields": "21012023",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "02",
"EnquiryAmountFieldLength": "07",
"EnquiryAmount": "1500000"
},
{
"Length": "04",
"SegmentTag": "I002",
"DateOfEnquiryFields": "14122022",
"EnquiringMemberShortNameFieldLength": "06",
"EnquiringMemberShortName": "AU SFB",
"EnquiryPurpose": "10",
"EnquiryAmountFieldLength": "03",
"EnquiryAmount": "100"
},
{
"Length": "04",
"SegmentTag": "I003",
"DateOfEnquiryFields": "03122022",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "02",
"EnquiryAmountFieldLength": "01",
"EnquiryAmount": "1"
},
{
"Length": "04",
"SegmentTag": "I004",
"DateOfEnquiryFields": "16112022",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "02",
"EnquiryAmountFieldLength": "06",
"EnquiryAmount": "500000"
},
{
"Length": "04",
"SegmentTag": "I005",
"DateOfEnquiryFields": "16112022",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "02",
"EnquiryAmountFieldLength": "06",
"EnquiryAmount": "500000"
},
{
"Length": "04",
"SegmentTag": "I006",
"DateOfEnquiryFields": "13082022",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "00",
"EnquiryAmountFieldLength": "01",
"EnquiryAmount": "1"
},
{
"Length": "04",
"SegmentTag": "I007",
"DateOfEnquiryFields": "17062022",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "00",
"EnquiryAmountFieldLength": "01",
"EnquiryAmount": "1"
},
{
"Length": "04",
"SegmentTag": "I008",
"DateOfEnquiryFields": "31122021",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "06",
"EnquiryAmountFieldLength": "05",
"EnquiryAmount": "13990"
},
{
"Length": "04",
"SegmentTag": "I009",
"DateOfEnquiryFields": "13122020",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "10",
"EnquiryAmountFieldLength": "04",
"EnquiryAmount": "1000"
},
{
"Length": "04",
"SegmentTag": "I010",
"DateOfEnquiryFields": "10122020",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "05",
"EnquiryAmountFieldLength": "03",
"EnquiryAmount": "100"
},
{
"Length": "04",
"SegmentTag": "I011",
"DateOfEnquiryFields": "19042019",
"EnquiringMemberShortNameFieldLength": "13",
"EnquiringMemberShortName": "NOT DISCLOSED",
"EnquiryPurpose": "06",
"EnquiryAmountFieldLength": "05",
"EnquiryAmount": "40990"
}
],
"End": [
{
"TotalLength": "0001735",
"SegmentTag": "ES07"
}
]
}
}
}
}
}
}
},
"ApplicationData": {
"FormattedReport": true,
"ConsumerConsentForUIDAIAuthentication": "N",
"NTCProductType": "PL",
"MFIBureauFlag": "True",
"IDVerificationFlag": "False",
"DSTuNtcFlag": "True",
"CibilBureauFlag": "False",
"Password": null,
"MemberCode": "NB66529999_CIRC2CNPE",
"GSTStateCode": "08",
"ScoreType": "08",
"Amount": "100",
"Purpose": "05",
"MFIDocRequest": "\n",
"ReturnMessage": "\n",
"DTTrail": {
"Step": {
"Name": "\n",
"Duration": "\n"
}
},
"Milestone": {
"Step": "\n"
},
"Start": "\n",
"InputValReasonCodes": "\n",
"User": "NB6652DC01_PROD001",
"BusinessUnitId": "23",
"ApplicationId": "353867102",
"SolutionSetId": "50",
"EnvironmentTypeId": "2",
"EnvironmentType": "Production"
},
"Decision": null,
"ApplicationId": null
}
},
"FileContent": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx=="
}
]
}
How to create Multiples columns and rows from json file and to be stored into data frame and use it for further analysis (loanId TransactionStatus AccountType OwenershipIndicator DateOpenedOrDisbursed DateOfLastPayment DateClosed DateReportedAndCertified HighCreditOrSanctionedAmountFieldLength HighCreditOrSanctionedAmount CurrentBalanceFieldLength CurrentBalance PaymentHistory1FieldLength PaymentHistory1 PaymentHistory2FieldLength PaymentHistory2 PaymentHistoryStartDate PaymentHistoryEndDate PaymentFrequency ActualPaymentAmountFieldLength ActualPaymentAmount RateOfInterest etc)
