CODEWITHSUNDEEP
rloopsdataframerows
user2543427
user2543427

Reputation: 133

R Finding the missing rows

Suppose I have two data frames, df1,df2, that look like:

df1

        User        Lab      Score
         A           1        52
         A           2        65
         A           3        87
         A           5        78
         B           2        56
         B           4        98
         C           6        78

Then I have another data frame where I have isolated User A from the rest of the data frame.

df2

        User       Lab      Score
        A          1         52
        A          2         65
        A          3         87
        A          5         78

If I want to create a data frame that will include all of the rows from df2, and if the user has not attempted the labs, for their score to 0. So I would want:

df3

        User       Lab      Score
        A          1         52
        A          2         65
        A          3         87
        A          4         0
        A          5         78                
        A          6         0

Upvotes: 1

Views: 67

Answers (4)

lauratboyer
lauratboyer

Reputation: 334

If you are willing to work with a matrix (and you have unique records for each User/Lab) then you could use tapply:

mat1 <- tapply(df1$Score, list(df1$Lab, df1$User), mean)
mat1[is.na(mat1)] <- 0
mat1

#   A   B  C
# 1 52  0  0
# 2 65 56  0
# 3 87  0  0
# 4  0 98  0
# 5 78  0  0
# 6  0  0 78

Now each of the columns represent a user and it is easy to extract statistics by users:

apply(mat1, 2, mean)
apply(mat1, 2, sd)

Upvotes: 0

brown10
brown10

Reputation: 21

User<-c(rep('A',4),rep('B',2),'C')
Lab<-c(1,2,3,4,5,2,6)
Score<-c(52,65,87,78,56,98,78)
df1<-data.frame(User,Lab,Score)
df2<-df1[df1$User=='A',]
## find different value of df1$Lab and df2$Lab
s1<-unique(df1$Lab)
s2<-unique(df2$Lab)
## compute length
f1<-length(s1)-length(s2)
f2<-dim(df2)[1]
## initiate df3
df3<-df2
## expand df3, setdiff means the difference of two sets 
df3[(f2+1):(f2+f1),]<-data.frame(rep('A',f1),setdiff(s1,s2),rep(0,f1))

Upvotes: 0

Se&#241;or O
Se&#241;or O

Reputation: 17412

Why not do it all at once with data.table?

FinalTable = data.table(User=unique(df1$User))[,list(Lab=1:6),by=User]
setkey(FinalTable, User, Lab)
setkey(df1, User, Lab)
FinalTable = df1[FinalTable]
FinalTable[is.na(Score),Score:=0L]

The Result:

    User Lab Score
 1:    A   1    52
 2:    A   2    65
 3:    A   3    87
 4:    A   4     0
 5:    A   5    78
 6:    A   6     0
 7:    B   1     0
 8:    B   2    56
 9:    B   3     0
10:    B   4    98
11:    B   5     0
12:    B   6     0
13:    C   1     0
14:    C   2     0
15:    C   3     0
16:    C   4     0
17:    C   5     0
18:    C   6    78

Upvotes: 2

Mayou
Mayou

Reputation: 8818

I am sure there is a better way of doing this, but here is what I can come up with:

 ##Get unique lab IDs
 labs = as.data.frame(unique(df$Lab))
 names(labs) <- "Lab"

 df3 = merge(df2, labs, all.y = TRUE)
 df3$User[is.na(df3$User)] <- "A"
 df3[is.na(df3)] <- 0
 df3 <- df3[,c(2,1,3)]


 #> df3
 #  User Lab Score
 #1    A   1    52
 #2    A   2    65
 #3    A   3    87
 #4    A   4     0
 #5    A   5    78
 #6    A   6     0

Upvotes: 1

Related Questions