import datetime

import numpy as np
import pandas as pd

%load_ext autoreload
%autoreload 2

%matplotlib inline

import notebook_utils

notebook_utils.notebook_config()
# matplotlib.rcParams["figure.figsize"] = (15, 6)

from notebook_utils import display_df

from IPython.display import display

np.random.seed(10)
df = pd.DataFrame(
    {
        "A": ["foo", "bar", "foo", "bar", "foo", "bar", "foo", "foo"],
        "B": ["one", "one", "two", "three", "two", "two", "one", "three"],
        "C": np.random.randn(8),
        "D": np.random.randn(8),
    }
)
df

grouped = df.groupby("A")

print("keys=", list(grouped.groups.keys()))

display_df(grouped.get_group("foo"))

idx = [1, 2, 3, 1, 2, 3]
s = pd.Series([1, 2, 3, 10, 20, 30], idx)
s

# groupby() by index values.
grouped = s.groupby(level=0)

print("keys=", list(grouped.groups.keys()))

print("first=\n", grouped.first())
print("last=\n", grouped.last())

df2 = pd.DataFrame({"X": ["B", "B", "A", "A"], "Y": [1, 2, 3, 4]})
df2

# Note that groupby() sorts the group keys.
df2.groupby(["X"]).sum()

# Observations are kept sorted within each group according to the original df.
df2.groupby("X").get_group("A")

# Get a single group.
df2.groupby("X").get_group("B")

# Get all groups and mapping.
df2.groupby("X").groups

# Group on two columns.
df2.groupby(["X", "Y"]).groups

# Iterate over groups.
grouped = df2.groupby("X")

for name, group in grouped:
    print("\nname=", name)
    print(group)

df_buyer = pd.DataFrame(
    {
        "Branch": "A A A A A A A B".split(),
        "Buyer": "Carl Mark Carl Carl Joe Joe Joe Carl".split(),
        "Quantity": [1, 3, 5, 1, 8, 1, 9, 3],
        "Date": [
            datetime.datetime(2013, 1, 1, 13, 0),
            datetime.datetime(2013, 1, 1, 13, 5),
            datetime.datetime(2013, 10, 1, 20, 0),
            datetime.datetime(2013, 10, 2, 10, 0),
            datetime.datetime(2013, 10, 1, 20, 0),
            datetime.datetime(2013, 10, 2, 10, 0),
            datetime.datetime(2013, 12, 2, 12, 0),
            datetime.datetime(2013, 12, 2, 14, 0),
        ],
    }
)

df_buyer

# Group, transform, concat.
df_buyer.groupby("Buyer").head(1)

# Group, transform, concat.
df_buyer.groupby("Buyer").head(2)

df_buyer.groupby("Buyer").nth(2)

_ = df_buyer.groupby("Buyer").boxplot()

# Group and then aggregate groups with generic function.
display_df(grouped.aggregate(np.sum))
# print grouped.agg(np.sum)

# Use pandas function.
grouped.sum()

df.groupby(["A", "B"]).sum()

# Group-by, accumulate, and then convert multi-index into a regular column (opposite of set_index()).
df.groupby(["A", "B"]).sum().reset_index()

# Since size() generates a single columns, the result is a multi-index series, instead of a df.
df.groupby(["A", "B"]).size()

# Apply multiple functions to each group, producing hierarchical columns.
df.groupby("A").agg([np.sum, np.mean, np.std])

# Use names from dict for columns.
# It doesn't seem to work.
if False:
    df.groupby("A").agg({"sum_": np.sum, "mean_": np.mean, "std_": np.std})

np.random.seed(5)
index = pd.date_range("10/1/1999", periods=1100)
ts = pd.Series(np.random.normal(0.05, 2, 1100), index)
ts.cumsum().plot()

# Iterate by year.
sorted(ts.groupby(lambda x: x.year).groups.keys())

# This is an object computing a rolling window on the data.
rolling = ts.rolling(window=100, min_periods=100)
print(type(rolling))

# Compute a rolling window and apply mean: i.e., a rolling mean.
ts2 = rolling.mean()
ts2.plot()

