R table with values of all rows and columns

Question

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!

@Edward I did this and got an error message wide.verb.century <- verb.century.counts |> group_by(AUX) |> tidyr::spread(Century, n, fill = 0) |> addmargins(as.matrix(wide.verb.century[,-1])) — Freya
– Freya, Commented Aug 14, 2024 at 10:41

B. Christian Kamgang · Accepted Answer · 2024-08-14 11:01:23Z

Onw way to solve your problem:

addmargins(xtabs(n ~ AUX + Century, df)) 

        Century
AUX        13   14   15   16   17   18   19   20   21  Sum
  launch   32   15  101  153   95   40  500  500  500 1936
  pull      0    0    2    0    0    8    0    7   81   98
  shoot     0    0    0    0    3    0    1   20    3   27
  throw     0    0    0    0    0    0   55  125  500  680
  toss      0    0    0   20   47   16   38   11   28  160
  Sum      32   15  103  173  145   64  594  663 1112 2901

In case you would like the output to be a data.frame/tibble instead of a table object, then use

addmargins(xtabs(n ~ AUX + Century, df)) |> 
  unclass() |> 
  as_tibble(rownames="AUX")

# A tibble: 6 × 11
  AUX     `13`  `14`  `15`  `16`  `17`  `18`  `19`  `20`  `21`   Sum
  <chr>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 launch    32    15   101   153    95    40   500   500   500  1936
2 pull       0     0     2     0     0     8     0     7    81    98
3 shoot      0     0     0     0     3     0     1    20     3    27
4 throw      0     0     0     0     0     0    55   125   500   680
5 toss       0     0     0    20    47    16    38    11    28   160
6 Sum       32    15   103   173   145    64   594   663  1112  2901

Collectives™ on Stack Overflow

R table with values of all rows and columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related