Package Loading & Data Prep

library(readr)
library(dplyr)
library(ggplot2)
library(scales)
library(lubridate)
library(tidyr)
library(knitr)

flu<-readr::read_csv("/Users/Brett/Desktop/FluLike.csv",
                 col_types = cols(Date = col_date(format = "%b-%y")))%>% #Making sure date imports as a date.
          select(IllnessType = "Ind1Name",
                 Borough ="Dim1Value",
                 Age = "Dim2Value",
                 count = "X9",
                 Date)%>%
  mutate(Year = year(Date), #Creating a column for just "Year"
         Month = month(Date, label = TRUE))%>% #Creating a column for just "Month"
  filter(Borough !="Citywide", #Filtering out aggregate rows.
         Age !="All age groups")

head(flu)%>%
  kable()

IllnessType	Borough	Age	count	Date	Year	Month
Influenza-like illness (ILI)	Bronx	Ages 0-4 years	849	2016-01-01	2016	Jan
Influenza-like illness (ILI)	Bronx	Ages 0-4 years	758	2016-02-01	2016	Feb
Influenza-like illness (ILI)	Bronx	Ages 0-4 years	661	2016-03-01	2016	Mar
Influenza-like illness (ILI)	Bronx	Ages 0-4 years	494	2016-04-01	2016	Apr
Influenza-like illness (ILI)	Bronx	Ages 0-4 years	445	2016-05-01	2016	May
Influenza-like illness (ILI)	Bronx	Ages 0-4 years	395	2016-06-01	2016	Jun

Number of Flu-like Illness Reports

Jan-Mar Number of Reports of Flu-like Illness, by Borough & Year

flu%>%
  filter(Month %in% c("Jan","Feb","Mar"))%>%
  group_by(Borough, Year)%>%
  summarize(count=sum(count))%>%
  ggplot()+
  geom_col(aes(x=Year,y=count, fill=count))+
  geom_label(aes(x=Year,y=count, label=count))+
  facet_wrap(~Borough)+
  theme_minimal()+
  theme(legend.position = "none")

Jan-Mar Number of Reports of Flu-like Illness, by Age & Year

flu%>%
  filter(Month %in% c("Jan","Feb","Mar"))%>%
  group_by(Age, Year)%>%
  summarize(count=sum(count))%>%
  ggplot()+
  geom_col(aes(x=Year,y=count, fill=count))+
  geom_label(aes(x=Year,y=count, label=count))+
  facet_wrap(~Age)+
  theme_minimal()+
  theme(legend.position = "none")

YOY % Change

Percent Change in Flu-like Illness from 2019-2020 (Jan-Mar), by Age

flu%>%
  filter(Month %in% c("Jan","Feb","Mar"))%>%
  group_by(Age, Year)%>%
  summarize(count=sum(count))%>%
  spread(Year,count)%>%
  mutate(YOYChange = percent((`2020` - `2019`)/(`2020`)))%>%
  kable()

Age	2016	2017	2018	2019	2020	YOYChange
Ages 0-4 years	9952	9223	13403	9714	14155	31.4%
Ages 18-64 years	12652	10829	23367	13412	42814	68.7%
Ages 5-17 years	8365	9921	15627	9904	14844	33.3%
Ages 65+ years	1328	1890	3935	2118	5905	64.1%

Percent Change in Flu-like Illness from 2019-2020 (Jan-Mar), by Borough

flu%>%
  filter(Month %in% c("Jan","Feb","Mar"))%>%
  group_by(Borough, Year)%>%
  summarize(count=sum(count))%>%
  spread(Year,count)%>%
  mutate(YOYChange = percent((`2020` - `2019`)/(`2020`)))%>%
  kable()

Borough	2016	2017	2018	2019	2020	YOYChange
Bronx	6806	6783	12605	7938	19378	59.0%
Brooklyn	8151	9464	15767	9752	20405	52.2%
Manhattan	4526	4428	8027	4377	10542	58.5%
Queens	10492	9053	15948	10807	21985	50.8%
Staten Island	1406	1214	2248	1187	2666	55.5%
Unknown	916	921	1737	1087	2742	60.4%

Introducing Zip Code

fluzip<-readr::read_csv("/Users/Brett/Desktop/Flulike by zip.csv",
                 col_types = cols(Date = col_date(format = "%b-%y"),
                                  Zip = col_character()))%>% #Making sure date imports as a date.
          select(Zip ="Zip",
                 Age = "Dim2Value",
                 Count,
                 Date)%>%
  mutate(Year = year(Date), #Creating a column for just "Year"
         Month = month(Date, label = TRUE))%>% #Creating a column for just "Month"
  filter(Zip !="Citwide", #Filtering out aggregate rows.
         Age !="All age groups")

Number of Flulike Illness Reports

Jan-Mar N Flu-like Illness Reports, by Zip & Year