# Group by year.
key = lambda x: x.year
grouped = ts2.groupby(key)

# Transform each group by zscoring within the group.
zscore = lambda x: (x - x.mean()) / x.std()
transformed = grouped.transform(zscore)
transformed.plot()

df

if False:

    def print_(x):
        print("\n", x)

    df.groupby("A").transform(print_)

np.random.seed(0)
df = pd.DataFrame(np.random.rand(100, 1))
f = lambda df: df.mean()
# res = pd.rolling_apply(df, 3, f, min_periods=3)
res = df.rolling(min_periods=3, window=3).apply(f)
res.head(10)

df = pd.DataFrame(np.random.rand(100, 1))
# f = lambda df: [df.mean(), df.std()]
f = lambda df: df.mean()
# res = pd.rolling_apply(df, 3, f, min_periods=3)
res = df.rolling(min_periods=3, window=3).apply(f)
res.head(10)

# It doesn't seem to work for

sf = pd.Series([1, 1, 2, 3, 3, 3])
print(sf.groupby(sf).groups)

# Filter groupby by picking only groups with a sum larger.
sf.groupby(sf).filter(lambda x: x.sum() > 2)

np.random.seed(10)
df = pd.DataFrame(
    {
        "A": ["foo", "bar", "foo", "bar", "foo", "bar", "foo", "foo"],
        "B": ["one", "one", "two", "three", "two", "two", "one", "three"],
        "C": np.random.randn(8),
        "D": np.random.randn(8),
    }
)
df

df

# Apply calls describe on each group and concat the results.
df.groupby("A").apply(lambda x: x.describe())

df_buyer = pd.DataFrame(
    {
        "Branch": "A A A A A A A B".split(),
        "Buyer": "Carl Mark Carl Carl Joe Joe Joe Carl".split(),
        "Quantity": [1, 3, 5, 1, 8, 1, 9, 3],
        "Date": [
            datetime.datetime(2013, 1, 1, 13, 0),
            datetime.datetime(2013, 1, 1, 13, 5),
            datetime.datetime(2013, 10, 1, 20, 0),
            datetime.datetime(2013, 10, 2, 10, 0),
            datetime.datetime(2013, 10, 1, 20, 0),
            datetime.datetime(2013, 10, 2, 10, 0),
            datetime.datetime(2013, 12, 2, 12, 0),
            datetime.datetime(2013, 12, 2, 14, 0),
        ],
    }
)

df_buyer

# This is like resampling.
grouper = pd.Grouper(freq="1M", key="Date")

df_tmp = df_buyer.groupby(grouper).sum().dropna()
df_tmp

df_tmp2 = df_tmp.copy()
df_tmp2.index += pd.offsets.MonthEnd(1)
df_tmp2

# Build a df with 100 seconds worth of (random) data.
np.random.seed(1000)
rng = pd.date_range("1/1/2012", periods=100, freq="S")
ts0 = pd.Series(np.random.randint(0, 500, len(rng)), index=rng)
print(ts0.head())
print(ts0.tail())

# Summarize in 1 minute intervals.
ts0.resample("1Min").sum()

ts0.resample("1Min").max()

# 'label' and 'loffset' can be used to manipulate the labels used to mark the intervals.
print(ts0.resample("1Min", label="right").mean())

print(ts0.resample("1Min", label="left").mean())

print(ts0.resample("1Min", label="left", loffset="1s").mean())

# Resample without summarizing.

# Build a df with 100 seconds worth of data.
np.random.seed(1000)
rng = pd.date_range("1/1/2012", periods=100, freq="2Min")
ts0_1 = pd.Series(np.arange(0, len(rng)) ** 2, index=rng)
ts0_1.plot(style=".-", lw=3)

# pandas 0.14 uses this syntax.
ts1_1 = ts0_1.resample("1H", how="last", closed="right", label="right")
ts1_1.plot(kind="line", style="o--")

# Get a df with 2 seconds worth of data.
ts[:2]

# 'closed' can be used to specify which end of the interval is closed.

