CODEWITHSUNDEEP
rdataframe
MonahAbouAlezz
MonahAbouAlezz

Reputation: 55

Shift certain values in a column of dataframe to a next column in R

I have a dataframe that looks like the following:

x y z
1 2 3
1 2 3
1 2 3
2 3
1 2 3
1 3

I would like to ask if there is a command in R that will allow to obtain the following dataframe (by shifting and aligning similar values)

x y z
1 2 3
1 2 3
1 2 3
NA 2 3
1 2 3
1 NA 3

Upvotes: 0

Views: 219

Answers (3)

AntoniosK
AntoniosK

Reputation: 16121

An alternative solution, where the main idea is to capture the pattern of your dataset based on rows that don't have NAs and then perform some reshaping using the pattern you captured.

df = read.table(text = "
x y z
1 2 3
1 2 3
1 2 3
2 3 NA
1 2 3
1 3 NA
", header= T)

library(tidyverse)

# get the column names of your dataset
names = names(df)

# get unique values after omitting rows with NAs
value = unlist(unique(na.omit(df)))

# create a dataset with names and values
# (this is the pattern you want to follow)
df3 = data.frame(names, value)


df %>%
  mutate(id = row_number()) %>%   # flag the row number
  gather(v,value,-id) %>%         # reshape
  na.omit() %>%                   # remove rows with NAs
  left_join(df3, by="value") %>%  # join info about your pattern
  select(-v) %>%                  # remove that column
  spread(names, value) %>%        # reshape
  select(-id)                     # remove row number

#    x  y z
# 1  1  2 3
# 2  1  2 3
# 3  1  2 3
# 4 NA  2 3
# 5  1  2 3
# 6  1 NA 3

Upvotes: 1

IceCreamToucan
IceCreamToucan

Reputation: 28705

library(tidyverse)

df %>% 
  pmap_dfr(~{ x <- list(...)
              if(any(is.na(x))) intersect(x, df[1,]) # match with first row's values to assign names
              else x})

Output:

# # A tibble: 6 x 3
#       x     y     z
#   <int> <int> <int>
# 1     1     2     3
# 2     1     2     3
# 3     1     2     3
# 4    NA     2     3
# 5     1     2     3
# 6     1    NA     3

Upvotes: 0

Andre Elrico
Andre Elrico

Reputation: 11500

reading your data: 

df<- fread("x y z
    1 2 3
    1 2 3
    1 2 3
    2 3 NA
    1 2 3
    1 3 NA") %>% setDF

code: 

library(magrittr)
getmode <- function(v) {
    uniqv <- unique(v)
    uniqv[which.max(tabulate(match(v, uniqv)))]
}

pattern <- sapply(df,getmode)

df[!complete.cases(df),] %<>% apply(1,function(x){tmp<-pattern;tmp[!(tmp%in%x)] <- NA;return(tmp)}) %>% t %>% data.frame

result:

> df
   x  y z
1  1  2 3
2  1  2 3
3  1  2 3
4 NA  2 3
5  1  2 3
6  1 NA 3

Upvotes: 0

Related Questions