Import data from file contaning nested JSON objects in R

Question

I'm new to programming in R, and I'm trying to parse file containing nested JSON objects and convert it to a R dataframe.

File format is as follows:

{
    "collect": [{
        "Record_Id": 32738,
        "Log_Number": 3,
        "Service_Event": "Desktop Support",
        "System_Type": "Custom Application",
        "SLA": "B",
        "SLA_Met": "No",
        "Priority": "Medium",
        "DateTime_Occured": "2017-01-18 18:57:20",
        "DateTime_Reported_Diff": 595578,
        "DateTime_Responded_Diff": 563091,
        "DateTime_Resolved_Diff": 607595,
        "Reported_By": "Finance",
        "Assigned_To": "Custom Application Team C",
        "Customer_Satisfaction": 5,
        "Region": "Europe",
        "Resolution_Effort": "Low",
        "Escalation_Level": 3,
        "Related_Problem_ID": 6897,
        "Report_Source": "Chat",
        "DateTime_Reported": "2017-01-25 16:23:38",
        "DateTime_Responded": "2017-02-01 04:48:29",
        "DateTime_Resolved": "2017-02-08 05:35:04",
        "Incident_ID": 32738,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.4993265Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:40.2000000Z"
    },

     {
        "Record_Id": 32803,
        "Log_Number": 2,
        "Service_Event": "Password Reset",
        "System_Type": "Email",
        "SLA": "B",
        "SLA_Met": "Yes",
        "Priority": "Low",
        "DateTime_Occured": "2016-04-28 08:08:18",
        "DateTime_Reported_Diff": 382084,
        "DateTime_Responded_Diff": 394858,
        "DateTime_Resolved_Diff": 429286,
        "Reported_By": "Sales",
        "Assigned_To": "Email Team B",
        "Customer_Satisfaction": 3,
        "Region": "Americas",
        "Resolution_Effort": "Medium",
        "Escalation_Level": 1,
        "Related_Problem_ID": 4768,
        "Report_Source": "Phone",
        "DateTime_Reported": "2016-05-02 18:16:22",
        "DateTime_Responded": "2016-05-07 07:57:20",
        "DateTime_Resolved": "2016-05-12 07:12:06",
        "Incident_ID": 32803,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.5149287Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:46.0650000Z"
    }, 

     {
        "Record_Id": 32804,
        "Log_Number": 1,
        "Service_Event": "Password Reset",
        "System_Type": "Custom Application",
        "SLA": "B",
        "SLA_Met": "No",
        "Priority": "Medium",
        "DateTime_Occured": "2016-10-11 08:50:33",
        "DateTime_Reported_Diff": 541423,
        "DateTime_Responded_Diff": 468816,
        "DateTime_Resolved_Diff": 573017,
        "Reported_By": "Sales",
        "Assigned_To": "Custom Application Team C",
        "Customer_Satisfaction": 4,
        "Region": "Americas",
        "Resolution_Effort": "Low",
        "Escalation_Level": 1,
        "Related_Problem_ID": 6916,
        "Report_Source": "Phone",
        "DateTime_Reported": "2016-10-17 15:14:16",
        "DateTime_Responded": "2016-10-23 01:27:52",
        "DateTime_Resolved": "2016-10-29 16:38:09",
        "Incident_ID": 32804,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.5149287Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:46.1110000Z"
    }, {
        "Record_Id": 32809,
        "Log_Number": 1,
        "Service_Event": "Password Reset",
        "System_Type": "Email",
        "SLA": "B",
        "SLA_Met": "No",
        "Priority": "Low",
        "DateTime_Occured": "2016-05-23 13:00:28",
        "DateTime_Reported_Diff": 454176,
        "DateTime_Responded_Diff": 475141,
        "DateTime_Resolved_Diff": 453268,
        "Reported_By": "Sales",
        "Assigned_To": "Email Team B",
        "Customer_Satisfaction": 4,
        "Region": "Americas",
        "Resolution_Effort": "Low",
        "Escalation_Level": 1,
        "Related_Problem_ID": 5081,
        "Report_Source": "Phone",
        "DateTime_Reported": "2016-05-28 19:10:04",
        "DateTime_Responded": "2016-06-03 07:09:05",
        "DateTime_Resolved": "2016-06-08 13:03:33",
        "Incident_ID": 32809,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.5149287Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:47.0380000Z"
    }, {
        "Record_Id": 32814,
        "Log_Number": 2,
        "Service_Event": "Password Reset",
        "System_Type": "Network",
        "SLA": "B",
        "SLA_Met": "Yes",
        "Priority": "Low",
        "DateTime_Occured": "2016-01-23 08:12:12",
        "DateTime_Reported_Diff": 261519,
        "DateTime_Responded_Diff": 385940,
        "DateTime_Resolved_Diff": 264070,
        "Reported_By": "Supply Chain",
        "Assigned_To": "Network Team B",
        "Customer_Satisfaction": 3,
        "Region": "Americas",
        "Resolution_Effort": "Medium",
        "Escalation_Level": 2,
        "Related_Problem_ID": 3334,
        "Report_Source": "Phone",
        "DateTime_Reported": "2016-01-26 08:50:51",
        "DateTime_Responded": "2016-01-30 20:03:11",
        "DateTime_Resolved": "2016-02-02 21:24:21",
        "Incident_ID": 32814,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.5149287Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:47.9450000Z"
    }, {
        "Record_Id": 32821,
        "Log_Number": 3,
        "Service_Event": "Desktop Support",
        "System_Type": "Server",
        "SLA": "B",
        "SLA_Met": "No",
        "Priority": "Medium",
        "DateTime_Occured": "2017-01-18 01:11:32",
        "DateTime_Reported_Diff": 584226,
        "DateTime_Responded_Diff": 502059,
        "DateTime_Resolved_Diff": 583814,
        "Reported_By": "Finance",
        "Assigned_To": "Server Team D",
        "Customer_Satisfaction": 5,
        "Region": "Americas",
        "Resolution_Effort": "Low",
        "Escalation_Level": 3,
        "Related_Problem_ID": 7399,
        "Report_Source": "Chat",
        "DateTime_Reported": "2017-01-24 19:28:38",
        "DateTime_Responded": "2017-01-30 14:56:17",
        "DateTime_Resolved": "2017-02-06 09:06:31",
        "Incident_ID": 32821,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.5149287Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:47.9920000Z"
    }, {
        "Record_Id": 32822,
        "Log_Number": 1,
        "Service_Event": "Password Reset",
        "System_Type": "Email",
        "SLA": "B",
        "SLA_Met": "Yes",
        "Priority": "Medium",
        "DateTime_Occured": "2016-10-17 18:14:33",
        "DateTime_Reported_Diff": 461604,
        "DateTime_Responded_Diff": 418947,
        "DateTime_Resolved_Diff": 463982,
        "Reported_By": "Sales",
        "Assigned_To": "Email Team C",
        "Customer_Satisfaction": 4,
        "Region": "Americas",
        "Resolution_Effort": "Medium",
        "Escalation_Level": 1,
        "Related_Problem_ID": 5178,
        "Report_Source": "Phone",
        "DateTime_Reported": "2016-10-23 02:27:57",
        "DateTime_Responded": "2016-10-27 22:50:24",
        "DateTime_Resolved": "2016-11-02 07:43:26",
        "Incident_ID": 32822,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.5149287Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:48.0080000Z"
    }, {
        "Record_Id": 32824,
        "Log_Number": 2,
        "Service_Event": "Password Reset",
        "System_Type": "Network",
        "SLA": "B",
        "SLA_Met": "Yes",
        "Priority": "Low",
        "DateTime_Occured": "2016-04-14 13:05:53",
        "DateTime_Reported_Diff": 421053,
        "DateTime_Responded_Diff": 439267,
        "DateTime_Resolved_Diff": 388328,
        "Reported_By": "Sales",
        "Assigned_To": "Network Team B",
        "Customer_Satisfaction": 3,
        "Region": "Americas",
        "Resolution_Effort": "Low",
        "Escalation_Level": 1,
        "Related_Problem_ID": 4909,
        "Report_Source": "Email",
        "DateTime_Reported": "2016-04-19 10:03:26",
        "DateTime_Responded": "2016-04-24 12:04:33",
        "DateTime_Resolved": "2016-04-28 23:56:41",
        "Incident_ID": 32824,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.5149287Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:48.2890000Z"
    }, {
        "Record_Id": 32825,
        "Log_Number": 1,
        "Service_Event": "Password Reset",
        "System_Type": "Custom Application",
        "SLA": "B",
        "SLA_Met": "Yes",
        "Priority": "Medium",
        "DateTime_Occured": "2016-12-24 17:15:54",
        "DateTime_Reported_Diff": 569741,
        "DateTime_Responded_Diff": 464857,
        "DateTime_Resolved_Diff": 648378,
        "Reported_By": "HR",
        "Assigned_To": "Custom Application Team C",
        "Customer_Satisfaction": 4,
        "Region": "Europe",
        "Resolution_Effort": "Low",
        "Escalation_Level": 1,
        "Related_Problem_ID": 6803,
        "Report_Source": "Phone",
        "DateTime_Reported": "2016-12-31 07:31:35",
        "DateTime_Responded": "2017-01-05 16:39:12",
        "DateTime_Resolved": "2017-01-13 04:45:30",
        "Incident_ID": 32825,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.5149287Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:48.6020000Z"
    },  {
        "Record_Id": 32838,
        "Log_Number": 1,
        "Service_Event": "Desktop Support",
        "System_Type": "Email",
        "SLA": "B",
        "SLA_Met": "Yes",
        "Priority": "Medium",
        "DateTime_Occured": "2016-08-28 14:17:06",
        "DateTime_Reported_Diff": 431181,
        "DateTime_Responded_Diff": 534914,
        "DateTime_Resolved_Diff": 390543,
        "Reported_By": "HR",
        "Assigned_To": "Email Team C",
        "Customer_Satisfaction": 4,
        "Region": "Americas",
        "Resolution_Effort": "Low",
        "Escalation_Level": 1,
        "Related_Problem_ID": 5063,
        "Report_Source": "Phone",
        "DateTime_Reported": "2016-09-02 14:03:27",
        "DateTime_Responded": "2016-09-08 18:38:41",
        "DateTime_Resolved": "2016-09-13 07:07:44",
        "Incident_ID": 32838,
        "EventProcessedUtcTime": "2017-02-11T09:39:35.5149287Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2017-02-11T09:38:49.7780000Z"
    }]
}

