Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wicker
Frequent Visitor

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

85e9f56560.jpg

 

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

 

In R Frame this columns are lists

441db056bc.jpg

 

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

5 REPLIES 5
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for help!

But now I get error...

 

007ee41983[2].jpg

 

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.

 

 

Hi @wicker,

 

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

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi, @v-jiascu-msft

 

This is JSON sample from Mongo - https://pastebin.com/2QRQBJQz

 

Desired result from this JSON is on the screen below

633e7a1c67[1].jpg

 

All tags are in column "tags", separated by commas. For each custom_field we create column with it's value.

Hi

 

Were you able to get through this? I am having a similar requirement.

 

Thanks 

Ravi

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.