CODEWITHSUNDEEP
rdplyrtidyr
BarneyC
BarneyC

Reputation: 529

R Add missing columns AND rows of data (Dplyr/TidyR & Complete?)

I'm fairly used to adding in missing cases for data but this use case escapes me.

I have a number of dataframes (which differ slightly), an example would be:

> t1
  3 4 5
2 1 0 0
3 0 2 2
4 2 6 4
5 1 2 1

structure(list(`3` = c(1L, 0L, 2L, 1L), `4` = c(0L, 2L, 6L, 2L
), `5` = c(0L, 2L, 4L, 1L)), .Names = c("3", "4", "5"), row.names = c("2", 
"3", "4", "5"), class = "data.frame")

Row names & Column names should be from 1:5 and, obviously, where these were missing the cell value set to NA. For the example above this would give:

> t1
  1  2  3  4  5
1 NA NA NA NA NA
2 NA NA 1  0  0
3 NA NA 0  2  2
4 NA NA 2  6  4
5 NA NA 1  2  1

In each case ANY one or more rows AND/OR columns might be missing.

I can readily get the missing columns using the method described by Josh O'Brien here but am missing the row method.

Can anyone help?

Upvotes: 4

Views: 1469

Answers (2)

Fadwa
Fadwa

Reputation: 1931

Based on the solution you mentioned by Josh O'Brien, you can do the same but use rownames instead of names. Take a look at the code below.. 

df <- data.frame(a=1:4, e=4:1)
colnms <- c("a", "b", "d", "e") 
rownms <- c("1", "2", "3", "4", "5")
rownames(df) <- c("1", "3", "4", "5")

## find missing columns and replace with zero, and order them
Missing <- setdiff(colnms, names(df))
df[Missing] <- 0
df <- df[colnms]
df

## do the same for rows
MissingR <- setdiff(rownms, rownames(df))
df[MissingR,] <- 0
df <- df[rownms,]
df

# > df
#  a b d e
#1 1 0 0 4
#2 0 0 0 0
#3 2 0 0 3
#4 3 0 0 2
#5 4 0 0 1

Upvotes: 0

akrun
akrun

Reputation: 886938

We can do this in a much easier way with base R by creating a matrix of NAs of the required dimensions and then assign the values of 't1' based on the row names and column names of 't1'

m1 <- matrix(NA, ncol=5, nrow=5, dimnames = list(1:5, 1:5))
m1[row.names(t1), colnames(t1)] <- unlist(t1)
m1
#   1  2  3  4  5
#1 NA NA NA NA NA
#2 NA NA  1  0  0
#3 NA NA  0  2  2
#4 NA NA  2  6  4
#5 NA NA  1  2  1

Or using tidyverse

library(tidyverse)
rownames_to_column(t1, "rn") %>% 
      gather(Var, Val, -rn) %>% 
      mutate_at(vars(rn, Var), as.integer) %>%
      complete(rn = seq_len(max(rn)), Var = seq_len(max(Var))) %>% 
      spread(Var, Val)
# A tibble: 5 × 6
#     rn   `1`   `2`   `3`   `4`   `5`
#* <int> <int> <int> <int> <int> <int>
#1     1    NA    NA    NA    NA    NA
#2     2    NA    NA     1     0     0
#3     3    NA    NA     0     2     2
#4     4    NA    NA     2     6     4
#5     5    NA    NA     1     2     1

Upvotes: 2

Related Questions