-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathTransposeFoods.R
135 lines (115 loc) · 10.7 KB
/
TransposeFoods.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
library(dplyr)
#Substitutes 0 for NA
DR1IFF_G1[is.na(DR1IFFC_G)] = 0 #2011-2012 dataset
DR1IFF_H1[is.na(DR1IFFC_H)] = 0 #2013-2014 dataset
#Selects only columns of interest
DR1IFF_G2 <- select(DR1IFF_G1, SEQN, WTDRD1, DR1DRSTZ, DR1IFDCD, DR1IGRMS) #2011-2012 dataset
DR1IFF_H2 <- select(DR1IFF_H2, SEQN, WTDRD1, DR1DRSTZ, DR1IFDCD, DR1IGRMS) #2013-2014 dataset
#Sums grams of repeated foods for the same person
DR1IFF_G3 <- aggregate(DR1IGRMS ~ SEQN+WTDRD1+DR1DRSTZ+DR1IFDCD, DR1IFF_G2, FUN = sum) #2011-2012 dataset
DR1IFF_H3 <- aggregate(DR1IGRMS ~ SEQN+WTDRD1+DR1DRSTZ+DR1IFDCD, DR1IFF_H2, FUN = sum) #2013-2014 dataset
#Transposes datasets to create only one row for SEQN
DR1IFF_G4 <- DR1IFF_G3 %>% pivot_wider (id_cols = c("SEQN", “WTDRD1”, “DR1DRSTZ”), names_from = “DR1IFDCD”, values_from = “DR1IGRMS”) #2011-2012 dataset
DR1IFF_H4 <- DR1IFF_H3 %>% pivot_wider (id_cols = c("SEQN", “WTDRD1”, “DR1DRSTZ”), names_from = “DR1IFDCD”, values_from = “DR1IGRMS”) #2013-2014 dataset
#combine datasets from 2011-2012 and 2012-2014
Dr1iff <- bind_rows(DR1IFF_G4, DR1IFF_H4)
write.csv(Dr1iff, file="Path/Dr1iffSugar.csv") #saves
#Selects only columns from SSBs
SoftEnergyDrink <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "92400000", "92410110", "92410310", "92410340", "92410360",
"92410390", "92410410", "92410510", "92410550", "92410610", "92410710", "92410810", "92411510", "92432000", "92433000", "92510610",
"92510650", "92510720", "92510730", "92510955", "92510960", "92511015", "92511250", "92513000", "92530410", "92530510", "92530610",
"92530950", "92531030", "92541010", "92542000", "92552030", "92582110", "92900110", "95310200", "95310400", "95310500", "95310560",
"95310600", "95310700", "95310750", "95310800", "95311000", "95320200", "95320500", "95321000")
write.csv(SoftEnergyDrink, file="Path/SoftEnergyDrink.csv") #saves
#Selects only columns from Milk desserts
MilkDesserts <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "13110000", "13110100", "13110110", "13110120", "13110130", "13110200",
"13110210", "13120050", "13120100", "13120110", "13120120", "13120121", "13120130", "13120140", "13120300", "13120310", "13120400",
"13120500", "13120550", "13120700", "13120710", "13120720", "13120730", "13120750", "13120760", "13120770", "13120780", "13120790",
"13120800", "13120810", "13121100", "13121300", "13122100", "13122500", "13127000", "13142000", "13150000", "13160400", "13160410",
"13161000", "13200110", "13210110", "13210180", "13210220", "13210270", "13210280", "13210300", "13210350", "13210410", "13210500",
"13210520", "13210610", "13210820", "13220110", "13220120", "13220230", "13220235", "13220240", "13230110", "13230130", "13230200",
"13230500", "13230510", "13241000", "13250000", "13250100", "13250200", "13252200", "13252500", "13252600")
write.csv(MilkDesserts, file="Path/MilkDesserts.csv") #saves
#Selects only columns from Yeast breads
YeastBreads <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "51160000", "51160100", "51160110", "51161000", "51161020", "51161050",
"51161250", "51161270", "51161280", "51166200")
write.csv(YeastBreads, file="Path/YeastBreads.csv") #saves
#Selects only columns from Quick breads
QuickBreads <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "52301000", "52302010", "52302020", "52302500", "52302600", "52303010",
"52304000", "52304010", "52304040", "52304100", "52304150", "52306010", "52306500", "52306550", "52306700", "52311010")
write.csv(QuickBreads, file="Path/QuickBreads.csv") #saves
#Selects only columns from Cakes
Cakes <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "53100050", "53100070", "53100100", "53101100", "53101200", "53101250",
"53102100", "53102200", "53102600", "53102700", "53102800", "53103000", "53104100", "53104260", "53104400", "53104500", "53104550",
"53104600", "53105270", "53105275", "53105300", "53106500", "53108200", "53109200", "53109220", "53110000", "53111000", "53112000",
"53112100", "53113000", "53114000", "53114100", "53115100", "53115200", "53115310", "53115320", "53115410", "53115450", "53116000",
"53116020", "53116270", "53116500", "53116510", "53116550", "53116600", "53117100", "53117200", "53118100", "53118200", "53118410",
"53118500", "53118550", "53119000", "53120270", "53120275", "53121270", "53121275", "53122080", "53123070", "53123500", "53124110")
write.csv(Cakes, file="Path/Cakes.csv") #saves
#Selects only columns from Cookies
Cookies <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "53200100", "53201000", "53202000", "53203500", "53204000", "53204010", "53204100",
"53204840", "53205250", "53205260", "53206000", "53206020", "53206030", "53206100", "53206500", "53206550", "53207000", "53207020", "53207050",
"53208000", "53208200", "53209005", "53209010", "53209015", "53209020", "53209100", "53209500", "53210000", "53210900", "53215500", "53220000",
"53220030", "53220040", "53222010", "53222020", "53223000", "53224000", "53224250", "53225000", "53226000", "53226500", "53226550", "53230000",
"53231000", "53231400", "53233000", "53233010", "53233040", "53233050", "53233060", "53233080", "53233100", "53234000", "53234100", "53234250",
"53235000", "53235500", "53235600", "53236000", "53236100", "53237000", "53237010", "53237500", "53238000", "53239000", "53239010", "53239050",
"53240000", "53240010", "53241500", "53241510", "53241600", "53242000", "53242500", "53243000", "53243010", "53243050", "53244010", "53244020",
"53246000", "53247000", "53247050", "53247500", "53251100", "53261000", "53270100")
write.csv(Cookies, file="Path/Cookies.csv") #saves
#Selects only columns from Pies
Pies <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "53300100", "53300170", "53301000", "53301070", "53301080", "53301500", "53303000", "53303500",
"53303570", "53304000", "53304070", "53305000", "53305010", "53305070", "53305700", "53305720", "53306000", "53307000", "53307050", "53307070",
"53308000", "53310050", "53311000", "53311050", "53312000", "53313000", "53341000", "53341500", "53342000", "53342070", "53343000", "53343070",
"53344000", "53344070", "53344200", "53344300", "53345000", "53346000", "53347000", "53347600", "53360000", "53381000", "53381070", "53385000",
"53385500", "53386250", "53386500", "53387000", "53391000", "53391100", "53400200", "53410100", "53410300", "53410500", "53410800", "53415100",
"53415120", "53415200", "53415400", "53420100", "53420200")
write.csv(Pies, file="Path/Pies.csv") #saves
#Selects only columns from Pastries
Pastries <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "53430100", "53430200", "53430750", "53440000", "53440500", "53440600", "53441110",
"53441210", "53450000", "53450500", "53450800", "53451500", "53452100", "53452150", "53452170", "53452200", "53452400", "53452420", "53452150",
"53452170", "53452200", "53452400", "53452420", "53453150", "53510000", "53510100", "53511000", "53520000", "53520110", "53520120", "53520140",
"53520150", "53520160", "53520200", "53520500", "53521100", "53521110", "53521120", "53521130", "53521140", "53521210", "53521230", "53530000",
"53530010", "53610100", "53610170", "53610200")
write.csv(Pastries, file="Path/Pastries.csv") #saves
#Selects only columns from Sweets
Sweets <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "91401000", "91402000", "91404000", "91406000", "91501010", "91501020", "91501030",
"91501040","91501090", "91501100", "91501110", "91501120", "91520100", "91550300", "91601000", "91611000", "91611050", "91611100", "91621000",
"91700010", "91700500", "91701010", "91701020", "91701030", "91702010", "91703010", "91703020", "91703030", "91703040", "91703060", "91703070",
"91703150", "91703200", "91703250", "91703300", "91703400", "91703500", "91703600", "91705010", "91705020", "91705030", "91705040", "91705050",
"91705060", "91705070", "91705090", "91705200", "91705300", "91705310", "91705400", "91705420", "91705430", "91705500", "91706000", "91706100",
"91706400", "91707000", "91707010", "91708000", "91708010", "91708020", "91708030", "91708070", "91708100", "91709000", "91713010", "91713030",
"91713040", "91713050", "91713060", "91715000", "91715100", "91715200", "91715300", "91718000", "91718100", "91718110", "91718200", "91718300",
"91721000", "91723000", "91723010", "91723020", "91723050", "91726000", "91726110", "91726130", "91726140", "91726150", "91726410", "91726420",
"91726425", "91727010", "91728000", "91731000", "91731010", "91731060", "91731100", "91731150", "91732000", "91732100", "91733000", "91733200",
"91734000", "91734100", "91734200", "91734300", "91734400", "91735000", "91739010", "91739600", "91742010", "91745010", "91745020", "91745040",
"91745100", "91746010", "91746100", "91746120", "91746150", "91746200", "91750000", "91760000", "91760100", "91760200", "91760500", "91800100",
"91801000")
write.csv(Sweets, file="Path/Sweets.csv") #saves
#Selects only columns from 100% fruit juices
FruitJuices <- select(Dr1iff, "SEQN", "WTDRD1", "DR1DRSTZ", "61201010", "61201020", "61201220", "61201225", "61204000", "61204010", "61204200",
"61207000", "61207010", "61207200", "61210000", "61210010", "61210220", "61210250", "61210620", "61210820", "61213220", "61213800", "61213900",
"64100100", "64100110", "64100200", "64100220", "64101010", "64104010", "64104030", "64104600", "64105400", "64116020", "64116060", "64120010",
"64121000", "64124020", "64126000", "64132010", "64132500", "64133100", "64201010", "64202010", "64203020", "64204010", "64205010", "64210010",
"64213010", "64215010", "64221010")
write.csv(FruitJuices, file="Path/FruitJuices.csv") #saves
#Merge all solid desserts datasets
Solids <- merge(MilkDesserts, YeastBreads, all.x = TRUE, all.y = TRUE)
Solids2 <- merge(Solids, QuickBreads, all.x = TRUE, all.y = TRUE)
Solids3 <- merge(Solids2, Cakes, all.x = TRUE, all.y = TRUE)
Solids4 <- merge(Solids3, Cookies, all.x = TRUE, all.y = TRUE)
Solids5 <- merge(Solids4, Pies, all.x = TRUE, all.y = TRUE)
Solids6 <- merge(Solids5, Pastries, all.x = TRUE, all.y = TRUE)
Solids7 <- merge(Solids6, Sweets, all.x = TRUE, all.y = TRUE)
write.csv(Solids7, file="Path/Solids7.csv") #saves
#Sums grams of SSBs
SoftEnergyDrink[is.na(SoftEnergyDrink)] = 0 #Substitutes 0 for NA
SoftEnergyDrink2 <- SoftEnergyDrink %>% rowwise() %>% mutate(RefriGrams = sum(c_across(92400000:95321000))) #sum grams of SSBs
write.csv(SoftEnergyDrink2, file="Path/SoftEnergyDrink2.csv") #saves
#Sums grams of Solid desserts
Solids7[is.na(Solids7)] = 0 #Substitutes 0 for NA
Solids8 <- Solids7 %>% rowwise() %>% mutate(SolidsGrams = sum(c_across(13110000:91801000))) #sum grams of Solid desserts
write.csv(Solids8, file="Path/Solids8.csv") #saves
#Sums grams of 100% fruit juice
FruitJuices[is.na(FruitJuices)] = 0 #Substitutes 0 for NA
FruitJuices2 <- FruitJuices %>% rowwise() %>% mutate(JuiceGrams = sum(c_across(61201010:64221010)))) #sum grams of juice
write.csv(FruitJuices2, file="Path/FruitJuices2.csv") #saves