Read string from array


#1

hi Bloggers,
im looking for a solution to obtain data from Dataframe List, which was pulled from Mongo Lite

the data looks like below

"user_master" : [
	{ "ID": "ID101", "classes": [{"**NY**": "A, B, C, B"},{"LD": "A, B, C, B"},{"SG": "A, B, C, B"}},
	{ "ID": "ID102", "classes": [{"LD": "A, B, C, B"},{"SG": "A, B, C, B"}},
	{ "ID": "ID103", "classes": [{"NY": "A, B, C, B"},{"LD": "A, B, C, B"}},
]

"final_data" :[
	{ "ID": "ID101", "loc":"**NY**", "cls": classes[loc]},
	{ "ID": "ID101", "loc":"SG", "cls": classes[loc]},
	{ "ID": "ID102", "loc":"LD", "cls": classes[loc]},
	{ "ID": "ID103", "loc":"NY", "cls": classes[loc]},
]

from user_master i need to collect the cls field based on the final_data.loc as final_data..


#2

Hi,

Unfortunately, I am not sure to understand fully your question.

Did you use mongolite :package: ? If so, have you looked into its documentation ?

What is Dataframe List ?

What is the format of the data you posted ? Json format ?

To help us help you, can you try to provide a reprex ?


#3
ID <-c("Name1", "Name2","Name3")
classes=data.frame("NY"= "A, B, C, B","LD"= "X, Y, Z", "SG"="P, Q, R")
user_master <-data.frame(ID, classes)

IDS <-c("Name1", "Name1","Name3", "Name2")
loc<-c("NY", "SG", "LD", "SG")
collect<-data.frame(IDS, loc)

in the above Collect DF, i need to populate the "class" field with appropriate loc value from user_master Classes field.

thanks in advance


#4

You can do this with a join on your table after tidying them a bit.

I did not change the provided data but you should use stringsAsFactors = FALSE in your data.frame call to avoid having factors. You can also embrace tidyverse and use tibble format.(using data_frame() or tibble() that take care of that.

  1. I transfrom your factor columns in character column
  2. I tidy user_master by gathering column in rows to be able to join
  3. I join tidy_user_master on tidy_collect keeping only rows from the later.
ID <-c("Name1", "Name2","Name3")
classes=data.frame("NY"= "A, B, C, B","LD"= "X, Y, Z", "SG"="P, Q, R")
user_master <-data.frame(ID, classes)

IDS <-c("Name1", "Name1","Name3", "Name2")
loc<-c("NY", "SG", "LD", "SG")
collect<-data.frame(IDS, loc)

library(tidyverse)
tidy_user_master <- user_master %>%
  mutate_if(is.factor, as.character) %>%
  gather(loc, classes, -ID)

tidy_collect <- collect %>%
  mutate_if(is.factor, as.character)

tidy_collect %>%
  left_join(tidy_user_master, by = c(IDS = "ID", loc = "loc"))
#>     IDS loc    classes
#> 1 Name1  NY A, B, C, B
#> 2 Name1  SG    P, Q, R
#> 3 Name3  LD    X, Y, Z
#> 4 Name2  SG    P, Q, R

Created on 2018-06-06 by the reprex package (v0.2.0).

Whatever tool you use in the end, I think a merge (join) operation is what you looking for.


#5

thanks @cderv

appreciate your answer, however i'm looking for solution with out using other packages.

i tried as below

user_master <-data.frame(id=c("Name1", "Name2","Name3"), locs=list("NY"= "A, B, C, B","LD"= "X, Y, Z", "SG"="P, Q, R"), stringsAsFactors = FALSE)
collect<-data.frame(id=c("Name1", "Name1","Name3", "Name2"), loc=c("NY", "SG", "LD", "SG"), stringsAsFactors = FALSE)

for(i in 1:nrow(collect)) {
    crec<-collect[i, ]
    
    cid<- crec$id
    lcc<- crec$loc
    
    srv<-user_master[user_master$id == cid, ]$locs[lcc][[1]]
    collect$service[collect$id == cid  & collect$loc == lcc ]<-srv
    
}
#This is how it set the value in my result set.
collect$classes[collect$id == "Name1"  & collect$loc == "NY" ]<-"srv" 

this approach worked fine in my actual implementation, however it is not working here with sample data..


#6

Can I ask why you don't want to use any :package: ?
You'll miss a lot in any langage when not using available libraries. Efficiency and time-to-it-from-scratch.

For your example, it is not working on your sample data because of lcc not being the column name is user_master. I change it so it works.

user_master <-data.frame(id=c("Name1", "Name2","Name3"), locs=list("NY"= "A, B, C, B","LD"= "X, Y, Z", "SG"="P, Q, R"), stringsAsFactors = FALSE)
collect<-data.frame(id=c("Name1", "Name1","Name3", "Name2"), loc=c("NY", "SG", "LD", "SG"), stringsAsFactors = FALSE)

for(i in 1:nrow(collect)) {
  crec<-collect[i, ]
  
  cid<- crec$id
  #you need a prefix as you created locs column with list in user_master
  lcc<- paste0("locs.", crec$loc)
  #you select the value based on collect rows
  srv<-user_master[user_master$id == cid, lcc]
  collect$service[collect$id == cid  & collect$loc == crec$loc ] <- srv
  
}
collect
#>      id loc    service
#> 1 Name1  NY A, B, C, B
#> 2 Name1  SG    P, Q, R
#> 3 Name3  LD    X, Y, Z
#> 4 Name2  SG    P, Q, R

#7

the reason im not using package is because this is a new requirement in existing code base build using R 2.X or so. i'm upgrading one by one.. it is in process.