Request Time

The Analysis of Philly 311 calls shows that most of service requests are from 9 am to 3 pm during the weekdays, especially on Monday and Tuesday between 9 am and 10 am. Potential Reasons include 311 Call center is closed during weekends and request sent during weekends through mobile app will be transferred to the department together on Monday.

phlcalls$request_date<-substr(phlcalls$requesteddatetime,1,10)
phlcalls$request_date<-as.Date(phlcalls$request_date,"%m/%d/%Y")
phlcalls$request_weekday<-weekdays(phlcalls$request_date)
phlcalls$request_time<-substr(phlcalls$requesteddatetime,12,22)
phlcalls$request_time<-strptime(phlcalls$request_time, format="%I:%M:%S %p")
phlcalls$request_time<-substr(phlcalls$request_time,12,19)
phlcalls$request_year<-substr(phlcalls$request_date,1,4)
daily_counts <- phlcalls%>%
  group_by(phlcalls$request_time)%>%
  summarise(dailyCounts=n())
daily_counts<-data.frame(daily_counts)
daily_counts<-daily_counts%>%
  rename(request_time=phlcalls.request_time,Counts=dailyCounts)%>%
  mutate(time=substr(request_time,1,5))
daily_counts2<-daily_counts%>%
  group_by(time)%>%
  summarise(Tot.counts=sum(Counts))


daily_counts2$time<-strptime(daily_counts2$time,format="%H:%M")
daily_counts2$time<-format(daily_counts2$time, format="%H:%M")

daily_counts2$timelabel<-paste(substr(daily_counts2$time,1,2),":00")
summary<-quantile(daily_counts2$Tot.counts)  #3rd Quantile: 324.5
daily_counts2$proxy.count<-daily_counts2$Tot.counts>summary[4]



ggplot(data=daily_counts2,aes(x=time,y=Tot.counts))+geom_col(aes(fill = proxy.count))+
  scale_fill_manual(labels=c("Rank Below 75% of Number of 311 Calls","Rank Above 75% of Number of 311 calls "),values = c("#424242","dark red" ))+
  labs(x="Time of Day",y="Number of 311 Calls")+
  scale_x_discrete(breaks=c("00:00","01:01","02:00",'03:00',"04:00",'05:00','06:00',
                            '07:00','08:00','09:00','10:00','11:00','12:00','13:00',
                            '14:00','15:00','16:00','17:00','18:00','19:00','20:00','21:00',
                            '22:00','23:00'),
                   labels=unique(daily_counts2$timelabel))+
  ggtitle('Philadelphia Number of 311 Calls by Time of  Day ')+
  theme.graph()

weekly_counts<-phlcalls%>%
  group_by(request_weekday,request_time)%>%
  summarise(counts=n())

weekly_counts$request_time<-substr(weekly_counts$request_time,1,2)
weekly_counts<-weekly_counts%>%
  group_by(request_weekday,request_time)%>%
  summarise(counts=sum(counts))

weekly_counts$request_weekday<-factor(weekly_counts$request_weekday,levels=c("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"),ordered=TRUE)
weekly_counts$request_time<-paste(weekly_counts$request_time,": 00")

ggplot(data = weekly_counts, mapping = aes(x =request_time, y =request_weekday, fill =counts))+
  geom_tile(colour="Black") +
  scale_fill_gradientn(breaks=c(38.5,142.5,2628.2,5164),colors=c('#f8edf1','#cf0029','#7b000b','#49000e'))+
  labs(x="Time of Day",y="Day of Week")+
  ggtitle('Philadelphia Number of 311 Calls by Day of Week ')+
  guides(fill=guide_legend(title="Number of 311 Calls"))+
  theme.graph()

Services and Responsible Agencies

Considering the reasons why people call 311, Maintenance Residential or Commercial is the most common request except for information request; Of the top 20 service requests, most are related to housing and transportation, which leads to the imbalanced responsible agencies showing in the right graph. Of all the agencies, Street Department and License & Inspections respond to the majority of 311 calls.

Service_counts<-phlcalls%>%
  group_by(servicename)%>%
  summarise(Counts=n())
Service_counts<-data.frame(Service_counts)

