Data Overview, Slicing and Selection¶

import pandas as pd

stock_data = pd.read_csv("pair_ETF_0302.csv")

When you read from raw data file, you get a Dataframe.

type(stock_data)

pandas.core.frame.DataFrame

Data Overview¶

Get an overview of the data that you are working on. In this stage, you will focus on getting a board idea of data size, data type and information.

stock_data.head() #This is a function call, remember the ()

stock_data.tail()

You can access columns the below methods

stock_data.pairs.head()

0    ('NYT', 'BANC')
1     ('EEA', 'ESS')
2     ('BXP', 'LFC')
3    ('PCF', 'ANIK')
4    ('NDSN', 'POL')
Name: pairs, dtype: object

stock_data["pairs"].head()

0    ('NYT', 'BANC')
1     ('EEA', 'ESS')
2     ('BXP', 'LFC')
3    ('PCF', 'ANIK')
4    ('NDSN', 'POL')
Name: pairs, dtype: object

You will get a data type of Series. You can view Series as a single unit of Dataframe.

type(stock_data["pairs"])

pandas.core.series.Series

You can access specific cell like this.

stock_data["pairs"][0]

"('NYT', 'BANC')"

type(stock_data["pairs"][0])

str

Slicing and Selection¶

You can access a certain portion of data. Please remind that index starts from 0.

stock_data[:].head() #Showing all slices

stock_data[2:].head()  #start from the 3rd row

stock_data[:3] #get until the 4th row

stock_data["pairs"].head()  # Usually I use column name to access column

0    ('NYT', 'BANC')
1     ('EEA', 'ESS')
2     ('BXP', 'LFC')
3    ('PCF', 'ANIK')
4    ('NDSN', 'POL')
Name: pairs, dtype: object

More Slicing and Selection¶

This doesn’t work because it’s accessing the index, and the Dataframe doesn’t have the index named 0

stock_data[0]

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-15-e3654389130b> in <module>()
----> 1 stock_data[0]

C:\ProgramData\Anaconda2\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
   2683             return self._getitem_multilevel(key)
   2684         else:
-> 2685             return self._getitem_column(key)
   2686 
   2687     def _getitem_column(self, key):

C:\ProgramData\Anaconda2\lib\site-packages\pandas\core\frame.py in _getitem_column(self, key)
   2690         # get column
   2691         if self.columns.is_unique:
-> 2692             return self._get_item_cache(key)
   2693 
   2694         # duplicate columns & possible reduce dimensionality

C:\ProgramData\Anaconda2\lib\site-packages\pandas\core\generic.py in _get_item_cache(self, item)
   2484         res = cache.get(item)
   2485         if res is None:
-> 2486             values = self._data.get(item)
   2487             res = self._box_item_values(item, values)
   2488             cache[item] = res

C:\ProgramData\Anaconda2\lib\site-packages\pandas\core\internals.py in get(self, item, fastpath)
   4113 
   4114             if not isna(item):
-> 4115                 loc = self.items.get_loc(item)
   4116             else:
   4117                 indexer = np.arange(len(self.items))[isna(self.items)]

C:\ProgramData\Anaconda2\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   3063                 return self._engine.get_loc(key)
   3064             except KeyError:
-> 3065                 return self._engine.get_loc(self._maybe_cast_indexer(key))
   3066 
   3067         indexer = self.get_indexer([key], method=method, tolerance=tolerance)

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 0

stock_data.iloc[0] #But you can access nth row using .iloc[n]

pairs           ('NYT', 'BANC')
ave_return              -1683.6
total_return           -15.1676
volatility              139.661
sharp_ratio           -0.108603
Name: 0, dtype: object

type(stock_data.iloc[0]) #it returns a Series

pandas.core.series.Series

stock_data.iloc[0:2] #You can also feed a portion to .iloc and it will return Dataframe

stock_data.loc[[1,2]] #.loc can also be feeded a list of index

Using .loc[], you can conduct more complex selection

stock_data.loc[:,"pairs"].head() # .loc can also be used to access columns

0    ('NYT', 'BANC')
1     ('EEA', 'ESS')
2     ('BXP', 'LFC')
3    ('PCF', 'ANIK')
4    ('NDSN', 'POL')
Name: pairs, dtype: object

