Introduction
Source: Covid-19 Record, Local Train Passenger, Airplane Passenger, Vaccination Record
Project Type: Regression Analysis
Long holidays due to Eid Al-Fitr and school holidays are expected to be the main cause of the surge in covid-19 cases in mid-2021 along with the increasing rate of inter-regional travel in the country. This increase caused hospitals to be overwhelmed due to limited treatment facilities and oxygen reserves, so the number of deaths from covid-19 also increased. Therefore, the vaccination program has been accelerated so that herd immunity is more quickly achieved and the severity of symptoms caused by covid-19 infection can be reduced.
The following is an analysis of the development of covid-19 cases in West Java, Indonesia, and case estimates for the next month based on the number of rail passengers in the agglomeration area of Jabodetabek, aircraft passengers who departed from Soekarno-Hatta International Airport, and vaccination rates since March 2020 until October 2021.
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import seaborn as snsData Preparation
covid_case = pd.read_csv('Dataset/satgas-covid-19-dp_cvd_agregat_kasus_terkonfirmasi_harian_covid19_jabar_data.csv')
covid_case.head(5)
def data_content(data):
cols = data.shape[1]
rows = data.shape[0]
any_null = data.isna().values.any()
any_duplicated = data.duplicated().any()
print('Has ',cols,' Columns and ',rows,' Rows')
print('Any Null Value is ',any_null)
print('Any Duplicate Row is',any_duplicated)
data_content(covid_case)Output: Has 6 Columns and 642 Rows Any Null Value is False Any Duplicate Row is False
covid_case['Tanggal'] = pd.to_datetime(covid_case['tanggal'])
covid_case['Month'] = covid_case['Tanggal'].dt.strftime('%B') + ' ' + covid_case['Tanggal'].dt.strftime('%Y')
covid_case['kasus_baru'] = covid_case['total_konfirmasi'].diff()
covid_case['kasus_baru'] = covid_case['kasus_baru'].replace(np.nan, covid_case['total_konfirmasi']).astype(int)
covid_case['meninggal_baru'] = covid_case['meninggal'].diff()
covid_case['meninggal_baru'] = covid_case['meninggal_baru'].replace(np.nan, covid_case['meninggal']).astype(int)
covid_case['sembuh_baru'] = covid_case['sembuh'].diff()
covid_case['sembuh_baru'] = covid_case['sembuh_baru'].replace(np.nan, covid_case['sembuh']).astype(int)
covid_case.head()
covid_case_per_month = covid_case.groupby('Month')['kasus_baru','meninggal_baru','sembuh_baru'].sum().reset_index()
covid_case_per_month['Month'] = pd.Categorical(covid_case_per_month['Month'], ['January 2020','February 2020','March 2020','April 2020','May 2020','June 2020',
'July 2020','August 2020','September 2020','October 2020','November 2020','December 2020',
'January 2021','February 2021','March 2021','April 2021','May 2021','June 2021',
'July 2021','August 2021','September 2021','October 2021','November 2021','December 2021'])
covid_case_per_month = covid_case_per_month.sort_values('Month')
covid_case_per_month.index = np.arange(2,len(covid_case_per_month)+2)
covid_case_per_month.tail()
train_passenger_2020 = pd.read_excel('Dataset/Jumlah Penumpang Kereta Api 2020.xlsx',header=2)
train_passenger_2020 = train_passenger_2020.T
train_passenger_2020 = train_passenger_2020.rename(columns=train_passenger_2020.iloc[0]).drop(train_passenger_2020.index[0])
train_passenger_2020 = train_passenger_2020.drop(train_passenger_2020.iloc[:,4:],axis=1).reset_index()
train_passenger_2020 = train_passenger_2020.drop([12])
train_passenger_2020['Bulan'] = train_passenger_2020['index']+' 2020'
train_passenger_2021 = pd.read_excel('Dataset/Jumlah Penumpang Kereta Api 2021.xlsx',header=2)
train_passenger_2021 = train_passenger_2021.T
train_passenger_2021 = train_passenger_2021.rename(columns=train_passenger_2021.iloc[0]).drop(train_passenger_2021.index[0])
train_passenger_2021 = train_passenger_2021.drop(train_passenger_2021.iloc[:,4:],axis=1).reset_index()
train_passenger_2021 = train_passenger_2021.drop([12])
train_passenger_2021['Bulan'] = train_passenger_2020['index']+' 2021'
train_passenger = pd.concat([train_passenger_2020,train_passenger_2021], ignore_index=True)
train_passenger = train_passenger.drop(columns='index',axis=1)
train_passenger = train_passenger[['Bulan','Jabodetabek', 'Non Jabodetabek (Jawa)',
'Jawa (Jabodetabek+Non Jabodetabek)', 'Sumatera']]
train_passenger = train_passenger.iloc[:22,:2]
train_passenger['Jabodetabek'] = pd.to_numeric(train_passenger['Jabodetabek'])
train_passenger.tail()
airplane_passenger_2020 = pd.read_excel('Dataset/Jumlah Penumpang Pesawat di Bandara Utama 2020.xlsx',header=3)
airplane_passenger_2020 = airplane_passenger_2020.T
airplane_passenger_2020 = airplane_passenger_2020.rename(columns=airplane_passenger_2020.iloc[0]).drop(airplane_passenger_2020.index[0])
airplane_passenger_2020 = airplane_passenger_2020.drop(airplane_passenger_2020.iloc[:,5:],axis=1).reset_index()
airplane_passenger_2020 = airplane_passenger_2020.drop([12])
airplane_passenger_2020['Bulan'] = airplane_passenger_2020['index']+' 2020'
airplane_passenger_2020 = airplane_passenger_2020.loc[:12] #Domestic departure
airplane_passenger_2021 = pd.read_excel('Dataset/Jumlah Penumpang Pesawat di Bandara Utama 2021.xlsx',header=3)
airplane_passenger_2021 = airplane_passenger_2021.T
airplane_passenger_2021 = airplane_passenger_2021.rename(columns=airplane_passenger_2021.iloc[0]).drop(airplane_passenger_2021.index[0])
airplane_passenger_2021 = airplane_passenger_2021.drop(airplane_passenger_2021.iloc[:,5:],axis=1).reset_index()
airplane_passenger_2021 = airplane_passenger_2021.drop([12])
airplane_passenger_2021['Bulan'] = airplane_passenger_2020['index']+' 2021'
airplane_passenger_2021 = airplane_passenger_2021.loc[:12] #Domestic departure
airplane_passenger = pd.concat([airplane_passenger_2020,airplane_passenger_2021], ignore_index=True)
airplane_passenger = airplane_passenger.drop(columns='index',axis=1)
airplane_passenger = airplane_passenger[['Bulan','Soekarno Hatta','Juanda','Ngurah Rai','Hasanudin','Polonia']]
airplane_passenger = airplane_passenger.iloc[:22,:2]
airplane_passenger['Soekarno Hatta'] = pd.to_numeric(airplane_passenger['Soekarno Hatta'])
airplane_passenger.tail()
passenger_num = pd.merge(train_passenger, airplane_passenger, on='Bulan')
passenger_num.tail()
vaccination_rate = pd.read_csv('Dataset/Time_Series_Pelaksanaan_Vaksinasi_Pertama_Per_Prov_data.csv')
vaccination_rate['Tanggal'] = pd.to_datetime(vaccination_rate['Tanggal'],dayfirst = True)
vaccination_rate['Month'] = vaccination_rate['Tanggal'].dt.strftime('%B') + ' ' + vaccination_rate['Tanggal'].dt.strftime('%Y')
vaccination_rate['New_Vaccinated'] = vaccination_rate['divaksin1 (ribu)']*1000
vaccination_rate['Total_Vaccinated'] = vaccination_rate['Running Sum of divaksin1 (ribu)']*1000vaccination_rate_per_month = vaccination_rate.drop(vaccination_rate[['Running Sum of divaksin1 (ribu)','Total_Vaccinated','divaksin1 (ribu)','Tanggal','Provinsi']],axis=1)
vaccination_rate_per_month = vaccination_rate_per_month.groupby(['Month'], sort=False)['New_Vaccinated'].sum().reset_index()
vaccination_total_per_month = vaccination_rate.groupby(['Month'], sort=False)['Total_Vaccinated'].max()
vaccination_per_month = pd.merge(vaccination_rate_per_month,vaccination_total_per_month,on='Month')
row_vr = pd.DataFrame([['March 2020',0,0],['April 2020',0,0],['May 2020',0,0],['June 2020',0,0],['July 2020',0,0],['August 2020',0,0],['September 2020',0,0],['October 2020',0,0],['November 2020',0,0],['December 2020',0,0]],columns=['Month','New_Vaccinated','Total_Vaccinated'])
vaccination_per_month = pd.concat([row_vr, vaccination_per_month])
vaccination_per_month.index = np.arange(2,len(vaccination_per_month)+2)
vaccination_per_month
df = pd.merge(passenger_num, covid_case_per_month, left_index=True, right_index=True)
df = pd.merge(df, vaccination_per_month, left_index=True, right_index=True)
df.tail()
df = df.drop(['Bulan','Month_x'],axis=1)
df = df.rename(columns={'Month_y':'Month','Jabodetabek':'Rail_Jabodetabek','Soekarno Hatta':'AirPlane_Soetta','kasus_baru':'New_Cases','meninggal_baru':'New_Deaths','sembuh_baru':'New_Recovers','New_Vaccinated':'New_Vaccinated','Total_Vaccinated':'Total_Vaccinated'}).reset_index()
df = df[['Month', 'Rail_Jabodetabek', 'AirPlane_Soetta','New_Vaccinated','Total_Vaccinated', 'New_Cases',
'New_Deaths', 'New_Recovers']]
df.tail()
plt.subplots(figsize=(15,5))
sns.lineplot(data=df, x='Month', y='New_Cases',color='red')
sns.lineplot(data=df, x='Month', y='New_Deaths',color='black')
sns.lineplot(data=df, x='Month', y='New_Recovers',color='green')
plt.axvline(x='May 2020',color='salmon',linewidth=15, alpha=0.1) #Libur Idul fitri
plt.axvline(x='June 2020',color='yellow',linewidth=15, alpha=0.1) #Libur Sekolah
plt.axvline(x='December 2020',color='salmon',linewidth=15, alpha=0.1) #Libur Natal, Cuti Bersama Idul Fitri
plt.axvline(x='May 2021',color='salmon',linewidth=15, alpha=0.1) #Libur Idul fitri
plt.axvline(x='June 2021',color='yellow',linewidth=15, alpha=0.1) #Libur Sekolah
plt.xticks(rotation = 45)
plt.title('Development of Covid-19 Cases in West Java')
plt.legend(['New Cases','New Deaths','New Recovery','Public Holiday','School Holiday'])
plt.ylabel('Number of People')
plt.show()
plt.subplots(figsize=(15,5))
sns.lineplot(data=df, x='Month', y='Rail_Jabodetabek',color='darkorange')
sns.lineplot(data=df, x='Month', y='AirPlane_Soetta',color='dodgerblue')
sns.lineplot(data=df, x='Month', y='Total_Vaccinated',color='hotpink')
plt.axvline(x='May 2020',color='salmon',linewidth=15, alpha=0.1) #Libur Idul fitri
plt.axvline(x='June 2020',color='yellow',linewidth=15, alpha=0.1) #Libur Sekolah
plt.axvline(x='December 2020',color='salmon',linewidth=15, alpha=0.1) #Libur Natal, Cuti Bersama Idul Fitri
plt.axvline(x='May 2021',color='salmon',linewidth=15, alpha=0.1) #Libur Idul fitri
plt.axvline(x='June 2021',color='yellow',linewidth=15, alpha=0.1) #Libur Sekolah
plt.xticks(rotation = 45)
plt.title('Factors Affecting Covid-19 Cases Development')
plt.legend(['Rail Passenge','Air Plane Passenger','Total Vaccinated','Public Holiday','School Holiday'])
plt.ylabel('Number of People')
plt.show()
corr = df.corr()
plt.figure(figsize=(10,8))
plt.title('Covid-19 In Jabar')
sns.heatmap(corr.abs(), cmap="RdYlGn_r",annot=True)
plt.show()
x = df[['Total_Vaccinated','Rail_Jabodetabek','AirPlane_Soetta']]
y1 = df[['New_Cases']]
y2 = df[['New_Deaths']]
y3 = df[['New_Recovers']]
test_nov = np.array([[25126194,14000,1080000]])
test_dec = np.array([[27987652,17000,1110000]])from sklearn.linear_model import LogisticRegression
logreg = LogisticRegression(solver='saga',random_state=0,max_iter=10)logreg_new_cases = logreg.fit(x,np.ravel(y1))
logreg_nce = logreg_new_cases.predict(test_nov)
logreg_new_deaths = logreg.fit(x,np.ravel(y2))
logreg_nde = logreg_new_deaths.predict(test_nov)
logreg_new_recovers = logreg.fit(x,np.ravel(y3))
logreg_nre = logreg_new_recovers.predict(test_nov)
print('Estimation of Covid-19 Record in Jabar')
print('-'*50)
print('Vaccinated People: ',test_nov[0][0])
print('Passenger Travel by Train: ',test_nov[0][1])
print('Passenger Travel by Air Plane: ',test_nov[0][2])
print('-'*50)
print('New Cases Estimation: ',int(logreg_nce))
print('New Death Estimation: ',int(logreg_nde))
print('New Recoveries Estimation: ',int(logreg_nre))
Output: Estimation of Covid-19 Record in Jabar -------------------------------------------------- Vaccinated People: 25126194 Passenger Travel by Train: 14000 Passenger Travel by Air Plane: 1080000 -------------------------------------------------- New Cases Estimation: 3057 New Death Estimation: 72 New Recoveries Estimation: 4492
Conclusion
Estimates of new cases, new deaths, and new recoveries of Covid-19 in November were 29,082, 614, and 305,531 people respectively. While the record addition of new cases in November was 1,967, new deaths were 41, and new cures were 2,371.
Fadhilah Qalbi Annisa 