Need to convert the Multiple Data frames with Array to single data frame

ode:
request_body <- data.frame(DivisionId=c(DivisionId),PrimoId=c(PrimoId),
Ename=c(Ename),Sname=c(Sname),StartDepth=c(StartDepth),
EndDepth=c(EndDepth),SidetrackNum=c(SidetrackNum))

bodyTest <- toJSON(request_body)

r <- POST(url, body = bodyTest, encode = "json")

resultRaw <- content(r, as = "text")

dt <- fromJSON(resultRaw)

  • Need to convert this one

division <- length(unlist(DivisionId))

df <- lapply(dt, function(col)
{
data.frame(matrix(unlist(col), ncol=division,byrow=T))
})

df <- data.frame(do.call(cbind, df))

Getting this with Multiple columns instead of one columns for exm. DivisionId, PrimoId etc.

Any Help?

Thanks,
Navjeet

Able to convert till thios but still not able to get the Array Columns Mds and Vls in Column form.

Any Ideas/?
dtFlat2df <- as.data.frame(sapply(dt, FUN = unlist,USE.NAMES = TRUE))

image

Any Ideas on the problem?

Actually, I've never worked with json. So, I was having hard time in reading data itself. I finally managed to do something, but I'm not sure whether that's correct or not, and whether that's what you want or not. I hope others will chime in and help in your problem.

Here's what I attempted to do:

json <- '[
{
"DivisionId": 20,
"PrimoId": 73413,
"Wits": "0113",
"Ename": "IRK_DRILLING",
"Sname": "RATE_OF_PENETRATION",
"Adname": "Depth",
"Array": {
"Size": 1733,
"Mds": [
2,
4,
5,
9,
12
],
"Tms": null,
"Vls": [
0.26,
0.13,
0.88,
4.91,
9.71
],
"Ads": null
}
},
{
"DivisionId": 56,
"PrimoId": 88245,
"Wits": "0115",
"Ename": "IRK_DRILLING",
"Sname": "HOOK_LOAD",
"Adname": "Depth",
"Array": {
"Size": 1892,
"Mds": [
3546.99,
3547,
3548,
3549,
3550
],
"Tms": null,
"Vls": [
-999.25,
43.04,
110.8,
107.83,
108.61
],
"Ads": null
}
}
]'

# flatten = TRUE - no error
as.data.frame(x = lapply(X = jsonlite::fromJSON(txt = json,
                                                flatten = TRUE),
                         FUN = unlist))
#>    DivisionId PrimoId Wits        Ename               Sname Adname
#> 1          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> 2          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> 3          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> 4          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> 5          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> 6          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> 7          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> 8          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> 9          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> 10         56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#>    Array.Size Array.Mds Array.Tms Array.Vls Array.Ads
#> 1        1733      2.00        NA      0.26        NA
#> 2        1892      4.00        NA      0.13        NA
#> 3        1733      5.00        NA      0.88        NA
#> 4        1892      9.00        NA      4.91        NA
#> 5        1733     12.00        NA      9.71        NA
#> 6        1892   3546.99        NA   -999.25        NA
#> 7        1733   3547.00        NA     43.04        NA
#> 8        1892   3548.00        NA    110.80        NA
#> 9        1733   3549.00        NA    107.83        NA
#> 10       1892   3550.00        NA    108.61        NA

# flatten = FALSE - similar result as yours
as.data.frame(x = lapply(X = jsonlite::fromJSON(txt = json),
                         FUN = unlist))
#>       DivisionId PrimoId Wits        Ename               Sname Adname
#> Size1         20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Size2         56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Mds1          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Mds2          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Mds3          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Mds4          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Mds5          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Mds6          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Mds7          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Mds8          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Mds9          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Mds10         56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Tms1          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Tms2          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Vls1          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Vls2          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Vls3          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Vls4          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Vls5          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Vls6          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Vls7          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Vls8          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Vls9          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Vls10         56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#> Ads1          20   73413 0113 IRK_DRILLING RATE_OF_PENETRATION  Depth
#> Ads2          56   88245 0115 IRK_DRILLING           HOOK_LOAD  Depth
#>         Array
#> Size1 1733.00
#> Size2 1892.00
#> Mds1     2.00
#> Mds2     4.00
#> Mds3     5.00
#> Mds4     9.00
#> Mds5    12.00
#> Mds6  3546.99
#> Mds7  3547.00
#> Mds8  3548.00
#> Mds9  3549.00
#> Mds10 3550.00
#> Tms1       NA
#> Tms2       NA
#> Vls1     0.26
#> Vls2     0.13
#> Vls3     0.88
#> Vls4     4.91
#> Vls5     9.71
#> Vls6  -999.25
#> Vls7    43.04
#> Vls8   110.80
#> Vls9   107.83
#> Vls10  108.61
#> Ads1       NA
#> Ads2       NA

