tidyr::separate_rows()

Function of the Week: separate_rows

separate_rows

In this document, I will introduce the “separate_rows” function and show what it’s for.

library(tidyverse)
library(dplyr)
library(readxl)
library(here)
# tidyr hosts separate_rows 
library(tidyr)

What is it for?

This function separates multiple values recorded for an observation for one variable. For example, three bill length is recorded for one observation below, ‘separate_rows’ function will separate them into three rows.

funcDATA = read_excel(here('data/function.xlsx'), 
                      sheet=1, 
                      na="NA")
slice(funcDATA)
ABCDEFGHIJ0123456789
species
<chr>
island
<chr>
bill_length_mm
<chr>
bill_depth_mm
<chr>
AdelieTorgersen39.1, 40.2, 38.918.7, 16.3, 17.1
GentooBiscoe50, 52.516
ChinstrapDream52, 50.2, 46.818.1, 17.5, 16
GentooBiscoe48.7, 47.414.1, 15.8
ChinstrapDream4717.3
5 rows
separate_rows(data = funcDATA, bill_length_mm, convert = TRUE)
ABCDEFGHIJ0123456789
species
<chr>
island
<chr>
bill_length_mm
<dbl>
bill_depth_mm
<chr>
AdelieTorgersen39.118.7, 16.3, 17.1
AdelieTorgersen40.218.7, 16.3, 17.1
AdelieTorgersen38.918.7, 16.3, 17.1
GentooBiscoe50.016
GentooBiscoe52.516
ChinstrapDream52.018.1, 17.5, 16
ChinstrapDream50.218.1, 17.5, 16
ChinstrapDream46.818.1, 17.5, 16
GentooBiscoe48.714.1, 15.8
GentooBiscoe47.414.1, 15.8
Next
12
Previous
1-10 of 11 rows

We can also do it for multiple variables having more than one data point for one variable.

separate_rows(data = funcDATA, 
              bill_length_mm, bill_depth_mm, 
              convert = TRUE)
ABCDEFGHIJ0123456789
species
<chr>
island
<chr>
bill_length_mm
<dbl>
bill_depth_mm
<dbl>
AdelieTorgersen39.118.7
AdelieTorgersen40.216.3
AdelieTorgersen38.917.1
GentooBiscoe50.016.0
GentooBiscoe52.516.0
ChinstrapDream52.018.1
ChinstrapDream50.217.5
ChinstrapDream46.816.0
GentooBiscoe48.714.1
GentooBiscoe47.415.8
Next
12
Previous
1-10 of 11 rows

Is it helpful?

I think it is a great tool to separate multiple values from a variable into separate rows when working on a dataset with this problem.

