SD Warren Mailchimp Data - Dec 4
Load libraries and data
library(dplyr); library(data.table)
library(ggmap); library(sp)
library(ggplot2); library(ggrepel)
mc = fread("subscribed_members_export_f1b36eada0.csv")
zip = fread("zip-to-coord.txt", stringsAsFactors = T)
# merge zip to coordinatesSummarize zip data on MC
- 271 observations but 115 with zip codes
mc %>%
summarize(zip = sum(ifelse(`Zip Code` =="", 0, 1)),
no_zip = sum(ifelse(`Zip Code` != "", 0, 1)),
total = n())## zip no_zip total
## 1 115 156 271Merge lat/lon with the zip
# change zip to factor
zip$ZIP = factor(zip$ZIP)
mc$`Zip Code` = factor(mc$`Zip Code`)
# join tables
mc.join1 = merge(x = mc[,1:10], y = zip,
by.x = 'Zip Code', by.y = "ZIP")
mc.join1## Zip Code First Name Last Name Email Address Phone Number
## 1: 11377 Susan Lee sslee327@gmail.com 646-496-3520
## 2: 85338 Jan Marc Hano joydviver@gmail.com
## 3: 91910 Rosario Rodriguez rodriguezrosie688@gmail.com 6198821172
## 4: 91910 Marlon Bryce marlo231@yahoo.com (619)758-5227
## 5: 91911 Russell Pryor rpryor@ucsd.edu 8585344546
## ---
## 110: 92126 Barbara Bush doveinhand@gmail.com
## 111: 92128 Darelene Garvis d_garvais@hotmail.com 8587746698
## 112: 92130 Rima Goldenberg rimagold@gmail.com
## 113: 92131 Ben Koval ben_koval@hotmail.com 8584725458
## 114: 92154 Kevin White kpwmsw@yahoo.com 619-794-4222
## Notes
## 1:
## 2: from Goodyear, AZ
## 3:
## 4: Sonâ\200\231s schedule can limit availability
## 5:
## ---
## 110:
## 111:
## 112:
## 113:
## 114: wants to stay involved
## Volunteer Interest MEMBER_RATING
## 1: 3
## 2: 2
## 3: Texting, Other 3
## 4: Canvassing, Phone Bank 3
## 5: Other 2
## ---
## 110: Texting, Organizing, Other 4
## 111: 3
## 112: Canvassing, Phone Bank, Organizing, Other 5
## 113: 2
## 114: 3
## OPTIN_TIME OPTIN_IP LAT LNG
## 1: 2019-10-23 17:41:17 NA 40.74482 -73.90516
## 2: 2019-11-14 20:24:41 NA 33.37297 -112.41083
## 3: 2019-11-04 16:18:13 NA 32.63787 -117.05803
## 4: 2019-11-12 13:47:51 NA 32.63787 -117.05803
## 5: 2019-11-04 18:04:15 NA 32.60818 -117.05451
## ---
## 110: 2019-10-10 16:16:29 NA 32.90845 -117.14138
## 111: 2019-11-14 06:47:42 NA 32.99982 -117.07181
## 112: 2019-10-05 11:50:45 NA 32.94579 -117.21437
## 113: 2019-11-19 20:00:00 NA 32.88607 -117.08598
## 114: 2019-11-14 20:24:41 NA 32.55702 -117.00621# and get rid of weird zips
mc.join1 <- mc.join1[!(`Zip Code` %in% c(11377, 85338))]
mc.join1## Zip Code First Name Last Name Email Address Phone Number
## 1: 91910 Rosario Rodriguez rodriguezrosie688@gmail.com 6198821172
## 2: 91910 Marlon Bryce marlo231@yahoo.com (619)758-5227
## 3: 91911 Russell Pryor rpryor@ucsd.edu 8585344546
## 4: 91913 Ingrid Contreras ingridec1993@gmail.com 415-994-7564
## 5: 91932 Diana Sanchez ds0606384@swccd.edu 8586993044
## ---
## 108: 92126 Barbara Bush doveinhand@gmail.com
## 109: 92128 Darelene Garvis d_garvais@hotmail.com 8587746698
## 110: 92130 Rima Goldenberg rimagold@gmail.com
## 111: 92131 Ben Koval ben_koval@hotmail.com 8584725458
## 112: 92154 Kevin White kpwmsw@yahoo.com 619-794-4222
## Notes
## 1:
## 2: Sonâ\200\231s schedule can limit availability
## 3:
## 4:
## 5:
## ---
## 108:
## 109:
## 110:
## 111:
## 112: wants to stay involved
## Volunteer Interest MEMBER_RATING
## 1: Texting, Other 3
## 2: Canvassing, Phone Bank 3
## 3: Other 2
## 4: Organizing, Hosting events 4
## 5: 2
## ---
## 108: Texting, Organizing, Other 4
## 109: 3
## 110: Canvassing, Phone Bank, Organizing, Other 5
## 111: 2
## 112: 3
## OPTIN_TIME OPTIN_IP LAT LNG
## 1: 2019-11-04 16:18:13 NA 32.63787 -117.0580
## 2: 2019-11-12 13:47:51 NA 32.63787 -117.0580
## 3: 2019-11-04 18:04:15 NA 32.60818 -117.0545
## 4: 2019-10-05 11:43:55 NA 32.61946 -116.9858
## 5: 2019-11-19 20:00:00 NA 32.57375 -117.1209
## ---
## 108: 2019-10-10 16:16:29 NA 32.90845 -117.1414
## 109: 2019-11-14 06:47:42 NA 32.99982 -117.0718
## 110: 2019-10-05 11:50:45 NA 32.94579 -117.2144
## 111: 2019-11-19 20:00:00 NA 32.88607 -117.0860
## 112: 2019-11-14 20:24:41 NA 32.55702 -117.0062plot
plot(mc.join1$LAT, mc.join1$LNG)
on a map (wee!)
sd_get_map <- get_map(c(lon= -117., lat = 32.9),
maptype = "toner-lite",
zoom = 10, source="stamen")
sd_map <- ggmap(sd_get_map)
sd_map +
geom_jitter(aes(x = LNG,
y = LAT,
color = MEMBER_RATING,
size = MEMBER_RATING,
alpha = 0.3),
data = mc.join1) +
ggtitle("Base map")
## with labels of high member rating
sd_map +
geom_jitter(aes(x = LNG,
y = LAT,
color = MEMBER_RATING,
size = MEMBER_RATING,
alpha = 0.3),
data = mc.join1) +
geom_text_repel(data = mc.join1,
aes(x = LNG, y = LAT),
label = ifelse(mc.join1$MEMBER_RATING > 3,
mc.join1$`First Name`, "")) +
ggtitle("Most engaged")## Warning in min(x): no non-missing arguments to min; returning Inf## Warning in max(x): no non-missing arguments to max; returning -Inf## Warning in min(x): no non-missing arguments to min; returning Inf## Warning in max(x): no non-missing arguments to max; returning -Inf
let’s add contours
sd_map +
stat_density2d(aes(x = LNG, y = LAT,
fill = ..level..,
alpha = ..level../5),
bins = 10, geom = "polygon",
data = mc.join1) +
scale_fill_gradient(low = "black", high = "red") +
ggtitle("Engagement density")