r - Merge two tables and aggregate

Question

I have two tables name and grades which looks like:

table:name

Male   Female

Sam    Tina
       Anna

table:grades

Class  Name  Grades
A      Sam   5
B      Sam   5
A      Tina  5
B      Tina  5
C      Tina  5
A      Anna  5

After merging the new table should looks like:

Class Gender Grades
A     Male   5
B     Male   5
A     Female 10
B     Female 5
C     Female 5

I have tried groupby, merge, but it always give me a wrong table. Is there any function that I can use to get the right table? Thanks!!

标签： r

Answer 1

使用stack将名称数据帧转换为长格式。然后像往常一样合并，然后聚合Gender和Class：

aggregate(Grades ~ Class + ind,
          merge(grades, na.omit(stack(name)), by.x = "Name", by.y = "values"), sum)
#   Class    ind Grades
# 1     A   Male      5
# 2     B   Male      5
# 3     A Female     10
# 4     B Female      5
# 5     C Female      5

#data
name <- read.table(text = "
Male Female
Sam Tina
NA Anna", header = TRUE)

grades <- read.table(text = "
Class  Name  Grades
A      Sam   5
B      Sam   5
A      Tina  5
B      Tina  5
C      Tina  5
A      Anna  5", header = TRUE)