import pandas as pd
stocks = pd.read_csv('data/stocks.csv')
stocks
Date | Close | Volume | Symbol | |
---|---|---|---|---|
0 | 2016-10-03 | 31.50 | 14070500 | CSCO |
1 | 2016-10-03 | 112.52 | 21701800 | AAPL |
2 | 2016-10-03 | 57.42 | 19189500 | MSFT |
3 | 2016-10-04 | 113.00 | 29736800 | AAPL |
4 | 2016-10-04 | 57.24 | 20085900 | MSFT |
5 | 2016-10-04 | 31.35 | 18460400 | CSCO |
6 | 2016-10-05 | 57.64 | 16726400 | MSFT |
7 | 2016-10-05 | 31.59 | 11808600 | CSCO |
8 | 2016-10-05 | 113.05 | 21453100 | AAPL |
stocks.index
RangeIndex(start=0, stop=9, step=1)
stocks.groupby('Symbol').Close.mean()
Symbol AAPL 112.856667 CSCO 31.480000 MSFT 57.433333 Name: Close, dtype: float64
ser = stocks.groupby(['Symbol', 'Date']).Close.mean()
ser
Symbol Date AAPL 2016-10-03 112.52 2016-10-04 113.00 2016-10-05 113.05 CSCO 2016-10-03 31.50 2016-10-04 31.35 2016-10-05 31.59 MSFT 2016-10-03 57.42 2016-10-04 57.24 2016-10-05 57.64 Name: Close, dtype: float64
ser.index
MultiIndex(levels=[['AAPL', 'CSCO', 'MSFT'], ['2016-10-03', '2016-10-04', '2016-10-05']], codes=[[0, 0, 0, 1, 1, 1, 2, 2, 2], [0, 1, 2, 0, 1, 2, 0, 1, 2]], names=['Symbol', 'Date'])
ser.unstack()
Date | 2016-10-03 | 2016-10-04 | 2016-10-05 |
---|---|---|---|
Symbol | |||
AAPL | 112.52 | 113.00 | 113.05 |
CSCO | 31.50 | 31.35 | 31.59 |
MSFT | 57.42 | 57.24 | 57.64 |
df = stocks.pivot_table(values='Close', index='Symbol', columns='Date')
df
Date | 2016-10-03 | 2016-10-04 | 2016-10-05 |
---|---|---|---|
Symbol | |||
AAPL | 112.52 | 113.00 | 113.05 |
CSCO | 31.50 | 31.35 | 31.59 |
MSFT | 57.42 | 57.24 | 57.64 |
ser
Symbol Date AAPL 2016-10-03 112.52 2016-10-04 113.00 2016-10-05 113.05 CSCO 2016-10-03 31.50 2016-10-04 31.35 2016-10-05 31.59 MSFT 2016-10-03 57.42 2016-10-04 57.24 2016-10-05 57.64 Name: Close, dtype: float64
ser.loc['AAPL']
Date 2016-10-03 112.52 2016-10-04 113.00 2016-10-05 113.05 Name: Close, dtype: float64
ser.loc['AAPL', '2016-10-03']
112.52
ser.loc[:, '2016-10-03']
Symbol AAPL 112.52 CSCO 31.50 MSFT 57.42 Name: Close, dtype: float64
df
Date | 2016-10-03 | 2016-10-04 | 2016-10-05 |
---|---|---|---|
Symbol | |||
AAPL | 112.52 | 113.00 | 113.05 |
CSCO | 31.50 | 31.35 | 31.59 |
MSFT | 57.42 | 57.24 | 57.64 |
df.loc['AAPL']
Date 2016-10-03 112.52 2016-10-04 113.00 2016-10-05 113.05 Name: AAPL, dtype: float64
df.loc['AAPL', '2016-10-03']
112.52
df.loc[:, '2016-10-03']
Symbol AAPL 112.52 CSCO 31.50 MSFT 57.42 Name: 2016-10-03, dtype: float64
stocks.set_index(['Symbol', 'Date'], inplace=True)
stocks
Close | Volume | ||
---|---|---|---|
Symbol | Date | ||
CSCO | 2016-10-03 | 31.50 | 14070500 |
AAPL | 2016-10-03 | 112.52 | 21701800 |
MSFT | 2016-10-03 | 57.42 | 19189500 |
AAPL | 2016-10-04 | 113.00 | 29736800 |
MSFT | 2016-10-04 | 57.24 | 20085900 |
CSCO | 2016-10-04 | 31.35 | 18460400 |
MSFT | 2016-10-05 | 57.64 | 16726400 |
CSCO | 2016-10-05 | 31.59 | 11808600 |
AAPL | 2016-10-05 | 113.05 | 21453100 |
stocks.index
MultiIndex(levels=[['AAPL', 'CSCO', 'MSFT'], ['2016-10-03', '2016-10-04', '2016-10-05']], codes=[[1, 0, 2, 0, 2, 1, 2, 1, 0], [0, 0, 0, 1, 1, 1, 2, 2, 2]], names=['Symbol', 'Date'])
stocks.sort_index(inplace=True)
stocks
Close | Volume | ||
---|---|---|---|
Symbol | Date | ||
AAPL | 2016-10-03 | 112.52 | 21701800 |
2016-10-04 | 113.00 | 29736800 | |
2016-10-05 | 113.05 | 21453100 | |
CSCO | 2016-10-03 | 31.50 | 14070500 |
2016-10-04 | 31.35 | 18460400 | |
2016-10-05 | 31.59 | 11808600 | |
MSFT | 2016-10-03 | 57.42 | 19189500 |
2016-10-04 | 57.24 | 20085900 | |
2016-10-05 | 57.64 | 16726400 |
stocks.loc['AAPL']
Close | Volume | |
---|---|---|
Date | ||
2016-10-03 | 112.52 | 21701800 |
2016-10-04 | 113.00 | 29736800 |
2016-10-05 | 113.05 | 21453100 |
stocks.loc[('AAPL', '2016-10-03'), :]
Close 112.52 Volume 21701800.00 Name: (AAPL, 2016-10-03), dtype: float64
stocks.loc[('AAPL', '2016-10-03'), 'Close']
112.52
stocks.loc[['AAPL', 'MSFT'], :]
Close | Volume | ||
---|---|---|---|
Symbol | Date | ||
AAPL | 2016-10-03 | 112.52 | 21701800 |
2016-10-04 | 113.00 | 29736800 | |
2016-10-05 | 113.05 | 21453100 | |
MSFT | 2016-10-03 | 57.42 | 19189500 |
2016-10-04 | 57.24 | 20085900 | |
2016-10-05 | 57.64 | 16726400 |
stocks.loc[(['AAPL', 'MSFT'], '2016-10-03'), :]
Close | Volume | ||
---|---|---|---|
Symbol | Date | ||
AAPL | 2016-10-03 | 112.52 | 21701800 |
MSFT | 2016-10-03 | 57.42 | 19189500 |
stocks.loc[(['AAPL', 'MSFT'], '2016-10-03'), 'Close']
Symbol Date AAPL 2016-10-03 112.52 MSFT 2016-10-03 57.42 Name: Close, dtype: float64
stocks.loc[('AAPL', ['2016-10-03', '2016-10-04']), 'Close']
Symbol Date AAPL 2016-10-03 112.52 2016-10-04 113.00 Name: Close, dtype: float64
stocks.loc[(slice(None), ['2016-10-03', '2016-10-04']), :]
Close | Volume | ||
---|---|---|---|
Symbol | Date | ||
AAPL | 2016-10-03 | 112.52 | 21701800 |
2016-10-04 | 113.00 | 29736800 | |
CSCO | 2016-10-03 | 31.50 | 14070500 |
2016-10-04 | 31.35 | 18460400 | |
MSFT | 2016-10-03 | 57.42 | 19189500 |
2016-10-04 | 57.24 | 20085900 |
close = pd.read_csv('data/stocks.csv', usecols=[0, 1, 3], index_col=['Symbol', 'Date']).sort_index()
close
Close | ||
---|---|---|
Symbol | Date | |
AAPL | 2016-10-03 | 112.52 |
2016-10-04 | 113.00 | |
2016-10-05 | 113.05 | |
CSCO | 2016-10-03 | 31.50 |
2016-10-04 | 31.35 | |
2016-10-05 | 31.59 | |
MSFT | 2016-10-03 | 57.42 |
2016-10-04 | 57.24 | |
2016-10-05 | 57.64 |
volume = pd.read_csv('data/stocks.csv', usecols=[0, 2, 3], index_col=['Symbol', 'Date']).sort_index()
volume
Volume | ||
---|---|---|
Symbol | Date | |
AAPL | 2016-10-03 | 21701800 |
2016-10-04 | 29736800 | |
2016-10-05 | 21453100 | |
CSCO | 2016-10-03 | 14070500 |
2016-10-04 | 18460400 | |
2016-10-05 | 11808600 | |
MSFT | 2016-10-03 | 19189500 |
2016-10-04 | 20085900 | |
2016-10-05 | 16726400 |
both = pd.merge(close, volume, left_index=True, right_index=True)
both
Close | Volume | ||
---|---|---|---|
Symbol | Date | ||
AAPL | 2016-10-03 | 112.52 | 21701800 |
2016-10-04 | 113.00 | 29736800 | |
2016-10-05 | 113.05 | 21453100 | |
CSCO | 2016-10-03 | 31.50 | 14070500 |
2016-10-04 | 31.35 | 18460400 | |
2016-10-05 | 31.59 | 11808600 | |
MSFT | 2016-10-03 | 57.42 | 19189500 |
2016-10-04 | 57.24 | 20085900 | |
2016-10-05 | 57.64 | 16726400 |
both.reset_index()
Symbol | Date | Close | Volume | |
---|---|---|---|---|
0 | AAPL | 2016-10-03 | 112.52 | 21701800 |
1 | AAPL | 2016-10-04 | 113.00 | 29736800 |
2 | AAPL | 2016-10-05 | 113.05 | 21453100 |
3 | CSCO | 2016-10-03 | 31.50 | 14070500 |
4 | CSCO | 2016-10-04 | 31.35 | 18460400 |
5 | CSCO | 2016-10-05 | 31.59 | 11808600 |
6 | MSFT | 2016-10-03 | 57.42 | 19189500 |
7 | MSFT | 2016-10-04 | 57.24 | 20085900 |
8 | MSFT | 2016-10-05 | 57.64 | 16726400 |