# By default intervals are [a, b)
print(ts.resample("1Min").mean().head(10))
print(ts.resample("1Min", closed="left").mean().head(10))

# closed=right => (a, b]
print(ts.resample("1Min", closed="right").mean().head(10))

# Resample from seconds to 250 millisecs.
# asfreq() is used to expand the resampler.
ts[:2].resample("250L").asfreq().head(10)

ts[:2].resample("250L").ffill().head(10)

ts[:2].resample("250L").ffill(limit=2).head(10)

rng = pd.date_range("2014-1-1", periods=100, freq="D")
rng += pd.Timedelta("1s")
print(rng[:5])

ts3 = pd.Series(list(range(100)), index=rng)
ts3.head()

# Upsample the daily ts every 3 mins, getting a very sparse time series.
ts4 = ts3.resample("3T").sum()

ts4.index

# Get a data frame with 60 hours.
idx = pd.date_range("2012-01-01-17", freq="H", periods=60)
ts5 = pd.Series(data=[1] * 60, index=idx)
print(ts5.head())
print(ts5.tail())

ts5.resample("D").sum()

# By specifying a period in hours and setting the initial hour, one can shift the sampling intervals.
ts5.resample("24H", base=17).sum()

# Resample daily starting from 7:45am (= 60 * 24 + 45 = 465).
ts5.resample("1440Min", base=465).sum()

# Df with 1000 seconds worth of data and multiple columns.
np.random.seed(1000)
df = pd.DataFrame(
    np.random.randn(1000, 3),
    index=pd.date_range("1/1/2012", freq="S", periods=1000),
    columns="A B C".split(" "),
)
print(df.head(2))
print("...")
print(df.tail(2))

# Apply the same function to each column.
df.resample("3T").mean()

# Specify custom function.
df.resample("3T").agg(lambda x: x.mean())

# Apply function to only certain columns.
# NOTE: we tell the resampler which columns to resample instead of selecting columns first.
df.resample("3T")[["A", "B"]].mean()

# Apply multiple functions to a single column.
df.resample("3T")["A"].agg([np.sum, np.mean, np.std])

# Assign names to columns.
df.resample("3T")["A"].agg({"res1": np.sum, "res2": np.mean})

# Apply multiple functions to each column, returning a hierarchical index.
df.resample("3T").agg([np.sum, np.mean])

# Specify different multiple functions for each column, returning a hierarchical index.
df.resample("3T").agg({"A": [np.sum, np.mean], "B": [np.mean]})

np.random.seed(1000)
idx = pd.date_range("1/1/2010", periods=10, freq=pd.offsets.BDay())
ts6 = pd.Series(np.random.rand(idx.shape[0]), index=idx)
ts6

# Resample using a certain frequency: from business days to calendar days.
ts6.asfreq(pd.offsets.Day())

# groupby using time intervals and compute a function for each group.
# Note that we keep all dates.
grouper = pd.TimeGrouper("1W")

ts6.groupby(grouper).transform(lambda x: x.mean())

# Data in "stacked" or "record" format.
# For each date and each variable there is an observation.
idx = pd.date_range(start="20010101", end="20010103").tolist() * 4
np.random.seed(1000)
df = pd.DataFrame(np.random.rand(len(idx), 1), columns="value".split())
df.insert(0, "date", idx)
df.insert(1, "variable", ["A"] * 3 + ["B"] * 3 + ["C"] * 3 + ["D"] * 3)
df.index.name = "date"
print(df)

# Assume we are interested in observations for variable "A".
df[df["variable"] == "A"]

# The index is date, the values in "variable" become columns, and the values in "value"
# become the values in the df.
df.pivot(index="date", columns="variable", values="value")

# If there are more than one value (i.e., more type of observations for a variable)
# one gets multi-index columns.
df["value2"] = df["value"] * 2.0
print(df)

df.pivot(index="date", columns="variable")

# zip(list1, list2) creates pairs iterating on the lists.
# In other words it iterates on the columns of the stacked versions of the lists,
# effectively transposing.
list(zip("a b c d".split(), "1 2 3 4".split()))

