Fill missing values in column and then filter

user_14167846 0
Viewed 64

I'm scraping some PDF data and it needs some attention before I'm ready to move onto the next step. Column x1 is a unique ID, and then x2 is the type of acquisition. I need to replicate the UID across those types so that I can filter based on the type. Below is some sample data as well as what I want to get to. I am specifically interested in type4 right now, but may need the other types later on.

I don't have a code I've tried because I'm not sure how to approach this problem. The acquisition types are consistent- in example data, there will always be 4 types, and the UID does appear in both the x1 and x2 columns.

How Data Reads Into R:

df <- data.frame(x1 = c(100, "", "", "", "", 101, "", "", "", "", 102, "", "", "", ""),
                 x2 = c(100, "type1", "type2", "type3", "type4", 101, "type1", "type2", "type3", "type4", 102, "type1", "type2", "type3", "type4"),
                 x3 = c(1:15),
                 x4 = c(31:45),
                 x5 = c(100:114))

    x1    x2 x3 x4  x5
1  100   100  1 31 100
2      type1  2 32 101
3      type2  3 33 102
4      type3  4 34 103
5      type4  5 35 104
6  101   101  6 36 105
7      type1  7 37 106
8      type2  8 38 107
9      type3  9 39 108
10     type4 10 40 109
11 102   102 11 41 110
12     type1 12 42 111
13     type2 13 43 112
14     type3 14 44 113
15     type4 15 45 114

Desired Data

goal <- data.frame(x1 = c(100, 101, 102),
                   x2 = c("type4", "type4", "type4"),
                   x3 = c(5, 10, 15),
                   x4 = c(35, 40, 45),
                   x5 = c(104, 109, 114))

   x1    x2 x3 x4  x5
1 100 type4  5 35 104
2 101 type4 10 40 109
3 102 type4 15 45 114

I am also open to a different way to solve this problem, but duplicating the UID down until there is a new UID is the way I imagine working the best.

missing-data na r
2 Answers

As requested - first fill, then filter... This is using only the tidyverse. I have replaced the empty string "" with NA first.

library(tidyverse)
df <- data.frame(x1 = c(100, "", "", "", "", 101, "", "", "", "", 102, "", "", "", ""),
                 x2 = c(100, "type1", "type2", "type3", "type4", 101, "type1", "type2", "type3", "type4", 102, "type1", "type2", "type3", "type4"),
                 x3 = c(1:15),
                 x4 = c(31:45),
                 x5 = c(100:114))

df %>% 
  mutate(x1 = as.integer(x1)) %>%
  fill(x1) %>%
  filter(x2 == "type4")

#>    x1    x2 x3 x4  x5
#> 1 100 type4  5 35 104
#> 2 101 type4 10 40 109
#> 3 102 type4 15 45 114

Another way makes use of the apparently very ordered and regular structure of your data: (using base R only in this example)

x1 <- na.omit(as.integer(df$x1))
df2 <- subset(df, x2 == "type4")
df2$x1 <- x1

df2
#>     x1    x2 x3 x4  x5
#> 5  100 type4  5 35 104
#> 10 101 type4 10 40 109
#> 15 102 type4 15 45 114
user_79411880

Split-combine using by. Recycling first element of x1 using transform.

res <- `rownames<-`(do.call(rbind, by(df, rep(1:(nrow(df)/5), each=5), function(x) {
  transform(x, x1=x1[1])
})), NULL)
res
#     x1    x2 x3 x4  x5
# 1  100   100  1 31 100
# 2  100 type1  2 32 101
# 3  100 type2  3 33 102
# 4  100 type3  4 34 103
# 5  100 type4  5 35 104
# 6  101   101  6 36 105
# 7  101 type1  7 37 106
# 8  101 type2  8 38 107
# 9  101 type3  9 39 108
# 10 101 type4 10 40 109
# 11 102   102 11 41 110
# 12 102 type1 12 42 111
# 13 102 type2 13 43 112
# 14 102 type3 14 44 113
# 15 102 type4 15 45 114

Then filter at will.

res[res$x2 %in% "type4", ]
#     x1    x2 x3 x4  x5
# 5  100 type4  5 35 104
# 10 101 type4 10 40 109
# 15 102 type4 15 45 114

Note: `rownames<-`(..., NULL) is just cosmetics, you can also leave it out.

user_65740380
Related