How can I determine the number of times an individual has won a fight?-CodePudding

I want to combine my winner and loser sequences with another dataset that contains their sex so that I end up with a table that has the total number of wins over males per female, and the group that they are in. I will do this for several different groups

This is one group (C.1), there are 591 rows

   Winner  Loser
1   George   Paul
2   George   Paul
3   George   Paul
4    Horst   Paul
5      Tom Louise
6   George  Horst
7   George Louise
8      Tom  Nobel
9   George  Adele
10   Rufus   Paul

I want to combine it with C1dat with their sex

Individual
Adele   F
George  M
Horst   M
Laggie  M
Louise  F
Max     M
Nobel   F
Paul    M
Rufus   M
Tom     M

And produce a table that looks like this

Individual female      Number of wins against males          Group
Adele                                                         C.1
Louise                                                        C.1
Nobel                                                         C.1

CodePudding user response：

I edited your data a little bit just so that there would be something to see

df1=structure(list(Winner = c("George", "George", "George", "Horst", 
"Tom", "George", "George", "Tom", "George", "Rufus"), Loser = c("Paul", 
"Paul", "Paul", "Paul", "Louise", "Horst", "Louise", "Nobel", 
"Adele", "Paul")), class = "data.frame", row.names = c("1", "2", 
"3", "4", "5", "6", "7", "8", "9", "10"))

df1$Group="C1"
df1=rbind(
  df1,
  cbind("Winner"=df1$Loser,"Loser"=df1$Winner,"Group"="C2")
)

df2=structure(list(Individual = c("F", "M", "M", "M", "F", "M", "F", 
"M", "M", "M")), class = "data.frame", row.names = c("Adele", 
"George", "Horst", "Laggie", "Louise", "Max", "Nobel", "Paul", 
"Rufus", "Tom"))

nms=setNames(rownames(df2),df2$Individual)

and for the main part

setNames(
  aggregate(
    Loser~Group Winner,
    df1[
      names(nms[match(df1$Winner,nms)])=="F" & names(nms[match(df1$Loser,nms)])=="M",
      c("Group","Winner","Loser")
    ],
    length
  ),
  c("Group","Individual female","Number of wins against males")
)

resulting in

  Group Individual female Number of wins against males
1    C2             Adele                            1
2    C2            Louise                            2
3    C2             Nobel                            1

CodePudding user response：

You can try

data

x <- "
Winner  Loser
1   George   Paul
2   George   Paul
3   George   Paul
4    Horst   Paul
5      Tom Louise
6   George  Horst
7   George Louise
8      Tom  Nobel
9   George  Adele
10   Rufus   Paul"

df <- read.table(textConnection(x) , header = T)


x <- "
Individual sex
Adele   F
George  M
Horst   M
Laggie  M
Louise  F
Max     M
Nobel   F
Paul    M
Rufus   M
Tom     M"

gender <- read.table(textConnection(x) , header = T)

then add tow columns of gender of winners and losers

library(dplyr)

df$wingender <- sapply(1:nrow(df) , \(x) gender$sex[which(df$Winner[x] == gender$Individual)])

df$lossgender <- sapply(1:nrow(df) , \(x) gender$sex[which(df$Loser[x] == gender$Individual)])


df |> filter(wingender == "F" & lossgender == "M") |> group_by(Winner) |> summarise(n())