# System
from datetime import datetime
import requests
import os
# DataFrame
import pandas as pd
# Visualization
import plotly.express as px
import plotly.graph_objects as go
import pycountry
# SQL Database
from sqllex import *
from sqllex.types import SQLRequest
vaccine_db = SQLite3x(path="Database/vaccine_data.db")
df = pd.read_html(
"https://www.pharmaceutical-technology.com/covid-19-vaccination-tracker/"
)
data = pd.DataFrame(df[0])
data["Fully Vaccinated Population (%)"] = (
data["Fully Vaccinated Population (%)"].str.replace("%", "").astype("float")
)/100
data["Fully Vaccinated Population (%)"] = data["Fully Vaccinated Population (%)"].astype("str")\
+ " %"
data.head()
UPDATE = True
World_dose_df = data["Doses Administered"].values[0]
World_dose_sql = vaccine_db.select(SELECT="Doses_Administered", FROM="World")[-1][0]
if World_dose_df == World_dose_sql:
UPDATE = False
print("No New Updates")
else:
UPDATE = True
print("Update the entire database")
# datetime object containing current date and time
now = datetime.now()
dt_string = now.strftime("%d/%m/%Y %H:%M:%S")
data["Date_Time"] = dt_string
vaccine_db.updatemany("Total", data.drop(columns="Date_Time", axis=1).values)
if UPDATE:
vaccine_db.insertmany(
"Pakistan",
data[data["Country"] == "Pakistan"].drop(columns="Country", axis=1).values,
);
vaccine_db.insertmany(
"World", data[data["Country"] == "World"].drop(columns=["Country","Vaccine being used in a country"], axis=1).values
);
vaccine_db.execute(
"SELECT Country,Fully_Vaccinated_Population FROM Total WHERE Doses_per_1000 > 200 LIMIT 5"
)
vaccine_db.select("Pakistan", ALL, LIMIT= 2)
vaccine_db.select("World", ALL, LIMIT= 2)
for column_name in vaccine_db.get_columns_names('World'):
print(column_name)
pakistan = pd.DataFrame(
vaccine_db.select("Pakistan"),
columns=[
"Doses Administered",
"Doses per 1000",
"Fully Vaccinated Population (%)",
"Vaccine being used in a country",
"Date_Time",
],
)
pakistan["Date_Time"] = pd.to_datetime(
pakistan["Date_Time"], format="%d/%m/%Y %H:%M:%S", errors="ignore"
)
pakistan.sort_values(by="Date_Time", inplace=True)
world = pd.DataFrame(
vaccine_db.select("World"),
columns=[
"Doses Administered",
"Doses per 1000",
"Fully Vaccinated Population (%)",
"Date_Time",
],
)
world["Date_Time"] = pd.to_datetime(
world["Date_Time"], format="%d/%m/%Y %H:%M:%S", errors="ignore"
)
total = pd.DataFrame(
vaccine_db.select("Total"),
columns=[
"Country",
"Doses Administered",
"Doses per 1000",
"Fully Vaccinated Population (%)",
"Vaccine being used in a country",
],
)
world["Fully Vaccinated Population (%)"] = (
world["Fully Vaccinated Population (%)"].str.replace("%", "").astype("float")
)
total["Fully Vaccinated Population (%)"] = (
total["Fully Vaccinated Population (%)"].str.replace("%", "").astype("float")
)
pakistan["Fully Vaccinated Population (%)"] = (
pakistan["Fully Vaccinated Population (%)"].str.replace("%", "").astype("float")
)
pakistan.tail(2)
world.tail(2)
world.to_csv("Kaggle/World-Vaccine-Progress/world_time_series.csv",index=False)
total.to_csv("Kaggle/World-Vaccine-Progress/World_Vaccination_Progress.csv",index=False)
pakistan.to_csv("Kaggle/World-Vaccine-Progress/pakistan_time_series.csv",index=False)
message = str("New Update : " + dt_string)
!echo $message
if UPDATE:
!kaggle datasets version -p /work/Kaggle/World-Vaccine-Progress -m "$message"
pakistan["Country"] = "Pakistan"
world["Country"] = "World"
world_pak = pd.concat([pakistan, world])
fig = px.line(
world_pak,
x="Date_Time",
y="Doses Administered",
log_y=True,
color="Country",
title="Dose Administered",
)
fig.show()
fig = px.line(
world_pak,
x="Date_Time",
y="Doses per 1000",
color="Country",
title="Doses per 1000",
)
fig.show()
fig = px.line(
world_pak,
x="Date_Time",
y="Fully Vaccinated Population (%)",
color="Country",
title="Fully Vaccinated Population (%)",
)
fig.show()
fig = px.bar(
world_pak.sort_values(by="Date_Time").tail(2),
x="Country",
y="Fully Vaccinated Population (%)",
)
fig.show()
total.drop(0, axis=0, inplace=True)
fig = px.scatter(
total.head(10),
x="Doses per 1000",
y="Doses Administered",
size="Fully Vaccinated Population (%)",
color="Country",
title="World Vaccine Data",
hover_name="Country",
log_x=True,
size_max=60,
)
fig.show()
fig = px.pie(
total.head(10),
values="Fully Vaccinated Population (%)",
names="Country",
title="World Fully Vaccinated Population (%)",
)
fig.show()
geo = pd.read_csv("Database/countries.csv")
geo_total = pd.merge(
total,
geo,
how="left",
on="Country"
)
geo_total.head(2)
fig = px.scatter_mapbox(
geo_total,
lat="Latitude",
lon="Longitude",
color="Fully Vaccinated Population (%)",
size="Doses Administered",
color_continuous_scale="Inferno",
size_max=50,
hover_data=["Country"],
mapbox_style="carto-positron",
zoom=0.5,
)
fig.update_layout(margin={"r": 0, "t": 0, "l": 0, "b": 0})
fig.show()
fig = px.bar(
total,
x="Country",
y="Fully Vaccinated Population (%)",
log_y=True,
title="World Fully Vaccinated Population",
).update_xaxes(categoryorder="total ascending")
fig.show()
fig = px.bar(
total,
x="Country",
y="Doses Administered",
log_y=True,
title="World Fully Doses Administered",
).update_xaxes(categoryorder="total descending")
fig.show()
def telegram_sendtext(bot_message):
send_text = (
"https://api.telegram.org/bot" + os.environ["TELE_TOKEN"] + "/sendMessage"
)
response = requests.get(
send_text,
params={
"chat_id": os.environ["TELE_BOT_ID"],
"parse_mode": "Markdown",
"text": bot_message,
},
)
return response.json()
def update_msg():
tail = world.tail(1).values
names = ["Doses Administered", "Doses per 1000", "Fully Vaccinated Population (%)"]
col = [0, 1, 2]
telegram_sendtext("New Update at " + str(tail[0, 4]));
T_text = []
for n, c in zip(names, col):
Text = n + " : " + str(tail[0, c])
T_text.append(Text)
telegram_sendtext(str(T_text));
if UPDATE:
update_msg()
else:
telegram_sendtext("No Update Today")