import pandas as pd

# import pandas_datareader.data as web
print(pd.__version__)

import numpy as np

# Create a hierarchical index.
np.random.seed(10)

data = pd.Series(
    np.random.randn(9),
    index=[
        ["a", "a", "a", "b", "b", "c", "c", "d", "d"],
        [1, 2, 3, 1, 3, 1, 2, 2, 3],
    ],
)

data

# There are
# - "levels", i.e., the values that are used as Cartesian product.
# - the subset of the indices (in the "labels" sets).
# E.g.,
#   (0, 0) -> (a, 1)
#   (0, 1) -> (a, 2)
# ...

data.index

# Partial indexing.

# Extract the values for the first level.
data["b"]

# Range of one level of an index.
data["b":"c"]

# Select some indices with .loc[list].
data.loc[["b", "d"]]

# Select all the first level and part of the second.
# Indexing works like a numpy array.
data.loc[:, 2]

# .unstack() moves one level of indexing into the columns.
data2 = data.unstack()
data2

# .stack() moves the column index into a hierarchical index.
data2.stack()

frame = pd.DataFrame(np.arange(12).reshape((4, 3)))
frame

frame = pd.DataFrame(
    np.arange(12).reshape((4, 3)),
    index=["a a b b".split(), list(map(int, "1 2 1 2".split()))],
    columns=["Ohio Ohio Colorado".split(), "Green Red Green".split()],
)
frame

frame.index

frame.columns

# The levels have no name.
print(frame.index.names)
print(frame.columns.names)

# Assign names to hierarchical levels.
frame.index.names = ["key1", "key2"]
frame.columns.names = ["state", "color"]

frame

# A multiindex can be created by itself and used.

print(frame.columns)

pd.MultiIndex.from_arrays(
    ["Ohio Ohio Colorado".split(), "Green Red Green".split()],
    names=["state", "color"],
)

# Swap (or reorder) the order of the levels on an axis.
display(frame)
frame.swaplevel("key1", "key2")

# Sort the values in one specific level.
display(frame)
frame2 = frame.sort_index(
    # level=1 is key2
    # level="key2")
    level=1
)

frame2

frame2.sort_index(
    # level=0 is key1
    level=0
)

frame

# frame.sum(level='key2')
frame.sum(level=1)

frame.sum(level="color", axis=1)

# Build from dictionary: each (key, value) becomes a column.
# A new index is added.
frame = pd.DataFrame(
    {
        "a": list(range(7)),
        "b": list(range(7, 0, -1)),
        "c": ["one", "one", "one", "two", "two", "two", "two"],
        "d": [0, 1, 2, 0, 1, 2, 3],
    }
)

frame

# Use two columns as index.
frame2 = frame.set_index(["c", "d"])

frame2

# Move index to columns and reindex with unique integer
frame2.reset_index()

# Two reset index creates two indices.
frame2.reset_index().reset_index()

df1 = pd.DataFrame(
    {"key": ["b", "b", "a", "c", "a", "a", "b"], "data1": list(range(7))}
)
df2 = pd.DataFrame({"key": ["a", "b", "d"], "data2": list(range(3))})

print("df1=")
display(df1)
print("\ndf2=")
display(df2)

# We want to merge on column "key"
# This is a many-to-one join
# - the data in df1['key'] has multiple rows with 'a', and 'b'
# - df2['key'] has fewer
# so there will be a partial Cartesian product.

# Default values
# - Without specifying the columns to join, the common columns are used
# - inner join

# pd.merge(df1, df2, on='key')
pd.merge(df1, df2)

# One can specify explicitly different column names
# E.g.,
#   pd.merge(df3, df4, left_on='lkey', right_on='rkey')

# - Indexes are discarded
# - Common columns not used for joined are renamed _x, _y

# To merge on index
#   pd.merge(df1, df2, left_on='key', right_index=True)

# Merge on index with multi-index
#   pd.merge(df1, df2, left_on=['key1', 'key2'], right_index=True)

# Merge on both indices
#   pd.merge(df1, df2, how='outer', left_index=True, right_index=True)

arr = np.arange(12)
arr

arr = np.arange(12).reshape((3, 4))
arr

# Concatenate horizontally.
np.concatenate([arr, arr], axis=1)

# Concatenate vertically.
np.concatenate([arr, arr], axis=0)

s1 = pd.Series([0, 1], index="a b".split())
print("s1=\n%s" % s1)

s2 = pd.Series([2, 3, 4], index="c d e".split())
print("\ns2=\n%s" % s2)

s3 = pd.Series([5, 6], index="f g".split())
print("\ns3=\n%s" % s3)

