Table of Contents - DATA605 -- Tutorials

0.1 Imports

1 Misc

1.1 Multiply df by series

1.2 Index minute and hour

1.2.1 Merging a column to all dfs into a panel

2 hdf5 format

2.1 Write / read h5 objects

2.2 Read / write objects

2.3 Fixed vs table format.

2.3.1 Fixed format.

2.3.2 Table format

2.4 Table format

2.4.1 Hierarchical keys

2.4.2 Querying

Imports¶


import numpy as np
import pandas as pd

%matplotlib inline

import notebook_utils

notebook_utils.notebook_config()

from notebook_utils import display_df, delete_file_name

numpy= 1.12.1
pandas= 0.19.2
matplotlib= 2.0.2

Misc¶

Multiply df by series¶

index = pd.date_range("2010-01-01", "2010-01-10")

np.random.seed(42)

df = pd.DataFrame(np.random.rand(len(index), 2), index=index, columns=["a", "b"])
srs = pd.Series(np.random.rand(len(index)), index=index)

display_df(df)
display_df(srs)

2010-01-01    0.611853
2010-01-02    0.139494
2010-01-03    0.292145
2010-01-04    0.366362
2010-01-05    0.456070
2010-01-06    0.785176
2010-01-07    0.199674
2010-01-08    0.514234
2010-01-09    0.592415
2010-01-10    0.046450
Freq: D, dtype: float64

df.multiply(srs, axis=0)

Index minute and hour¶

index = pd.date_range("2010-01-01", "2010-01-02", freq="2H")

np.random.seed(42)

df = pd.DataFrame(np.random.rand(len(index), 2), index=index, columns=["a", "b"])
display_df(df)

print(df.index.hour)

[ 0  2  4  6  8 10 12 14 16 18 20 22  0]

print(df.index.minute)

[0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0]

Merging a column to all dfs into a panel¶

panel = notebook_utils.get_random_panel()

print(panel)

<class 'pandas.core.panel.Panel'>
Dimensions: 3 (items) x 5 (major_axis) x 2 (minor_axis)
Items axis: 1 to 3
Major_axis axis: 2001-01-01 00:00:00 to 2001-01-05 00:00:00
Minor_axis axis: a to b

index_rets = pd.date_range("2001-01-01", "2001-01-7")
columns = ["c"]
rets = notebook_utils.get_random_dataframe(index=index_rets, columns=columns)
print(rets)

                   c
2001-01-01  0.374540
2001-01-02  0.950714
2001-01-03  0.731994
2001-01-04  0.598658
2001-01-05  0.156019
2001-01-06  0.155995
2001-01-07  0.058084

panel_tmp = panel.copy()
panel_tmp.loc[:, :, "c"] = rets["c"]
print(panel_tmp)

# Note that the new column is not merged.

<class 'pandas.core.panel.Panel'>
Dimensions: 3 (items) x 5 (major_axis) x 3 (minor_axis)
Items axis: 1 to 3
Major_axis axis: 2001-01-01 00:00:00 to 2001-01-05 00:00:00
Minor_axis axis: a to c

panel_tmp = panel.copy()
panel_tmp.join(df)

pd.concat([rets, panel_tmp], join="outer")

<class 'pandas.core.panel.Panel'>
Dimensions: 4 (items) x 7 (major_axis) x 3 (minor_axis)
Items axis: 0 to 3
Major_axis axis: 2001-01-01 00:00:00 to 2001-01-07 00:00:00
Minor_axis axis: a to c

hdf5 format¶

reading, saving slicing columns and indices

Write / read h5 objects¶

file_name = "store.h5"

store = pd.HDFStore(file_name)

print(store)

<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
Empty

np.random.seed(1234)

index = pd.date_range("1/1/2000", periods=8)
s = pd.Series(randn(5), index="a b c d e".split())
print("# s=\n", s)

df = pd.DataFrame(randn(8, 3), index=index, columns="A B C".split())
print()
print("# df=\n", df)

wp = pd.Panel(
    rand(2, 5, 4),
    items="Item1 Item2".split(),
    major_axis=pd.date_range("1/1/2000", periods=5),
    minor_axis="A B C D".split(),
)
print()
print("# wp=\n", wp)

# s=
a    0.471435
b   -1.190976
c    1.432707
d   -0.312652
e   -0.720589
dtype: float64

# df=
                   A         B         C
2000-01-01  0.887163  0.859588 -0.636524
2000-01-02  0.015696 -2.242685  1.150036
2000-01-03  0.991946  0.953324 -2.021255
2000-01-04 -0.334077  0.002118  0.405453
2000-01-05  0.289092  1.321158 -1.546906
2000-01-06 -0.202646 -0.655969  0.193421
2000-01-07  0.553439  1.318152 -0.469305
2000-01-08  0.675554 -1.817027 -0.183109

# wp=
<class 'pandas.core.panel.Panel'>
Dimensions: 2 (items) x 5 (major_axis) x 4 (minor_axis)
Items axis: Item1 to Item2
Major_axis axis: 2000-01-01 00:00:00 to 2000-01-05 00:00:00
Minor_axis axis: A to D

