How to separate multiple names in one column of spreadsheet using R

Question

I have a spreadsheet with two columns. One with the country of origin of the person, and another with the name of multiple persons that come from that country. Each name includes first name and last name and the full names are separated by a comma. Similar to this one: spreadsheet

I want to separate each name and last name, so that every person is an observation in the dataset. I have tried this code in R, but it is unable to separate the second person of each row.

df <- read_xlsx("data/fake_names.xlsx") 
unglue_unnest(df, names, "{FirstName} {LastName}")

Can you help with this problem? Thank you in advance.

score 1 · Accepted Answer · answered Feb 11 '21 at 02:36

Consider using tidyr:

library(tidyr)
df <- data.frame(state = c("MEX", "MEX"),
                 name = c("Alfred Nobel, Max Planck", "Louis Pasteur, Marie Curie"),
                 stringsAsFactors = FALSE)

df <- df %>%
 separate_rows(name, sep = ", ")

How to separate multiple names in one column of spreadsheet using R

1 Answers1