# Concat series along axis=0 producing another Series with index and values glued together.
pd.concat([s1, s2, s3], axis=0)

# Concat series along axis=1, does a union of the indices and then merges into a DataFrame
# (sorted outer join).
pd.concat([s1, s2, s3], axis=1, sort=True)

# concat() can also do a inner join.
pd.concat([s1, s2, s3], axis=1, join="inner")

# If you want to identify where the data came from, you can use
# a hierarchical axis.
result = pd.concat([s1, s1, s3], keys="one two three".split())
print(result.index)
result

# Unstack moves a level of hierarchical indexing into columns.
result.unstack()

df1 = pd.DataFrame(
    np.arange(6).reshape(3, 2), index=["a", "b", "c"], columns=["one", "two"]
)

print("df1=")
display(df1)

df2 = pd.DataFrame(
    5 + np.arange(4).reshape(2, 2), index=["a", "c"], columns=["three", "four"]
)

print("\ndf2=")
display(df2)

# Merge using hierarchical columns to track where the data is coming from.
# axis=1 means (columns)
pd.concat([df1, df2], axis=1, keys=["level1", "level2"])

# "ignore_index=True" is used to ignore the indices and create a new
# numeric index.
pd.concat([df1, df2], axis=0, sort=True)

pd.concat([df1, df2], axis=0, ignore_index=True, sort=True)

# Check if there are duplicated indices.
# pd.concat([df1, df2], axis=0, verify_integrity=True)

# np.where() performs a vectorized if-then-else operation.

a = pd.Series(
    [np.nan, 2.5, np.nan, 3.5, 4.5, np.nan], index="f e d c b a".split()
)
print(a)

b = pd.Series(np.arange(len(a), dtype=np.float64), index="f e d c b a".split())
b[-1] = np.nan

display(b)

# We can combine the two series by using the values from a and
# using values from b to fill the nan.

# Compute the null values
np.where(pd.isnull(a), b, a)

a.combine_first(b)

data = pd.DataFrame(
    np.arange(6).reshape((2, 3)),
    index=pd.Index(["Ohio", "Colorado"], name="state"),
    columns=pd.Index(["one", "two", "three"], name="number"),
)

data

# Note that it produces a Series since it has a single column.
result = data.stack()

print(type(result))
print(type(result.index))
result

result.unstack()

# One can unstack a different level than the innermost.
# Level 0 is the outermost.
result.unstack(0)

# unstack() can produce nan if there are missing values.
s1 = pd.Series([0, 1, 2, 3], index=["a", "b", "c", "d"])
s2 = pd.Series([4, 5, 6], index=["c", "d", "e"])
# Concat vertically the series.
data2 = pd.concat([s1, s2], keys=["one", "two"])

print("data2=")
print(type(data2))
display(data2)

print("data2.unstack=")
data2.unstack()

print("data2=")
# gives the original object.
data2.unstack().stack()

# git clone https://github.com/wesm/pydata-book
data = pd.read_csv("/Users/saggese/src/pydata-book/examples/macrodata.csv")

display(data.head())
display(data.tail())

# Combine year and quarter to create a kind of time interval type.
periods = pd.PeriodIndex(year=data.year, quarter=data.quarter, name="date")
print(periods)

data2 = data.copy()
data2.index = periods.to_timestamp("D", "end")

# The data is in "wide" format.
data2.head()

# We need to assign a name to the column otherwise when we "melt"
# the column is not going to have a name.
columns = pd.Index(["realgdp", "infl", "unemp"], name="item")
# Use reindex to extract columns.
data2 = data2.reindex(columns=columns)
print("columns=", columns)

data2.head()

# Put data in "long" format by moving the three columns into one.
ldata = data2.stack().reset_index()
# Rename column to value.
ldata = ldata.rename(columns={0: "value"})
ldata.head()

# pivot() allows to transform from long format to wide format.
pivoted = ldata.pivot("date", "item", "value")
pivoted.head()

# Add another row: now we have an item and 2 values for each.
ldata2 = ldata.copy()
ldata2["value2"] = np.random.randn(len(ldata))

display(ldata2.head())

# Pivoting when there are multiple values creates a hierarchical
# data frame.
pivoted = ldata2.pivot("date", "item")

pivoted.head()

# The inverse operation to "pivot" is "melt".

df = pd.DataFrame(
    {
        "key": ["foo", "bar", "baz"],
        "A": [1, 2, 3],
        "B": [4, 5, 6],
        "C": [7, 8, 9],
    }
)

df

# We need to decide which column is:
# - the variable
# - the value

melted = pd.melt(df, ["key"])

melted