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

navjeet.singh · March 26, 2019, 3:15pm

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

navjeet.singh · March 27, 2019, 4:31pm

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

Yarnabrina · March 27, 2019, 4:36pm

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:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners Guides & FAQs

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

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

navjeet.singh · March 27, 2019, 8:08pm

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

Yarnabrina · March 28, 2019, 1:33am

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:

navjeet.singh · March 28, 2019, 3:25pm

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

navjeet.singh · March 28, 2019, 4:46pm

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

navjeet.singh · March 29, 2019, 5:10pm

Any Ideas on the problem?

Yarnabrina · March 30, 2019, 3:11am

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

navjeet.singh · April 1, 2019, 3:07pm

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

system · April 22, 2019, 3:07pm

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.