Created on 2019-03-30 by the reprex package (v0.2.1)

it still give me same error as the number of rows are not matching with the original JSON as compare to the one i sent as example in which it is same.

df <- as.data.frame(x = lapply(X = jsonlite::fromJSON(txt = json,

  •                                                   flatten = TRUE),
    
  •                            FUN = unlist))
    

Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE, :
arguments imply differing number of rows: 2, 3625

if we use below one added another Row it fails

json <- '[{"DivisionId": 20,"PrimoId": 73413,"Wits": "0113","Ename": "IRK_DRILLING","Sname": "RATE_OF_PENETRATION", "Adname": "Depth","Array": {"Size": 1733,"Mds": [2,4,5,9,12],"Tms": null,"Vls": [0.26,0.13,0.88,4.91,9.71],"Ads": null} },{"DivisionId": 56,"PrimoId": 88245,"Wits": "0115","Ename": "IRK_DRILLING","Sname": "HOOK_LOAD","Adname": "Depth","Array":{"Size": 1892,"Mds": [3546.99,3547,3548,3549,3550,35561],"Tms": null,"Vls": [-999.25,43.04,110.8,107.83,108.61,109.61],"Ads": null} }]'

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

When i am trying to load the datapasta it gives me error as my data frame is containing Array.

datapasta::df_paste(head(dt,5)[,])
Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE, :
arguments imply differing number of rows: 2, 5

It's hard to understand what problem you're facing and what exactly do you want.

Can you please provide a REPRoducible EXample of your problem?

In case you don't know how to make a reprex, here's a great link:

To provide a sample of your data, please use the datapasta package as recommended in the link above.

I cannot open this in excel as well as this is JSON format data which i am converting into dataframe from POST Api

Below is sample data in JSON format which i need in the dataframe

[
{
"DivisionId": 20,
"PrimoId": 73413,
"Wits": "0113",
"Ename": "IRK_DRILLING",
"Sname": "RATE_OF_PENETRATION",
"Adname": "Depth",
"Array": {
"Size": 1733,
"Mds": [
2,
4,
5,
9,
12,
],
"Tms": null,
"Vls": [
0.26,
0.13,
0.88,
4.91,
9.71,
],
"Ads": null
}
},
{
"DivisionId": 56,
"PrimoId": 88245,
"Wits": "0115",
"Ename": "IRK_DRILLING",
"Sname": "HOOK_LOAD",
"Adname": "Depth",
"Array": {
"Size": 1892,
"Mds": [
3546.99,
3547,
3548,
3549,
3550,
],
"Tms": null,
"Vls": [
-999.25,
43.04,
110.8,
107.83,
108.61,
],
"Ads": null
}
}
]
Code i used
resultRaw <- content(r, as = "text")

dt <- fromJSON(resultRaw)

testB2 <-

  • data.frame(
    
  •     DivisionId=unlist(dt$DivisionId),
    
  •     PrimoId=unlist(dt$PrimoId),
    
  •     Wits=unlist(dt$Wits),
    
  •     Adname=unlist(dt$Adname),
    
  •     Mds = unlist(dt$Array$Mds), 
    
  •     Tms = unlist(dt$Array$Tms),
    
  •     Vls = unlist(dt$Array$Vls), 
    
  •     Ads = unlist(dt$Array$Ads))
    

Error in data.frame(DivisionId = unlist(dt$DivisionId), PrimoId = unlist(dt$PrimoId), :
arguments imply differing number of rows: 2, 3625

I'm not sure why did you use [,] part. But, if you're able to open the dataset in Excel, you can use datapasta easily. Look here:

if i try below it also gives me same error.

dtFlat2df <- as.data.frame(lapply(X=fromJSON(resultRaw,flatten=TRUE),FUN=unlist))
Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE, :
arguments imply differing number of rows: 2, 3625
if i do not flatten then data is not comming in correct format.
dtFlat2df <- as.data.frame(lapply(X=fromJSON(resultRaw),FUN=unlist))