Replace values into previous row with a condition

Question

I want to get data where ID column doesn´t start with 00 and append this value of ID column to the end of Description column in previous row.

Then replace the rest of values into after Name column in the previous row. How can I do that with R?

Here is source of dummy data: https://docs.google.com/spreadsheets/d/1SbmaM8hXck-z5nsNfDMbhwijvAGPkPPBgQ_eY4JAMC8/edit?usp=sharing

ID      Year    Description  Name   User       Factor_1  Factor_2   Factor_3
0011    2016    blue colour  AA     James      Xfac      NA         NA
is nice XXX     XLM          Yfac   different  Yfac      NA         NA
0024    2017    red colour   DD     Mark       Zfac      NA         NA
is good YYY     STM          Lfac   unique     Zfac      NA         NA

What I want to have:

ID      Year    Description          Name   User  Factor_1   Factor_2   Factor_3
0011    2016    blue colour is nice  XXX    XLM   Yfac       different  Yfac
0024    2017    red colour is good   YYY    STM   Lfac       unique     Zfac

Answer 1

There's the first part where you want to paste the descriptions together,
and there's the part where you want to move your variables as well, as you want "XXX" and "YYY" in your "user" column.

Also, in Viveks answer all wrong lines are pasted with ALL "right" lines, which works in your example, but not if you have a few right lines, and then a wrong one. Working with booleans (TRUE/FALSE) sometimes works fine, but in this case, I think you want to use an integer index, as that makes it easier to refer to "the previous line". Which gives me code:

rmlines <- which(!substr(df$ID,1,2)=="00")
df$Description[rmlines-1] <- paste(df$Description[rmlines-1], df[rmlines,1], sep=" ")
df[rmlines-1, 4:8] <- df[rmlines, 2:6]
df <- df[-rmlines,]

But there's one more problem to consider: what classes are your columns?
When I tried it out, I treated everything as a character, which means you can move columns around fine. In your data, some may be factors or something else, so you might want to change the classes. I think it's easiest to first change it all to character, and then change it (back) to the final class you want your columns to be.

# To change everything to character:
df <- as.data.frame(lapply(df, as.character), stringsAsFactors = FALSE)
# And to assign the right classes, you need to decide case-by-case:
df$Year <- as.integer(df$Year)
df$Factor_1 <- as.factor(df$Factor1) # Optionally provide levels

Answer 2

Here's a solution with dplyr:

library(dplyr)

df %>% 
  bind_cols(df %>% rename_all(function(x) paste0(x, "_dummy"))) %>%
  mutate(
    Description = ifelse(substr(lead(ID), 1, 2) != "00", 
                         paste(Description, lead(ID)), Description),
    Name = lead(Year_dummy),
    User = lead(Description_dummy),
    Factor_1 = lead(Name_dummy),
    Factor_2 = lead(User_dummy),
    Factor_3 = lead(Factor_1_dummy)
  ) %>% select(-ends_with("dummy")) %>%
  filter(substr(ID, 1, 2) == "00")

Output:

    ID Year       Description Name User Factor_1  Factor_2 Factor_3
1 0011 2016 blue colour is nice  XXX  XLM     Yfac different     Yfac
2 0024 2017  red colour is good  YYY  STM     Lfac    unique     Zfac

In case you're dealing with a large number of columns, a combination of dplyr and base R could do it:

library(dplyr)

df_combo <- cbind(df, df)

df$Description <- ifelse(substr(lead(df$ID), 1, 2) != "00", 
                               paste(df$Description, lead(df$ID)), df$Description)

for (i in (ncol(df) + 4):ncol(df_combo)) {

  df_combo[[i]] <- lead(df_combo[[i - ncol(df) - 2]])

}

df_combo <- subset(df_combo, substr(ID, 1, 2) == "00")

df_descr <- subset(df, substr(ID, 1, 2) == "00")

df_final <- df_combo[, (ncol(df) + 1):ncol(df_combo)]

df_final$Description <- df_descr$Description

rm(df_descr, df_combo)

Output:

     ID Year       Description Name User Factor_1  Factor_2 Factor_3
1: 0011 2016 blue colour is nice  XXX  XLM     Yfac different     Yfac
2: 0024 2017  red colour is good  YYY  STM     Lfac    unique     Zfac