CODEWITHSUNDEEP
rdataframedplyr
JWH2006
JWH2006

Reputation: 239

combining two rows in R (appending one row to the end of the other)

I have the following dataset

df <- structure(list(
X1 = c("A1", "1", "A2", "2", "A3", "3"), 
X2 = c("B1", NA, "B2", NA, "B3", NA),
X3 = c("C1", NA, "C2", NA, "C3", NA),
X4 = c("D", "D", "D", "D", "D", "D")),
class = "data.frame", row.names = c(NA, -6L))

What I am trying to do is get to the following dataset

df <- structure(list(
X1 = c("A1", "A2", "A3"), 
X2 = c("B1", "B2", "B3"),
X3 = c("C1", "C2", "C3"),
X4 = c("D", "D", "D"),
X5 = c("1", "2", "3")),
class = "data.frame", row.names = c(NA, -3L))

In short, what I need to do is take the even rows with the NAs and append that to the end of the odd rows.

Any help would be appreciated.

Upvotes: 1

Views: 158

Answers (1)

akrun
akrun

Reputation: 887118

Create a 'grp' based on the occurrence of NA in 'X2', then create the 'X5' as the last value from 'X1', ungroup and remove the NA rows with na.omit

library(dplyr)
df %>% 
  group_by(grp =  cumsum(lag(is.na(X2), default = TRUE))) %>% 
  mutate(X5 = last(X1)) %>% 
  ungroup %>% 
  select(-grp) %>%
  na.omit

-output

# A tibble: 3 x 5
#  X1    X2    X3    X4    X5   
#  <chr> <chr> <chr> <chr> <chr>
#1 A1    B1    C1    D     1    
#2 A2    B2    C2    D     2    
#3 A3    B3    C3    D     3

Or another option is to make use of the even condition

library(tidyr)
df %>%
    mutate(X5 = case_when(row_number() %%2 == 0  ~X1)) %>% 
    fill(X5, .direction = 'updown') %>%
    na.omit
#  X1 X2 X3 X4 X5
#1 A1 B1 C1  D  1
#3 A2 B2 C2  D  2
#5 A3 B3 C3  D  3

Or with base R

df$X5 <- NA
df$X5[c(TRUE, FALSE)] <- df$X1[c(FALSE, TRUE)]
out <- na.omit(df)

Upvotes: 1

Related Questions