how to extract a specific row and make it a column in R?

user_9392842 0
Viewed 118

I have a really messy data set and the following data frame represents a part of it 

Causenum<-c("TIMOTHY JAMES", "Disp Type/Case ID","DTCGL","CR01785",
            "CR02099","DTCNG","CR01061","CR01329") 

case<-c(NA,"Description","Tried by Court - Guilty","ST V WILLIAMS","ST V YOUNG III","Tried by Court - 
Not Guilty",
        "ST V HARRISON","ST V GOMEZ")

data.frame(Causenum,case)

           Causenum                        case
1     TIMOTHY JAMES                        <NA>
2 Disp Type/Case ID                 Description
3             DTCGL     Tried by Court - Guilty
4           CR01785               ST V WILLIAMS
5           CR02099              ST V YOUNG III
6             DTCNG Tried by Court - Not Guilty
7           CR01061               ST V HARRISON
8           CR01329                  ST V GOMEZ

The two columns contain a lot of information that should be there. Basically I want to make sure that all the rows below DTCGL are grouped by DTCGL and the same for DTCNG

so that It looks as follows

  Causenum               case            newcol                      
  CR01785               ST V WILLIAMS    DTCGL
  CR02099               ST V YOUNG III   DTCGL
  CR01061               ST V HARRISON    DTCNG
  CR01329               ST V GOMEZ       DTCNG

how do I do this?

dplyr r
3 Answers

We can create a grouping column based on the the elements in 'Causenum' with values 'DTC', create a 'newcol' with the first element of 'Causenum', and filter out the rows that are 'CRO' from 'Causenum'

library(dplyr)
library(stringr)
df1 %>%
    group_by(grp = cumsum(str_detect(Causenum, "^DTC"))) %>% 
    na.omit %>% 
    mutate(newcol = first(Causenum)) %>%
    ungroup %>% 
    filter(str_detect(Causenum, "CR0")) %>%
    select(-grp)
# A tibble: 4 x 3
#  Causenum case           newcol
#  <chr>    <chr>          <chr> 
#1 CR01785  ST V WILLIAMS  DTCGL 
#2 CR02099  ST V YOUNG III DTCGL 
#3 CR01061  ST V HARRISON  DTCNG 
#4 CR01329  ST V GOMEZ     DTCNG
user_37322710

Base R solution :

df1=df1[which(df1$Causenum=="DTCGL"):length(df1$Causenum),]
df1$newcol=ifelse(seq_along(df1$Causenum)>=which(df1$Causenum=="DTCNG"),"DTCNG","DTCGL")
df1=df1[!startsWith(as.character(df1$Causenum),"DT"),]

#  Causenum           case newcol
#4  CR01785  ST V WILLIAMS  DTCGL
#5  CR02099 ST V YOUNG III  DTCGL
#7  CR01061  ST V HARRISON  DTCNG
#8  CR01329     ST V GOMEZ  DTCNG
user_99787460

We can get all the variabkes which starts with "DTC" in new column, fill the missing values and remove the first row from each group. 

library(dplyr)

df %>%
  mutate(newcol = replace(Causenum, !grepl('^DTC', Causenum), NA)) %>%
  tidyr::fill(newcol) %>%
  na.omit %>%
  group_by(newcol) %>%
  slice(-1L)

# Causenum case           newcol
#  <fct>    <fct>          <fct> 
#1 CR01785  ST V WILLIAMS  DTCGL 
#2 CR02099  ST V YOUNG III DTCGL 
#3 CR01061  ST V HARRISON  DTCNG 
#4 CR01329  ST V GOMEZ     DTCNG
user_39629140
Related