How to use Dplyr on a SQlite database to un-nest a value?

tidyverse
, ,
1

I currently have a Rsqlite db set up on disk with the following table:

id_no | website_link 
1     | google.com msn.com gmail.com 
2     | stackoverflow.com reddit.com 
3     | msn.com

Two columns are: id_no = gives you id number website_link = gives you the links visited for that id number

Each value within the website_link column is separated by a single tabular space " ". I would like to un nest the values to essentially create the following table

id_no | website_link 
1     | google.com
1     | msn.com 
1     | gmail.com
2     | stackoverflow.com
2     | reddit.com
3     | msn.com

The only thing is that I am using dbplyr to communicate with the Rsqlite db i created and so only Dplyr syntax works with the data cleaning (or SQL).

I cannot seem to use the tidytext pacakges unnest_tokens() function which would help me unnest the data to each row. It seems to throw an error when using functions different from dplyr!

Is there a way I can do this in either SQL that would work with SQlite or in Dplyr?

Basicalyl all i want to do is replicate how the tidytext::unest_tokens() function works but with Dplyr code - because whenever i use any function that is not from the dplyr package i just get errors.

2

This is because dbplyr doesn't translate tidytext verbs. I'd suggest using the tidyr verb separate_rows(), but that isn't implemented in dbplyr either. However, I think this StackOverflow threads might contain what you're looking for in SQL:

3

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.