Read single cell of a csv file which has data in JSON format and convert it as a dataframe (columnar) using R

Question

I need to read a csv file, which has one header and one cell of data, which is in JSON format. Need to read the JSON data (from that cell) and convert it into dataframe using R. Though I found a similar question online, but I do not find a working solutions. Pls help. I tried the below code, but it didnt work, no idea what I miss here.

install.packages("rjson")
library(rjson)

myData <- read.csv("sampleData.csv", nrows=1,skip = 1)
json_data_frame <- as.data.frame(myData)

dat_r = toJSON(myData)
dat_c = toJSON(myData)
dat_v = toJSON(myData)

Data in that cell:

{
  "sample": {
    "leadID": "",
    "branchID": "",
    "leadType": "",
    "fundingType": "",
    "createdDate": "",
    "outFlowDetails": [
      {
        "customersub_cat": "",
        "repayableInMonths": ,
        "amountRequested": ,
        "financeAmount": ,
        "moratorium": ,
        "emiPattern": "",
        "emiLtv": ,
        "netAssetCost": ,
        "vehicleDetail": {
          "YOM": ,
          "manufactureID": "",
          "makeID": "",
          "modelID": "",
          "schemeID": "",
          "vehicleType": "",
          "isPreDelivered": ,
          "agreementNo": "",
          "noOfOwners": 
        }
      },
      {
        "customersub_cat": "",
        "repayableInMonths": ,
        "amountRequested": ,
        "financeAmount": ,
        "moratorium": ,
        "emiPattern": "",
        "emiLtv": ,
        "netAssetCost": ,
        "vehicleDetail": {
          "YOM": ,
          "manufactureID": "",
          "makeID": "",
          "modelID": "",
          "schemeID": "",
          "vehicleType": "",
          "isPreDelivered": ,
          "agreementNo": "",
          "noOfOwners": 
        }
      }
    ],
    "priorVehicleSummary": {
      "fleetSize": ,
      "noOfFreeVehicle": ,
      "vehicleAddedLastOneYear": 
    },
    "partyDetails": [
      {
        "partyID": "",
        "partyType": "",
        "coBorrowerRelationship": "",
        "age": ,
        "customerType": "",
        "category": "",
        "negativeList": "",
        "negativeArea": "",
        "industry": "",
        "natureOfBuisness": "",
        "trustingSocialScore": "",
        "SherlockRules": [
          "",
          "",
          "",
          "",
          ""
        ],
        "documents": [
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          }
        ],
        "properties": [
          {
            "ownership": ""
          },
          {
            "ownership": ""
          }
        ],
        "occupation": {
          "businessNature": "",
          "yearsInBusiness": 
        },
        "addressDetails": [
          {
            "CurrentAddress": 
          },
          {
            "CurrentAddress": 
          },
          {
            "CurrentAddress": 
          }
        ],
        "ScoreSG_": {
          "Length": "",
          "ScoreName": "",
          "ScoreCardName": "",
          "ScoreCardVersion": "",
          "ScoreDate": "",
          "Score": "",
          "reason1FL": "",
          "reason1": "",
          "reason2FL": "",
          "reason2": "",
          "reason3FL": "",
          "reason3": "",
          "reason4FL": "",
          "reason4": "",
          "reason5FL": "",
          "reason5": ""
        },
        "acc": [
          {
            "Length": "",
            "SG_Tag": "",
            "acc_Summary_SG__Fields": {
              "ShrtNameFL": ""
            },
            "acc_NonSummary_SG__Fields": {
              "ShrtNameFL": "",
              "ShrtName": "",
              "accType": "",
              "OwnershipIndicator": "",
              "DateOpenedOrDisbursed": "",
              "DateOfLastPayment": "",
              "DateReportedAndCertified": "",
              "AmtFL": "",
              "Amt": "",
              "CB_FL": "",
              "CB_": "",
              "LastPaid1FL": "",
              "LastPaid1": "",
              "LastPaidStartDate": "",
              "LastPaidEndDate": "",
              "CL_FL": "",
              "CL_": ""
            }
          }
          
        ],
        "Enq": [
          {
            "Length": "",
            "SG_Tag": "",
            "DateOfEnqFields": "",
            "EnquiringMemberShortNameFL": "",
            "EnquiringMemberShortName": "",
            "EnqPurpose": "",
            "EnqAmountFL": "",
            "EnqAmount": ""
          }
        ]
      },
     {
        "partyID": "",
        "partyType": "",
        "coBorrowerRelationship": "",
        "age": ,
        "customerType": "",
        "category": "",
        "negativeList": "",
        "negativeArea": "",
        "industry": "",
        "natureOfBuisness": "",
        "trustingSocialScore": "",
        "SherlockRules": [
          "",
          "",
          "",
          "",
          ""
        ],
        "documents": [
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          },
          {
            "sub_cat": "",
            "ProofValue": ""
          }
        ],
        "properties": [
          {
            "ownership": ""
          },
          {
            "ownership": ""
          }
        ],
        "occupation": {
          "businessNature": "",
          "yearsInBusiness": 
        },
        "addressDetails": [
          {
            "CurrentAddress": 
          },
          {
            "CurrentAddress": 
          },
          {
            "CurrentAddress": 
          }
        ],
        "ScoreSG_": {
          "Length": "",
          "ScoreName": "",
          "ScoreCardName": "08",
          "ScoreCardVersion": "10",
          "ScoreDate": "21102020",
          "Score": "00622",
          "reason1FL": "02",
          "reason1": "04",
          "reason2FL": "02",
          "reason2": "32",
          "reason3FL": "02",
          "reason3": "28",
          "reason4FL": "02",
          "reason4": "31",
          "reason5FL": "02",
          "reason5": "29"
        },
        "acc": [
          {
            "Length": "04",
            "SG_Tag": "T001",
            "acc_Summary_SG__Fields": {
              "ShrtNameFL": "13"
            },
            "acc_NonSummary_SG__Fields": {
              "ShrtNameFL": "13",
              "ShrtName": "NOT DISCLOSED",
              "accType": "10",
              "OwnershipIndicator": "1",
              "DateOpenedOrDisbursed": "09012020",
              "DateOfLastPayment": "07082020",
              "DateReportedAndCertified": "20082020",
              "AmtFL": "05",
              "Amt": "77159",
              "CB_FL": "01",
              "CB_": "0",
              "LastPaid1FL": "21",
              "LastPaid1": "000000000XXX000000000",
              "LastPaidStartDate": "01082020",
              "LastPaidEndDate": "01022020",
              "CL_FL": "05",
              "CL_": "80000"
            }
          }
          
        ],
        "Enq": [
          {
            "Length": "04",
            "SG_Tag": "I001",
            "DateOfEnqFields": "26022020",
            "EnquiringMemberShortNameFL": "13",
            "EnquiringMemberShortName": "NOT DISCLOSED",
            "EnqPurpose": "02",
            "EnqAmountFL": "07",
            "EnqAmount": "3500000"
          }
        ]
      } 
    ]
  }
}

