Gather and Spread

These notes demonstrate the use of gather() and spread() in restructuring data.

library(tidyverse)
## ── Attaching packages ─────────────────────────────────────── tidyverse 1.2.1 ──
## ✔ ggplot2 3.0.0       ✔ purrr   0.2.5  
## ✔ tibble  2.0.1       ✔ dplyr   0.8.0.1
## ✔ tidyr   0.8.1       ✔ stringr 1.4.0  
## ✔ readr   1.1.1       ✔ forcats 0.3.0
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
library(readr)
library(knitr)

Data

The first step is to import the data, which was downloaded from BEA. Prior to importing, extraneous rows at the top and bottom were removed in Excel.

sainc1 <- read_csv("~/Dropbox/RProjects/Fertility Study/sainc1.csv")
## Parsed with column specification:
## cols(
##   GeoFips = col_integer(),
##   GeoName = col_character(),
##   `2003` = col_integer(),
##   `2004` = col_integer(),
##   `2005` = col_integer(),
##   `2006` = col_integer(),
##   `2007` = col_integer(),
##   `2008` = col_integer(),
##   `2009` = col_integer(),
##   `2010` = col_integer(),
##   `2011` = col_integer(),
##   `2012` = col_integer(),
##   `2013` = col_integer(),
##   `2014` = col_integer(),
##   `2015` = col_integer(),
##   `2016` = col_integer(),
##   `2017` = col_integer()
## )
kable(sainc1) # From knitr
GeoFips GeoName 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017
1000 Alabama 26693 28399 29808 31306 32573 33377 32631 33844 34942 35818 36067 37235 38612 39294 40805
2000 Alaska* 35765 36813 38817 40881 43676 47569 46858 49247 52032 53122 52259 55561 57306 55914 57179
4000 Arizona 28094 29954 32222 34667 35776 35543 33327 33559 34932 36132 36664 38331 39811 40672 42280
5000 Arkansas 25588 26970 28096 29422 31062 31935 31152 31866 33801 36124 35995 37985 39067 39746 41046
6000 California 35452 37364 39326 42139 43669 43895 42050 43609 46145 48751 49173 52237 55679 57497 59796
8000 Colorado 34787 35722 37732 39997 41889 42492 39851 40549 43502 45637 47308 50746 52228 52372 54646
9000 Connecticut 45199 47603 50110 54183 58122 61209 59994 62185 63769 64958 63637 66624 68453 69547 71823
10000 Delaware 37935 38873 39243 40995 41681 40768 40697 40799 43835 43964 44209 45733 47677 48197 49673
11000 District of Columbia 46040 50214 53383 56138 60058 61240 60455 63655 67654 68627 68249 71583 75731 77506 79989
12000 Florida 31646 33744 35962 38414 39545 39240 36580 38511 40120 40944 40582 43146 45352 46148 47684
13000 Georgia 30724 31869 33243 34609 35443 35113 34028 34524 36652 37110 37404 39556 41457 42621 44145
15000 Hawaii* 32652 34592 36635 38766 40679 42160 41593 41869 43475 44828 44995 47188 49304 50851 52787
16000 Idaho 26641 28202 29239 31322 32554 32646 31142 31921 33503 35187 36167 37792 39780 40508 41826
17000 Illinois 35198 36464 37828 40278 42422 43306 41071 42088 44172 46067 47160 49530 51648 52473 54203
18000 Indiana 29394 30598 31339 32973 33913 35152 34015 35358 37535 39209 39517 40890 42312 43588 45150
19000 Iowa 29458 31680 32510 34348 36645 38576 37202 38146 40992 42930 43496 44806 46235 46363 47062
20000 Kansas 29947 30669 32234 35408 37776 40738 39002 39421 42630 45005 45826 46702 47229 47496 48559
21000 Kentucky 26611 27953 29053 30507 31599 32745 32160 33121 34582 35703 35882 37495 38978 39452 40597
22000 Louisiana 26778 27717 29656 33410 35832 37872 36375 37510 38513 40412 40714 42524 42832 42581 43660
23000 Maine 30705 32094 32735 34397 35614 36951 36917 37854 39350 40185 40148 41889 43665 44787 46455
24000 Maryland 39394 41732 43693 45796 47465 49307 48609 49862 52229 53320 52792 54431 56877 58738 60847
25000 Massachusetts 40453 42637 44609 48012 50254 51859 50962 53073 55281 57353 57418 59943 63522 65186 67630
26000 Michigan 31254 32076 32755 33624 34704 35595 33938 35302 37482 38983 39328 41116 43471 44751 46201
27000 Minnesota 35195 37027 37862 39531 41527 42934 40860 42479 45162 47666 47695 49938 51929 52735 54359
28000 Mississippi 24049 25232 26764 27907 29406 30544 29938 30948 32190 33326 33851 34633 35137 35812 36636
29000 Missouri 30089 31419 32274 33965 35360 37038 36158 36784 38209 40007 40152 41538 42839 43587 44978
30000 Montana 26187 27951 29737 31828 33865 35250 34279 35899 38172 40301 40187 41891 43629 44045 45385
31000 Nebraska 32358 33510 34462 35863 38442 40302 39279 40868 45326 46428 46419 48830 50410 49660 50809
32000 Nevada 33159 35377 38373 39792 40159 38725 36334 37445 38697 39764 39440 41467 44026 44486 46159
33000 New Hampshire 37600 39559 40755 43346 44878 45897 45283 47320 49640 51901 51501 53065 55205 57114 59668
34000 New Jersey 41389 43185 44723 48025 50802 52042 50176 51379 53582 55354 55679 58125 60536 61905 64537
35000 New Mexico 26120 27363 28869 30427 31752 33355 32737 33547 35048 35796 35204 37321 38397 38929 39811
36000 New York 37479 39287 41150 44335 47619 48259 47371 49181 51689 54291 54845 56939 59170 60916 64540
37000 North Carolina 28633 30577 32287 34241 36072 37636 35746 35592 36665 38854 38078 39976 41814 42707 44222
38000 North Dakota 29777 30000 31599 33005 36455 40620 39863 43655 48090 55414 53765 56004 53559 52632 52269
39000 Ohio 30591 31719 32632 34240 35624 36724 35638 36663 39148 40695 41187 42826 44451 45176 46732
40000 Oklahoma 26906 28853 31295 34337 35170 38557 35347 36528 39041 41474 43082 45516 44216 42106 44376
41000 Oregon 29758 31234 32421 34862 35955 37015 35402 36050 37788 39566 39964 42411 45069 46413 48137
42000 Pennsylvania 33104 34922 36296 38115 40314 41575 40398 41997 44152 45982 46341 48310 50240 51393 53300
44000 Rhode Island 34358 36044 37046 39186 41049 41787 40760 42875 44607 46276 46332 48259 50199 50806 52786
45000 South Carolina 26785 27905 29153 30882 32249 32951 31724 32454 34047 35503 35665 37537 39424 40312 41633
46000 South Dakota 30010 31923 33598 35125 38579 40836 39255 41058 44497 45068 45092 46881 48652 48504 48818
47000 Tennessee 29187 30577 31523 33040 34178 35020 34462 35835 37798 39439 39549 40977 42810 43932 45517
48000 Texas 29768 30672 32799 35106 36583 39349 36740 38306 41258 43477 43821 46406 46709 46146 47362
49000 Utah 25911 27152 29046 31454 33247 34009 31907 32194 34209 36167 36764 38531 40831 42179 43459
50000 Vermont 32067 33818 34557 36830 38802 40645 39996 41204 43676 45242 45831 47642 49428 50648 52225
51000 Virginia 36104 38118 40216 42505 44289 45501 44026 45405 47647 49431 48666 50528 52687 53323 55105
53000 Washington 34545 36577 37592 40265 43125 44550 41851 42533 44571 47737 48237 51505 53776 55519 57896
54000 West Virginia 24759 25592 26828 28546 29551 31328 31470 32390 34206 35169 34979 36089 36818 36958 38479
55000 Wisconsin 32098 33354 34353 36297 37764 38914 38085 38995 41112 42947 43079 44785 46571 47426 48941
56000 Wyoming 33109 34977 38071 43192 44905 48227 43488 46010 50081 53680 53082 56605 57182 55452 57346

