Exercises: Introduction to pandas¶
By Christopher van Hoecke, Maxwell Margenot
Lecture Link :¶
https://www.quantopian.com/lectures/introduction-to-pandas
IMPORTANT NOTE:¶
This lecture corresponds to the Introduction to Pandas lecture, which is part of the Quantopian lecture series. This homework expects you to rely heavily on the code presented in the corresponding lecture. Please copy and paste regularly from that lecture when starting to work on the problems, as trying to do them from scratch will likely be too difficult.
Part of the Quantopian Lecture Series:
# Useful Functions
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
l = np.random.randint(1,100, size=1000)
s = pd.Series(l)
new_index = pd.date_range("2016-01-01", periods=len(s), freq="D")
s.index = new_index
print s
b. Accessing Series Elements.¶
- Print every other element of the first 50 elements of series
s. - Find the value associated with the index
2017-02-20.
# Print every other element of the first 50 elements of series `s`.
print s.iloc[0:50:2]
# Find the value associated with the index `2017-02-20`.
print s['2017-02-20']
c. Boolean Indexing.¶
In the series s, print all the values between 1 and 3.
print s.loc[(s < 3) & (s > 1)]
print s.head(5)
print s.tail(5)
b. Resampling¶
- Using the resample method, upsample the daily data to monthly frequency. Use the median method so that each monthly value is the median price of all the days in that month.
- Take the daily data and fill in every day, including weekends and holidays, using forward-fills.
symbol = "CMG"
start = "2012-01-01"
end = "2016-01-01"
prices = get_pricing(symbol, start_date=start, end_date=end, fields="price")
monthly_prices_med = prices.resample('M').median()
monthly_prices_med.head(24)
calendar_dates = pd.date_range(start=start, end=end, freq='D', tz='UTC')
calendar_prices = prices.reindex(calendar_dates, method='ffill')
calendar_prices.head(15)
Exercise 3 : Missing Data¶
- Replace all instances of
NaNusing the forward fill method. - Instead of filling, remove all instances of
NaNfrom the data.
# Fill missing data using Backwards fill method
bfilled_prices = calendar_prices.fillna(method='bfill')
bfilled_prices.head(10)
dropped_prices = calendar_prices.dropna()
dropped_prices.head(10)
print "Summary Statistics"
print prices.describe()
b. Series Operations¶
- Get the additive and multiplicative returns of this series.
- Calculate the rolling mean with a 60 day window.
- Calculate the standard deviation with a 60 day window.
data = get_pricing('GE', fields='open_price', start_date='2016-01-01', end_date='2017-01-01')
add_returns = data.diff()[1:]
mult_returns = data.pct_change()[1:]
# Rolling mean
#rolling_mean = pd.rolling_mean(data, 60)
rolling_mean = data.rolling(window=60).mean()
rolling_mean.name = "60-day rolling mean"
data.plot()
rolling_mean.plot()
plt.title(symbol + "Price")
plt.xlabel("Date")
plt.ylabel("Price")
plt.legend();
# Rolling Standard Deviation
# rolling_std = pd.rolling_std(data, 60)
rolling_std = data.rolling(window=60).std()
rolling_std.name = "60-day rolling volatility"
rolling_std.plot()
plt.title(rolling_std.name);
plt.xlabel("Date")
plt.ylabel("Standard Deviation");
l = {'fifth','fourth', 'third', 'second', 'first'}
dict_data = {'a' : [1, 2, 3, 4, 5], 'b' : ['L', 'K', 'J', 'M', 'Z'],'c' : np.random.normal(0, 1, 5)}
frame_data = pd.DataFrame(dict_data, index=l)
print frame_data
b. DataFrames Manipulation¶
- Concatenate the following two series to form a dataframe.
- Rename the columns to
Good NumbersandBad Numbers. - Change the index to be a datetime index starting on
2016-01-01.
s1 = pd.Series([2, 3, 5, 7, 11, 13], name='prime')
s2 = pd.Series([1, 4, 6, 8, 9, 10], name='other')
numbers = pd.concat([s1, s2], axis=1)
print numbers
numbers.columns = ['Good Numbers', 'Bad Numbers']
print numbers
numbers.index = pd.date_range("2016-01-01", periods=len(numbers))
print numbers
symbol = ["XOM", "BP", "COP", "TOT"]
start = "2012-01-01"
end = "2016-01-01"
prices = get_pricing(symbol, start_date=start, end_date=end, fields="price")
if isinstance(symbol, list):
prices.columns = map(lambda x: x.symbol, prices.columns)
else:
prices.name = symbol
# Check Type of Data for these two.
print prices.XOM.head()
print prices.loc[:, 'XOM'].head()
print type(prices.XOM)
print type(prices.loc[:, 'XOM'])
prices.loc['2013-01-01':'2013-01-10']
Exercise 7 : Boolean Indexing¶
a. Filtering.¶
- Filter pricing data from the last question (stored in
prices) to only print values where:- BP > 30
- XOM < 100
- The intersection of both above conditions (BP > 30 and XOM < 100)
- The union of the previous composite condition along with TOT having no
nanvalues ((BP > 30 and XOM < 100) or TOT is non-NaN).
- Add a column for TSLA and drop the column for XOM.
# Filter the data for prices to only print out values where
# BP > 30
print prices.loc[prices.BP > 30].head()
# XOM < 100
print prices.loc[prices.XOM < 100].head()
# BP > 30 AND XOM < 100
print prices.loc[(prices.XOM < 100) & (prices.BP > 30)].head()
# The union of (BP > 30 AND XOM < 100) with TOT being non-nan
print prices.loc[((prices.XOM < 100) & (prices.BP > 30)) & ~prices.TOT.isnull()].head()
# Add a column for TSLA and drop the column for XOM
s_1 = get_pricing('TSLA', start_date=start, end_date=end, fields='price')
prices.loc[:, 'TSLA'] = s_1
prices = prices.drop('XOM', axis=1)
prices.head(5)
b. DataFrame Manipulation (again)¶
- Concatenate these DataFrames.
- Fill the missing data with 0s
# Concatenate these dataframes
df_1 = get_pricing(['SPY', 'VXX'], start_date=start, end_date=end, fields='price')
df_2 = get_pricing(['MSFT', 'AAPL', 'GOOG'], start_date=start, end_date=end, fields='price')
df_3 = pd.concat([df_1, df_2], axis=1)
df_3.head()
# Fill GOOG missing data with 0
filled0_df_3 = df_3.fillna(0)
filled0_df_3.head(5)
Exercise 8 : Time Series Analysis¶
a. Summary¶
- Print out a summary of the
pricesDataFrame from above. - Take the log returns and print the first 10 values.
- Print the multiplicative returns of each company.
- Normalize and plot the returns from 2014 to 2015.
- Plot a 60 day window rolling mean of the prices.
- Plot a 60 day window rolling standfard deviation of the prices.
# Print a summary of the 'prices' times series.
prices.describe()
# Print the natural log returns of the first 10 values
np.log(prices).head(10)
# Print the Muliplicative returns
mult_returns = prices.pct_change()[1:]
mult_returns.head()
# Normlalize the returns and plot
norm_returns = (mult_returns - mult_returns.mean(axis=0))/mult_returns.std(axis=0)
norm_returns.loc['2014-01-01':'2015-01-01'].plot();
# Rolling mean
rolling_mean = prices.rolling(window=60).mean()
rolling_mean.columns = prices.columns
# Rolling standard deviation
rolling_std = prices.rolling(window=60).std()
rolling_mean.columns = prices.columns
# Plotting
rolling_mean.plot()
plt.title("Rolling Mean of Prices")
plt.xlabel("Date")
plt.ylabel("Price")
plt.legend();
rolling_std.plot()
plt.title("Rolling Standard Deviation of Prices")
plt.xlabel("Date")
plt.ylabel("Price")
plt.legend();
Congratulations on completing the Introduction to pandas exercises!
As you learn more about writing trading algorithms and the Quantopian platform, be sure to check out the daily Quantopian Contest, in which you can compete for a cash prize every day.
Start by going through the Writing a Contest Algorithm Tutorial.
This presentation is for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation for any security; nor does it constitute an offer to provide investment advisory or other services by Quantopian, Inc. ("Quantopian"). Nothing contained herein constitutes investment advice or offers any opinion with respect to the suitability of any security, and any views expressed herein should not be taken as advice to buy, sell, or hold any security or as an endorsement of any security or company. In preparing the information contained herein, Quantopian, Inc. has not taken into account the investment needs, objectives, and financial circumstances of any particular investor. Any views expressed and data illustrated herein were prepared based upon information, believed to be reliable, available to Quantopian, Inc. at the time of publication. Quantopian makes no guarantees as to their accuracy or completeness. All information is subject to change and may quickly become unreliable for various reasons, including changes in market conditions or economic circumstances.