Transposing and Filling NAs in R

user_13503034 0
Viewed 42

My data.frame df1 looks like this:

A NA NA B NA NA C NA NA
1  2  3 4  5  6 7  8  9

I want it to look like this:

A 1
A 2
A 3
B 4
B 5
B 6
C 7
C 8
C 9

Any ideas?

spread tidyr data-manipulation r
2 Answers

Suppose you have a data.frame like this:

df <- data.frame(matrix(c("A", NA, NA, "B", NA, NA, "C", NA, NA, 1:9), byrow = TRUE, nrow = 2))

> df
  X1   X2   X3 X4   X5   X6 X7   X8   X9
1  A <NA> <NA>  B <NA> <NA>  C <NA> <NA>
2  1    2    3  4    5    6  7    8    9

Using tidyr:

df %>%
  t() %>% 
  as.data.frame() %>%
  fill(V1, .direction = "down")

gives you

  V1 V2
1  A  1
2  A  2
3  A  3
4  B  4
5  B  5
6  B  6
7  C  7
8  C  8
9  C  9
user_125052510

An option with na.locf from zoo

library(zoo)
na.locf(as.data.frame(t(df)))
#   V1 V2
#X1  A  1
#X2  A  2
#X3  A  3
#X4  B  4
#X5  B  5
#X6  B  6
#X7  C  7
#X8  C  8
#X9  C  9

data

df <- data.frame(matrix(c("A", NA, NA, "B", NA, NA, "C", NA, NA, 1:9),
byrow = TRUE, nrow = 2))
user_37322710
Related