def merge_newcases_change_in_cases_dataframes(self, dataframe_confirmed):
""" Combines dataframes containing new cases, and the change in new cases, and inserts the dataframe to the SQLite database
Args:
dataframe_confirmed: The first parameter.
Returns:
df: A dataframe containing Columns Change_in_cases_added, ObservationDate.
"""
try:
df_newcases = self.generate_new_cases_per_day(dataframe_confirmed)
df = pd.merge(dataframe_confirmed,
df_newcases,
how='outer',
on='SNo')
df_changeinnewcases = self.generate_change_in_cases_added(df)
df = pd.merge(df_changeinnewcases, df, how='outer', on='SNo')
except Exception as e:
print(e)
try:
engine, meta = db_connector.db_engine()
df.to_sql("daily_change", engine, if_exists="replace")
except Exception as e:
print(e)
def query_state_data(Country,state):
"""Function Queries data by a state or province within a country
"""
sql_string = f"""SELECT * FROM [daily_change] WHERE [Country/Region] = '{Country}' and [Province/State] = '{state}' ORDER BY [Province/State] ASC, [ObservationDate] ASC;"""
engine,meta = db_connector.db_engine()
conn = engine.connect()
df = pd.read_sql_query(sql_string,conn)
return df
def select_data_by_country(self, country_name: str):
""" Queries the Covid19 SQLite database by country name.
:returns Dataframe:
"""
engine, meta = db_connector.db_engine()
query_string = f"""SELECT * FROM 'covid19basic' WHERE [Country/Region] = '{country_name}' ORDER BY [Province/State] ASC, [ObservationDate] ASC;"""
df = pd.read_sql_query(query_string, engine)
return df
def df_to_sql(self):
try:
parent_dir = os.getcwd()
data_folder_dir = os.path.join(parent_dir, "data")
data_path = os.path.join(data_folder_dir, "covid_19_data.csv")
df = pd.read_csv(data_path)
engine, meta = db_connector.db_engine()
df.to_sql("covid19basic", engine, if_exists="replace")
except Exception as e:
print(e)
def get_states_in_data(dataframe):
"""Function returns a list of all states present in a country
"""
try:
country_name = dataframe["Country/Region"].values[1]
sql_string = f"""SELECT DISTINCT [Province/State],[Country/Region] FROM [daily_change] WHERE [Country/Region] = '{country_name}' ORDER BY [Province/State] ASC; """
engine,meta = db_connector.db_engine()
conn = engine.connect()
df = pd.read_sql_query(sql_string,conn)
return df
except Exception as e:
print(e)
def query_country_data(country):
""" Function queries data by a countries name
"""
try:
engine, meta = db_connector.db_engine()
conn = engine.connect()
df_select = pd.read_sql_query(f"""SELECT * FROM [daily_change] WHERE [Country/Region] = '{country}' ORDER BY [Province/State] ASC, [ObservationDate] ASC; """,conn)
if len(df_select) == 0:
list_country = [country]
initlize_db(list_country)
try:
engine, meta = db_connector.db_engine()
conn = engine.connect()
sql_string = f"""SELECT * FROM [daily_change] WHERE [Country/Region] = '{country}' ORDER BY [Province/State] ASC, [ObservationDate] ASC; """
df_select = pd.read_sql_query(sql_string,conn)
return df_select
except Exception as e:
print(e)
print("Country Data Present")
return df_select
else:
return df_select
except Exception as e:
print(e)
def df_to_sql(self, country_ISO):
#Processes one countries data for a specific timerange
try:
#Collect List of States
r = requests.get(
'https://covid-api.com/api/reports?date=2020-12-01&iso=DEU')
jsonify = r.json()
df = pd.DataFrame(jsonify['data'])
#Because of nested json the regions column needs to be refactored
regiondf = pd.DataFrame(df['region'])
x = json.loads(regiondf.to_json())
states = pd.DataFrame(x['region'])
states = states.transpose()
#remove column filled with nested JSON metadata
Maindataframe = pd.DataFrame()
for date in self.daterange(datetime.date(2020, 12, 1),
datetime.date(2020, 12, 5)):
date = date.strftime("%Y-%m-%d")
for rows in states['province']:
r = requests.get(
f'https://covid-api.com/api/reports?date={date}&iso=DEU®ion_province={rows}'
)
jsonify = r.json()
df = pd.DataFrame(jsonify['data'])
df = df.drop(columns=['region'])
df['State'] = rows
#TODO: Change the coutnry name so its updated by the ISO or read in
df['Country'] = "Germany"
#check if the dataframe has data in it or not
#If not then append the dataframe df to the Maindataframe
if Maindataframe.size == 0:
Maindataframe = df
else:
Maindataframe = Maindataframe.append(df,
ignore_index=True)
#TODO: Cleanup data so that the state and country are columns
engine, meta = db_connector.db_engine()
df.to_sql("covid19basic", engine, if_exists="replace")
except Exception as e:
print(e)
def __init__(self, countries: list):
"""
"""
# try:
# sql_string = "SELECT DISTINCT * FROM [daily_change] ORDER by ObservationDate Desc"
# df = db_connector.df_sql_query(sql_string)
# except sqlite3.OperationalError as e:
# print(e)
if type(countries) is list:
for country in countries:
try:
df = self.select_data_by_country(country)
self.merge_newcases_change_in_cases_dataframes(df)
print(f"Succesfully generated and inserted data for {country}")
except Exception as e:
print(e)
else:
if type(countries) is str:
try:
df = self.select_data_by_country(countries)
self.merge_newcases_change_in_cases_dataframes(df)
print(f"Succesfully generated and inserted data for {countries}")
except Exception as e:
print(e)
else:
raise TypeError("Values given is not a String")
sql_string = "SELECT DISTINCT * FROM [daily_change]"
df = db_connector.df_sql_query(sql_string)
engine = db_connector.db_engine()
df.to_sql("daily_change",engine, if_exists="replace")
def __init__(self, countries: list):
"""
"""
# try:
# sql_string = "SELECT DISTINCT * FROM [daily_change] ORDER by ObservationDate Desc"
# df = db_connector.df_sql_query(sql_string)
# except sqlite3.OperationalError as e:
# print(e)
if type(countries) is list:
for country in countries:
try:
df = self.select_data_by_country(country)
self.merge_newcases_change_in_cases_dataframes(df)
print(
f"Succesfully generated and inserted data for {country}"
)
except Exception as e:
print(e)
else:
if type(countries) is str:
try:
df = self.select_data_by_country(countries)
self.merge_newcases_change_in_cases_dataframes(df)
print(
f"Succesfully generated and inserted data for {countries}"
)
except Exception as e:
print(e)
else:
raise TypeError("Values given is not a String")
sql_string = "SELECT DISTINCT * FROM [daily_change]"
df = db_connector.df_sql_query(sql_string)
engine = db_connector.db_engine()
df.to_sql("daily_change", engine, if_exists="replace")
# response example
# "data": [
# {
# "date": "2020-12-01",
# "confirmed": 152782,
# "deaths": 2825,
# "recovered": 107339,
# "confirmed_diff": 2063,
# "deaths_diff": 49,
# "recovered_diff": 2701,
# "last_update": "2020-12-02 05:27:41",
# "active": 42618,
# "active_diff": -687,
# "fatality_rate": 0.0185,
# "region": {
# "iso": "DEU",
# "name": "Germany",
# "province": "Baden-Wurttemberg",
# "lat": "48.6616",
# "long": "9.3501",
# "cities": []
# }