1

I have a dataframe called df:

ID    Message
1     {"user":user10, "message":{"sender":"k3532", "card":87876}}
2     {"user":user14, "message":{"sender":"hg769", "card":6434363654}}
3     {"user":user22, "message":{"sender":"gjhyj5", "card":87676876, "allowed":true}}

which you can create via

df = structure(list(ID = 1:3, Message = c("{\"user\":\"user10\", \"message\":{\"sender\":\"k3532\", \"card\":87876}}", 
                                     "{\"user\":\"user14\", \"message\":{\"sender\":\"hg769\", \"card\":6434363654}}", 
                                     "{\"user\":\"user22\", \"message\":{\"sender\":\"gjhyj5\", \"card\":87676876, \"allowed\":true}}"
)), row.names = c(NA, -3L), class = c("data.frame"
))

I do this to turn json into dataframe:

message1 <- df[1,]$Message %>% 
  fromJSON() 
data_raw1 <- enframe(unlist(message1))

I get dataframe data_raw1 from json from first row, which looks like this:

name              value
user              user10
message.sender    k3532
message.card      87876

Now, I want to trnaspose it and bind it with original dataframe in this way. So, after transposing it must look like this:

name    user       message.sender   message.card
value   user10       k3532            87876

And i want to do that with each json in each row and then bind them with original datafarme. So the final result must look like this:

ID    name    user       message.sender   message.card    message.allowed
1     value   user10       k3532            87876            NA
2     value   user14       hg769            6434363654       NA
3     value   user22       gjhyj5           87676876         TRUE

How could i do that? This kind of transformation is way too complicated for my level. Its necessary, that it happens with that transposing operation

Improve this question
1
  • please use dput(df) in future to make a MWE that is copy-and-pastable Commented Aug 20, 2020 at 1:27

3 Answers 3

Reset to default
1

jsonlite::stream_in is good for when you have multiple separate json chunks:

cbind(df["ID"], stream_in(textConnection(df$Message)))

# Found 3 records...
# Imported 3 records. Simplifying...
#  ID   user message.sender message.card message.allowed
#1  1 user10          k3532        87876              NA
#2  2 user14          hg769   6434363654              NA
#3  3 user22         gjhyj5     87676876            TRUE
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

This post has similar needs to yours


df = data.frame(df)


out = purrr::map_dfr(df$Message, function(msg) {
  msg %>% 
    jsonlite::fromJSON() %>% 
    data.frame
})


out = cbind(df, out)
out$Message = NULL
out

which yields

  ID   user message.sender message.card message.allowed
1  1 user10          k3532        87876              NA
2  2 user14          hg769   6434363654              NA
3  3 user22         gjhyj5     87676876            TRUE
Improve this answer

Comments

0 
'%!in%' = function(x,y)!('%in%'(x,y))
df = cbind(df$ID, df = data.frame(
            t(apply(df, 1, function(x) {
                x <- unlist(fromJSON(x[2]))
                if ('message.allowed' %!in% names(x)) x <- c(x, NA)
                return(x)
            }))
        )
)
names(df) <- c('ID', 'user', 'message.sender', 'message.card', 'message.allowed')
df

> df
  ID   user message.sender message.card message.allowed
1  1 user10          k3532        87876            <NA>
2  2 user14          hg769   6434363654            <NA>
3  3 user22         gjhyj5     87676876            TRUE
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.