store["s"] = s
store["df"] = df
store["wp"] = wp

print(store)

<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
/df            frame        (shape->[8,3])  
/s             series       (shape->[5])    
/wp            wide         (shape->[2,5,4])

store.close()

# Using context manager.
with pd.HDFStore(file_name) as store:
    print(list(store.keys()))

['/df', '/s', '/wp']

delete_file_name(file_name)

Read / write objects¶

file_name = "store_t1.h5"
delete_file_name(file_name)

# Write.
df_tl = pd.DataFrame(dict(A=list(range(5)), B=list(range(5))))
display(df_tl)
df_tl.to_hdf(file_name, "table", append=True)

# Read with filtering.
pd.read_hdf(file_name, "table", where=["index>2"])

df_with_missing = pd.DataFrame(
    {
        "col1": [0, np.nan, 2],
        "col2": [1, np.nan, np.nan],
    }
)

display_df(df_with_missing)

df_with_missing.to_hdf(
    # File.
    file_name,
    # Name of dict key.
    "df_with_missing",
    #
    format="table",
    mode="w",
)

df = pd.read_hdf(file_name, "df_with_missing")
display_df(df)

delete_file_name(file_name)

Fixed vs table format.¶

from timeit import default_timer
import time


def _convert_bytes(nbytes, unit="B"):
    if unit == "B":
        res = nbytes
    elif unit == "MB":
        res = float(nbytes / (1024 * 1024))
    else:
        raise ValueError("Invalid unit='%s'" % unit)
    return res


def pd_size(df, unit="B"):
    # nbytes obj.values.nbytes + obj.index.nbytes + obj.columns.nbytes
    nbytes = df.memory_usage().sum()
    res = _convert_bytes(nbytes, unit=unit)
    return res


def file_size(file_name, unit="B"):
    nbytes = os.stat(file_name).st_size
    res = _convert_bytes(nbytes, unit=unit)
    return res


class timer:
    def __init__(self):
        pass

    def __enter__(self):
        self.start = default_timer()
        return self

    def __exit__(self, *args):
        self.end = default_timer()
        self.elapsed = self.end - self.start
        print("time=%.1f s" % self.elapsed)


with timer() as timer_:
    print("test")
    time.sleep(0.1)


print(timer_)

test
time=0.1 s
<__main__.timer instance at 0x114c16b90>

exps = []

with timer() as timer_:
    idx = pd.date_range("1/1/2010", "1/1/2017", freq="1Min")
    cols = ["bid", "ask"]
    df = pd.DataFrame(
        np.random.randn(len(idx), len(cols)), index=idx, columns=cols
    )
    df["start.dt"] = df.index
    df["end.dt"] = df.index.shift(1)

print(idx[:5])
print("shape=", idx.shape)
display(df.head())

with timer() as timer_:
    mem = pd_size(df, unit="MB")
    print("memory=%.1f MB" % mem)

exps.append(["create_df", timer_.elapsed, mem])

orig_df = df.copy()

time=0.4 s
DatetimeIndex(['2010-01-01 00:00:00', '2010-01-01 00:01:00',
               '2010-01-01 00:02:00', '2010-01-01 00:03:00',
               '2010-01-01 00:04:00'],
              dtype='datetime64[ns]', freq='T')
shape= (3682081,)

memory=140.0 MB
time=0.0 s

Fixed format.¶

fixed stores
- are not appendable once written: one needs to remove and rewrite
- are not queryable: they must be retrieve in their entirety
- do not support data frames with duplicated column names
- very fast writing and slightly faster reading than table stores

file_name = "./test.fixed.h5"
delete_file_name(file_name)

exp = "write_all_data.fixed"
print("#", exp)

# Write all data.
with timer() as timer_:
    df.to_hdf(file_name, "df", format="fixed")

mem = file_size(file_name, unit="MB")
print("size=%.1f MB" % mem)

exps.append([exp, timer_.elapsed, mem])

# write_all_data.fixed
time=2.9 s
size=140.0 MB

# Read all data.

exp = "read_all_data.fixed"
print("#", exp)

with timer() as timer_:
    df = pd.read_hdf(file_name, "df")
    _ = df.head(), df.tail()

mem = pd_size(df, unit="MB")
print("memory=%.1f MB" % mem)

exps.append([exp, timer_.elapsed, mem])

# read_all_data.fixed
time=0.2 s
memory=140.0 MB

Table format¶

file_name = "./test.table.h5"
delete_file_name(file_name)

exp = "write_all_data.table"

# Write all data.
print("# ", exp)
with timer() as timer_:
    df.to_hdf(file_name, "df", format="table")

mem = file_size(file_name, unit="MB")
print("size=%.1f MB" % mem)

exps.append([exp, timer_.elapsed, mem])

#  write_all_data.table
time=9.2 s
size=145.0 MB

# Read all data.
exp = "read_all_data.table"
print("#", exp)

with timer() as timer_:
    df = pd.read_hdf(file_name, "df")
    _ = df.head(), df.tail()

mem = pd_size(df, unit="MB")
print("memory=%.1f MB" % mem)