Remember to feed a list when you want to select multiple columns or rows

stock_data.loc[:,["pairs","total_return"]].head()

stock_data.loc[stock_data["ave_return"]>0].head()

.loc is for index access, which means you should feed index or index generator into .iloc[]

stock_data.loc[[True,True,False]] #Boolean can be feeded into .loc, empty means false

To use more complex selection, you can use lambda functions

stock_data.loc[lambda stock_data:stock_data["total_return"]>200000]

One Last Thing¶

As a good practice, you should always use .loc to set value. Because if you use direct access, like stock_data[“pairs”], Python will return a copy of the Dataframe, which is not changeable.

stock_data.loc[0,"ave_return"] = 0

stock_data.head()

Series & Numpy Array¶

Pandas’s Series is internally Numpy Array. In the following demonstration, you will see if you take .values of the Series, you get an array.

stock_data.head()["pairs"].values

array(["('NYT', 'BANC')", "('EEA', 'ESS')", "('BXP', 'LFC')",
       "('PCF', 'ANIK')", "('NDSN', 'POL')"], dtype=object)

import datetime

Dictionary

Dictionary is used when there exists a mapping relationship, for example, in stock market data, stock prices are linked to a specific date.

stock = {"Header":["Open","High","Low","Close"],"12/12/2015":[32.03,50,40,32]}

stock["12/12/2015"]

[32.03, 50, 40, 32]

Dictionary provides some useful methods

list(stock.iterkeys())  #key iterator, giving you a list

['Header', '12/12/2015']

list(stock.iteritems()) #iterm iterator, giving you tuples, representing relations

[('Header', ['Open', 'High', 'Low', 'Close']),
 ('12/12/2015', [32.03, 50, 40, 32])]

Dictionary can also be feeded in the constructor of pandas dataframe. We will talk about this in my later posts.

Tuple

Anything you want to be considered as a whole should use turple becuase it’s immutable. Turple often used in feeding a set of arguments into function caller.

x=12
y=23
z=23.6

point = (x,y,z) # a coordinate can use turple to replesent because the
                # position of each element matters

Some people also argues that you can compare tuple to strcut in C/C++ since tuple usually holds heterogeneous collections.

As I mentioned, tuple also used to represent relations in discrete data structure. I can use you an example using dictionary and tuple

# for example, we have y = x^2
f = {1:1,2:4,3:9}
f.items()  # the items method can turn dictionary into a tuple

[(1, 1), (2, 4), (3, 9)]

List

List is like array in other programming language. You should use list in situations that uses array. In python, list provides you more methods to perform comprehensive analysis.

List as stack operation

l = []
l.append(1)
l.append(2)
l.append(3)

l

[1, 2, 3]

l.pop()  # First in first out

1

l

[1, 2]

l.append(4)
l

[1, 2, 4]

l.pop()

4

l.pop()

2

l

[1]

List sorting

l = [2,3,9,4]
l.sort()

l

[2, 3, 4, 9]

List Reversing

l.reverse()

l

[9, 4, 3, 2]

List Extend Method

l.extend([1]) # need to feed a list, and will insert the element to a proper place

l

[9, 4, 3, 2, 1]

Other List Methods

l.remove(1) #remove elements
l

[9, 4, 3, 2]

l.index(4) # return the position of an element

1

l.insert(0,2) # insert 2 into index 0
l

[2, 9, 4, 3, 2]

We will talk about data structure usage more in-depth later in my blog when we go further into analysis.

A set in python is the same concept of the set in Discret Math. A set contains unique unorder elements.

In python, a set is definded by putting “{}”

s = {1,2,3}
s

{1, 2, 3}

s = {"1231",(1,2,3),2} # A set can contain different data type

A set is nonordered, so it can not access by indexing

s[0]

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-7-4e98c4f87897> in <module>()
----> 1 s[0]

TypeError: 'set' object does not support indexing

However, it can be access by iterating

for ele in s:
    print ele

2
1231
(1, 2, 3)

If feeding a set with same elements, it will only save that element once, for example,

s_1 = {1,1,(1,2),(1,2)}
s_1