The Problem

We have personal income by year and state but the structure is different from our fertility data. In the fertility data, year is a single variable and the value of this variable is the year of the observation. In the BEA data, individual years are variables. We need the gather() function from tidyr to make the BEA data compatible with the fertility data. Review the documentation on gather().

sainc1 %>% 
  gather(year,pcinc,"2003":"2017") %>% arrange(GeoName,year) -> saincRes
kable(saincRes)
GeoFips GeoName year pcinc
1000 Alabama 2003 26693
1000 Alabama 2004 28399
1000 Alabama 2005 29808
1000 Alabama 2006 31306
1000 Alabama 2007 32573
1000 Alabama 2008 33377
1000 Alabama 2009 32631
1000 Alabama 2010 33844
1000 Alabama 2011 34942
1000 Alabama 2012 35818
1000 Alabama 2013 36067
1000 Alabama 2014 37235
1000 Alabama 2015 38612
1000 Alabama 2016 39294
1000 Alabama 2017 40805
2000 Alaska* 2003 35765
2000 Alaska* 2004 36813
2000 Alaska* 2005 38817
2000 Alaska* 2006 40881
2000 Alaska* 2007 43676
2000 Alaska* 2008 47569
2000 Alaska* 2009 46858
2000 Alaska* 2010 49247
2000 Alaska* 2011 52032
2000 Alaska* 2012 53122
2000 Alaska* 2013 52259
2000 Alaska* 2014 55561
2000 Alaska* 2015 57306
2000 Alaska* 2016 55914
2000 Alaska* 2017 57179
4000 Arizona 2003 28094
4000 Arizona 2004 29954
4000 Arizona 2005 32222
4000 Arizona 2006 34667
4000 Arizona 2007 35776
4000 Arizona 2008 35543
4000 Arizona 2009 33327
4000 Arizona 2010 33559
4000 Arizona 2011 34932
4000 Arizona 2012 36132
4000 Arizona 2013 36664
4000 Arizona 2014 38331
4000 Arizona 2015 39811
4000 Arizona 2016 40672
4000 Arizona 2017 42280
5000 Arkansas 2003 25588
5000 Arkansas 2004 26970
5000 Arkansas 2005 28096
5000 Arkansas 2006 29422
5000 Arkansas 2007 31062
5000 Arkansas 2008 31935
5000 Arkansas 2009 31152
5000 Arkansas 2010 31866
5000 Arkansas 2011 33801
5000 Arkansas 2012 36124
5000 Arkansas 2013 35995
5000 Arkansas 2014 37985
5000 Arkansas 2015 39067
5000 Arkansas 2016 39746
5000 Arkansas 2017 41046
6000 California 2003 35452
6000 California 2004 37364
6000 California 2005 39326
6000 California 2006 42139
6000 California 2007 43669
6000 California 2008 43895
6000 California 2009 42050
6000 California 2010 43609
6000 California 2011 46145
6000 California 2012 48751
6000 California 2013 49173
6000 California 2014 52237
6000 California 2015 55679
6000 California 2016 57497
6000 California 2017 59796
8000 Colorado 2003 34787
8000 Colorado 2004 35722
8000 Colorado 2005 37732
8000 Colorado 2006 39997
8000 Colorado 2007 41889
8000 Colorado 2008 42492
8000 Colorado 2009 39851
8000 Colorado 2010 40549
8000 Colorado 2011 43502
8000 Colorado 2012 45637
8000 Colorado 2013 47308
8000 Colorado 2014 50746
8000 Colorado 2015 52228
8000 Colorado 2016 52372
8000 Colorado 2017 54646
9000 Connecticut 2003 45199
9000 Connecticut 2004 47603
9000 Connecticut 2005 50110
9000 Connecticut 2006 54183
9000 Connecticut 2007 58122
9000 Connecticut 2008 61209
9000 Connecticut 2009 59994
9000 Connecticut 2010 62185
9000 Connecticut 2011 63769
9000 Connecticut 2012 64958
9000 Connecticut 2013 63637
9000 Connecticut 2014 66624
9000 Connecticut 2015 68453
9000 Connecticut 2016 69547
9000 Connecticut 2017 71823
10000 Delaware 2003 37935
10000 Delaware 2004 38873
10000 Delaware 2005 39243
10000 Delaware 2006 40995
10000 Delaware 2007 41681
10000 Delaware 2008 40768
10000 Delaware 2009 40697
10000 Delaware 2010 40799
10000 Delaware 2011 43835
10000 Delaware 2012 43964
10000 Delaware 2013 44209
10000 Delaware 2014 45733
10000 Delaware 2015 47677
10000 Delaware 2016 48197
10000 Delaware 2017 49673
11000 District of Columbia 2003 46040
11000 District of Columbia 2004 50214
11000 District of Columbia 2005 53383
11000 District of Columbia 2006 56138
11000 District of Columbia 2007 60058
11000 District of Columbia 2008 61240
11000 District of Columbia 2009 60455
11000 District of Columbia 2010 63655
11000 District of Columbia 2011 67654
11000 District of Columbia 2012 68627
11000 District of Columbia 2013 68249
11000 District of Columbia 2014 71583
11000 District of Columbia 2015 75731
11000 District of Columbia 2016 77506
11000 District of Columbia 2017 79989
12000 Florida 2003 31646
12000 Florida 2004 33744
12000 Florida 2005 35962
12000 Florida 2006 38414
12000 Florida 2007 39545
12000 Florida 2008 39240
12000 Florida 2009 36580
12000 Florida 2010 38511
12000 Florida 2011 40120
12000 Florida 2012 40944
12000 Florida 2013 40582
12000 Florida 2014 43146
12000 Florida 2015 45352
12000 Florida 2016 46148
12000 Florida 2017 47684
13000 Georgia 2003 30724
13000 Georgia 2004 31869
13000 Georgia 2005 33243
13000 Georgia 2006 34609
13000 Georgia 2007 35443
13000 Georgia 2008 35113
13000 Georgia 2009 34028
13000 Georgia 2010 34524
13000 Georgia 2011 36652
13000 Georgia 2012 37110
13000 Georgia 2013 37404
13000 Georgia 2014 39556
13000 Georgia 2015 41457
13000 Georgia 2016 42621
13000 Georgia 2017 44145
15000 Hawaii* 2003 32652
15000 Hawaii* 2004 34592
15000 Hawaii* 2005 36635
15000 Hawaii* 2006 38766
15000 Hawaii* 2007 40679
15000 Hawaii* 2008 42160
15000 Hawaii* 2009 41593
15000 Hawaii* 2010 41869
15000 Hawaii* 2011 43475
15000 Hawaii* 2012 44828
15000 Hawaii* 2013 44995
15000 Hawaii* 2014 47188
15000 Hawaii* 2015 49304
15000 Hawaii* 2016 50851
15000 Hawaii* 2017 52787
16000 Idaho 2003 26641
16000 Idaho 2004 28202
16000 Idaho 2005 29239
16000 Idaho 2006 31322
16000 Idaho 2007 32554
16000 Idaho 2008 32646
16000 Idaho 2009 31142
16000 Idaho 2010 31921
16000 Idaho 2011 33503
16000 Idaho 2012 35187
16000 Idaho 2013 36167
16000 Idaho 2014 37792
16000 Idaho 2015 39780
16000 Idaho 2016 40508
16000 Idaho 2017 41826
17000 Illinois 2003 35198
17000 Illinois 2004 36464
17000 Illinois 2005 37828
17000 Illinois 2006 40278
17000 Illinois 2007 42422
17000 Illinois 2008 43306
17000 Illinois 2009 41071
17000 Illinois 2010 42088
17000 Illinois 2011 44172
17000 Illinois 2012 46067
17000 Illinois 2013 47160
17000 Illinois 2014 49530
17000 Illinois 2015 51648
17000 Illinois 2016 52473
17000 Illinois 2017 54203
18000 Indiana 2003 29394
18000 Indiana 2004 30598
18000 Indiana 2005 31339
18000 Indiana 2006 32973
18000 Indiana 2007 33913
18000 Indiana 2008 35152
18000 Indiana 2009 34015
18000 Indiana 2010 35358
18000 Indiana 2011 37535
18000 Indiana 2012 39209
18000 Indiana 2013 39517
18000 Indiana 2014 40890
18000 Indiana 2015 42312
18000 Indiana 2016 43588
18000 Indiana 2017 45150
19000 Iowa 2003 29458
19000 Iowa 2004 31680
19000 Iowa 2005 32510
19000 Iowa 2006 34348
19000 Iowa 2007 36645
19000 Iowa 2008 38576
19000 Iowa 2009 37202
19000 Iowa 2010 38146
19000 Iowa 2011 40992
19000 Iowa 2012 42930
19000 Iowa 2013 43496
19000 Iowa 2014 44806
19000 Iowa 2015 46235
19000 Iowa 2016 46363
19000 Iowa 2017 47062
20000 Kansas 2003 29947
20000 Kansas 2004 30669
20000 Kansas 2005 32234
20000 Kansas 2006 35408
20000 Kansas 2007 37776
20000 Kansas 2008 40738
20000 Kansas 2009 39002
20000 Kansas 2010 39421
20000 Kansas 2011 42630
20000 Kansas 2012 45005
20000 Kansas 2013 45826
20000 Kansas 2014 46702
20000 Kansas 2015 47229
20000 Kansas 2016 47496
20000 Kansas 2017 48559
21000 Kentucky 2003 26611
21000 Kentucky 2004 27953
21000 Kentucky 2005 29053
21000 Kentucky 2006 30507
21000 Kentucky 2007 31599
21000 Kentucky 2008 32745
21000 Kentucky 2009 32160
21000 Kentucky 2010 33121
21000 Kentucky 2011 34582
21000 Kentucky 2012 35703
21000 Kentucky 2013 35882
21000 Kentucky 2014 37495
21000 Kentucky 2015 38978
21000 Kentucky 2016 39452
21000 Kentucky 2017 40597
22000 Louisiana 2003 26778
22000 Louisiana 2004 27717
22000 Louisiana 2005 29656
22000 Louisiana 2006 33410
22000 Louisiana 2007 35832
22000 Louisiana 2008 37872
22000 Louisiana 2009 36375
22000 Louisiana 2010 37510
22000 Louisiana 2011 38513
22000 Louisiana 2012 40412
22000 Louisiana 2013 40714
22000 Louisiana 2014 42524
22000 Louisiana 2015 42832
22000 Louisiana 2016 42581
22000 Louisiana 2017 43660
23000 Maine 2003 30705
23000 Maine 2004 32094
23000 Maine 2005 32735
23000 Maine 2006 34397
23000 Maine 2007 35614
23000 Maine 2008 36951
23000 Maine 2009 36917
23000 Maine 2010 37854
23000 Maine 2011 39350
23000 Maine 2012 40185
23000 Maine 2013 40148
23000 Maine 2014 41889
23000 Maine 2015 43665
23000 Maine 2016 44787
23000 Maine 2017 46455
24000 Maryland 2003 39394
24000 Maryland 2004 41732
24000 Maryland 2005 43693
24000 Maryland 2006 45796
24000 Maryland 2007 47465
24000 Maryland 2008 49307
24000 Maryland 2009 48609
24000 Maryland 2010 49862
24000 Maryland 2011 52229
24000 Maryland 2012 53320
24000 Maryland 2013 52792
24000 Maryland 2014 54431
24000 Maryland 2015 56877
24000 Maryland 2016 58738
24000 Maryland 2017 60847
25000 Massachusetts 2003 40453
25000 Massachusetts 2004 42637
25000 Massachusetts 2005 44609
25000 Massachusetts 2006 48012
25000 Massachusetts 2007 50254
25000 Massachusetts 2008 51859
25000 Massachusetts 2009 50962
25000 Massachusetts 2010 53073
25000 Massachusetts 2011 55281
25000 Massachusetts 2012 57353
25000 Massachusetts 2013 57418
25000 Massachusetts 2014 59943
25000 Massachusetts 2015 63522
25000 Massachusetts 2016 65186
25000 Massachusetts 2017 67630
26000 Michigan 2003 31254
26000 Michigan 2004 32076
26000 Michigan 2005 32755
26000 Michigan 2006 33624
26000 Michigan 2007 34704
26000 Michigan 2008 35595
26000 Michigan 2009 33938
26000 Michigan 2010 35302
26000 Michigan 2011 37482
26000 Michigan 2012 38983
26000 Michigan 2013 39328
26000 Michigan 2014 41116
26000 Michigan 2015 43471
26000 Michigan 2016 44751
26000 Michigan 2017 46201
27000 Minnesota 2003 35195
27000 Minnesota 2004 37027
27000 Minnesota 2005 37862
27000 Minnesota 2006 39531
27000 Minnesota 2007 41527
27000 Minnesota 2008 42934
27000 Minnesota 2009 40860
27000 Minnesota 2010 42479
27000 Minnesota 2011 45162
27000 Minnesota 2012 47666
27000 Minnesota 2013 47695
27000 Minnesota 2014 49938
27000 Minnesota 2015 51929
27000 Minnesota 2016 52735
27000 Minnesota 2017 54359
28000 Mississippi 2003 24049
28000 Mississippi 2004 25232
28000 Mississippi 2005 26764
28000 Mississippi 2006 27907
28000 Mississippi 2007 29406
28000 Mississippi 2008 30544
28000 Mississippi 2009 29938
28000 Mississippi 2010 30948
28000 Mississippi 2011 32190
28000 Mississippi 2012 33326
28000 Mississippi 2013 33851
28000 Mississippi 2014 34633
28000 Mississippi 2015 35137
28000 Mississippi 2016 35812
28000 Mississippi 2017 36636
29000 Missouri 2003 30089
29000 Missouri 2004 31419
29000 Missouri 2005 32274
29000 Missouri 2006 33965
29000 Missouri 2007 35360
29000 Missouri 2008 37038
29000 Missouri 2009 36158
29000 Missouri 2010 36784
29000 Missouri 2011 38209
29000 Missouri 2012 40007
29000 Missouri 2013 40152
29000 Missouri 2014 41538
29000 Missouri 2015 42839
29000 Missouri 2016 43587
29000 Missouri 2017 44978
30000 Montana 2003 26187
30000 Montana 2004 27951
30000 Montana 2005 29737
30000 Montana 2006 31828
30000 Montana 2007 33865
30000 Montana 2008 35250
30000 Montana 2009 34279
30000 Montana 2010 35899
30000 Montana 2011 38172
30000 Montana 2012 40301
30000 Montana 2013 40187
30000 Montana 2014 41891
30000 Montana 2015 43629
30000 Montana 2016 44045
30000 Montana 2017 45385
31000 Nebraska 2003 32358
31000 Nebraska 2004 33510
31000 Nebraska 2005 34462
31000 Nebraska 2006 35863
31000 Nebraska 2007 38442
31000 Nebraska 2008 40302
31000 Nebraska 2009 39279
31000 Nebraska 2010 40868
31000 Nebraska 2011 45326
31000 Nebraska 2012 46428
31000 Nebraska 2013 46419
31000 Nebraska 2014 48830
31000 Nebraska 2015 50410
31000 Nebraska 2016 49660
31000 Nebraska 2017 50809
32000 Nevada 2003 33159
32000 Nevada 2004 35377
32000 Nevada 2005 38373
32000 Nevada 2006 39792
32000 Nevada 2007 40159
32000 Nevada 2008 38725
32000 Nevada 2009 36334
32000 Nevada 2010 37445
32000 Nevada 2011 38697
32000 Nevada 2012 39764
32000 Nevada 2013 39440
32000 Nevada 2014 41467
32000 Nevada 2015 44026
32000 Nevada 2016 44486
32000 Nevada 2017 46159
33000 New Hampshire 2003 37600
33000 New Hampshire 2004 39559
33000 New Hampshire 2005 40755
33000 New Hampshire 2006 43346
33000 New Hampshire 2007 44878
33000 New Hampshire 2008 45897
33000 New Hampshire 2009 45283
33000 New Hampshire 2010 47320
33000 New Hampshire 2011 49640
33000 New Hampshire 2012 51901
33000 New Hampshire 2013 51501
33000 New Hampshire 2014 53065
33000 New Hampshire 2015 55205
33000 New Hampshire 2016 57114
33000 New Hampshire 2017 59668
34000 New Jersey 2003 41389
34000 New Jersey 2004 43185
34000 New Jersey 2005 44723
34000 New Jersey 2006 48025
34000 New Jersey 2007 50802
34000 New Jersey 2008 52042
34000 New Jersey 2009 50176
34000 New Jersey 2010 51379
34000 New Jersey 2011 53582
34000 New Jersey 2012 55354
34000 New Jersey 2013 55679
34000 New Jersey 2014 58125
34000 New Jersey 2015 60536
34000 New Jersey 2016 61905
34000 New Jersey 2017 64537
35000 New Mexico 2003 26120
35000 New Mexico 2004 27363
35000 New Mexico 2005 28869
35000 New Mexico 2006 30427
35000 New Mexico 2007 31752
35000 New Mexico 2008 33355
35000 New Mexico 2009 32737
35000 New Mexico 2010 33547
35000 New Mexico 2011 35048
35000 New Mexico 2012 35796
35000 New Mexico 2013 35204
35000 New Mexico 2014 37321
35000 New Mexico 2015 38397
35000 New Mexico 2016 38929
35000 New Mexico 2017 39811
36000 New York 2003 37479
36000 New York 2004 39287
36000 New York 2005 41150
36000 New York 2006 44335
36000 New York 2007 47619
36000 New York 2008 48259
36000 New York 2009 47371
36000 New York 2010 49181
36000 New York 2011 51689
36000 New York 2012 54291
36000 New York 2013 54845
36000 New York 2014 56939
36000 New York 2015 59170
36000 New York 2016 60916
36000 New York 2017 64540
37000 North Carolina 2003 28633
37000 North Carolina 2004 30577
37000 North Carolina 2005 32287
37000 North Carolina 2006 34241
37000 North Carolina 2007 36072
37000 North Carolina 2008 37636
37000 North Carolina 2009 35746
37000 North Carolina 2010 35592
37000 North Carolina 2011 36665
37000 North Carolina 2012 38854
37000 North Carolina 2013 38078
37000 North Carolina 2014 39976
37000 North Carolina 2015 41814
37000 North Carolina 2016 42707
37000 North Carolina 2017 44222
38000 North Dakota 2003 29777
38000 North Dakota 2004 30000
38000 North Dakota 2005 31599
38000 North Dakota 2006 33005
38000 North Dakota 2007 36455
38000 North Dakota 2008 40620
38000 North Dakota 2009 39863
38000 North Dakota 2010 43655
38000 North Dakota 2011 48090
38000 North Dakota 2012 55414
38000 North Dakota 2013 53765
38000 North Dakota 2014 56004
38000 North Dakota 2015 53559
38000 North Dakota 2016 52632
38000 North Dakota 2017 52269
39000 Ohio 2003 30591
39000 Ohio 2004 31719
39000 Ohio 2005 32632
39000 Ohio 2006 34240
39000 Ohio 2007 35624
39000 Ohio 2008 36724
39000 Ohio 2009 35638
39000 Ohio 2010 36663
39000 Ohio 2011 39148
39000 Ohio 2012 40695
39000 Ohio 2013 41187
39000 Ohio 2014 42826
39000 Ohio 2015 44451
39000 Ohio 2016 45176
39000 Ohio 2017 46732
40000 Oklahoma 2003 26906
40000 Oklahoma 2004 28853
40000 Oklahoma 2005 31295
40000 Oklahoma 2006 34337
40000 Oklahoma 2007 35170
40000 Oklahoma 2008 38557
40000 Oklahoma 2009 35347
40000 Oklahoma 2010 36528
40000 Oklahoma 2011 39041
40000 Oklahoma 2012 41474
40000 Oklahoma 2013 43082
40000 Oklahoma 2014 45516
40000 Oklahoma 2015 44216
40000 Oklahoma 2016 42106
40000 Oklahoma 2017 44376
41000 Oregon 2003 29758
41000 Oregon 2004 31234
41000 Oregon 2005 32421
41000 Oregon 2006 34862
41000 Oregon 2007 35955
41000 Oregon 2008 37015
41000 Oregon 2009 35402
41000 Oregon 2010 36050
41000 Oregon 2011 37788
41000 Oregon 2012 39566
41000 Oregon 2013 39964
41000 Oregon 2014 42411
41000 Oregon 2015 45069
41000 Oregon 2016 46413
41000 Oregon 2017 48137
42000 Pennsylvania 2003 33104
42000 Pennsylvania 2004 34922
42000 Pennsylvania 2005 36296
42000 Pennsylvania 2006 38115
42000 Pennsylvania 2007 40314
42000 Pennsylvania 2008 41575
42000 Pennsylvania 2009 40398
42000 Pennsylvania 2010 41997
42000 Pennsylvania 2011 44152
42000 Pennsylvania 2012 45982
42000 Pennsylvania 2013 46341
42000 Pennsylvania 2014 48310
42000 Pennsylvania 2015 50240
42000 Pennsylvania 2016 51393
42000 Pennsylvania 2017 53300
44000 Rhode Island 2003 34358
44000 Rhode Island 2004 36044
44000 Rhode Island 2005 37046
44000 Rhode Island 2006 39186
44000 Rhode Island 2007 41049
44000 Rhode Island 2008 41787
44000 Rhode Island 2009 40760
44000 Rhode Island 2010 42875
44000 Rhode Island 2011 44607
44000 Rhode Island 2012 46276
44000 Rhode Island 2013 46332
44000 Rhode Island 2014 48259
44000 Rhode Island 2015 50199
44000 Rhode Island 2016 50806
44000 Rhode Island 2017 52786
45000 South Carolina 2003 26785
45000 South Carolina 2004 27905
45000 South Carolina 2005 29153
45000 South Carolina 2006 30882
45000 South Carolina 2007 32249
45000 South Carolina 2008 32951
45000 South Carolina 2009 31724
45000 South Carolina 2010 32454
45000 South Carolina 2011 34047
45000 South Carolina 2012 35503
45000 South Carolina 2013 35665
45000 South Carolina 2014 37537
45000 South Carolina 2015 39424
45000 South Carolina 2016 40312
45000 South Carolina 2017 41633
46000 South Dakota 2003 30010
46000 South Dakota 2004 31923
46000 South Dakota 2005 33598
46000 South Dakota 2006 35125
46000 South Dakota 2007 38579
46000 South Dakota 2008 40836
46000 South Dakota 2009 39255
46000 South Dakota 2010 41058
46000 South Dakota 2011 44497
46000 South Dakota 2012 45068
46000 South Dakota 2013 45092
46000 South Dakota 2014 46881
46000 South Dakota 2015 48652
46000 South Dakota 2016 48504
46000 South Dakota 2017 48818
47000 Tennessee 2003 29187
47000 Tennessee 2004 30577
47000 Tennessee 2005 31523
47000 Tennessee 2006 33040
47000 Tennessee 2007 34178
47000 Tennessee 2008 35020
47000 Tennessee 2009 34462
47000 Tennessee 2010 35835
47000 Tennessee 2011 37798
47000 Tennessee 2012 39439
47000 Tennessee 2013 39549
47000 Tennessee 2014 40977
47000 Tennessee 2015 42810
47000 Tennessee 2016 43932
47000 Tennessee 2017 45517
48000 Texas 2003 29768
48000 Texas 2004 30672
48000 Texas 2005 32799
48000 Texas 2006 35106
48000 Texas 2007 36583
48000 Texas 2008 39349
48000 Texas 2009 36740
48000 Texas 2010 38306
48000 Texas 2011 41258
48000 Texas 2012 43477
48000 Texas 2013 43821
48000 Texas 2014 46406
48000 Texas 2015 46709
48000 Texas 2016 46146
48000 Texas 2017 47362
49000 Utah 2003 25911
49000 Utah 2004 27152
49000 Utah 2005 29046
49000 Utah 2006 31454
49000 Utah 2007 33247
49000 Utah 2008 34009
49000 Utah 2009 31907
49000 Utah 2010 32194
49000 Utah 2011 34209
49000 Utah 2012 36167
49000 Utah 2013 36764
49000 Utah 2014 38531
49000 Utah 2015 40831
49000 Utah 2016 42179
49000 Utah 2017 43459
50000 Vermont 2003 32067
50000 Vermont 2004 33818
50000 Vermont 2005 34557
50000 Vermont 2006 36830
50000 Vermont 2007 38802
50000 Vermont 2008 40645
50000 Vermont 2009 39996
50000 Vermont 2010 41204
50000 Vermont 2011 43676
50000 Vermont 2012 45242
50000 Vermont 2013 45831
50000 Vermont 2014 47642
50000 Vermont 2015 49428
50000 Vermont 2016 50648
50000 Vermont 2017 52225
51000 Virginia 2003 36104
51000 Virginia 2004 38118
51000 Virginia 2005 40216
51000 Virginia 2006 42505
51000 Virginia 2007 44289
51000 Virginia 2008 45501
51000 Virginia 2009 44026
51000 Virginia 2010 45405
51000 Virginia 2011 47647
51000 Virginia 2012 49431
51000 Virginia 2013 48666
51000 Virginia 2014 50528
51000 Virginia 2015 52687
51000 Virginia 2016 53323
51000 Virginia 2017 55105
53000 Washington 2003 34545
53000 Washington 2004 36577
53000 Washington 2005 37592
53000 Washington 2006 40265
53000 Washington 2007 43125
53000 Washington 2008 44550
53000 Washington 2009 41851
53000 Washington 2010 42533
53000 Washington 2011 44571
53000 Washington 2012 47737
53000 Washington 2013 48237
53000 Washington 2014 51505
53000 Washington 2015 53776
53000 Washington 2016 55519
53000 Washington 2017 57896
54000 West Virginia 2003 24759
54000 West Virginia 2004 25592
54000 West Virginia 2005 26828
54000 West Virginia 2006 28546
54000 West Virginia 2007 29551
54000 West Virginia 2008 31328
54000 West Virginia 2009 31470
54000 West Virginia 2010 32390
54000 West Virginia 2011 34206
54000 West Virginia 2012 35169
54000 West Virginia 2013 34979
54000 West Virginia 2014 36089
54000 West Virginia 2015 36818
54000 West Virginia 2016 36958
54000 West Virginia 2017 38479
55000 Wisconsin 2003 32098
55000 Wisconsin 2004 33354
55000 Wisconsin 2005 34353
55000 Wisconsin 2006 36297
55000 Wisconsin 2007 37764
55000 Wisconsin 2008 38914
55000 Wisconsin 2009 38085
55000 Wisconsin 2010 38995
55000 Wisconsin 2011 41112
55000 Wisconsin 2012 42947
55000 Wisconsin 2013 43079
55000 Wisconsin 2014 44785
55000 Wisconsin 2015 46571
55000 Wisconsin 2016 47426
55000 Wisconsin 2017 48941
56000 Wyoming 2003 33109
56000 Wyoming 2004 34977
56000 Wyoming 2005 38071
56000 Wyoming 2006 43192
56000 Wyoming 2007 44905
56000 Wyoming 2008 48227
56000 Wyoming 2009 43488
56000 Wyoming 2010 46010
56000 Wyoming 2011 50081
56000 Wyoming 2012 53680
56000 Wyoming 2013 53082
56000 Wyoming 2014 56605
56000 Wyoming 2015 57182
56000 Wyoming 2016 55452
56000 Wyoming 2017 57346
str(saincRes)
## Classes 'tbl_df', 'tbl' and 'data.frame':    765 obs. of  4 variables:
##  $ GeoFips: int  1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 ...
##  $ GeoName: chr  "Alabama" "Alabama" "Alabama" "Alabama" ...
##  $ year   : chr  "2003" "2004" "2005" "2006" ...
##  $ pcinc  : int  26693 28399 29808 31306 32573 33377 32631 33844 34942 35818 ...

