<jupyter_start><jupyter_text>Python for Finance **Analyze Big Financial Data**O'Reilly (2014)<NAME>  **Buy the book ** |O'Reilly |Amazon**All book codes & IPYNBs** |http://oreilly.quant-platform.com**The Python Quants GmbH** | http://tpq.io**Contact us** | <EMAIL>  Input-Output Operations<jupyter_code>from pylab import plt
plt.style.use('ggplot')
import matplotlib as mpl
mpl.rcParams['font.family'] = 'serif'<jupyter_output><empty_output><jupyter_text>Basic I/O with Python  Writing Objects to Disk<jupyter_code>path = './data/'
import numpy as np
from random import gauss
a = [gauss(1.5, 2) for i in range(1000000)]
  # generation of normally distributed randoms
import pickle
pkl_file = open(path + 'data.pkl', 'wb')
  # open file for writing
  # Note: existing file might be overwritten
%time pickle.dump(a, pkl_file)
pkl_file
pkl_file.close()
ll $path*
pkl_file = open(path + 'data.pkl', 'rb')  # open file for reading
%time b = pickle.load(pkl_file)
b[:5]
a[:5]
np.allclose(np.array(a), np.array(b))
np.sum(np.array(a) - np.array(b))
pkl_file = open(path + 'data.pkl', 'wb')  # open file for writing
%time pickle.dump(np.array(a), pkl_file)
%time pickle.dump(np.array(a) ** 2, pkl_file)
pkl_file.close()
ll $path*
pkl_file = open(path + 'data.pkl', 'rb')  # open file for reading
x = pickle.load(pkl_file)
x
y = pickle.load(pkl_file)
y
pkl_file.close()

pkl_file = open(path + 'data.pkl', 'wb')  # open file for writing
pickle.dump({'x' : x, 'y' : y}, pkl_file)
pkl_file.close()
pkl_file = open(path + 'data.pkl', 'rb')  # open file for writing
data = pickle.load(pkl_file)
pkl_file.close()
for key in data.keys():
    print(key, data[key][:4])
!rm -f $path*<jupyter_output><empty_output><jupyter_text>Reading and Writing Text Files<jupyter_code>rows = 5000
a = np.random.standard_normal((rows, 5))  # dummy data
a.round(4)
import pandas as pd
t = pd.date_range(start='2014/1/1', periods=rows, freq='H')
    # set of hourly datetime objects
t
csv_file = open(path + 'data.csv', 'w')  # open file for writing
header = 'date,no1,no2,no3,no4,no5\n'
csv_file.write(header)
for t_, (no1, no2, no3, no4, no5) in zip(t, a):
    s = '%s,%f,%f,%f,%f,%f\n' % (t_, no1, no2, no3, no4, no5)
    csv_file.write(s)
csv_file.close()
ll $path*
csv_file = open(path + 'data.csv', 'r')  # open file for reading
for i in range(5):
    print(csv_file.readline(), end='')
csv_file = open(path + 'data.csv', 'r')
content = csv_file.readlines()
for line in content[:5]:
    print(line, end='')
csv_file.close()
!rm -f $path*<jupyter_output><empty_output><jupyter_text>SQL Databases<jupyter_code>import sqlite3 as sq3
query = 'CREATE TABLE numbs (Date date, No1 real, No2 real)'
con = sq3.connect(path + 'numbs.db')
con.execute(query)
con.commit()
import datetime as dt
con.execute('INSERT INTO numbs VALUES(?, ?, ?)',
            (dt.datetime.now(), 0.12, 7.3))
data = np.random.standard_normal((10000, 2)).round(5)
for row in data:
    con.execute('INSERT INTO numbs VALUES(?, ?, ?)',
                (dt.datetime.now(), row[0], row[1]))
con.commit()
con.execute('SELECT * FROM numbs').fetchmany(10)
pointer = con.execute('SELECT * FROM numbs')
for i in range(3):
    print(pointer.fetchone())
con.close()
!rm -f $path*<jupyter_output><empty_output><jupyter_text>Writing and Reading Numpy Arrays<jupyter_code>import numpy as np
dtimes = np.arange('2015-01-01 10:00:00', '2021-12-31 22:00:00',
                  dtype='datetime64[m]')  # minute intervals
len(dtimes)
dty = np.dtype([('Date', 'datetime64[m]'), ('No1', 'f'), ('No2', 'f')])
data = np.zeros(len(dtimes), dtype=dty)
data['Date'] = dtimes
a = np.random.standard_normal((len(dtimes), 2)).round(5)
data['No1'] = a[:, 0]
data['No2'] = a[:, 1]
%time np.save(path + 'array', data)  # suffix .npy is added
ll $path*
%time np.load(path + 'array.npy')
data = np.random.standard_normal((10000, 6000))
%time np.save(path + 'array', data) 
ll $path*
%time np.load(path + 'array.npy')
data = 0.0
!rm -f $path*<jupyter_output><empty_output><jupyter_text>I/O with pandas<jupyter_code>import numpy as np
import pandas as pd
data = np.random.standard_normal((1000000, 5)).round(5)
        # sample data set