{1, (1, 2)}

You can turn other data strcture in python into a set by using set() function

l = [1,2,3,3,2]
t = (2,2,2,1)
set(l)

{1, 2, 3}

set(t)

{1, 2}

First of all, a tuple is defined by putting “()”, and using “,” to seperate values. Turple is sequencial, meaning the ordering matters.

t=(1,2,3)
t

(1, 2, 3)

Same as list or ditionary, tuple can hold values of different type

t=("1",2.0,3)
t

('1', 2.0, 3)

t[0]  #accessing tuple value is the same as list

'1'

However, you can not alter element in tuple because tuple is considered, because turple is considered immutable. In discrete math, turple usually represent functions and directional object.

t[0]=2

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-6-0c0cc230f53e> in <module>()
----> 1 t[0]=2

TypeError: 'tuple' object does not support item assignment

One thing needs to pay attention is when you want to assign a singlton, you need to leave a comma, for example,

t2=(2)   # this won't give you a turple
t2

2

type(t2)

int

t3=(2,)  # you have to leave a comma
t3

(2,)

type(t3)

tuple

In this part, we will show examples of dictionary. Dictionary provides a way for you to map a specific value to another one.

my_dict = {"1":1,"2":2}
print my_dict

{'1': 1, '2': 2}

Different than list, dictionary require the get method for its elements

my_dict.get("1")

1

my_dict.update({"1":2})

You can use copy method to define a new dictionary

new_dict = my_dict.copy()
print new_dict

{'1': 2, '2': 2}

You can use iterkeys method to get the iternerator of keys, and wrap a list function to get the list value.

print (list(new_dict.iterkeys()))

print (list(new_dict.itervalues()))

['1', '2']
[2, 2]

new_dict["3"] =3
print new_dict

{'1': 2, '3': 3, '2': 2}

List is the most common type of handling data

#generate list of 0 to 9 
my_list = range(10)

my_list

