I have a large dataframe that looks like the following example:
Say that there is a ball game, where you can throw the ball to hit a target and get points. There is a team A and B, and everyone can decide how many times they want to throw the ball (so this is unequal). It looks like this:
df<-data.frame("ID" = as.factor(c(1,2,3,4,5,6,7,8,9,10)),
"Group" = c("A","A","A","A","A","B","B","B","B","B"),
"Number_of_hits" = c(1,2,5,3,3,1,2,1,1,3),
"Score_per_hit"=c("60","23.4/18.1","30/34.1/40/10.2/15.5","12/15.0/40","40/23.1/22","20.5","64.8/12.2","50","30","20/43.3/65"))
ID Group Number_of_hits Score_per_hit
1 A 1 60
2 A 2 23.4/18.1
3 A 5 30/34.1/40/10.2/15.5
4 A 3 12/15.0/40
5 A 3 40/23.1/22
6 B 1 20.5
7 B 2 64.8/12.2
8 B 1 50
9 B 1 30
10 B 3 20/43.3/65
Now, I would like to change it in a way that I can do something with the Scoring, so I would like it to be like this:
ID Group Score_per_hit
1 A 60.0
2 A 23.4
2 A 18.1
3 A 30.0
3 A 34.1
3 A 40.0
3 A 10.2
3 A 15.5
4 A 12.0
4 A 15.0
4 A 40.0
5 A 40.0
5 A 23.1
5 A 22.0
etc..
I have seen/tried some things with str_extract but I only get errors.. Could somebody help me? It's very much appreciated!
There may be a more elegant way, but here is an option using a for loop:
df2 <- vector("list", nrow(df))
for(i in seq(df2)){
df2[[i]] <- expand.grid(
ID = df$ID[i],
Group=df$Group[i],
Score_per_hit = as.numeric(
strsplit(as.character(df$Score_per_hit[i]), "/", fixed = TRUE)[[1]])
)
}
df2 <- do.call("rbind", df2)
df2