R: Adding a column with the name of

user_1722950 0
Viewed 32

I have a table like:

    C1, C2, C3
1   1.1, 2.3, 5.5
2   3.3, 4.3, 2.5

Sample reproducible data:

df <- tribble(
  ~ C1, ~ C2, ~ C3,
   1.1, 2.3, 5.5,
   3.3, 4.3, 2.5
)

I am then using the following:

df %>%
  mutate(Type = row_number()) %>%
  pivot_longer(-Type, names_to = "name", values_to = "Time") %>%
  select(-name)

To get it into a format like:

    Type Time
 1   1.1   
 1   2.3  
 1   5.5  
 2   3.3  
 2   4.3  
 2   2.5

How can I create a third column which has the numeric part of C1/C2/C3 to create eg:

Type Time Value
 1   1.1  1
 1   2.3  2
 1   5.5  3
 2   3.3  1
 2   4.3  2
 2   2.5  3
r
3 Answers

You could use parse_number to extract the numeric part of C1/C2/C3.

df %>%
  mutate(Type = row_number()) %>%
  pivot_longer(-Type, names_to = "name", values_to = "Time") %>% 
  mutate(Value = parse_number(name), name=NULL)

Output:

   Type  Time Value
  <int> <dbl> <dbl>
1     1   1.1     1
2     1   2.3     2
3     1   5.5     3
4     2   3.3     1
5     2   4.3     2
6     2   2.5     3
user_133216470

Here's what I did:

df %>%
  mutate(Type = row_number()) %>%
  pivot_longer(-Type, names_to = "name", values_to = "Time") %>%
  mutate(name = as.numeric(gsub("C", "", name)))

Basically, instead of erasing the name column, I took the "C" out and turned the values into nuemric.

user_145069880

You can use names_pattern in pivot_longer and specify a regex to extract the part that you need from the column name.

library(dplyr)
library(tidyr)

df %>%
  mutate(Type = row_number()) %>%
  pivot_longer(-Type, names_to = "Value", 
               values_to = "Time", 
               names_pattern = 'C(\\d+)')

#   Type Value  Time
#  <int> <chr> <dbl>
#1     1 1       1.1
#2     1 2       2.3
#3     1 3       5.5
#4     2 1       3.3
#5     2 2       4.3
#6     2 3       2.5
user_39629140
Related