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

reprex
httr
jsonlite
purrr
dplyr
#1

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

0 Likes

#2

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

0 Likes

#3

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.

0 Likes

#4

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

0 Likes

#5

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:

0 Likes

#6

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

0 Likes

#7

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))

0 Likes

#8

Any Ideas on the problem?

0 Likes

#9

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)

0 Likes

#10

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} }]'

0 Likes

closed #11

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.

0 Likes