for(i in 1:5){
adms_list[[i]] <- readxl::read_excel("_data/ActiveDuty_MaritalStatus.xls", sheet = sheet_names[i], range = "B9:Q31")
}Challenge 8
We’re going to be working with the dataset ActiveDuty_MaritalStatus.xls which is an Excel document with sheets. We’re going to first read in the sheet using this for loop.
Then we will look at a preview of one of the sheets available to us.
# A tibble: 6 × 16
`Pay Grade` Male...2 Female...3 Total...4 Male...5 Female...6 Total...7
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 E-1 31229 5717 36946 563 122 685
2 E-2 53094 8388 61482 1457 275 1732
3 E-3 131091 21019 152110 4264 1920 6184
4 E-4 112710 16381 129091 9491 4662 14153
5 E-5 57989 11021 69010 10937 6576 17513
6 E-6 19125 4654 23779 10369 4962 15331
# ℹ 9 more variables: Male...8 <dbl>, Female...9 <dbl>, Total...10 <dbl>,
# Male...11 <dbl>, Female...12 <dbl>, Total...13 <dbl>, Male...14 <dbl>,
# Female...15 <dbl>, Total...16 <dbl>Now we will merge all the sheets together and add a column labeled Branch to see the datasets that have been combined.
Branch Pay Grade Male_SWOC Female_SWOC Total_SWOC Male_SWC
1 Marine E-1 6232 583 6815 54
2 Air Force E-1 7721 1550 9271 27
3 Navy E-1 7820 2275 10095 117
4 Army E-1 9456 1309 10765 365
5 Marine E-2 4380 1010 5390 33
6 Air Force E-2 11198 2718 13916 237
7 Navy E-2 15916 1336 17252 190
8 Army E-2 21600 3324 24924 997
9 Marine E-3 28163 6396 34559 681
10 Air Force E-3 29725 7108 36833 396
11 Navy E-3 34868 1864 36732 574
12 Army E-3 38335 5651 43986 2613
13 Marine E-4 17862 1026 18888 527
14 Air Force E-4 20805 4756 25561 987
15 Navy E-4 23285 4266 27551 967
16 Army E-4 50758 6333 57091 7010
17 Marine E-5 7490 590 8080 931
18 Air Force E-5 14623 4104 18727 2755
19 Navy E-5 17020 2678 19698 4414
20 Army E-5 18856 3649 22505 2837
21 Marine E-6 2051 208 2259 1060
22 Air Force E-6 3660 1377 5037 2446
23 Navy E-6 5917 1429 7346 2725
24 Army E-6 7497 1640 9137 4138
25 Marine E-7 565 72 637 622
26 Air Force E-7 1294 395 1689 1371
27 Navy E-7 1441 617 2058 1539
28 Army E-7 2146 829 2975 2998
29 Marine E-8 148 21 169 270
30 Air Force E-8 182 139 321 236
31 Navy E-8 219 84 303 329
32 Army E-8 460 194 654 951
33 Marine E-9 68 10 78 106
34 Air Force E-9 83 48 131 106
35 Navy E-9 96 56 152 118
36 Army E-9 134 88 222 249
37 Marine GRAND TOTAL 94800 25247 120047 9544
38 Air Force O-1 2227 204 2431 26
39 Navy O-1 3529 911 4440 83
40 Army O-1 3831 1068 4899 45
41 Marine O-1 3908 898 4806 248
42 Air Force O-10 0 0 0 0
43 Navy O-10 0 0 0 0
44 Army O-10 0 0 0 0
45 Marine O-10 1 0 1 0
46 Air Force O-2 1509 133 1642 29
47 Navy O-2 2358 664 3022 98
48 Army O-2 2710 819 3529 63
49 Marine O-2 4452 1099 5551 236
50 Air Force O-3 1510 200 1710 110
51 Navy O-3 3263 1177 4440 338
52 Army O-3 4035 1642 5677 338
53 Marine O-3 5743 2037 7780 656
54 Air Force O-4 319 62 381 127
55 Navy O-4 862 380 1242 274
56 Army O-4 1077 597 1674 293
57 Marine O-4 1222 681 1903 496
58 Air Force O-5 76 7 83 55
59 Navy O-5 344 209 553 168
60 Army O-5 399 294 693 294
61 Marine O-5 425 300 725 212
62 Air Force O-6 23 7 30 17
63 Navy O-6 99 112 211 74
64 Army O-6 101 107 208 67
65 Marine O-6 130 123 253 103
66 Air Force O-7 0 2 2 1
67 Navy O-7 1 0 1 2
68 Army O-7 2 1 3 1
69 Marine O-7 2 4 6 3
70 Air Force O-8 0 0 0 0
71 Navy O-8 1 1 2 0
72 Army O-8 1 3 4 0
73 Marine O-8 2 3 5 0
74 Air Force O-9 0 0 0 0
75 Navy O-9 0 0 0 0
76 Army O-9 0 1 1 1
77 Marine O-9 1 0 1 0
78 Air Force TOTAL ENLISTED 82620 20709 103329 8525
79 Navy TOTAL ENLISTED 85200 5710 90910 4334
80 Army TOTAL ENLISTED 96848 21268 118116 9382
81 Marine TOTAL ENLISTED 147406 22046 169452 23735
82 Air Force TOTAL OFFICER 5667 614 6281 365
83 Navy TOTAL OFFICER 10458 3457 13915 1038
84 Army TOTAL OFFICER 12180 4538 16718 1019
85 Marine TOTAL OFFICER 15858 5137 20995 2036
86 Air Force W-1 26 3 29 20
87 Navy W-1 328 65 393 140
88 Army W-2 19 10 29 27
Female_SWC Total_SWC Male_JSM Female_JSM Total_JSM Male_CM Female_CM
1 3 57 20 19 39 611 21
2 5 32 49 27 76 1064 178
3 34 151 30 57 87 806 162
4 80 445 40 38 78 2579 358
5 9 42 97 105 202 802 163
6 87 324 113 164 277 2474 388
7 12 202 98 109 207 2399 96
8 167 1164 130 201 331 6808 1035
9 788 1469 861 1288 2149 11297 1791
10 266 662 1258 1687 2945 10436 1631
11 167 741 780 928 1708 14643 344
12 699 3312 680 999 1679 18419 2875
13 272 799 1070 944 2014 15599 414
14 842 1829 3036 3207 6243 15363 1769
15 949 1916 1526 1691 3217 16277 1768
16 2599 9609 3029 3936 6965 58317 6010
17 338 1269 1199 730 1929 17921 344
18 2171 4926 6154 5519 11673 31711 2889
19 2280 6694 2915 2769 5684 49821 3087
20 1787 4624 2191 2099 4290 31491 2272
21 226 1286 568 341 909 12005 221
22 1449 3895 3654 3263 6917 23868 2026
23 1284 4009 1654 1461 3115 30404 1561
24 2003 6141 2598 1896 4494 44045 2019
25 119 741 308 185 493 6970 120
26 382 1753 759 506 1265 16449 527
27 734 2273 2118 1419 3537 17290 1188
28 1350 4348 1880 1181 3061 29292 1371
29 42 312 115 66 181 3268 73
30 110 346 505 241 746 3655 228
31 70 399 186 66 252 4935 146
32 291 1242 617 278 895 9221 373
33 20 126 42 10 52 1340 21
34 39 145 204 73 277 1975 108
35 21 139 54 16 70 2171 57
36 64 313 158 51 209 2729 105
37 6313 15857 20760 18574 39334 142573 13982
38 5 31 35 53 88 1006 23
39 50 133 108 158 266 1864 201
40 43 88 182 265 447 1693 211
41 131 379 101 193 294 2396 393
42 0 0 0 0 0 10 0
43 0 0 0 1 1 10 0
44 0 0 1 0 1 12 0
45 0 0 0 0 0 3 0
46 8 37 81 53 134 1330 20
47 54 152 163 220 383 2519 263
48 45 108 381 472 853 2443 310
49 192 428 285 449 734 3778 503
50 14 124 189 133 322 3892 52
51 158 496 450 556 1006 9436 806
52 295 633 1378 1256 2634 11841 1436
53 473 1129 1000 1229 2229 13794 1592
54 12 139 93 42 135 3121 48
55 108 382 282 310 592 7413 569
56 191 484 969 655 1624 9545 995
57 223 719 614 632 1246 11785 804
58 2 57 37 10 47 1704 20
59 43 211 147 179 326 5395 415
60 128 422 298 296 594 7589 536
61 94 306 590 321 911 7608 607
62 1 18 10 2 12 623 12
63 31 105 58 48 106 2700 215
64 20 87 174 58 232 3015 230
65 42 145 122 74 196 3666 258
66 0 1 5 5 10 131 5
67 0 2 1 0 1 107 9
68 0 1 1 0 1 36 0
69 1 4 2 1 3 136 4
70 0 0 0 0 0 23 1
71 0 0 2 1 3 99 4
72 0 0 4 1 5 86 7
73 0 0 1 0 1 64 4
74 0 0 0 0 0 16 0
75 0 0 1 0 1 35 1
76 0 1 0 0 0 41 0
77 0 0 0 1 1 52 0
78 5625 14150 17075 15541 32616 106164 10180
79 1199 5533 4200 3332 7532 74756 1654
80 5402 14784 7374 7348 14722 116304 8672
81 9533 33268 12047 11349 23396 221231 17233
82 42 407 446 293 739 11754 176
83 444 1482 1210 1471 2681 29549 2482
84 688 1707 3685 3033 6718 36409 3802
85 1190 3226 2424 2877 5301 43305 4094
86 7 27 25 12 37 386 5
87 74 214 88 95 183 1985 92
88 4 31 22 6 28 418 6
Total_CM Total_Male Total_Female Total
1 632 6917 626 7543
2 1242 8861 1760 10621
3 968 8773 2528 11301
4 2937 12440 1785 14225
5 965 5312 1287 6599
6 2862 14022 3357 17379
7 2495 18603 1553 20156
8 7843 29535 4727 34262
9 13088 41002 10263 51265
10 12067 41815 10692 52507
11 14987 50865 3303 54168
12 21294 60047 10224 70271
13 16013 35058 2656 37714
14 17132 40191 10574 50765
15 18045 42055 8674 50729
16 64327 119114 18878 137992
17 18265 27541 2002 29543
18 34600 55243 14683 69926
19 52908 74170 10814 84984
20 33763 55375 9807 65182
21 12226 15684 996 16680
22 25894 33628 8115 41743
23 31965 40700 5735 46435
24 46064 58278 7558 65836
25 7090 8465 496 8961
26 16976 19873 1810 21683
27 18478 22388 3958 26346
28 30663 36316 4731 41047
29 3341 3801 202 4003
30 3883 4578 718 5296
31 5081 5669 366 6035
32 9594 11249 1136 12385
33 1361 1556 61 1617
34 2083 2368 268 2636
35 2228 2439 150 2589
36 2834 3270 308 3578
37 156555 267677 64116 331793
38 1029 3294 285 3579
39 2065 5584 1320 6904
40 1904 5751 1587 7338
41 2789 6653 1615 8268
42 10 10 0 10
43 10 10 1 11
44 12 13 0 13
45 3 4 0 4
46 1350 2949 214 3163
47 2782 5138 1201 6339
48 2753 5597 1646 7243
49 4281 8751 2243 10994
50 3944 5701 399 6100
51 10242 13487 2697 16184
52 13277 17592 4629 22221
53 15386 21193 5331 26524
54 3169 3660 164 3824
55 7982 8831 1367 10198
56 10540 11884 2438 14322
57 12589 14117 2340 16457
58 1724 1872 39 1911
59 5810 6054 846 6900
60 8125 8580 1254 9834
61 8215 8835 1322 10157
62 635 673 22 695
63 2915 2931 406 3337
64 3245 3357 415 3772
65 3924 4021 497 4518
66 136 137 12 149
67 116 111 9 120
68 36 40 1 41
69 140 143 10 153
70 24 23 1 24
71 103 102 6 108
72 93 91 11 102
73 68 67 7 74
74 16 16 0 16
75 36 36 1 37
76 41 42 1 43
77 52 53 1 54
78 116344 214384 52055 266439
79 76410 168490 11895 180385
80 124976 229908 42690 272598
81 238464 404419 60161 464580
82 11930 18232 1125 19357
83 32031 42255 7854 50109
84 40211 53293 12061 65354
85 47399 63623 13298 76921
86 391 457 27 484
87 2077 2541 326 2867
88 424 486 26 512