Forum Discussion

wicker's avatar
wicker
Frequent Visitor
7 years ago

Expand Columns from lists of R data.frame

I get data from MongoDB with R library mongolite like this:

 

library(mongolite)

m <- mongo(collection = collectionname,
db = dbname,
url = urlname)

Query<-m$find(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0, handler = NULL, pagesize = 1000)

 

what I get in BI

 

I can't expand this columns like using JSON, for example.

 

In R Frame this columns are lists

 

How should I change R-script to get data from this lists too?

Need Help

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi wicker,

     

    You need to convert it in R. Try this formula, please.

    library(mongolite)

m <- mongo(collection = "inventory", db = "retail", url = "mongodb://localhost/?ssl=false")
Query <- m$find(query = "{}", fields = "{\"_id\":0}", sort = "{}", skip = 0, limit = 0, handler = NULL, pagesize = 1000)
nrows <- nrow(Query)  
colnames <- names(unlist(Query))
result <- data.frame(matrix(unlist(Query), nrow = nrows, byrow = T))
names(result) <- colnames  #add names to the result set

    Expand_Columns_from_lists_of_R_data_frame

     

    Best Regards,

    Dale

    • wicker's avatar
      wicker
      Frequent Visitor
      7 years ago

      Thanks for help!

      But now I get error...

       

       

      Also, I have such unusual structure of lists. There are different columns in custom_fields for different documents in MongoDB. For example, there are 2 columns on screenshot below, but sometimes there are 3 or more columns or 2 another columns' names.

       

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi wicker,

         

        Please share a sample. It would be great to have a format that can be imported into Mongo.

         

        Best Regards,

        Dale