ggplot(Service_counts[-20,],aes(x=reorder(servicename,Counts),y=Counts))+
  geom_bar(stat="identity",fill = "dark red")+
  labs(x="Names of Requested Service",y="Number of requests")+
  ggtitle('Requested Services via 311 Calls in Philadelphia')+
  geom_text(aes(label = Counts), hjust = 1.1, color = "#FFFFFF")+
  theme.widegraph+
  coord_flip()  #without information request

Agency_counts<-phlcalls%>%
  group_by(agencyresponsible)%>%
  summarise(Counts=n())
Agency_counts<-data.frame(Agency_counts)
Agency_counts$agencyresponsible[1]<-"No Information"

ggplot(Agency_counts[Agency_counts$Counts>1670,],aes(x=reorder(agencyresponsible,Counts),y=Counts))+
  geom_bar(stat="identity",fill = "dark red")+
  labs(x="Names of Responsible Agencies",y="Number of requests")+
  ggtitle('Responsible Agencies in Philadelphia (Top 20 service requests)')+
  geom_text(aes(label = Counts), hjust = 1.1, color = "#FFFFFF")+
  theme.widegraph+
  coord_flip()  #without information request

Geographical Patterns

Considering the Geographical patterns of 311, the top six census tracts with over 100 service requests per 1000 people is located dispersedly across the Philadelphia, however, there are three high request services cluster areas, which are neighborhoods along Delaware Ave, like Port Richmond; the border area of Center City and South Philadelphia, and West Philadelphia.

census_api_key("2c3e9f9d2d65abab5f7b81fe418054415d363a43",overwrite=TRUE)
acs_variable_list.2015 <- load_variables(2015, #year
                                         "acs5", #five year ACS estimates
                                         cache = TRUE)
acs_vars<-c("B01001_001E"  # ACS total Pop estimates
            ) 

acsTracts.2015<-get_acs(geography = "tract",
                        year=2015,
                        variables=acs_vars,
                        geometry=TRUE,
                        state="PA",
                        county="Philadelphia",
                        output= "wide")%>%
  dplyr::select(GEOID,NAME,acs_vars)%>%
  rename(total.pop.2015 = B01001_001E )%>%
  st_as_sf()%>%
  st_transform(crs=4326)

philadelphia<-counties('PA')%>%
  st_as_sf()%>%
  st_transform(crs=4326)%>%
  filter(NAME == 'Philadelphia')


geophlcalls<-phlcalls%>%
  subset(is.na(censustract)==FALSE)%>%
  dplyr::select(servicerequestid,servicename,agencyresponsible,
         latitude,longitude,censustract,request_date,
         request_weekday,request_time,request_year)%>%
  st_as_sf(coords = c("longitude", "latitude"), crs = 4326, agr = "constant")

Tracts.phlcalls <-st_intersection(geophlcalls,acsTracts.2015)
calls.CTs<-Tracts.phlcalls%>%
  group_by(GEOID)%>%
  summarise(counts=n())%>%
  data.frame()%>%
  dplyr::select(GEOID,counts)

acsTracts.2015<-left_join(acsTracts.2015,calls.CTs,by='GEOID')
acsTracts.2015$counts.person<-(acsTracts.2015$counts/acsTracts.2015$total.pop.2015)*1000

acsTracts.2015_2<-filter(acsTracts.2015,total.pop.2015>counts)  #exclude outliers

acsTracts.2015_2$counts.person.pct<-ecdf(acsTracts.2015_2$counts.person)(acsTracts.2015_2$counts.person)
acsTracts.2015_2$counts.person.pct<-acsTracts.2015_2$counts.person.pct*100


acsTracts.2015_2<-acsTracts.2015_2%>%
  mutate(lon=map_dbl(geometry, ~st_point_on_surface(.x)[[1]]),
         lat = map_dbl(geometry, ~st_point_on_surface(.x)[[2]]))  #create centroid

