R: Adding a column of a conditional observation count

Question

I am looking to add a column to my data that will list the individual count of the observation in the dataset. I have data on NBA teams and each of their games. They are listed by date, and I want to create a column that lists what # in each season each game is for each team.

My data looks like this:

# gmDate         teamAbbr opptAbbr    id 
# 2012-10-30     WAS       CLE      2012-10-30WAS
# 2012-10-30     CLE       WAS       2012-10-30CLE
# 2012-10-30     BOS       MIA       2012-10-30BOS

Commas separate each column

I've tried to use "add_count" but this has provided me with the total # of games each team has played in the dataset.

Prior attempts:

nba_box %>% add_count()

I expect the added column to display the # game for each team (1-82), but instead it now shows the total number of games in the dataset (82).

What about `nba_box %>% arrange(gmDate) %>% group_by(teamAbbr) %>% seq_along()`? — Rui Barradas, May 05 '19 at 21:54
Or, if my previous comment is completely off, use `seq_along()` instead of `add_count()`. — Rui Barradas, May 05 '19 at 21:56
Look at the docs for `add_count`: it adds the count of observations within a group. In this case, that means how many games a team played. Instead, it sounds like you want to add a column (such as with `mutate`) that contains a row number for each team. — camille, May 05 '19 at 22:20
`nba_box %>% group_by(teamAbbr) %>% mutate(rn = row_number())` ? — Ronak Shah, May 05 '19 at 23:20

Evan Friedland · Answer 1 · 2019-05-05T22:38:07.047

Here is a base R example that approaches the problem from a for loop standpoint. Given that a team can be either column, we keep track of the teams position by unlisting the data and using the table function to sum the previous rows.

# intialize some fake data
test <- as.data.frame(t(replicate(6, sample( LETTERS[1:3],2))),
                      stringsAsFactors = F)
colnames(test) <- c("team1","team2")

# initialize two new columns
test$team2_gamenum <- test$team1_gamenum <- NA
count <- NULL
for(i in 1:nrow(test)){
  out <- c(count, table(unlist(test[i,c("team1","team2")])))
  count <- table(rep(names(out), out)) # prob not optimum way of combining two table results
  test$team1_gamenum[i] <- count[which(names(count) == test[i,1])]
  test$team2_gamenum[i] <- count[which(names(count) == test[i,2])]
}
test
#  team1 team2 team1_gamenum team2_gamenum
#1     B     A             1             1
#2     A     C             2             1
#3     C     B             2             2
#4     C     B             3             3
#5     A     C             3             4
#6     A     C             4             5

R: Adding a column of a conditional observation count

1 Answers1