filename = path + 'numbs'<jupyter_output><empty_output><jupyter_text>SQL Database<jupyter_code>import sqlite3 as sq3
query = 'CREATE TABLE numbers (No1 real, No2 real,\
        No3 real, No4 real, No5 real)'
con = sq3.Connection(filename + '.db')
con.execute(query)
%%time
con.executemany('INSERT INTO numbers VALUES (?, ?, ?, ?, ?)', data)
con.commit()
ll $path*
%%time
temp = con.execute('SELECT * FROM numbers').fetchall()
print(temp[:2])
temp = 0.0
%%time
query = 'SELECT * FROM numbers WHERE No1 > 0 AND No2 < 0'
res = np.array(con.execute(query).fetchall()).round(3)
res = res[::100]  # every 100th result
import matplotlib.pyplot as plt
%matplotlib inline
plt.plot(res[:, 0], res[:, 1], 'ro')
plt.grid(True); plt.xlim(-0.5, 4.5); plt.ylim(-4.5, 0.5)
# tag: scatter_query
# title: Plot of the query result
# size: 60<jupyter_output><empty_output><jupyter_text>From SQL to pandas<jupyter_code>import pandas.io.sql as pds
%time data = pds.read_sql('SELECT * FROM numbers', con)
data.head()
%time data[(data['No1'] > 0) & (data['No2'] < 0)].head()
%%time
res = data[['No1', 'No2']][((data['No1'] > 0.5) | (data['No1'] < -0.5))
                     & ((data['No2'] < -1) | (data['No2'] > 1))]
plt.plot(res.No1, res.No2, 'ro')
plt.grid(True); plt.axis('tight')
# tag: data_scatter_1
# title: Scatter plot of complex query results
# size: 55
h5s = pd.HDFStore(filename + '.h5s', 'w')
%time h5s['data'] = data
h5s
h5s.close()
%%time
h5s = pd.HDFStore(filename + '.h5s', 'r')
temp = h5s['data']
h5s.close()
np.allclose(np.array(temp), np.array(data))
temp = 0.0
ll $path*<jupyter_output>-rw-r--r--@ 1 yves  staff  52633600 Jul 11 19:24 ./data/numbs.db
-rw-r--r--@ 1 yves  staff  48007192 Jul 11 19:24 ./data/numbs.h5s<jupyter_text>Data as CSV File<jupyter_code>%time data.to_csv(filename + '.csv')
ls data/
%%time
pd.read_csv(filename + '.csv')[['No1', 'No2',
                                'No3', 'No4']].hist(bins=20)
# tag: data_hist_3
# title: Histogram of 4 data sets
# size: 60<jupyter_output>CPU times: user 1.25 s, sys: 151 ms, total: 1.41 s
Wall time: 1.41 s<jupyter_text>Data as Excel File<jupyter_code>%time data[:100000].to_excel(filename + '.xlsx')
%time pd.read_excel(filename + '.xlsx', 'Sheet1').cumsum().plot()
# tag: data_paths
# title: Paths of random data from Excel file
# size: 60
ll $path*
rm -f $path*<jupyter_output><empty_output><jupyter_text>Fast I/O with PyTables<jupyter_code>import numpy as np
import tables as tb
import datetime as dt
import matplotlib.pyplot as plt
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Working with Tables<jupyter_code>filename = path + 'tab.h5'
h5 = tb.open_file(filename, 'w') 
rows = 2000000
row_des = {
    'Date': tb.StringCol(26, pos=1),
    'No1': tb.IntCol(pos=2),
    'No2': tb.IntCol(pos=3),
    'No3': tb.Float64Col(pos=4),
    'No4': tb.Float64Col(pos=5)
    }
filters = tb.Filters(complevel=0)  # no compression
tab = h5.create_table('/', 'ints_floats', row_des,
                      title='Integers and Floats',
                      expectedrows=rows, filters=filters)
tab
pointer = tab.row
ran_int = np.random.randint(0, 10000, size=(rows, 2))
ran_flo = np.random.standard_normal((rows, 2)).round(5)
%%time
for i in range(rows):
    pointer['Date'] = dt.datetime.now()
    pointer['No1'] = ran_int[i, 0]
    pointer['No2'] = ran_int[i, 1] 
    pointer['No3'] = ran_flo[i, 0]
    pointer['No4'] = ran_flo[i, 1] 
    pointer.append()
      # this appends the data and
      # moves the pointer one row forward
tab.flush()
tab
ll $path*
dty = np.dtype([('Date', 'S26'), ('No1', '<i4'), ('No2', '<i4'),
                                 ('No3', '<f8'), ('No4', '<f8')])
