Splitting columns and pivoting table

user_10924836 0
Viewed 41

I am working with data that contain 10 columns and 2 rows. Below you can see my data.

df<-data.frame(
                'Company with premium status-Land' = c(5,1),
                'Company with premium status-Sea' = c(5,1),
                'Company with premium status-Air' = c(5,1),
                'Company with premium status-Unkown' = c(5,1),
                'Company with premium status-Total' = c(5,1),
                'Company with low class status-Land' = c(10,1),
                'Company with low class status-Sea' = c(10,1),
                'Company with low class status-Air' = c(10,1),
                'Company with low class status-Unkown' = c(10,1),
                'Company with low class status-Total' = c(10,1),
                check.names = FALSE)

df

Now I want to pivot this data and split columns by this delimiter -. I tried with this line of codes but it is not working.

df1<-df %>% pivot_longer(c(
                    "Company with premium status-Land",
                    "Company with premium status-Sea",
                    "Company with premium status-Air",
                    "Company with premium status-Unkown",
                    "Company with premium status-Total",
                    "Company with low class status-Land",
                    "Company with low class status-Sea",
                    "Company with low class status-Air",
                    "Company with low class status-Unkown",
                    "Company with low class status-Total"
), names_to = c("Company with", ".value"), 
names_pattern = "(.*)-(.*)")

In the end, I need to have output as the picture shown below

enter image description here

So can anybody help me how to solve this problem?

dplyr r
2 Answers

You could use pivot_longer() from tidyr and set ".value" in names_to.

From the doc of pivot_longer:

".value" indicates that the corresponding component of the column name defines the name of the output column containing the cell values, overriding values_to entirely.

library(tidyr)

df %>%
  pivot_longer(
    everything(),
    names_to = c("col", ".value"),
    names_sep = "-"
  )

# # A tibble: 4 × 6
#   col                            Land   Sea   Air Unkown Total
#   <chr>                         <dbl> <dbl> <dbl>  <dbl> <dbl>
# 1 Company with premium status       5     5     5      5     5
# 2 Company with low class status    10    10    10     10    10
# 3 Company with premium status       1     1     1      1     1
# 4 Company with low class status     1     1     1      1     1
user_100689850

Another possible solution (which requires, as pointed out by @Darren Tsai, check.names = F when creating the dataframe with data.frame):

library(tidyverse)

df %>% 
  pivot_longer(everything(), names_to = c("Company with", ".value"), 
               names_pattern = "(.*)-(.*)") 

#> # A tibble: 4 × 6
#>   `Company with`                 Land   Sea   Air Unkown Total
#>   <chr>                         <dbl> <dbl> <dbl>  <dbl> <dbl>
#> 1 Company with premium status       5     5     5      5     5
#> 2 Company with low class status    10    10    10     10    10
#> 3 Company with premium status       1     1     1      1     1
#> 4 Company with low class status     1     1     1      1     1
user_115644870
Related