[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

As you can see, list is braced by [ ]

# You can retrive list element
my_list[0]

0

List can hold different data types

# To add something into a list, use append methon
# This methond can only append one element at a time
my_list.append("1")

my_list

[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, '1']

# You can also store a list in a list
my_list.append(["1",1.2032,0x2f2])

# when you print the list hexedecimal number will be converted to decimal
my_list

[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, '1', ['1', 1.2032, 754]]

List Indexing

# remember index starts with 0
print (my_list[0])
print (my_list[11])
# You can also count backward, and it starts with -1
print (my_list[-1])

0
['1', 1.2032, 754]
['1', 1.2032, 754]

List Slicing

start_position = 0
end_position = 10

my_list[start_position:end_position]

[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

start_position = 2
end_position = 3

my_list[start_position:end_position]

[2]

start_position = -5
end_position = -1

my_list[start_position:end_position]

[7, 8, 9, '1']

Just pay attention to these 3 examples. When doing list slicing, you are not pointing element, but slicing between elements. That's why my_list[2:3] gives you the second element.

	pairs	ave_return	total_return	volatility	sharp_ratio
0	(‘NYT’, ‘BANC’)	-1683.602874	-15.167593	139.660600	-0.108603
1	(‘EEA’, ‘ESS’)	7.915179	0.121772	123.131991	0.000989
2	(‘BXP’, ‘LFC’)	1194.149695	11.593686	88.070115	0.131642
3	(‘PCF’, ‘ANIK’)	2.441353	0.021605	13.484533	0.001602
4	(‘NDSN’, ‘POL’)	-293.861701	-3.719768	36.593718	-0.101650

	pairs	ave_return	total_return	volatility	sharp_ratio
58587	(‘INFO’, ‘MYF’)	1111.644206	8.964873	84.062452	0.106645
58588	(‘AL’, ‘CSL’)	917.521116	7.399364	63.715749	0.116131
58589	(‘PFL’, ‘DSW’)	-876.763054	-7.070670	49.698238	-0.142272
58590	(‘HWBK’, ‘KRNY’)	-106.253729	-0.856885	22.987587	-0.037276
58591	(‘AN’, ‘KAP’)	36.888918	0.297491	7.444777	0.039960

	pairs	ave_return	total_return	volatility	sharp_ratio
0	(‘NYT’, ‘BANC’)	-1683.602874	-15.167593	139.660600	-0.108603
1	(‘EEA’, ‘ESS’)	7.915179	0.121772	123.131991	0.000989
2	(‘BXP’, ‘LFC’)	1194.149695	11.593686	88.070115	0.131642
3	(‘PCF’, ‘ANIK’)	2.441353	0.021605	13.484533	0.001602
4	(‘NDSN’, ‘POL’)	-293.861701	-3.719768	36.593718	-0.101650

	pairs	ave_return	total_return	volatility	sharp_ratio
2	(‘BXP’, ‘LFC’)	1194.149695	11.593686	88.070115	0.131642
3	(‘PCF’, ‘ANIK’)	2.441353	0.021605	13.484533	0.001602
4	(‘NDSN’, ‘POL’)	-293.861701	-3.719768	36.593718	-0.101650
5	(‘DAKT’, ‘LPG’)	-578.896261	-5.122976	255.150999	-0.020078
6	(‘AGR’, ‘MXIM’)	2203.294225	21.600924	163.622809	0.132017

	pairs	ave_return	total_return	volatility	sharp_ratio
0	(‘NYT’, ‘BANC’)	-1683.602874	-15.167593	139.660600	-0.108603
1	(‘EEA’, ‘ESS’)	7.915179	0.121772	123.131991	0.000989
2	(‘BXP’, ‘LFC’)	1194.149695	11.593686	88.070115	0.131642

Articles by kenli

Fwd: 早间市场快报：中国宣布对600亿美元美国商品征税；俄战机被击落推高油价；欧美股市小幅走高

一、热点回顾

中国宣布对美国进行600亿美元关税反击

中国承诺要稳投资和促进外贸

俄侦察机被击落加剧地缘政治紧张局势

二、隔夜金融市场回顾

三、今日市场展望

经济事件预告

四、深度/独家报道

美国贸易战反助中国制造业升级

小型股或推动美股继续走高

Pandas — Data Overviewing, Slicing and Selection

Data Overview, Slicing and Selection¶

Data Overview¶

Slicing and Selection¶

More Slicing and Selection¶

One Last Thing¶

Series & Numpy Array¶

Pandas — Reading Raw Data

Choosing The Right Python Data Type for Analysis

Dictionary

Tuple

List

List as stack operation

List sorting

List Reversing

List Extend Method

Other List Methods

Python Basic Data Structure — Set

Python Basic Data Structure — Tuple

Python Basic Data Structure — Dictionary

Python Basic Data Structure– List

Skulpt for python on brower

Fwd: Share about saving energy.

	pairs	ave_return	total_return	volatility	sharp_ratio
4684	(‘ITG’, ‘OIS’)	inf	inf	NaN	NaN
12016	(‘LEA’, ‘CTX’)	1.873543e+08	1.510922e+06	1.684723e+07	0.089684
24034	(‘ENVA’, ‘CTX’)	1.718711e+08	1.386057e+06	1.537679e+07	0.090140
34122	(‘TVPT’, ‘FULT’)	inf	inf	NaN	NaN
42235	(‘FOE’, ‘AOD’)	inf	inf	NaN	NaN
42749	(‘ALTR’, ‘EPD’)	inf	inf	NaN	NaN
46295	(‘FORM’, ‘AMSF’)	inf	inf	NaN	NaN
46678	(‘GS’, ‘ONB’)	inf	inf	NaN	NaN
54771	(‘NOV’, ‘CTX’)	1.789989e+08	1.443540e+06	1.601276e+07	0.090149

	pairs	ave_return	total_return	volatility	sharp_ratio
0	(‘NYT’, ‘BANC’)	0.000000	-15.167593	139.660600	-0.108603
1	(‘EEA’, ‘ESS’)	7.915179	0.121772	123.131991	0.000989
2	(‘BXP’, ‘LFC’)	1194.149695	11.593686	88.070115	0.131642
3	(‘PCF’, ‘ANIK’)	2.441353	0.021605	13.484533	0.001602
4	(‘NDSN’, ‘POL’)	-293.861701	-3.719768	36.593718	-0.101650