I have a long table and want to have a long table with the sum of each column and each row and total sum.
| AUX | Century | n |
|---|---|---|
| launch | 19 | 500 |
| launch | 20 | 500 |
| launch | 21 | 500 |
| throw | 21 | 500 |
| launch | 16 | 153 |
| throw | 20 | 125 |
| launch | 15 | 101 |
| launch | 17 | 95 |
| pull | 21 | 81 |
| throw | 19 | 55 |
| toss | 17 | 47 |
| launch | 18 | 40 |
| toss | 19 | 38 |
| launch | 13 | 32 |
| toss | 21 | 28 |
| toss | 16 | 20 |
| toss | 18 | 16 |
| launch | 14 | 15 |
| toss | 20 | 11 |
| pull | 18 | 8 |
| pull | 20 | 7 |
| shoot | 17 | 3 |
| shoot | 21 | 3 |
| pull | 15 | 2 |
| shoot | 19 | 1 |
| shoot | 20 | 20 |
I used this code:
wide.verb.century <- verb.century.counts |>
group_by(AUX) |>
tidyr::spread(Century, n)
And got this wide table
| AUX | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
|---|---|---|---|---|---|---|---|---|---|
| toss | NA | NA | NA | 20 | 47 | 16 | 38 | 11 | 28 |
| shoot | NA | NA | NA | NA | 3 | NA | 1 | 1 | 3 |
| launch | 32 | 15 | 101 | 153 | 95 | 40 | 500 | 500 | 500 |
| throw | NA | NA | NA | NA | NA | 55 | 125 | 500 | |
| pull | NA | NA | 2 | NA | NA | 8 | NA | 7 | 81 |
Question1: How can I change NA to 0 (zero)? I tried this code, but got an error message.
wide.verb.century <- verb.century.counts |>
group_by(AUX) |>
summarise(n = sum(n, na.rm = TRUE)) |>
tidyr::spread(Century, n)
Question2: How can I add a row at the bottom and a column at the right end that each sum the values of all rows and columns, respectively?
Desired output:
| AUX | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | Total |
|---|---|---|---|---|---|---|---|---|---|---|
| toss | 0 | 0 | 0 | 20 | 47 | 16 | 38 | 11 | 28 | 160 |
| shoot | 0 | 0 | 0 | 0 | 3 | 0 | 1 | 1 | 3 | 8 |
| launch | 32 | 15 | 101 | 153 | 95 | 40 | 500 | 500 | 500 | 1936 |
| throw | 0 | 0 | 0 | 0 | 0 | 55 | 125 | 500 | 680 | |
| pull | 0 | 0 | 2 | 0 | 0 | 8 | 0 | 7 | 81 | 98 |
| Total | 32 | 15 | 103 | 173 | 145 | 64 | 594 | 644 | 1112 | 2882 |
Any help would be greatly appreciated! Thank you!