Spread

Spread() is the opposite of gather(). We can move information from the values of a variable into column names.

saincRes %>% 
  mutate(year = paste0("Yr",year)) %>% 
  spread(year,pcinc) -> rev
kable(rev)
GeoFips GeoName Yr2003 Yr2004 Yr2005 Yr2006 Yr2007 Yr2008 Yr2009 Yr2010 Yr2011 Yr2012 Yr2013 Yr2014 Yr2015 Yr2016 Yr2017
1000 Alabama 26693 28399 29808 31306 32573 33377 32631 33844 34942 35818 36067 37235 38612 39294 40805
2000 Alaska* 35765 36813 38817 40881 43676 47569 46858 49247 52032 53122 52259 55561 57306 55914 57179
4000 Arizona 28094 29954 32222 34667 35776 35543 33327 33559 34932 36132 36664 38331 39811 40672 42280
5000 Arkansas 25588 26970 28096 29422 31062 31935 31152 31866 33801 36124 35995 37985 39067 39746 41046
6000 California 35452 37364 39326 42139 43669 43895 42050 43609 46145 48751 49173 52237 55679 57497 59796
8000 Colorado 34787 35722 37732 39997 41889 42492 39851 40549 43502 45637 47308 50746 52228 52372 54646
9000 Connecticut 45199 47603 50110 54183 58122 61209 59994 62185 63769 64958 63637 66624 68453 69547 71823
10000 Delaware 37935 38873 39243 40995 41681 40768 40697 40799 43835 43964 44209 45733 47677 48197 49673
11000 District of Columbia 46040 50214 53383 56138 60058 61240 60455 63655 67654 68627 68249 71583 75731 77506 79989
12000 Florida 31646 33744 35962 38414 39545 39240 36580 38511 40120 40944 40582 43146 45352 46148 47684
13000 Georgia 30724 31869 33243 34609 35443 35113 34028 34524 36652 37110 37404 39556 41457 42621 44145
15000 Hawaii* 32652 34592 36635 38766 40679 42160 41593 41869 43475 44828 44995 47188 49304 50851 52787
16000 Idaho 26641 28202 29239 31322 32554 32646 31142 31921 33503 35187 36167 37792 39780 40508 41826
17000 Illinois 35198 36464 37828 40278 42422 43306 41071 42088 44172 46067 47160 49530 51648 52473 54203
18000 Indiana 29394 30598 31339 32973 33913 35152 34015 35358 37535 39209 39517 40890 42312 43588 45150
19000 Iowa 29458 31680 32510 34348 36645 38576 37202 38146 40992 42930 43496 44806 46235 46363 47062
20000 Kansas 29947 30669 32234 35408 37776 40738 39002 39421 42630 45005 45826 46702 47229 47496 48559
21000 Kentucky 26611 27953 29053 30507 31599 32745 32160 33121 34582 35703 35882 37495 38978 39452 40597
22000 Louisiana 26778 27717 29656 33410 35832 37872 36375 37510 38513 40412 40714 42524 42832 42581 43660
23000 Maine 30705 32094 32735 34397 35614 36951 36917 37854 39350 40185 40148 41889 43665 44787 46455
24000 Maryland 39394 41732 43693 45796 47465 49307 48609 49862 52229 53320 52792 54431 56877 58738 60847
25000 Massachusetts 40453 42637 44609 48012 50254 51859 50962 53073 55281 57353 57418 59943 63522 65186 67630
26000 Michigan 31254 32076 32755 33624 34704 35595 33938 35302 37482 38983 39328 41116 43471 44751 46201
27000 Minnesota 35195 37027 37862 39531 41527 42934 40860 42479 45162 47666 47695 49938 51929 52735 54359
28000 Mississippi 24049 25232 26764 27907 29406 30544 29938 30948 32190 33326 33851 34633 35137 35812 36636
29000 Missouri 30089 31419 32274 33965 35360 37038 36158 36784 38209 40007 40152 41538 42839 43587 44978
30000 Montana 26187 27951 29737 31828 33865 35250 34279 35899 38172 40301 40187 41891 43629 44045 45385
31000 Nebraska 32358 33510 34462 35863 38442 40302 39279 40868 45326 46428 46419 48830 50410 49660 50809
32000 Nevada 33159 35377 38373 39792 40159 38725 36334 37445 38697 39764 39440 41467 44026 44486 46159
33000 New Hampshire 37600 39559 40755 43346 44878 45897 45283 47320 49640 51901 51501 53065 55205 57114 59668
34000 New Jersey 41389 43185 44723 48025 50802 52042 50176 51379 53582 55354 55679 58125 60536 61905 64537
35000 New Mexico 26120 27363 28869 30427 31752 33355 32737 33547 35048 35796 35204 37321 38397 38929 39811
36000 New York 37479 39287 41150 44335 47619 48259 47371 49181 51689 54291 54845 56939 59170 60916 64540
37000 North Carolina 28633 30577 32287 34241 36072 37636 35746 35592 36665 38854 38078 39976 41814 42707 44222
38000 North Dakota 29777 30000 31599 33005 36455 40620 39863 43655 48090 55414 53765 56004 53559 52632 52269
39000 Ohio 30591 31719 32632 34240 35624 36724 35638 36663 39148 40695 41187 42826 44451 45176 46732
40000 Oklahoma 26906 28853 31295 34337 35170 38557 35347 36528 39041 41474 43082 45516 44216 42106 44376
41000 Oregon 29758 31234 32421 34862 35955 37015 35402 36050 37788 39566 39964 42411 45069 46413 48137
42000 Pennsylvania 33104 34922 36296 38115 40314 41575 40398 41997 44152 45982 46341 48310 50240 51393 53300
44000 Rhode Island 34358 36044 37046 39186 41049 41787 40760 42875 44607 46276 46332 48259 50199 50806 52786
45000 South Carolina 26785 27905 29153 30882 32249 32951 31724 32454 34047 35503 35665 37537 39424 40312 41633
46000 South Dakota 30010 31923 33598 35125 38579 40836 39255 41058 44497 45068 45092 46881 48652 48504 48818
47000 Tennessee 29187 30577 31523 33040 34178 35020 34462 35835 37798 39439 39549 40977 42810 43932 45517
48000 Texas 29768 30672 32799 35106 36583 39349 36740 38306 41258 43477 43821 46406 46709 46146 47362
49000 Utah 25911 27152 29046 31454 33247 34009 31907 32194 34209 36167 36764 38531 40831 42179 43459
50000 Vermont 32067 33818 34557 36830 38802 40645 39996 41204 43676 45242 45831 47642 49428 50648 52225
51000 Virginia 36104 38118 40216 42505 44289 45501 44026 45405 47647 49431 48666 50528 52687 53323 55105
53000 Washington 34545 36577 37592 40265 43125 44550 41851 42533 44571 47737 48237 51505 53776 55519 57896
54000 West Virginia 24759 25592 26828 28546 29551 31328 31470 32390 34206 35169 34979 36089 36818 36958 38479
55000 Wisconsin 32098 33354 34353 36297 37764 38914 38085 38995 41112 42947 43079 44785 46571 47426 48941
56000 Wyoming 33109 34977 38071 43192 44905 48227 43488 46010 50081 53680 53082 56605 57182 55452 57346