CODEWITHSUNDEEP
rdataframe
user98235
user98235

Reputation: 906

Filling in the missing field for column in data frame in R

I have the following columns:

casenum  box type  number of balls in the box
  1         A               10
  1         B               20
  2         B               1
  2         C               2
  2         D               12
  3         A               10
  3         B               20
  3         C               1
  3         D               2
  .         .               .
  .         .               .
  .         .               .

Basically there are 4 box types (A, B, C, D) and for each casenum, if there's no balls in a box, it doesn't appear. However, I want each box type to appear like this. 

casenum  box type  number of balls in the box
  1         A               10
  1         B               20
  1         C               0
  1         D               0
  1         A               0
  2         B               1
  2         C               2
  2         D               12
  3         A               10
  3         B               20
  3         C               1
  3         D               2
  .         .               .
  .         .               .
  .         .               .

is there an easy way to do that?

Or I can have in in a format 

casenum    ballnum in A     ballnum in B     ballnum in C     ballnum in D 
  1            10                20              0                  0
  2            0                  1              2                 12
  3            10                20              1                  2
  .            .                  .              .                  .
  .            .                  .              .                  .

I used while loop to achieve this, but I was wondering if there's a way of doing it (using some libraries that I'm not aware of) without using loops.

Upvotes: 1

Views: 126

Answers (3)

akrun
akrun

Reputation: 887881

We can use acast from reshape2

library(reshape2)
acast(df, casenum~box, fill=0)
#   A  B C  D
#1 10 20 0  0
#2  0  1 2 12
#3 10 20 1  2

Upvotes: 1

989
989

Reputation: 12935

Its the job for xtabs in base R where df is your data frame:

xtabs(number~., df)

#       box
#casenum  A  B  C  D
#      1 10 20  0  0
#      2  0  1  2 12
#      3 10 20  1  2

Upvotes: 1

user3640617
user3640617

Reputation: 1576

I would create a new data.frame with all the possible combinations of box and casenum and then add the number of balls:

df<-read.table(text="casenum  box  number
1         A               10
1         B               20
2         B               1
2         C               2
2         D               12
3         A               10
3         B               20
3         C               1
3         D               2", header=T)

dftot <- data.frame(casenum=rep(1:3, each=4), box=c("A","B","C","D"), stringsAsFactors = F) #create new df with all combinations of casenum and box
dftot$number <- df$number[match(paste(dftot$casenum,dftot$box),paste(df$casenum, df$box))] #add numbers from your original data.frame
dftot$number[is.na(dftot$number)] <- 0 #change all NA values to 0

Upvotes: 1

Related Questions