How to combine rows?

[Jens Willmer]

Hi,

I have the following kind of data and I like to combine rows with the same title. Image links should be combined as comma separated list. I looked in the WIKI but I did not find this feature, is it missing or just not obvious?

Title Image-Link Property Property Property Property

A Link_1 1 2 3 4

A Link_2

A Link_3

B Link_1 1 2 3 4

B Link_2

B Link_3

[Owen Stephens]

You can do this using the 'join multi-valued cells' option.

1) Sort the Title column (just to make sure that all rows with the same title are together)

2) Apply Sort permanently (this is in the 'Sort' menu which appears once you have sorted the Title column)

3) On the Title column use 'Edit Cells->Blank Down' menu option

4) This should have created a set of 'records' (you sometimes need to force OR to switch to the "Record" view by clicking the "Record" option towards the top left of the screen. More on Records at http://kb.refinepro.com/2012/03/difference-between-record-and-row.html

5) On the Image-Link column use the 'Edit Cells->Join Multi-valued cells' menu option, specifying the comma as the separating character

Done! 

Hope this helps. I've been quite brief here, so let me know if any of it isn't clear

Owen

[Jens Willmer]

That worked great! (after I moved the "Title" column to the start of the columns). Is it now possible to create one row out of the records or will a record be merged to one row on export?

 

Title Image-Link Property Property Property Property

A Link_1,_2,_3 1 2 3 4

A

A

B Link_1,_2,_3 1 2 3 4

B

B

[Owen Stephens]

Great!

You should be able to get rid of the unwanted rows just by using the 'Blank down' option on the Title col, then forcing OR into 'Rows' mode, then facet by blank on the Title column - then you should get all the rows you can get rid of.

Owen

[Jens Willmer]

Very nice, thank you for the quick help!

It takes time to get used to the tool and the workflow but the results are pretty good. I hope they extend the documentation with common use cases like mine in the future.   

[Owen Stephens]

No problem at all.

As you say - OpenRefine is a tool you need to get used to, and there are lots of tips and tricks that you will get to know as you use it more :)

The documentation on the Wiki is all contributed by the community, so it would be amazing if you were able to write up what you've achieved here and add it to the Recipes page https://github.com/OpenRefine/OpenRefine/wiki/Recipes - we all rely on sharing our knowledge of OpenRefine and adding to the documentation as we go.

Best wishes, and welcome to the OpenRefine community :)

Owen

[Jens Willmer]

Sure thing: https://github.com/OpenRefine/OpenRefine/wiki/Common-Use-Case-Examples

[peter.m...@hmri.org.au]

Jean, thanks for developing this -  and I can see what you have done.  What I am not sure I  understand is why you have done this and how you will use the image-link data -   it seem that you are flattening a relational database into a single table, and I guess as the comma separated data are URLs,  you can click on any of the relevant links.   Describing the data management usecase in the wiki before starting to show how to do it would have helped me.

Peter

[Jens Willmer]

Ah, I see. I use OpenRefine to clean up a CSV database for using it in a web project. So I remove/merge all duplicated records (rows).

[Owen Stephens]

Thanks Jens for adding this to the wiki. I see that you've added a new page, rather than using the existing "Recipes" page where I think it might fit. Was there a reason why you felt it didn't fit on the Recipes page?

I suspect that the structure of the wiki could be improved to make it more obvious where to look for this type of information, and it would be good to get feedback from you as to how you see it and where you would look for the kind of example you've now posted on the wiki.

Thanks

Owen

[Jens Willmer]

Hi, yes I put it separate because for me recipes are like small snippets like how to transform a string. The example I posted is more of a workflow. Feel free to rearrange it, I just thought it does not fir to recipes.

--
You received this message because you are subscribed to a topic in the Google Groups "OpenRefine" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openrefine/eeeONiiu2IA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openrefine+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Thad Guidry]

I personally don't like the word Recipes.  That's a throwback to our Google benefactors :)  We (or someone) can change that now.

Jens is correct however in that the Recipes were intended to be for short snippets of examples.  And individual tutorial pages (or more than a paragraph or two) for longer workflow examples, that can be mentioned somewhere.  Go ahead and create an new wiki page called Tutorials and begin putting links for internal wiki pages like the one Jens created.  I have moved the current Tutorials link(External Resources) that shows external tutorials into the Get help section on Home.  So in the end I'd like to see "Your Guys New Name for Recipes, whatever makes better sense", Tutorials, and External Tutorials under Get help.

Thanks guys. Get refining...our wiki pages :)

-Thad

+ThadGuidry  

You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.

[Patrick Maroney]

A rose by any other name? "How Tos", "Tips and Tricks", "Savoir-Faire" , "Recettes" ;-)