LS0tCnRpdGxlOiAnRnVuY3Rpb24gb2YgdGhlIFdlZWs6IHNlcGFyYXRlX3Jvd3MnCmF1dGhvcjogIkZhcnphbmEgTmFvc2hpbiIKZGF0ZTogImByIFN5cy5EYXRlKClgIgpvdXRwdXQ6CiAgaHRtbF9kb2N1bWVudDoKICAgIGRmX3ByaW50OiBwYWdlZAogICAgY29kZV9kb3dubG9hZDogdHJ1ZQogICAgdG9jOiB5ZXMKICAgIHRvY19mbG9hdDogZmFsc2UKICAgIGNvZGVfZm9sZGluZzogc2hvdwogICAgdGhlbWU6IGNlcnVsZWFuCi0tLQoKYGBge3Igc2V0dXAsIGluY2x1ZGU9RkFMU0V9CmtuaXRyOjpvcHRzX2NodW5rJHNldChlY2hvID0gVFJVRSkKYGBgCgoKKioqICAKCiMjIyMgYHNlcGFyYXRlX3Jvd3NgCgoqKiogCgojIyMjICoqSW4gdGhpcyBkb2N1bWVudCwgSSB3aWxsIGludHJvZHVjZSB0aGUgInNlcGFyYXRlX3Jvd3MiIGZ1bmN0aW9uIGFuZCBzaG93IHdoYXQgaXQncyBmb3IuKioKCioqKiAgCgpgYGB7ciBsb2FkbGliLCBlY2hvPVQsIHJlc3VsdHM9J2hpZGUnLCBtZXNzYWdlPUYsIHdhcm5pbmc9Rn0KbGlicmFyeSh0aWR5dmVyc2UpCmxpYnJhcnkoZHBseXIpCmxpYnJhcnkocmVhZHhsKQpsaWJyYXJ5KGhlcmUpCiMgdGlkeXIgaG9zdHMgc2VwYXJhdGVfcm93cyAKbGlicmFyeSh0aWR5cikKYGBgCgoqKiogIAoKIyMjIyAqKldoYXQgaXMgaXQgZm9yPyoqCgoqKiogIAoKPHN0eWxlPgpkaXYuYmx1ZSB7YmFja2dyb3VuZC1jb2xvcjojZTZmMGZmOyBib3JkZXItcmFkaXVzOiA0cHg7IHBhZGRpbmc6IDEwcHg7fQo8L3N0eWxlPgo8ZGl2IGNsYXNzID0gImJsdWUiPgojIyMjIFRoaXMgZnVuY3Rpb24gc2VwYXJhdGVzIG11bHRpcGxlIHZhbHVlcyByZWNvcmRlZCBmb3IgYW4gb2JzZXJ2YXRpb24gZm9yIG9uZSB2YXJpYWJsZS4gRm9yIGV4YW1wbGUsIHRocmVlIGJpbGwgbGVuZ3RoIGlzIHJlY29yZGVkIGZvciBvbmUgb2JzZXJ2YXRpb24gYmVsb3csICdzZXBhcmF0ZV9yb3dzJyBmdW5jdGlvbiB3aWxsIHNlcGFyYXRlIHRoZW0gaW50byB0aHJlZSByb3dzLiAKPC9kaXY+ICAKCioqKiAgIAoKCmBgYHtyfQpmdW5jREFUQSA9IHJlYWRfZXhjZWwoaGVyZSgnZGF0YS9mdW5jdGlvbi54bHN4JyksIAogICAgICAgICAgICAgICAgICAgICAgc2hlZXQ9MSwgCiAgICAgICAgICAgICAgICAgICAgICBuYT0iTkEiKQpzbGljZShmdW5jREFUQSkKYGBgCgoqKiogIAoKYGBge3J9CnNlcGFyYXRlX3Jvd3MoZGF0YSA9IGZ1bmNEQVRBLCBiaWxsX2xlbmd0aF9tbSwgY29udmVydCA9IFRSVUUpCmBgYAoKCioqKiAgCgo8c3R5bGU+CmRpdi5ibHVlIHtiYWNrZ3JvdW5kLWNvbG9yOiNlNmYwZmY7IGJvcmRlci1yYWRpdXM6IDRweDsgcGFkZGluZzogMTBweDt9Cjwvc3R5bGU+CjxkaXYgY2xhc3MgPSAiYmx1ZSI+CiMjIyMgV2UgY2FuIGFsc28gZG8gaXQgZm9yIG11bHRpcGxlIHZhcmlhYmxlcyBoYXZpbmcgbW9yZSB0aGFuIG9uZSBkYXRhIHBvaW50IGZvciBvbmUgdmFyaWFibGUuIAo8L2Rpdj4gIAoKKioqICAgIAoKCmBgYHtyfQpzZXBhcmF0ZV9yb3dzKGRhdGEgPSBmdW5jREFUQSwgCiAgICAgICAgICAgICAgYmlsbF9sZW5ndGhfbW0sIGJpbGxfZGVwdGhfbW0sIAogICAgICAgICAgICAgIGNvbnZlcnQgPSBUUlVFKQpgYGAKCgoKKioqICAKCiMjIyMgKipJcyBpdCBoZWxwZnVsPyoqCgoqKiogICAgCgo8c3R5bGU+CmRpdi5ibHVlIHtiYWNrZ3JvdW5kLWNvbG9yOiNlNmYwZmY7IGJvcmRlci1yYWRpdXM6IDRweDsgcGFkZGluZzogMTBweDt9Cjwvc3R5bGU+CjxkaXYgY2xhc3MgPSAiYmx1ZSI+CiMjIyMgSSB0aGluayBpdCBpcyBhIGdyZWF0IHRvb2wgdG8gc2VwYXJhdGUgbXVsdGlwbGUgdmFsdWVzIGZyb20gYSB2YXJpYWJsZSBpbnRvIHNlcGFyYXRlIHJvd3Mgd2hlbiB3b3JraW5nIG9uIGEgZGF0YXNldCB3aXRoIHRoaXMgcHJvYmxlbS4gCjwvZGl2PiAgCgoqKiogIAoK

Last updated on March 12, 2021

Edit this page