Assign row values of a column based on conditional values of that column in R

user_9115741 0
Viewed 55

I have a data frame in which the category name and it's labels are in the same column. The category names are in all caps, the labels are only first letter capitalized.

In its simplest form a sample data frame can be created:

xdata <- tibble(category_and_label=c('CATEGORY1','Name1','Name2','Name3','CATEGORY2','Name1','Name2','Name4'),
                values            =c(NA, 2,3,4,NA,5,6,7))

and it looks like

  category_and_label values
  <chr>               <dbl>
1 CATEGORY1              NA
2 Name1                   2
3 Name2                   3
4 Name3                   4
5 CATEGORY2              NA
6 Name1                   5
7 Name2                   6
8 Name4                   7

I need to have the category name and label in separate columns. The correctly modified df is:

  category  label values
  <chr>     <chr>  <dbl>
1 CATEGORY1 Name1      2
2 CATEGORY1 Name2      3
3 CATEGORY1 Name3      4
4 CATEGORY2 Name1      5
5 CATEGORY2 Name2      6
6 CATEGORY2 Name4      7

I can only conceive of the first part of the solution. It makes sense to me to create a column to label the category names.

xdata <- xdata %>% mutate(allcaps=if_else(str_detect(category_and_label,'[A-Z]{3,}'),1,0))

category_and_label values allcaps
  <chr>               <dbl>   <dbl>
1 CATEGORY1              NA       1
2 Name1                   2       0
3 Name2                   3       0
4 Name3                   4       0
5 CATEGORY2              NA       1
6 Name1                   5       0
7 Name2                   6       0
8 Name4                   7       0

This identifies the category names. Using dplyr, how would I assign the identified category names to new column with row values equal to the category name but only until the next category name is met.

I've tried a few ideas but none are worth showing.

I can use rename() to rename columns once the category and names are separated.

dplyr r
1 Answers

One way would be to cumsum() a boolean variable. In your example, the first row of each category has values = NA; if that applies in general, then the following code might be what you want:

xdata %>% 
    mutate(
        category = cumsum(is.na(values))
    ) %>% 
    filter(!is.na(values)) %>% 
    rename(label = category_and_label)

yields

# A tibble: 6 × 3
  label values category
  <chr>  <dbl>    <int>
1 Name1      2        1
2 Name2      3        1
3 Name3      4        1
4 Name1      5        2
5 Name2      6        2
6 Name4      7        2
user_19865320
Related