I don't get the right way to compute a new column which contains a sum up of a column in a data frame by aggregating anothor column by a grep function. The data frame looks like follows:
ID_Oeffnungszeit ID_Einrichtung Anzahl_Std Bez_Oeffnungszeit
1 3000001 0.50 Montag Vormittag
1 3000003 3.00 Montag Vormittag
1 3000008 2.50 Montag Vormittag
2 3000001 1.00 Montag Nachmittag
4 3000003 1.50 Dienstag Vormittag
1 3000023 1.00 Montag Vormittag
1 3000025 1.00 Montag Vormittag
1 3000026 1.00 Montag Vormittag
3 3000001 2.00 Montag Abend
...
1 3000038 3.50 Montag Vormittag
and so on...
There are 21 characteristics for the variable Bez_Oeffnungszeit, but they can be categorized due to the fact if they contain the words Vormittag, Nachmittag or Abend.
So I would like to generate a new column which contains the sums of the column Anzahl_Std for each ID_Einrichtung depending on which word the Bez_Oeffnungszeit contains.
I'm quite sure that I should use the grep and the by functions, but I couldn't manage to make the it work in my sense...
df <- structure(list(ID_Oeffnungszeit = c("1", "1", "1", "2", "4",
"1", "1", "1", "3"), ID_Einrichtung = c(3000001L, 3000003L, 3000008L,
3000001L, 3000003L, 3000023L, 3000025L, 3000026L, 3000001L),
Anzahl_Std = c(0.5, 3, 2.5, 1, 1.5, 1, 1, 1, 2), Bez_Oeffnungszeit = c("Montag Vormittag",
"Montag Vormittag", "Montag Vormittag", "Montag Nachmittag",
"Dienstag Vormittag", "Montag Vormittag", "Montag Vormittag",
"Montag Vormittag", "Montag Abend")), .Names = c("ID_Oeffnungszeit",
"ID_Einrichtung", "Anzahl_Std", "Bez_Oeffnungszeit"), class = "data.frame", row.names = c(NA,
-9L))
I think the easiest way is to create a new column only containing the word "Vormittag", "Nachmittag" or "Abend" and then use that as a grouping variable.
For instance:
# Set random seed for reproducibility
set.seed(12345)
# Make some data
my.data <- data.frame(ID_Oeffnungszeit = sample(1:10, 100, replace = TRUE),
ID_Einrichtung = sample(3000001:3000020, 100, replace = TRUE),
Anzahl_Std = rnorm(100),
Bez_Oeffnungszeit = sample(c("Montag Vormittag",
"Montag Nachmittag",
"Dienstag Vormittag",
"Montag Abend"), 100,
replace = T))
# Create a new column containing "Vormittag", "Nachmittag" or "Abend"
my.data$cat <- ""
my.data$cat[grep("Vormittag", my.data$Bez_Oeffnungszeit)] <- "Vormittag"
my.data$cat[grep("Nachmittag", my.data$Bez_Oeffnungszeit)] <- "Nachmittag"
my.data$cat[grep("Abend", my.data$Bez_Oeffnungszeit)] <- "Abend"
# Now just call aggregate over the Std variable using ID and category
# as grouping factors.
res <- aggregate(my.data$Anzahl_Std,
by = list(ID = my.data$ID_Einrichtung, cat = my.data$cat),
FUN = sum)