fluzip%>%
  filter(Month %in% c("Jan","Feb","Mar"))%>%
  group_by(Zip, Year)%>%
  summarize(Count=sum(Count))%>%
  arrange(-Count)%>%
  head(20)%>%
  kable()

Zip	Year	Count
11368	2020	2735
11373	2020	1832
11368	2018	1819
10467	2020	1783
10456	2020	1596
10030	2020	1438
11206	2020	1345
10452	2020	1297
11432	2020	1270
11220	2020	1255
11368	2019	1234
11368	2016	1193
10456	2018	1172
10466	2020	1171
11373	2018	1168
11208	2020	1161
11372	2020	1146
10457	2020	1140
10453	2020	1139
10029	2020	1116

% Change YOY

% Change in Flu-like Illness from 2019-2020 (Jan-Mar), by Borough

fluzip%>%
  filter(Month %in% c("Jan","Feb","Mar"))%>%
  group_by(Zip, Year)%>%
  summarize(Count=sum(Count))%>%
  spread(Year,Count)%>%
  mutate(YOYChange = 100*round((`2020` - `2019`)/(`2020`),2))%>%
  arrange(-YOYChange)%>%
  kable()

Zip	2016	2017	2018	2019	2020	YOYChange
88888	NA	0	5	2	27	93
10021	27	28	50	27	115	77
11426	29	38	64	28	108	74
10022	13	21	38	24	89	73
10028	37	39	108	37	138	73
11003	39	31	72	26	97	73
11040	41	34	66	35	128	73
10464	0	6	9	8	28	71
11102	135	82	139	71	246	71
10461	129	129	178	153	498	69
10475	63	47	117	83	271	69
11375	85	68	159	79	258	69
10030	502	530	918	462	1438	68
10014	26	22	65	17	52	67
10023	43	42	118	47	143	67
10065	16	12	38	23	70	67
10467	466	411	858	587	1783	67
10019	54	52	136	65	191	66
11209	91	125	252	136	404	66
11220	330	354	751	426	1255	66
10469	170	138	344	266	750	65
10026	167	135	226	121	336	64
10473	158	159	328	216	593	64
11214	151	137	294	174	479	64
11219	88	87	182	105	295	64
10035	235	219	390	210	575	63
10128	62	71	135	72	193	63
10306	93	92	205	99	266	63
10314	170	148	255	135	365	63
10016	98	138	247	166	434	62
10304	197	151	339	141	372	62
10462	258	271	474	320	838	62
10463	224	230	415	204	536	62
10465	44	72	102	107	284	62
10472	314	352	661	399	1057	62
11204	88	96	161	125	327	62
11355	301	297	433	257	676	62
10027	265	256	426	222	573	61
11378	110	81	169	119	303	61
10013	33	49	87	42	105	60
10040	169	213	354	188	475	60
10309	82	66	122	77	194	60
10458	346	359	603	404	1004	60
11106	106	92	159	86	215	60
11228	33	43	150	70	176	60
10034	156	184	329	163	395	59
10305	129	110	203	104	255	59
11213	190	294	459	264	637	59
11215	72	101	219	115	283	59
11218	139	166	272	176	434	59
11225	160	244	393	191	470	59
11234	258	250	386	224	543	59
11379	68	46	110	66	162	59
11413	231	196	361	182	445	59
10011	62	55	128	54	128	58
10025	168	159	288	131	313	58
10031	249	241	412	238	569	58
10036	121	85	201	120	289	58
10453	473	414	906	475	1139	58
10459	277	281	534	287	686	58
10468	336	361	679	405	966	58
11217	69	96	181	119	285	58
10312	152	132	234	134	310	57
11367	130	126	190	117	275	57
11374	79	87	199	102	238	57
10033	209	322	427	269	606	56
10454	442	495	747	470	1072	56
10456	656	676	1172	707	1596	56
11103	75	67	112	75	171	56
11223	180	184	325	208	476	56
11238	121	167	270	156	353	56
11369	350	321	451	297	670	56
10451	441	529	718	456	1023	55
10452	567	569	1000	580	1297	55
10457	494	449	961	515	1140	55
10460	276	258	541	354	782	55
11236	418	462	711	391	862	55
11364	23	24	51	27	60	55
11368	1193	1043	1819	1234	2735	55
11427	110	88	205	111	245	55
10024	40	41	73	54	117	54
10455	381	432	602	400	866	54
10466	399	154	663	544	1171	54
11201	118	141	238	151	325	54
11203	328	388	667	362	793	54
11211	160	220	361	236	515	54
11226	392	535	831	437	943	54
11233	267	389	680	394	860	54
11357	174	132	287	190	412	54
10032	340	394	617	353	755	53
11435	469	398	612	421	904	53
11212	477	616	732	438	921	52
11370	157	150	214	173	358	52
11422	62	35	109	71	148	52
10003	54	50	125	69	142	51
10029	490	489	966	552	1116	51
11105	59	47	74	52	107	51
11210	186	216	334	212	430	51
11229	182	148	284	196	398	51
11354	154	138	249	160	327	51
11417	194	160	339	201	409	51
11434	408	347	624	395	813	51
11216	192	226	393	209	418	50
11373	880	715	1168	924	1832	50
11418	573	455	820	513	1023	50
10301	191	173	294	160	311	49
11231	83	116	206	138	271	49
11235	253	209	440	305	601	49
11432	634	550	935	654	1270	49
10002	268	236	452	247	478	48
11365	133	141	200	141	273	48
11420	255	199	442	268	518	48
11222	41	63	103	67	126	47
11207	580	653	963	583	1080	46
11361	34	33	67	39	72	46
11385	443	389	760	458	854	46
10010	19	48	67	43	78	45
11101	214	190	302	243	439	45
10009	239	233	476	256	458	44
11205	155	215	359	246	442	44
11208	665	642	1083	647	1161	44
11224	224	223	375	256	454	44
11372	595	513	758	643	1146	44
11419	287	270	540	362	651	44
11421	269	196	365	268	479	44
10302	407	342	596	337	593	43
11221	436	530	877	594	1045	43
11433	266	222	445	298	524	43
11693	72	59	113	78	136	43
10038	94	65	133	106	184	42
11206	462	609	995	785	1345	42
11377	535	460	680	646	1105	42
11691	372	354	721	425	729	42
11237	378	345	606	395	673	41
11412	137	89	224	161	274	41
11230	119	174	239	222	373	40
11358	105	90	142	111	163	32
10271	NA	0	0	0	0	NaN
10278	NA	0	0	0	0	NaN
10279	NA	0	0	0	0	NaN