exps.append([exp, timer_.elapsed, mem])

# read_all_data.table
time=0.5 s
memory=140.0 MB

# Read all with index.
exp = "read_all_data_with_index.table"
print("#", exp)

with timer() as timer_:
    df = pd.read_hdf(file_name, "df", where="index > pd.Timestamp('2010-01-01')")
    _ = df.head(), df.tail()

mem = pd_size(df, unit="MB")
print("memory=%.1f MB" % mem)

exps.append([exp, timer_.elapsed, mem])

# read_all_data_with_index.table
time=2.0 s
memory=140.0 MB

# Read 1/2 data with index.
exp = "read_1/2_data_with_index.table"
print("#", exp)

with timer() as timer_:
    df = pd.read_hdf(file_name, "df", where="index > pd.Timestamp('2013-06-01')")
    _ = df.head(), df.tail()

mem = pd_size(df, unit="MB")
print("memory=%.1f MB" % mem)

exps.append([exp, timer_.elapsed, mem])

# read_1/2_data_with_index.table
time=1.0 s
memory=71.0 MB

exps_df = pd.DataFrame(exps, columns=["exp", "time [s]", "size [MB]"])
display(exps_df)

col = exps_df[["exp", "time [s]"]]
col.set_index("exp", inplace=True)


def print_stats(c1, c2):
    speedup = (float(col.loc[c1]) / col.loc[c2]).values[0]
    tag = "slower"
    if speedup < 1:
        speedup = 1.0 / speedup
        tag = "faster"
    print("%s / %s = %.1fx %s" % (c1, c2, speedup, tag))


print_stats("write_all_data.fixed", "read_all_data.fixed")
print_stats("write_all_data.table", "read_all_data.table")

print()
print_stats("write_all_data.fixed", "write_all_data.table")
print_stats("read_all_data.fixed", "read_all_data.table")

print()
print_stats("read_all_data.table", "read_all_data_with_index.table")

write_all_data.fixed / read_all_data.fixed = 16.6x slower
write_all_data.table / read_all_data.table = 20.3x slower

write_all_data.fixed / write_all_data.table = 3.2x faster
read_all_data.fixed / read_all_data.table = 2.6x faster

read_all_data.table / read_all_data_with_index.table = 4.4x faster

Table format¶

table is shaped like a data frame with rows and columns
one can add, delete and query

file_name = "store.h5"
delete_file_name(file_name)

store = pd.HDFStore(file_name)

df = pd.DataFrame(randn(8, 3), index=index, columns=["A", "B", "C"])

# Append to a df.
df1 = df[0:4]
store.append("df", df1)

df2 = df[4:]
store.append("df", df2)

print(store)
print(store.select("df"))

<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
/df            frame_table  (typ->appendable,nrows->8,ncols->3,indexers->[index])
                   A         B         C
2000-01-01  0.875337 -0.123463 -0.876084
2000-01-02 -0.958135  0.811010  0.981213
2000-01-03 -0.555193 -1.282372 -2.053056
2000-01-04  0.319026  1.703201  0.195315
2000-01-05 -1.579546 -0.078956 -1.230969
2000-01-06 -0.014638 -1.890617  0.788462
2000-01-07 -1.615003  0.188301 -0.217574
2000-01-08 -1.037868 -0.023512  1.060914

Hierarchical keys¶

keys are string that can be path-name like format

store.put("foo/bar/bah", df)
store.append("food/orange", df)
store.append("food/apple", df)

print("store=\n", store)
print("\nstore.keys=", list(store.keys()))

# Remove everything under the hierarchy 'food'.
store.remove("food")

print("\nstore=\n", store)

store=
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
/df                     frame_table  (typ->appendable,nrows->8,ncols->3,indexers->[index])
/foo/bar/bah            frame        (shape->[8,3])                                       
/food/apple             frame_table  (typ->appendable,nrows->8,ncols->3,indexers->[index])
/food/orange            frame_table  (typ->appendable,nrows->8,ncols->3,indexers->[index])

store.keys= ['/df', '/food/apple', '/food/orange', '/foo/bar/bah']

store=
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
/df                     frame_table  (typ->appendable,nrows->8,ncols->3,indexers->[index])
/foo/bar/bah            frame        (shape->[8,3])

Querying¶

file_name = "./test.table.h5"
store = pd.HDFStore(file_name)

print("store=\n", store)

store=
<class 'pandas.io.pytables.HDFStore'>
File path: ./test.table.h5
/df            frame_table  (typ->appendable,nrows->3682081,ncols->4,indexers->[index])

df_tmp = store.select("df", "index > pd.Timestamp('2013-06-01')")

print(df_tmp.shape)
display_df(df_tmp.head())

(1886400, 4)

df_tmp = store.select("df", "index == pd.Timestamp('2013-06-01')")

print(df_tmp.shape)
display_df(df_tmp.head())

(1, 4)

df_tmp = store.select(
    "df", "index > pd.Timestamp('2013-06-01') & columns == ['bid']"
)

print(df_tmp.shape)
display_df(df_tmp.head())

(1886400, 1)

# TODO: Finish this.