Tried importing by saving file as CSV and txt file, imported to R and tried to covert to JSON as follows

Event <- read.table("C:/Users/dkhan/desktop/InputEvent.csv", sep = ",")

Event1 <- toJSON(Event)

jfile <- fromJSON(Event1)

recorded <- lapply(jfile$collect, function(x) {unlist(x)})  
#could not run this command, as I got the error as '$' cannot be applied to atomic vectors#

record <- do.call("rbind", recorded)

record <- as.data.frame(record)

I'm unable to parse file with JSON objects as below mentioned method.

  fromJSON( file = json_file )

as the agrument for fromJSON I'm getting is fromJSON(txt, simplifyDataframa = , Flatten = )

Please let me how would I parse the file with JSON objects.
I have file with multiple such nested JSON strings in the file. I'll have to process them through 'for' loop. Which I'm not able to do as well. Have been trying to work on this for 2-3 days, but was not successful.

Answer 1

Why not just do:

library(jsonlite)

events <- fromJSON("~/data/input_event.json")

glimpse(events$collect)
## Observations: 10
## Variables: 26
## $ Record_Id               <int> 32738, 32803, 32804, 32809, 32814, 32821, 32822, 32824, 32825, 32838
## $ Log_Number              <int> 3, 2, 1, 1, 2, 3, 1, 2, 1, 1
## $ Service_Event           <chr> "Desktop Support", "Password Reset", "Password Reset", "Password Reset", "P...
## $ System_Type             <chr> "Custom Application", "Email", "Custom Application", "Email", "Network", "S...
## $ SLA                     <chr> "B", "B", "B", "B", "B", "B", "B", "B", "B", "B"
## $ SLA_Met                 <chr> "No", "Yes", "No", "No", "Yes", "No", "Yes", "Yes", "Yes", "Yes"
## $ Priority                <chr> "Medium", "Low", "Medium", "Low", "Low", "Medium", "Medium", "Low", "Medium...
## $ DateTime_Occured        <chr> "2017-01-18 18:57:20", "2016-04-28 08:08:18", "2016-10-11 08:50:33", "2016-...
## $ DateTime_Reported_Diff  <int> 595578, 382084, 541423, 454176, 261519, 584226, 461604, 421053, 569741, 431181
## $ DateTime_Responded_Diff <int> 563091, 394858, 468816, 475141, 385940, 502059, 418947, 439267, 464857, 534914
## $ DateTime_Resolved_Diff  <int> 607595, 429286, 573017, 453268, 264070, 583814, 463982, 388328, 648378, 390543
## $ Reported_By             <chr> "Finance", "Sales", "Sales", "Sales", "Supply Chain", "Finance", "Sales", "...
## $ Assigned_To             <chr> "Custom Application Team C", "Email Team B", "Custom Application Team C", "...
## $ Customer_Satisfaction   <int> 5, 3, 4, 4, 3, 5, 4, 3, 4, 4
## $ Region                  <chr> "Europe", "Americas", "Americas", "Americas", "Americas", "Americas", "Amer...
## $ Resolution_Effort       <chr> "Low", "Medium", "Low", "Low", "Medium", "Low", "Medium", "Low", "Low", "Low"
## $ Escalation_Level        <int> 3, 1, 1, 1, 2, 3, 1, 1, 1, 1
## $ Related_Problem_ID      <int> 6897, 4768, 6916, 5081, 3334, 7399, 5178, 4909, 6803, 5063
## $ Report_Source           <chr> "Chat", "Phone", "Phone", "Phone", "Phone", "Chat", "Phone", "Email", "Phon...
## $ DateTime_Reported       <chr> "2017-01-25 16:23:38", "2016-05-02 18:16:22", "2016-10-17 15:14:16", "2016-...
## $ DateTime_Responded      <chr> "2017-02-01 04:48:29", "2016-05-07 07:57:20", "2016-10-23 01:27:52", "2016-...
## $ DateTime_Resolved       <chr> "2017-02-08 05:35:04", "2016-05-12 07:12:06", "2016-10-29 16:38:09", "2016-...
## $ Incident_ID             <int> 32738, 32803, 32804, 32809, 32814, 32821, 32822, 32824, 32825, 32838
## $ EventProcessedUtcTime   <chr> "2017-02-11T09:39:35.4993265Z", "2017-02-11T09:39:35.5149287Z", "2017-02-11...
## $ PartitionId             <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
## $ EventEnqueuedUtcTime    <chr> "2017-02-11T09:38:40.2000000Z", "2017-02-11T09:38:46.0650000Z", "2017-02-11...