ggplot()+
  geom_sf(data=acsTracts.2015_2,
          aes(fill=counts.person.pct),
          color="transparent")+
  scale_fill_continuous('Cumulated Percentile of service requests',
                     low='light pink',
                     high= 'dark red',
                   na.value='grey50')+
  geom_sf(data=philadelphia,
          color='black',
          fill='transparent',
          size=0.5)+
  geom_text(data=acsTracts.2015_2,aes(x=lon,y=lat,label=round(counts.person)),alpha=0.75,size=2,color='white')+
  labs(
    title = "Numbers of Requested Services via 311 Calls in Philadelphia by Census Tracts"
  )+
  theme.map

Geographical Distribution of Philly 311 Calls

Services and Agencies

The analysis shows that service requests are mainly related to traffic and streets, with Traffic Signal Emergency and Street Defect standing out as two major requests in the University City. Therefore, Streets Department responds to the vast majority of 311 calls in the University City.

UniCity.phlcalls<-st_intersection(geophlcalls,UniCity.tracts)
UniCity.phlcalls.summary<-UniCity.phlcalls%>%
                          group_by(servicename,agencyresponsible,GEOID)%>%
                          summarise(service.counts=n())
UniCity.phlcalls.summary<-data.frame(UniCity.phlcalls.summary)

UniCity.service.summary<-UniCity.phlcalls.summary%>%
  group_by(servicename)%>%
  summarise(Tot.service.counts=sum(service.counts))
UniCity.service.summary<-data.frame(UniCity.service.summary)
ggplot(UniCity.service.summary,aes(x=reorder(servicename,Tot.service.counts),y=Tot.service.counts))+
  geom_bar(stat="identity",fill = "dark red")+
  labs(x="Names of Requested Service",y="Number of requests")+
  ggtitle('Requested Services via 311 Calls in University City')+
  geom_text(aes(label = Tot.service.counts), hjust = 1.1, color = "#FFFFFF")+
  theme.widegraph+
  coord_flip()

UniCity.agency.summary<-UniCity.phlcalls.summary%>%
  group_by(agencyresponsible)%>%
  summarise(Tot.agency.counts=sum(service.counts))

UniCity.agency.summary<-data.frame(UniCity.agency.summary)
ggplot(UniCity.agency.summary,aes(x=reorder(agencyresponsible,Tot.agency.counts),y=Tot.agency.counts))+
  geom_bar(stat="identity",fill = "dark red")+
  labs(x="Names of Responsible Agency",y="Number of requests")+
  ggtitle('Responsible Agency in University City')+
  geom_text(aes(label = Tot.agency.counts), hjust = 1.1, color = "#FFFFFF")+
  theme.widegraph+
  coord_flip()

Street Scope Complaints

This map shows the location of different service requests related to street department. From the map, we could find road improvements are needed on Market St, Chestnut St, Walnut St., and Baltimore Ave, especially on Chestnut Street where street light outage is a severe problem for students who walk from or to the school at night and improvement are also needed on intersections along N 38 th street.

UniCity.street<-UniCity.phlcalls%>%
  filter(agencyresponsible=='Streets Department')%>%
  dplyr::select(servicerequestid,servicename,agencyresponsible,GEOID)%>%
  st_drop_geometry()

UniCity.street<-left_join(UniCity.street,phlcalls,by='servicerequestid')
geo_UniCity.street<-SpatialPointsDataFrame(UniCity.street[,c("longitude", "latitude")], UniCity.street)
geo_UniCity.street$servicename.x<-factor(geo_UniCity.street$servicename.x)
domain<-levels(geo_UniCity.street$servicename.x)
pal<-colorFactor(c('antiquewhite1','chartreuse4','chocolate1','chocolate4','blue','cornflowerblue',
                   'darkgoldenrod1','darkorange2','darkolivegreen1','darkorchid2','darkseagreen3',
                   'deeppink','gray47','hotpink4','lightpink','lightskyblue4','red'),domain=domain)

leaflet()%>%
  addProviderTiles("Stamen.TonerLite") %>% 
  addCircleMarkers(data=geo_UniCity.street,
                   lng=~longitude,
                   lat=~latitude,
                   stroke=FALSE,
                   fillColor  =~pal(servicename.x),
                   fillOpacity = 0.8,
                   popup = ~servicename.x)%>%
  leaflet::addLegend("bottomright", 
            pal=pal,
            values=geo_UniCity.street$servicename.x,
            labels = levels(geo_UniCity.street$servicename.x),
            opacity = 1)