I am very new to R and trying to convert the below json file into nicer dataframe
json_file <- "path_to_json_file/file.json"
json_data <- read_json(json_file, simplifyVector = TRUE)
json_data <- json_data %>% as_tibble()
glimpse(json_data)
My json file file.json:
{
"data": {
"sessionId": "AV50BX6JXG5UN0UF",
"lang": "en-us",
"disposition": {
"bestPath": [],
"allPath": []
},
"nlpEntities": {
"status": "Success",
"message": "Success",
"sessionId": "AV50BX6JXG5UN0UF",
"lang": "en-us",
"startTime": 1660813927150,
"entities": [
{
"id": "2660",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "welcome to c",
"normalizedValue": "welcome to c",
"preprocessedValue": "welcome to c",
"valid": true,
"channel": "agent",
"intentTurnNo": 0,
"turnId": [
1
],
"asrValueCharSpan": {},
"position": 6,
"contributedWords": "welcome to c",
"detectedSentence": "agent: hello welcome to c my name is christian direc spread for today may i know your name please hello welcome to c my name is christian expired for today may i know your name please im sorry i cannot hear any response on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2661",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "cannot hear",
"normalizedValue": "cannot hear",
"preprocessedValue": "cannot hear",
"valid": true,
"channel": "agent",
"intentTurnNo": 0,
"turnId": [
1
],
"asrValueCharSpan": {},
"position": 214,
"contributedWords": "cannot hear",
"detectedSentence": "agent: hello welcome to c my name is christian direc spread for today may i know your name please hello welcome to c my name is christian expired for today may i know your name please im sorry i cannot hear any response on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2601",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "line",
"normalizedValue": "line",
"preprocessedValue": "line",
"valid": true,
"channel": "agent",
"intentTurnNo": 0,
"turnId": [
1
],
"asrValueCharSpan": {},
"position": 252,
"contributedWords": "line",
"detectedSentence": "agent: hello welcome to c my name is christian direc spread for today may i know your name please hello welcome to c my name is christian expired for today may i know your name please im sorry i cannot hear any response on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2559",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "line",
"normalizedValue": "line",
"preprocessedValue": "line",
"valid": true,
"channel": "agent",
"intentTurnNo": 0,
"turnId": [
1
],
"asrValueCharSpan": {},
"position": 252,
"contributedWords": "line",
"detectedSentence": "agent: hello welcome to c my name is christian direc spread for today may i know your name please hello welcome to c my name is christian expired for today may i know your name please im sorry i cannot hear any response on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2698",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "disconnect",
"normalizedValue": "disconnect",
"preprocessedValue": "disconnect",
"valid": true,
"channel": "agent",
"intentTurnNo": 0,
"turnId": [
1
],
"asrValueCharSpan": {},
"position": 276,
"contributedWords": "disconnect",
"detectedSentence": "agent: hello welcome to c my name is christian direc spread for today may i know your name please hello welcome to c my name is christian expired for today may i know your name please im sorry i cannot hear any response on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2389",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "call",
"normalizedValue": "call",
"preprocessedValue": "call",
"valid": true,
"channel": "agent",
"intentTurnNo": 0,
"turnId": [
1
],
"asrValueCharSpan": {},
"position": 291,
"contributedWords": "call",
"detectedSentence": "agent: hello welcome to c my name is christian direc spread for today may i know your name please hello welcome to c my name is christian expired for today may i know your name please im sorry i cannot hear any response on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2615",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "call us back",
"normalizedValue": "call us back",
"preprocessedValue": "call us back",
"valid": true,
"channel": "agent",
"intentTurnNo": 0,
"turnId": [
1
],
"asrValueCharSpan": {},
"position": 303,
"contributedWords": "call us back",
"detectedSentence": "agent: hello welcome to c my name is christian direc spread for today may i know your name please hello welcome to c my name is christian expired for today may i know your name please im sorry i cannot hear any response on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "3594",
"modelType": "nlp",
"intentChannel": "customer",
"entityType": "STRING",
"asrValue": "make",
"normalizedValue": "make",
"preprocessedValue": "make",
"valid": true,
"channel": "customer",
"intentTurnNo": 1,
"turnId": [
2
],
"asrValueCharSpan": {},
"position": 8,
"contributedWords": "make",
"detectedSentence": "customer: i wanna make a call complaining"
},
{
"id": "2661",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "hear me",
"normalizedValue": "hear me",
"preprocessedValue": "hear me",
"valid": true,
"channel": "agent",
"intentTurnNo": 2,
"turnId": [
3
],
"asrValueCharSpan": {},
"position": 14,
"contributedWords": "hear me",
"detectedSentence": "agent: hello can you hear me mm hello can you hear me mm im sorry i cant hear her any response on the other line and i will need to disconnect the call please call us back thank you im sorry i cannot hear any risk once on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2601",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "line",
"normalizedValue": "line",
"preprocessedValue": "line",
"valid": true,
"channel": "agent",
"intentTurnNo": 2,
"turnId": [
3
],
"asrValueCharSpan": {},
"position": 101,
"contributedWords": "line",
"detectedSentence": "agent: hello can you hear me mm hello can you hear me mm im sorry i cant hear her any response on the other line and i will need to disconnect the call please call us back thank you im sorry i cannot hear any risk once on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2559",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "line",
"normalizedValue": "line",
"preprocessedValue": "line",
"valid": true,
"channel": "agent",
"intentTurnNo": 2,
"turnId": [
3
],
"asrValueCharSpan": {},
"position": 101,
"contributedWords": "line",
"detectedSentence": "agent: hello can you hear me mm hello can you hear me mm im sorry i cant hear her any response on the other line and i will need to disconnect the call please call us back thank you im sorry i cannot hear any risk once on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2698",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "disconnect",
"normalizedValue": "disconnect",
"preprocessedValue": "disconnect",
"valid": true,
"channel": "agent",
"intentTurnNo": 2,
"turnId": [
3
],
"asrValueCharSpan": {},
"position": 125,
"contributedWords": "disconnect",
"detectedSentence": "agent: hello can you hear me mm hello can you hear me mm im sorry i cant hear her any response on the other line and i will need to disconnect the call please call us back thank you im sorry i cannot hear any risk once on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2389",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "call",
"normalizedValue": "call",
"preprocessedValue": "call",
"valid": true,
"channel": "agent",
"intentTurnNo": 2,
"turnId": [
3
],
"asrValueCharSpan": {},
"position": 140,
"contributedWords": "call",
"detectedSentence": "agent: hello can you hear me mm hello can you hear me mm im sorry i cant hear her any response on the other line and i will need to disconnect the call please call us back thank you im sorry i cannot hear any risk once on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2615",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "call us back",
"normalizedValue": "call us back",
"preprocessedValue": "call us back",
"valid": true,
"channel": "agent",
"intentTurnNo": 2,
"turnId": [
3
],
"asrValueCharSpan": {},
"position": 152,
"contributedWords": "call us back",
"detectedSentence": "agent: hello can you hear me mm hello can you hear me mm im sorry i cant hear her any response on the other line and i will need to disconnect the call please call us back thank you im sorry i cannot hear any risk once on the other line and i will need to disconnect the call please call us back thank you"
},
{
"id": "2661",
"modelType": "nlp",
"intentChannel": "agent",
"entityType": "STRING",
"asrValue": "cannot hear",
"normalizedValue": "cannot hear",
"preprocessedValue": "cannot hear",
"valid": true,
"channel": "agent",
"intentTurnNo": 2,
"turnId": [
3
],
"asrValueCharSpan": {},
"position": 186,
"contributedWords": "cannot hear",
"detectedSentence": "agent: hello can you hear me mm hello can you hear me mm im sorry i cant hear her any response on the other line and i will need to disconnect the call please call us back thank you im sorry i cannot hear any risk once on the other line and i will need to disconnect the call please call us back thank you"
}
],
"metadata": {
"agentId": "11",
"customerId": "44",
"agentClientId": "Agent_07",
"customerClientId": "77",
"tenantId": "33",
"organizationId": "185",
"categoryId": "314"
},
"intents": null,
"isChunk": false,
"isLastChunk": true
},
"connectorResponse": {},
"ruleOutput": [
{
"id": "3165",
"modelType": null,
"intentChannel": null,
"entityType": null,
"asrValue": null,
"normalizedValue": "#Ghost",
"preprocessedValue": null,
"valid": false,
"channel": null,
"intentTurnNo": 0,
"turnId": [
999999
],
"asrValueCharSpan": null,
"position": 0,
"contributedWords": "",
"detectedSentence": ""
},
{
"id": "3583",
"modelType": null,
"intentChannel": null,
"entityType": null,
"asrValue": null,
"normalizedValue": "No Response",
"preprocessedValue": null,
"valid": false,
"channel": null,
"intentTurnNo": 0,
"turnId": [
999999
],
"asrValueCharSpan": null,
"position": 0,
"contributedWords": "",
"detectedSentence": ""
},
{
"id": "3584",
"modelType": null,
"intentChannel": null,
"entityType": null,
"asrValue": null,
"normalizedValue": "Bug fixed",
"preprocessedValue": null,
"valid": false,
"channel": null,
"intentTurnNo": 0,
"turnId": [
999999
],
"asrValueCharSpan": null,
"position": 0,
"contributedWords": "",
"detectedSentence": ""
},
{
"id": "3582",
"modelType": null,
"intentChannel": null,
"entityType": null,
"asrValue": null,
"normalizedValue": "#Ghost",
"preprocessedValue": null,
"valid": false,
"channel": null,
"intentTurnNo": 0,
"turnId": [
999999
],
"asrValueCharSpan": null,
"position": 0,
"contributedWords": "",
"detectedSentence": ""
}
],
"summary": {
"format": "bulletPoint",
"templateId": 271,
"bullet": [
"Issue - No Response",
"Resolution - Bug fixed",
"Hashtags - #Ghost",
"Primary Intent - #Ghost"
],
"paragraph": null
}
},
"errors": ""
}
Json file has two different main keys data and error. data key has some data in it while error key is completely empty
I want extract specially only bullet key values from summary key which is in data key.
My desired output should look like:
Issue Resolution Hashtags Primary Intent
No Response Bug fixed #Ghost #Ghost
I would really appreciate any help.
CodePudding user response：
I think this should give you the desired output:
library(rjson)
library(janitor)
library(tidyverse)
data <- fromJSON(file = "file.json")
df <- data.frame(str_split(data$data$summary$bullet, pattern = " - ")) %>%
row_to_names(row_number = 1)
Created on 2022-08-18 with reprex v2.0.2