Load required packages

Load R packages needed for this data exploration and proper visualisation.

library(plotly)
library(tidyverse)
library(lubridate)
library(knitr)
library(kableExtra)
library(sf)
library(viridis)
library(glue)
library(scales)
library(widgetframe)
library(here)

Data… Data..

Data is obtained from Github.

• Removed “India” and “State unassigned” from ’State coloumn
• Calculate TPR rate = (Confirmed cases/Total tests)* 100)
• Calculate 7 day moving average of TPR
• Calculate active cases per state = Confirmed -(Recovered + Deceased)
• Calculate Mortality rate per confirmed cases = (Deceased / Confirmed ) * 100
• Calculate daily deaths

states <- read_csv("https://api.covid19india.org/csv/latest/states.csv")

states_daily <- states %>%
  filter(!(State %in% c("India","State Unassigned"))) %>% 
  mutate(TPR = (Confirmed/Tested)* 100) %>% 
  group_by(State) %>%
  mutate(Daily_cases = Confirmed - lag(Confirmed, default = 0)) %>% 
  mutate(Daily_deaths = Deceased - lag(Deceased, default = 0)) %>% 
  mutate(TPR_7d = zoo::rollmean(TPR, k=7,fill = NA)) %>% 
  mutate(Active_cases = Confirmed -  (Recovered + Deceased)) %>% 
  mutate(Mortality_rate = (Deceased / Confirmed ) * 100) %>% 
  mutate(across(where(is.numeric), ~ round(., 3))) %>% 
  mutate(Code = toupper(substr(State,0,3))) %>% 
  ungroup()
  
states_daily$Date <- ymd(states_daily$Date)

covid_latest <- states_daily %>% 
  filter(Date==max(Date)) %>% 
  rename(State_Name = State)

• A national level data subset

# India_stats
India_stats<- states %>%
  filter((State %in% c("India"))) %>% 
  mutate(TPR = (Confirmed/Tested)* 100) %>% 
  group_by(State) %>%
  mutate(Daily_cases = Confirmed - lag(Confirmed, default = 0)) %>% 
  mutate(Daily_deaths = Deceased - lag(Deceased, default = 0)) %>% 
  mutate(TPR_7d = zoo::rollmean(TPR, k=7,fill = NA)) %>% 
  mutate(Active_cases = Confirmed -  (Recovered + Deceased)) %>% 
  mutate(Mortality_rate = (Deceased / Confirmed ) * 100) %>% 
  mutate(across(where(is.numeric), ~ round(., 3))) %>% 
  ungroup()
  
India_stats$Date <- ymd(India_stats$Date)

# India latest map
map_in <- st_read(here("static","/data/india/India_State_Boundary.shp"))

## Reading layer `India_State_Boundary' from data source 
##   `/home/arungopinath/Public/arungopi.gitlab.io/static/data/india/India_State_Boundary.shp' 
##   using driver `ESRI Shapefile'
## Simple feature collection with 37 features and 1 field
## Geometry type: MULTIPOLYGON
## Dimension:     XY
## Bounding box:  xmin: 7583508 ymin: 753607.8 xmax: 10843390 ymax: 4452638
## Projected CRS: WGS 84 / Pseudo-Mercator

map_latest <- merge(map_in,covid_latest, by ="State_Name")

First reporting case - When ? Where ?

first <- states_daily %>% 
  filter(Date==min(Date)) %>% 
  select(Date,State,Confirmed) %>% 
  kable() %>% 
  kable_styling()
first

• State = Kerala & Date: 30 Jan,2020

Peak active covid case in India - When ?

Peak_day <- India_stats %>% 
  arrange(desc(Daily_cases)) %>% 
  top_n(1,Daily_cases) %>% 
  select(Date,Daily_cases) %>% 
  kable() %>% 
  kable_styling()
Peak_day

Which day records maximum TPR in India?

Peak_tpr <- India_stats %>% 
  arrange(desc(TPR)) %>% 
  top_n(1,TPR) %>% 
  select(Date,TPR) %>% 
  kable() %>% 
  kable_styling()
Peak_tpr

Highest death rate recorded ?

Peak_mortality <- India_stats %>% 
  arrange(desc(Mortality_rate)) %>% 
  top_n(1,Mortality_rate) %>% 
  select(Date,Mortality_rate) %>% 
  kable() %>% 
  kable_styling()
Peak_mortality

Top 5 states with cumulative confirmed cases ?

top_5 <- states_daily %>% 
  filter(Date==max(Date)) %>% 
  arrange(desc(Confirmed)) %>% 
  top_n(5,Confirmed)%>% 
  select(Date,State,Confirmed) %>% 
  kable() %>% 
  kable_styling()

top_5  

Least cumulative confirmed cases ?

least_affect <- states_daily %>% 
  filter(Date==max(Date)) %>% 
  arrange(desc(Confirmed)) %>% 
  slice_min(order_by = Confirmed) %>%
  select(Date,State,Confirmed) %>% 
  kable() %>% 
  kable_styling()

least_affect 

