How to update a summary data.frame field with a value from a different data.frame in R?

Question

I'm creating a summary data.frame with counts, N and a percentage. The summary data.frame has names -> category, n, count and percentage. The counts data.frame has names -> category, count. category is character. Often the rows in the counts data frame will be less than the rows in the summay.

The way I'd do this in MSSQL is

update summary
set summary.count = counts.count
from summary
inner join counts
on summary.category = counts.category

How could this be done in R?

Supporting code.

summary <- data.frame(category=c("apples","oranges","pears"),N=10,count=0,percentage=0)
> summary
  category  N count percentage
1   apples 10     0          0
2  oranges 10     0          0
3    pears 10     0          0
> counts <- data.frame( category=c("apples","pears"), count = c(5,5) )
> counts
  category count
1   apples     5
2    pears     5

# desired outcome after processing
> summary
  category  N count percentage
1   apples 10     5          0.5
2  oranges 10     0          0
3    pears 10     5          0.5

Thanks ... I discovered the merge command by searching for R data.frame union. — Keith John Hutchison, Dec 12 '16 at 00:10
Downvoters ... please comment if you think it's a not valid question. — Keith John Hutchison, Dec 12 '16 at 00:43

score 0 · Accepted Answer · edited May 23 '17 at 12:09

I discovered the merge command after searching for R data.frame union.

> summary <- data.frame(category=c("apples","oranges","pears"),N=10)
> summary
  category  N
1   apples 10
2  oranges 10
3    pears 10
> counts <- data.frame( category=c("apples","pears"), count = c(5,5) )
> counts
  category count
1   apples     5
2    pears     5
> merged <- merge( summary, counts, by="category", all.x = TRUE)
> merged
  category  N count
1   apples 10     5
2  oranges 10    NA
3    pears 10     5
> merged[is.na(merged)] <- 0
> merged
  category  N count
1   apples 10     5
2  oranges 10     0
3    pears 10     5
> merged["percentage"] = merged$count / merged$N
> merged
  category  N count percentage
1   apples 10     5        0.5
2  oranges 10     0        0.0
3    pears 10     5        0.5

To see an overview on how merge maps to SQL joins see How to join (merge) data frames (inner, outer, left, right)?

How to update a summary data.frame field with a value from a different data.frame in R?

1 Answers1