Full names (and titles, etc.) generally need to be separated into multiple columns to sort rows alphabetically by "last name." I've never come across a simple way to achieve this in SQL when a single column contains the full name.
However, I know R has thousands of libraries - and although I haven't come across any examples that can do this without separating the first name, last name and titles into their own respective columns - I thought I'd see if there's a slightly more efficient way to deal with this situation.
The data set I'm working with has a single column with full names. For example:
Names
1 Robert Johnson
2 Billy Joel
3 Donald Fagen
4 Trent Reznor
5 Wolfgang Mozart
I need to sort these alphabetically without creating additional columns. So far, I'm not sure this is possible, but I did find a relatively painless work-around. Thankfully, each name follows a "first name"(space)"last name" convention. So I can use separate() from the tidyr library to easily isolate last names:
library(tidyverse)
library(magrittr)
# Separate into "first name" and "last name" columns
data %<>% separate(Names, c('first_name', 'last_name'), sep = ' ')
first_name last_name
1 Robert Johnson
2 Billy Joel
3 Donald Fagen
4 Trent Reznor
5 Wolfgang Mozart
Then I can sort the new "last name" column alphabetically with arrange() and immediately rebuild the original column with unite():
# Arrange rows alphabetically by last name
data %<>% arrange(last_name)
# Rebuild original column and dissolve temporary 2nd column
data %>% unite(Names, first_name:last_name, sep=' ')
This successfully rebuilds the original table and alphabetizes column "Names" via last names. Is there another way to achieve this without ever (even temporarily) creating that second "last name" column? Any additional R libraries would be accepted. Thanks!