def test_shift():
assert str(dt.shift(f.A)) == str(f.A.shift())
assert str(dt.shift(f[:])) == str(f[:].shift())
DT = dt.Frame({"C": [2, 5, 30, 20, 10],
"D": [10, 8, 20, 20, 1]})
assert_equals(DT[:, f[:].shift()], DT[:, dt.shift(f[:])])
def add7dSumColumn(table, srcColumn, newColumn):
src = dt.f[srcColumn]
#src_na = dt.math.isna(src)
src_na = dt.math.isna(src)
table[src_na, src] = 0
newTable = table[:, dt.f[:].extend({newColumn: src + dt.shift(src, n=1) + dt.shift(src, n=2) + dt.shift(src, n=3)+
dt.shift(src, n=4)+ dt.shift(src, n=5)+ dt.shift(src, n=6)})]
#print(newTable)
return newTable
def add7dWTrendColumn(table, srcColumn, newColumn):
src = dt.f[srcColumn]
newTable = table[:, dt.f[:].extend({
newColumn:
(dt.math.abs(src) + 5) / (dt.shift(dt.math.abs(src), n=7) + 5)
})]
return newTable
def test_shift_all_columns():
DT = dt.Frame([[5, 9, 1], list("ABC"), [2.3, 4.4, -2.5]],
names=["A", "B", "D"])
RES = DT[:, shift(f[:], n=1)]
assert_equals(RES, dt.Frame(A=[None, 5, 9],
B=[None, "A", "B"],
D=[None, 2.3, 4.4]))
def test_shift_group_column():
DT = dt.Frame(A=[1, 2, 1, 1, 2])
RES = DT[:, shift(f.A), by(f.A)]
assert_equals(
RES, dt.Frame({
"A": [1, 1, 1, 2, 2],
"A.0": [None, 1, 1, None, 2]
}))
def add7dDifferenceColumn(table, srcColumn, newColumn):
src = dt.f[srcColumn]
#src_na = dt.math.isna(src)
src_na = dt.math.isna(src)
table[src_na, src] = 0
newTable = table[:, dt.f[:].extend({newColumn: src - dt.shift(src, n=7)})]
#print(newTable)
return newTable
def test_shift_stypes():
DT = dt.Frame([[0, 1, 2, 3, 4], [2.7, 9.4, -1.1, None, 3.4],
["one", "two", "three", "FOUR", "five"],
[True, False, True, True, False]])
RES = shift(DT, n=1)
assert_equals(
RES,
dt.Frame([[None, 0, 1, 2, 3], [None, 2.7, 9.4, -1.1, None],
[None, "one", "two", "three", "FOUR"],
[None, True, False, True, True]]))
def test_shift_amount():
DT = dt.Frame(range(5))
RES = DT[:, [shift(f.C0, n) for n in range(-5, 6)]]
assert_equals(
RES,
dt.Frame([[None, None, None, None, None], [4, None, None, None, None],
[3, 4, None, None, None], [2, 3, 4, None, None],
[1, 2, 3, 4, None], [0, 1, 2, 3, 4], [None, 0, 1, 2, 3],
[None, None, 0, 1, 2], [None, None, None, 0, 1],
[None, None, None, None, 0], [None, None, None, None, None]],
stype=dt.int32))
def test_shift_reduced_column():
DT = dt.Frame(A=[1, 2, 1, 1, 2, 1], B=range(6))
RES = DT[:, shift(dt.sum(f.B)), by(f.A)]
assert_equals(
RES,
dt.Frame(A=[1, 1, 1, 1, 2, 2],
B=[None, 10, 10, 10, None, 5],
stypes={
"A": dt.int32,
"B": dt.int64
}))
def test_shift_wrong_signature1():
msg = r"Function shift\(\) requires 1 positional argument"
with pytest.raises(TypeError, match=msg):
shift()
with pytest.raises(TypeError, match=msg):
shift(None)
with pytest.raises(TypeError, match=msg):
shift(n=3)
def create_data(X: dt.Frame = None) -> Union[
str, List[str],
dt.Frame, List[dt.Frame],
np.ndarray, List[np.ndarray],
pd.DataFrame, List[pd.DataFrame],
Dict[str, str], # {data set names : paths}
Dict[str, dt.Frame], # {data set names : dt frames}
Dict[str, np.ndarray], # {data set names : np arrays}
Dict[str, pd.DataFrame], # {data set names : pd frames}
]:
# define date column and forecast horizon
date_col = 'date'
forecast_len = 7
# get COVID19 data from NYTimes github
us_total = dt.fread("https://raw.githubusercontent.com/nytimes/covid-19-data/master/us.csv")
# produce lag of 1 unit and add as new feature for each column in the list
series_cols = ["cases", "deaths"]
aggs = {f"{col}_yesterday": shift(f[col]) for col in series_cols}
us_total[:, update(**aggs), sort(date_col)]
# update NA lags to 0
aggs = {f"{col}_yesterday": 0 for col in series_cols}
us_total[isna(f[f"{series_cols[0]}_yesterday"]), update(**aggs)]
# compute daily values by differentiating
aggs = {f"{col}_daily": f[col] - f[f"{col}_yesterday"] for col in series_cols}
us_total[:, update(**aggs), sort(date_col)]
# delete columns with yesterday (shift) values
series_cols_to_delete = [f"{col}_yesterday" for col in series_cols]
del us_total[:, series_cols_to_delete]
# set negative daily values to 0
us_total[f.cases_daily < 0, [f.cases_daily]] = 0
us_total[f.deaths_daily < 0, [f.deaths_daily]] = 0
# determine threshold to split train and test based on forecast horizon
dates = dt.unique(us_total[:, date_col])
split_date = dates[-(forecast_len + 1):, :, dt.sort(date_col)][0, 0]
test_date = dates[-1, :, dt.sort(date_col)][0, 0]
# split data to honor forecast horizon in test set
df = us_total[date_col].to_pandas()
train = us_total[df[date_col] <= split_date, :]
test = us_total[df[date_col] > split_date, :]
# return [train, test] and rename dataset names as needed
return {f"covid19_daily_{split_date}_us_train": train,
f"covid19_daily_{test_date}_us_test": test}
def add7dAvrgColumn(table, srcColumn, newColumn):
src = dt.f[srcColumn]
newTable = table[:, dt.f[:].extend({
newColumn:
(src + dt.shift(src, n=1) + dt.shift(src, n=2) + dt.shift(src, n=3) +
dt.shift(src, n=4) + dt.shift(src, n=5) + dt.shift(src, n=6)) / 7
})]
#print(newTable)
return newTable
def test_shift_with_by():
DT = dt.Frame(A=[1, 2, 1, 1, 2, 1, 2],
B=[3, 7, 9, 0, -1, 2, 1])
RES = DT[:, {"lag1": shift(f.B, 1),
"lag2": shift(f.B, 2),
"lag3": shift(f.B, 3),
"nolag": shift(f.B, 0),
"lead1": shift(f.B, -1),
"lead2": shift(f.B, -2),
"lead3": shift(f.B, -3),
}, by(f.A)]
assert_equals(RES, dt.Frame(A=[1, 1, 1, 1, 2, 2, 2],
lag1=[None, 3, 9, 0, None, 7, -1],
lag2=[None, None, 3, 9, None, None, 7],
lag3=[None, None, None, 3, None, None, None],
nolag=[3, 9, 0, 2, 7, -1, 1],
lead1=[9, 0, 2, None, -1, 1, None],
lead2=[0, 2, None, None, 1, None, None],
lead3=[2, None, None, None, None, None, None]))
def addShiftedColumn(table, srcColumn, newColumn, shift_rows):
src = dt.f[srcColumn]
newTable = table[:, dt.f[:].extend({newColumn: dt.shift(src, n=shift_rows)})]
#print(newTable)
return newTable
def add7dTrendColumn(table, srcColumn, newColumn):
src = dt.f[srcColumn]
newTable = table[:, dt.f[:].extend({newColumn: (src / dt.shift(src, n=7))})]
#print(newTable)
return newTable
# X: datatable - primary data set
# Parameters:
# time_col: date/time/int - time column to order rows before the shift op
# group_by_cols: list of column names - group columns
# shift_cols: list of column names - columns to shift
# Output:
# dataset augmented with shifted columns
from datatable import f, by, sort, update, shift, isna
time_col = "date"
group_by_cols = ["state"]
shift_cols = ["cases", "deaths"]
new_dataset_name = "new_dataset_name_with_shift"
# produce lag of 1 unit and add as new feature for each shift column
aggs = {f"{col}_yesterday": shift(f[col]) for col in shift_cols}
X[:, update(**aggs), sort(time_col), by(*group_by_cols)]
# update NA lags
aggs = {f"{col}_yesterday": 0 for col in shift_cols}
X[isna(f[f"{shift_cols[0]}_yesterday"]), update(**aggs)]
aggs = {f"{col}_daily": f[col] - f[f"{col}_yesterday"] for col in shift_cols}
X[:, update(**aggs), sort(time_col), by(group_by_cols)]
for col in shift_cols:
del X[:, f[f"{col}_yesterday"]]
return {new_dataset_name: X}
def create_data(X: dt.Frame = None) -> Union[
str, List[str],
dt.Frame, List[dt.Frame],
np.ndarray, List[np.ndarray],
pd.DataFrame, List[pd.DataFrame],
Dict[str, str], # {data set names : paths}
Dict[str, dt.Frame], # {data set names : dt frames}
Dict[str, np.ndarray], # {data set names : np arrays}
Dict[str, pd.DataFrame], # {data set names : pd frames}
]:
# define date column and forecast horizon
date_col = 'date'
group_by_cols = ["state"]
forecast_len = 7
# get COVID19 data from NYTimes github
us_states = dt.fread("https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-states.csv")
# get states population
us_states_pop = dt.fread(
"http://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-alldata.csv")
us_states_pop.names = {'NAME': 'state', 'POPESTIMATE2019': 'pop'}
us_states_pop.key = "state"
# augment data with state population figures and create adjusted case and death counts
series_cols = ["cases", "deaths"]
aggs = {f"{col}100k": dt.f[col] / (dt.g.pop / 100000) for col in series_cols}
us_states[:, dt.update(pop = g.pop, pop100k = g.pop / 10000, **aggs), join(us_states_pop)]
# remove rows without state defined (resulted in unmatched rows after left outer join)
del us_states[isna(f.pop), :]
# produce lag of 1 unit and add as new feature for each column in the list
series_cols.extend([col + "100k" for col in series_cols])
aggs = {f"{col}_yesterday": shift(f[col]) for col in series_cols}
us_states[:, update(**aggs), sort(date_col), by(group_by_cols)]
# update NA lags to 0
aggs = {f"{col}_yesterday": 0 for col in series_cols}
us_states[isna(f[f"{series_cols[0]}_yesterday"]), update(**aggs)]
# compute daily values by differentiating
aggs = {f"{col}_daily": f[col] - f[f"{col}_yesterday"] for col in series_cols}
us_states[:, update(**aggs), sort(date_col), by(group_by_cols)]
# delete columns with yesterday (shift) values
series_cols_to_delete = [f"{col}_yesterday" for col in series_cols]
del us_states[:, series_cols_to_delete]
# set negative daily values to 0
us_states[f.cases_daily < 0, [f.cases_daily, f.cases100k_daily]] = 0
us_states[f.deaths_daily < 0, [f.deaths_daily, f.deaths100k_daily]] = 0
# determine threshold to split train and test based on forecast horizon
dates = dt.unique(us_states[:, date_col])
split_date = dates[-(forecast_len + 1):, :, dt.sort(date_col)][0, 0]
test_date = dates[-1, :, dt.sort(date_col)][0, 0]
# split data to honor forecast horizon in test set
df = us_states[date_col].to_pandas()
train = us_states[df[date_col] <= split_date, :]
test = us_states[df[date_col] > split_date, :]
# return [train, test] and rename dataset names as needed
return {f"covid19_daily_{split_date}_by_states_train": train,
f"covid19_daily_{test_date}_by_states_test": test}
def test_shift_expr():
DT = dt.Frame(A=[3, 4, 5, 6], B=[-1, 2, -2, 3])
RES = DT[:, shift(f.A + f.B, n=1)]
assert_equals(RES, dt.Frame([None, 2, 6, 3]))
def test_shift_n():
DT = dt.Frame(a=range(10))
assert_equals(DT[:, [f.a.shift(n=3), f.a.shift(-1)]],
DT[:, [dt.shift(f.a, 3), dt.shift(f.a, -1)]])
def test_shift_frame():
DT = dt.Frame(A=range(5))
RES = shift(DT, 2)
assert_equals(RES, dt.Frame(A=[None, None, 0, 1, 2]))
def test_shift_default():
DT = dt.Frame(A=range(5))
assert_equals(DT[:, shift(f.A)],
dt.Frame(A=[None, 0, 1, 2, 3]))
def test_shift_wrong_signature3():
msg = r"Argument n in shift\(\) should be an integer"
for n in ["one", 0.0, f.B, range(3), [1, 2, 3]]:
with pytest.raises(TypeError, match=msg):
shift(f.A, n=n)
def test_shift_wrong_signature2():
msg = r"The first argument to shift\(\) must be a column expression " \
r"or a Frame"
for s in [3, 12.5, "hi", dt]:
with pytest.raises(TypeError, match=msg):
shift(s)
def test_shift_by_with_i():
DT = dt.Frame(A=[1, 2, 1, 2, 1, 2, 1, 2], B=range(8))
RES = DT[1:, shift(f.B), by(f.A)]
assert_equals(RES, dt.Frame(A=[1, 1, 1, 2, 2, 2],
B=[None, 2, 4, None, 3, 5]))
def create_data(
X: dt.Frame = None
) -> Union[str, List[str], dt.Frame, List[dt.Frame], np.ndarray,
List[np.ndarray], pd.DataFrame, List[pd.DataFrame], Dict[
str, str], # {data set names : paths}
Dict[str, dt.Frame], # {data set names : dt frames}
Dict[str, np.ndarray], # {data set names : np arrays}
Dict[str, pd.DataFrame], # {data set names : pd frames}
]:
# define date column and forecast horizon
date_col = 'date'
group_by_cols = ["state"]
forecast_len = 7
# state codes lookup table
us_state_codes = dt.Frame(
code=[
'AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC',
'FL', 'GA', 'GU', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY',
'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE',
'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'MP', 'OH', 'OK',
'OR', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT',
'VI', 'VA', 'WA', 'WV', 'WI', 'WY'
],
state=[
'Alabama', 'Alaska', 'American Samoa', 'Arizona', 'Arkansas',
'California', 'Colorado', 'Connecticut', 'Delaware',
'District of Columbia', 'Florida', 'Georgia', 'Guam', 'Hawaii',
'Idaho', 'Illinois', 'Indiana', 'Iowa', 'Kansas', 'Kentucky',
'Louisiana', 'Maine', 'Maryland', 'Massachusetts', 'Michigan',
'Minnesota', 'Mississippi', 'Missouri', 'Montana', 'Nebraska',
'Nevada', 'New Hampshire', 'New Jersey', 'New Mexico',
'New York', 'North Carolina', 'North Dakota',
'Northern Mariana Islands', 'Ohio', 'Oklahoma', 'Oregon',
'Pennsylvania', 'Puerto Rico', 'Rhode Island',
'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah',
'Vermont', 'Virgin Islands', 'Virginia', 'Washington',
'West Virginia', 'Wisconsin', 'Wyoming'
])
us_state_codes.key = "state"
# get states population lookup table
us_states_pop = dt.fread(
"http://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-alldata.csv"
)
us_states_pop.names = {'NAME': 'state', 'POPESTIMATE2019': 'pop'}
us_states_pop = us_states_pop[dt.f.STATE > 0, :]
us_states_pop.key = "state"
# join state codes and population into single lookup table
us_states_pop[:, dt.update(code=dt.g.code), dt.join(us_state_codes)]
us_states_pop.key = "code"
# US Covid Tracking API: https://covidtracking.com/data/api
us_states = dt.fread(
"https://covidtracking.com/api/v1/states/daily.csv")
# remove deprecated fields
deprecated = [
'checkTimeEt', 'commercialScore', 'dateChecked', 'dateModified',
'grade', 'hash', 'hospitalized', 'negativeIncrease',
'negativeRegularScore', 'negativeScore', 'posNeg', 'positiveScore',
'score', 'total'
]
us_states = us_states[:, list(set(us_states.names) - set(deprecated))]
us_states.names = {'state': 'code'}
series_cols = [
"positive", "negative", "hospitalizedCumulative",
"inIcuCumulative", "onVentilatorCumulative", "recovered", "death"
]
aggs = {f"{col}100k": f[col] / (g.pop / 100000) for col in series_cols}
us_states[:,
dt.update(
state=g.state, pop=g.pop, pop100k=g.pop / 10000, **aggs),
join(us_states_pop)]
us_states = us_states[~dt.isna(dt.f.state), :]
# produce lag of 1 unit and add as new feature for each shift column
series_cols.extend([col + "100k" for col in series_cols])
aggs = {f"{col}_yesterday": shift(f[col]) for col in series_cols}
us_states[:, update(**aggs), sort(date_col), by(group_by_cols)]
# update NA lags
aggs = {f"{col}_yesterday": 0 for col in series_cols}
us_states[isna(f[f"{series_cols[0]}_yesterday"]), update(**aggs)]
aggs = {
f"{col}_daily": f[col] - f[f"{col}_yesterday"]
for col in series_cols
}
us_states[:, update(**aggs), sort(date_col), by(group_by_cols)]
for col in series_cols:
del us_states[:, f[f"{col}_yesterday"]]
# validate dataset
if us_states[:, count(),
by(dt.f.state, f.date)][f.count > 1, :].shape[0] > 1:
raise ValueError(
"Found duplicate elements for the same date and state.")
# determine threshold to split train and test based on forecast horizon
dates = dt.unique(us_states[:, date_col])
split_date = dates[-(forecast_len + 1):, :, dt.sort(date_col)][0, 0]
test_date = dates[-1, :, dt.sort(date_col)][0, 0]
# split data to honor forecast horizon in test set
df = us_states[date_col].to_pandas()
train = us_states[df[date_col] <= split_date, :]
test = us_states[df[date_col] > split_date, :]
return {
f"covidtracking_daily_{split_date}_by_us_states_train": train,
f"covidtracking_daily_{test_date}_by_us_states_test": test
}