sarray = np.zeros(len(ran_int), dtype=dty)
sarray
%%time
sarray['Date'] = dt.datetime.now()
sarray['No1'] = ran_int[:, 0]
sarray['No2'] = ran_int[:, 1]
sarray['No3'] = ran_flo[:, 0]
sarray['No4'] = ran_flo[:, 1]
%%time
h5.create_table('/', 'ints_floats_from_array', sarray,
                      title='Integers and Floats',
                      expectedrows=rows, filters=filters)
h5
h5.remove_node('/', 'ints_floats_from_array')
tab[:3]
tab[:4]['No4']
%time np.sum(tab[:]['No3'])
%time np.sum(np.sqrt(tab[:]['No1']))
%%time
plt.hist(tab[:]['No3'], bins=30)
plt.grid(True)
print(len(tab[:]['No3']))
# tag: data_hist
# title: Histogram of data
# size: 60
%%time
res = np.array([(row['No3'], row['No4']) for row in
        tab.where('((No3 < -0.5) | (No3 > 0.5)) \
                 & ((No4 < -1) | (No4 > 1))')])[::100]
plt.plot(res.T[0], res.T[1], 'ro')
plt.grid(True)
# tag: scatter_data
# title: Scatter plot of query result
# size: 70
%%time
values = tab.cols.No3[:]
print("Max %18.3f" % values.max())
print("Ave %18.3f" % values.mean())
print("Min %18.3f" % values.min())
print("Std %18.3f" % values.std())
%%time
results = [(row['No1'], row['No2']) for row in
           tab.where('((No1 > 9800) | (No1 < 200)) \
                    & ((No2 > 4500) & (No2 < 5500))')]
for res in results[:4]:
    print(res)
%%time
results = [(row['No1'], row['No2']) for row in
           tab.where('(No1 == 1234) & (No2 > 9776)')]
for res in results:
    print(res)<jupyter_output>(1234, 9994)
(1234, 9997)
(1234, 9921)
(1234, 9902)
(1234, 9975)
(1234, 9970)
CPU times: user 28 ms, sys: 44.7 ms, total: 72.6 ms
Wall time: 51.7 ms<jupyter_text>Working with Compressed Tables<jupyter_code>filename = path + 'tab.h5c'
h5c = tb.open_file(filename, 'w') 
filters = tb.Filters(complevel=4, complib='blosc')
tabc = h5c.create_table('/', 'ints_floats', sarray,
                        title='Integers and Floats',
                      expectedrows=rows, filters=filters)
%%time
res = np.array([(row['No3'], row['No4']) for row in
             tabc.where('((No3 < -0.5) | (No3 > 0.5)) \
                       & ((No4 < -1) | (No4 > 1))')])[::100]
%time arr_non = tab.read()
%time arr_com = tabc.read()
ll $path*
h5c.close()<jupyter_output><empty_output><jupyter_text>Working with Arrays<jupyter_code>%%time
arr_int = h5.create_array('/', 'integers', ran_int)
arr_flo = h5.create_array('/', 'floats', ran_flo)
h5
ll $path*
h5.close()
!rm -f $path*<jupyter_output><empty_output><jupyter_text>Out-of-Memory Computations<jupyter_code>filename = path + 'array.h5'
h5 = tb.open_file(filename, 'w') 
n = 100
ear = h5.create_earray(h5.root, 'ear',
                      atom=tb.Float64Atom(),
                      shape=(0, n))
%%time
rand = np.random.standard_normal((n, n))
for i in range(750):
    ear.append(rand)
ear.flush()
ear
ear.size_on_disk
out = h5.create_earray(h5.root, 'out',
                      atom=tb.Float64Atom(),
                      shape=(0, n))
expr = tb.Expr('3 * sin(ear) + sqrt(abs(ear))')
  # the numerical expression as a string object
expr.set_output(out, append_mode=True)
  # target to store results is disk-based array
%time expr.eval()
  # evaluation of the numerical expression
  # and storage of results in disk-based array
out[0, :10]
%time imarray = ear.read()
  # read whole array into memory
import numexpr as ne
expr = '3 * sin(imarray) + sqrt(abs(imarray))'
ne.set_num_threads(16)
%time ne.evaluate(expr)[0, :10]
h5.close()
!rm -f $path*<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1857991312826877, 'fraction_numerical': 0.04520875808882191, 'mean_word_length': 4.098056936285585, 'pattern_counts': {'":': 0, '<': 74, '<?xml version=': 0, '>': 71, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '2598', 'license': 'CNRI-Python', 'n_tokens_mistral': 4747, 'n_tokens_neox': 4425, 'n_words': 1055, 'path': 'ipython3/07_Input_Output.ipynb', 'repo_name': 'libaiw/PythonFinTechST', 'size': '11291'}