list(zip("a b c d".split(), "1 2 3 4".split(), "x y z w".split()))

tuples = [("a", "1"), ("b", "2"), ("c", "3"), ("d", "4")]
print(list(zip(*tuples))[0])
print(list(zip(*tuples))[1])

tuples = list(
    zip(
        *[
            ["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"],
            ["one", "two", "one", "two", "one", "two", "one", "two"],
        ]
    )
)
print(tuples)
index = pd.MultiIndex.from_tuples(tuples, names=["first", "second"])

np.random.seed(1000)
df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=["A", "B"])

print(df)

df2 = df[:4]
print(df2)

# stack() compresses a level in the df columns, producing a series or a df.
# This is opposite of pivot() since it converts the df into records.

# In this case, columns A and B become values in a single column.
# Since now observations are now a single value, the return value is a series
# with a multi-index.
stacked = df2.stack()

print(type(stacked))
print(type(stacked.index))
print(stacked)

# Unstack is equivalent to pivot, but it works on the last level.
stacked.unstack()

#
stacked.unstack(1)

stacked.unstack(0)

cheese = pd.DataFrame(
    {
        "first": ["John", "Mary"],
        "last": ["Doe", "Bo"],
        "height": [5.5, 6.0],
        "weight": [130, 150],
    }
)
cheese

pd.melt(cheese, id_vars=["first", "last"])

df1 = pd.DataFrame(
    {
        "A": ["A0", "A1", "A2", "A3"],
        "B": ["B0", "B1", "B2", "B3"],
        "C": ["C0", "C1", "C2", "C3"],
        "D": ["D0", "D1", "D2", "D3"],
    },
    index=[0, 1, 2, 3],
)


df2 = pd.DataFrame(
    {
        "A": ["A4", "A5", "A6", "A7"],
        "B": ["B4", "B5", "B6", "B7"],
        "C": ["C4", "C5", "C6", "C7"],
        "D": ["D4", "D5", "D6", "D7"],
    },
    index=[4, 5, 6, 7],
)


df3 = pd.DataFrame(
    {
        "A": ["A8", "A9", "A10", "A11"],
        "B": ["B8", "B9", "B10", "B11"],
        "C": ["C8", "C9", "C10", "C11"],
        "D": ["D8", "D9", "D10", "D11"],
    },
    index=[8, 9, 10, 11],
)

result = pd.concat([df1, df2, df3])
result

# To track the origin of each record, one can use key to create a multiindex.
result = pd.concat([df1, df2, df3], keys="1 2 3".split())
result

result.loc["1"]

# When gluing together dfs, one can decide how to handle the other axes,
# e.g., union (outer join), intersection (inner join) or picking index of
# a specific df (right, left join).

# Index has some intersectio, but the columns are not exactly the same.
df4 = pd.DataFrame(
    {
        "B": ["B2", "B3", "B6", "B7"],
        "D": ["D2", "D3", "D6", "D7"],
        "F": ["F2", "F3", "F6", "F7"],
    },
    index=[2, 3, 6, 7],
)

print(df1)
print(df4)

pd.concat([df1, df4], axis=1, join="outer")

pd.concat([df1, df4], axis=1, join="inner")

pd.concat([df1, df4], axis=1, join_axes=[df1.index])

print(df1)
print(df2)

# The indices are disjoint and the columns are the same.
df1.append(df2)

df1.append(df4)

# TODO

df = pd.DataFrame(np.random.rand(5, 2))
display(df)

df.index.name = ""
df.reset_index(inplace=True)
df.insert(0, "datetime", pd.to_datetime("2001-01-1"))
df.set_index(["datetime", ""], inplace=True)

display(df)

isinstance(np.mean, object)

import helpers.unit_test as hut

df1 = hut.get_random_df(
    2, start="2010-01-01 09:00", end="2010-01-03 09:00", freq="30T"
)

df2 = hut.get_random_df(
    3, start="2010-01-02 09:05", end="2010-01-03 00:00", freq="30T"
)

display(df1.head())

display(df2.head())