I have a data frame like this:
Date Amount Category
1 02.07.15 1 1
2 02.07.15 2 1
3 02.07.15 3 1
4 02.07.15 4 2
5 03.07.15 5 2
6 04.07.15 6 3
7 05.07.15 7 3
8 06.07.15 8 3
9 07.07.15 9 4
10 08.07.15 10 5
11 09.07.15 11 6
12 10.07.15 12 4
13 11.07.15 13 4
14 12.07.15 14 5
15 13.07.15 15 5
16 14.07.15 16 6
17 15.07.15 17 6
18 16.07.15 18 5
19 17.07.15 19 4
I would like to calculate the sum of the amount for each single day in a category. My attempts like (see the code) are both not sufficient.
summarise(group_by(testData, Category), sum(Amount))
Wrong output --> here the sum is calculated over each group
Category sum(Amount)
1 1 6
2 2 9
3 3 21
4 4 53
5 5 57
6 6 44
summarise(group_by(testData, Date), sum(Amount), categories = toString(Category))
Wrong output --> here the sum is calculated over each day but the categories are not considered
Date sum(Amount) categories
1 02.07.15 10 1, 1, 1, 2
2 03.07.15 5 2
3 04.07.15 6 3
4 05.07.15 7 3
5 06.07.15 8 3
6 07.07.15 9 4
7 08.07.15 10 5
8 09.07.15 11 6
9 10.07.15 12 4
10 11.07.15 13 4
11 12.07.15 14 5
12 13.07.15 15 5
13 14.07.15 16 6
14 15.07.15 17 6
15 16.07.15 18 5
16 17.07.15 19 4
So far I did not succeed in combining both statements. How can I nest both group_by statements to calculate the sum of the amount for each single day in each category?
Nesting the groups like:
summarise(group_by(group_by(testData, Date), Category), sum(Amount), dates = toString(Date))
Category sum(Amount) dates
1 1 6 02.07.15, 02.07.15, 02.07.15
2 2 9 02.07.15, 03.07.15
3 3 21 04.07.15, 05.07.15, 06.07.15
4 4 53 07.07.15, 10.07.15, 11.07.15, 17.07.15
5 5 57 08.07.15, 12.07.15, 13.07.15, 16.07.15
6 6 44 09.07.15, 14.07.15, 15.07.15
does not work as intended.
I have heard of dplyr - summarise weighted data summarise_each but could not get it to work:
summarise_each(testData, funs(Category))
Error could not find function Category