Adding Neighborhood Names to the Data

zipmap<-read_csv("/Users/Brett/Library/Mobile Documents/com~apple~CloudDocs/All Files/Employers/NYSCI/Datasets/Outbound Data/US Postal Codes copy 2.csv")%>%
  rename(Zip = "Postal Code")%>%
  mutate(Zip = as.character(Zip))

## Parsed with column specification:
## cols(
##   `Postal Code` = col_double(),
##   `Place Name` = col_character(),
##   State = col_character(),
##   `State Abbreviation` = col_character(),
##   County = col_character(),
##   Latitude = col_double(),
##   Longitude = col_double(),
##   `Place Name, State Abbreviation` = col_character()
## )

fluzip_Named<-left_join(fluzip,zipmap)

## Joining, by = "Zip"

This is the same table as above, but with neighborhood names instead of Zips (The source for neighborhood names might be incomplete here…better to replace with your own source for zip/neighborhood names)

fluzip_Named%>%
  filter(Month %in% c("Jan","Feb","Mar"))%>%
  group_by(`Place Name`, Year)%>%
  summarize(Count=sum(Count))%>%
  spread(Year,Count)%>%
  mutate(YOYChange = 100*round((`2020` - `2019`)/(`2020`),2))%>%
  arrange(-YOYChange)%>%
  kable()

Place Name	2016	2017	2018	2019	2020	YOYChange
NA	NA	0	5	2	27	93
Bellerose	29	38	64	28	108	74
Elmont	39	31	72	26	97	73
New Hyde Park	41	34	66	35	128	73
Forest Hills	85	68	159	79	258	69
Astoria	375	288	484	284	739	62
Maspeth	110	81	169	119	303	61
Bronx	6914	6792	12612	7940	19380	59
Middle Village	68	46	110	66	162	59
New York City	4256	4429	8030	4378	10555	59
Springfield Gardens	231	196	361	182	445	59
Rego Park	79	87	199	102	238	57
Corona	1193	1043	1819	1234	2735	55
Flushing	690	651	1014	645	1441	55
Oakland Gardens	23	24	51	27	60	55
Queens Village	110	88	205	111	245	55
Staten Island	1421	1214	2248	1187	2666	55
East Elmhurst	507	471	665	470	1028	54
Whitestone	174	132	287	190	412	54
Brooklyn	8086	9464	15772	9753	20453	52
Rosedale	62	35	109	71	148	52
Ozone Park	194	160	339	201	409	51
Elmhurst	880	715	1168	924	1832	50
Jamaica	1777	1517	2616	1768	3511	50
Richmond Hill	573	455	820	513	1023	50
Fresh Meadows	133	141	200	141	273	48
South Ozone Park	255	199	442	268	518	48
Bayside	34	33	67	39	72	46
Ridgewood	443	389	760	458	854	46
Long Island City	214	190	302	243	439	45
Jackson Heights	595	513	758	643	1146	44
South Richmond Hill	287	270	540	362	651	44
Woodhaven	269	196	365	268	479	44
Far Rockaway	444	413	834	503	865	42
Woodside	535	460	680	646	1105	42
Saint Albans	137	89	224	161	274	41

Flu-like Illness by Age, Borough, and Zip Code