use `fromJSON()` instead of `toJSON()` and make sure to point it at the cell and not the full `data.frame`. Something like `fromJSON(myData[[1]])` should work. — Till, Apr 20 '21 at 20:42
@Till Thanks for your response. I tried, but it doesn't work. > dat_r = fromJSON(myData) Error in fromJSON(myData) : STRING_ELT() can only be applied to a 'character vector', not a 'list' > dat_r = fromJSON(myData[[1]]) Error in fromJSON(myData[[1]]) : STRING_ELT() can only be applied to a 'character vector', not a 'logical' > dat_c = toJSON(myData[[1]]) > dat_v = toJSON(myData[[1]]) > head(dat_c) [1] "[]" > head(dat_v) [1] "[]" — Arun, Apr 20 '21 at 20:50
It should work, if your data matches your description. `myData[[1]]` points at the first cell of the data.frame. I expected the JSON data to be in that cell, based on your description, but from the error messages you get it looks like there is a `logical` there. `fromJson$name_of_JSON_column[1]` should work, if you replace `name_of_JSON_column` with the actual name of that column. — Till, Apr 20 '21 at 21:10
Also it seems that the JSON you posted in your question is not valid, since it has a bunch of empty list entries. This might be an issue created by copy and pasting the data. You can check for the validity of the JSON data with [https://jsonlint.com/](https://jsonlint.com/). — Till, Apr 20 '21 at 21:26

Read single cell of a csv file which has data in JSON format and convert it as a dataframe (columnar) using R

0 Answers0