Most deaths - Top 5 ?

most_death <- states_daily %>% 
  filter(Date==max(Date)) %>% 
  arrange(desc(Deceased)) %>% 
  top_n(5,Deceased) %>%
  select(State,Deceased) %>% 
  kable() %>% 
  kable_styling()

most_death  

Least deaths - State or UT ?

least_death <- states_daily %>% 
  filter(Date==max(Date)) %>% 
  arrange(desc(Deceased)) %>% 
  top_n(-1,Deceased)%>%
  select(State,Deceased) %>% 
  kable() %>% 
  kable_styling()

least_death 

Which state has most number of active cases ?

active_cases_top <- states_daily %>% 
  filter(Date==max(Date)) %>% 
  arrange(desc(Active_cases)) %>% 
  top_n(1,Active_cases) %>% 
  select(Date,State,Active_cases,TPR) %>%
  kable() %>% 
  kable_styling() 

active_cases_top 

Which state has least number of active cases ?

active_cases_least<- states_daily %>% 
  filter(Date==max(Date)) %>% 
  arrange(desc(Active_cases)) %>% 
  top_n(-1,Active_cases) %>%
  select(Date,State,Active_cases,TPR) %>%
  kable() %>% 
  kable_styling()

active_cases_least 

Highest TPR ever recorded in a state ?

tpr_highest <- states_daily %>% 
  arrange(desc(TPR)) %>% 
  top_n(1,TPR)%>%
  select(Date,State,Active_cases,TPR) %>%
  kable() %>% 
  kable_styling()

tpr_highest   

Highest TPR 7 day moving average recorded ?

tpr_7_highest <- states_daily %>% 
  arrange(desc(TPR_7d)) %>% 
  top_n(1,TPR_7d)  %>%
  select(Date,State,Active_cases,TPR_7d) %>%
  kable() %>% 
  kable_styling()

tpr_7_highest 

Check weather any reletion between weekdays and Covid 19 deaths

# Calculate weekdays

India_stats <- India_stats %>% 
  mutate(weekdays = weekdays(Date))

# Reorder weekdays
India_stats$weekdays <- factor(India_stats$weekdays, weekdays(as.Date('1970-01-03') + 1:7))

ggplot(India_stats, aes(x=weekdays, y=Daily_deaths)) + 
  geom_bar(stat="identity", width=.5, fill="tomato3")+
  labs(title="Any relation between Weekdays and Daily deaths", 
       subtitle="Mondays have lowest reported deaths", 
       caption= glue("Last update: {max(map_latest$Date)}")) + 
  theme(axis.text.x = element_text(angle=65, vjust=0.6))

• Mondays record minimum deaths while tuesdays are having higher number of deaths.

Daily cases in India

# Daily cases

active <-  ggplot(India_stats)+
  geom_line(aes(Date,Daily_cases, color ="Daily_cases"),show.legend = F)+
  labs(title="Covid 19 cases in India a time series", 
       subtitle="", 
       caption= glue("Last update: {max(map_latest$Date)}")) + 
  theme(axis.text.x = element_text(angle=65, vjust=0.6),legend.position='none')

covid_time <- ggplotly(active, dynamicTicks = TRUE) %>%
  rangeslider() %>%
  layout(hovermode = "x") 

frameWidget(covid_time)

Covid 19 confirmed cases

Confirmed <- plot_ly(x=states_daily$Date, 
               y=states_daily$Code, 
               z = states_daily$Confirmed,
               type = "heatmap",
               hoverinfo='text',
               showscale=FALSE ,
               text = ~paste('State: ',states_daily$State, '<br>Date : </br>', states_daily$Date,'<br>Total Confirmed: </br>',states_daily$Confirmed),
              colorscale= "Portland")%>% 
  layout(title="Covid 19 cases in Indian states (Cummulative)")


frameWidget(Confirmed) 

Deaths during pandemic (Cummulative)

Deaths <- plot_ly(x=states_daily$Date, 
               y=states_daily$Code, 
               z = states_daily$Daily_deaths, 
               type = "heatmap",
               hoverinfo='text',
               showscale=FALSE ,
               text = ~paste('State: ',states_daily$State, '<br>Date : </br>', states_daily$Date,'<br>Deaths </br>',states_daily$Daily_deaths),
               colorscale= "Blackbody") %>% 
  layout(title="Life lost in Indian states during Covid 19 pandemic") 

frameWidget(Deaths) 

• Abnormality numbers may occur while states re-evaluate their stats.

Test Positivity Rate (TPR) during pandemic

TPR <- plot_ly(x=states_daily$Date, 
               y=states_daily$Code, 
               z = states_daily$TPR,
               type = "heatmap",
               showscale=FALSE ,
               hoverinfo='text',
               text = ~paste('State: ',states_daily$State, '<br>Date : </br>', states_daily$Date,'<br>TPR rate:</br>',states_daily$TPR, '<br>Cases today: </br>',states_daily$Daily_cases))%>% 
  layout(title="TPR rate in India states during Covid 19 pandemic") 
  
frameWidget(TPR)