pandas Archives - Technology Trekking

16/06/201802/07/2018

How to add points to timeseries graph to show buy-sell signal – matplotlib

While doing time series analysis, you can show buy/sell signal on the ghraph itself.

Following is the script for doing the same.

################################################################################################

#	name:	timeseries_simple_with_pointer.py

#	desc:	Plots buy and sell signal on line chart

#	date:	2018-06-15

#	Author:	conquistadorjd

################################################################################################

import pandas as pd

import numpy as np

from matplotlib import pyplot as plt

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered

df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format

df['Date'] 	= pd.to_datetime(df['Date'])

df['SMA5'] 	= df["Close Price"].rolling(20).mean()

df['SMA20'] = df["Close Price"].rolling(100).mean()

#Using matplotlib to add required columns

plt.plot(df['Date'], df['Close Price'],linewidth=0.5,color='black')

plt.plot(df['Date'], df['SMA5'],linewidth=0.5,color='blue')

plt.plot(df['Date'], df['SMA20'],linewidth=0.5,color='c')

df['SMA5']		=df['SMA5'].fillna(0)

df['SMA20']		=df['SMA20'].fillna(0)

#Identifying the buy/sell zone

df['Buy'] = np.where( (df['SMA5']> df['SMA20']), 1, 0)

df['Sell'] = np.where( (df['SMA5']< df['SMA20']), 1, 0)

##identify buy sell signal

df['Buy_ind'] = np.where( (df['Buy'] > df['Buy'].shift(1)),1,0)

df['Sell_ind'] = np.where( (df['Sell'] > df['Sell'].shift(1)),1,0)

# print(df.dtypes)

# print(df.head(20))

## plotting the buy and sellsignals on graph

plt.scatter(df.loc[df['Buy_ind'] ==1 , 'Date'].values,df.loc[df['Buy_ind'] ==1, 'Close Price'].values, label='skitscat', color='green', s=25, marker="^")

plt.scatter(df.loc[df['Sell_ind'] ==1 , 'Date'].values,df.loc[df['Sell_ind'] ==1, 'Close Price'].values, label='skitscat', color='red', s=25, marker="v")

## Adding labels

plt.xlabel('Date')  

plt.ylabel('Close Price')  

plt.title('HDFC stock price with buy and sell signal') 

# Saving image

plt.savefig('HDFC with SMA 20-100 Buy sell.png')

# In case you dont want to save image but just displya it

# plt.show()

# df.to_csv('temp_hdfc.csv')

print('*** Program ended ***')

view raw
timeseries_simple_with_pointer.py
hosted with ❤ by GitHub

While working on this code, I wasted quite a lot of time on below error.

Traceback (most recent call last):
File "timeseries_simple_with_pointer.py", line 35, in <module>
plt.scatter(df.loc[df['SMA20'] >1400.0 , 'Date'],df.loc[df['SMA20'] >1400.0, 'Close Price'], label='skitscat', color='red', s=25, marker="<")
File "/usr/local/lib/python3.6/site-packages/matplotlib/pyplot.py", line 3378, in scatter
edgecolors=edgecolors, data=data, **kwargs)
File "/usr/local/lib/python3.6/site-packages/matplotlib/__init__.py", line 1717, in inner
return func(ax, *args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/matplotlib/axes/_axes.py", line 4023, in scatter
offsets = np.column_stack([x, y])
File "/usr/local/lib/python3.6/site-packages/numpy/lib/shape_base.py", line 369, in column_stack
return _nx.concatenate(arrays, 1)
TypeError: invalid type promotion

On further research I found that matplotlib scatter plot does not support pandas series and the scatter plot output needs to be converted into list so I had to use .values to make it work.

Output of my strategy using SMA 5 and SMAA12

As you can see, in range bound market it creates a lot of whipsaw, however, it was able to capture a very good bull run.

I tried using larger duration SMA to see another strategy. Second time, I tried with SAM20 and SMA100 and here is the output

As expected, longer duration SMA strategy provides lesser number of signals, lesser whipsaw’s.

This result does not mean you need to use higher duration SMA. I am planning to run this logic for approx 200 securities, will update this post with the feedabck later.

16/06/201816/06/2018

How to create new column in a DataFrame based on values from other columns – pandas

Many times we cant to create an indicator based on other values, this can be achieved very easily in pandas.

################################################################################################

#	name:	create_column_from_other_columns.py

#	desc:	Create new columns in pandas DataFrame

#	date:	2018-06-16

#	Author:	conquistadorjd

################################################################################################

import pandas as pd

import numpy as np

# import pandas_datareader as datareader

import matplotlib.pyplot as plt

import datetime

from matplotlib.finance import candlestick_ohlc

# from mpl_finance import candlestick_ohlc

import matplotlib.dates as mdates

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered

df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format

df['Date'] = pd.to_datetime(df['Date'])

# print(df.dtypes)

# print(df.head())

#### Normail column creation

df['range'] = df['High Price'] – df['Low Price']

df['Average'] = (df['Close Price'] + df['Open Price'])/2

#### Conditional Value

df['GT1400'] = np.where( (df['Close Price']> 1400), 1, 0)

df['DailyTrend'] = np.where( (df['Close Price']>= df['Open Price']), 'Positive', 'Negative')

df['Calculated Column'] = np.where( (df['Close Price']>= df['Open Price']), df['Close Price']– df['Open Price'], (df['Close Price'] + df['Open Price'])/2)

df.to_csv('hdfc_with_calculated_columns.csv')

print('*** Program ended ***')

view raw
pandas_create_columns.py
hosted with ❤ by GitHub

15/06/201801/07/2018

How to plot simple and Candlestick chart using Python – pandas matplotlib

Pandas and Matplotlib can be used to plot various types of graphs. Simple timeseries plot and candlestick are basic graphs used by technical analyst for identifying the trend.

Simple time Series Chart using Python – pandas matplotlib

Here is the simplest graph. It uses close price of HDFCBANK for last 24 months to plot normal graph

################################################################################################

#	name:	timeseries_simple.py

#	desc:	Plots line chart using 'Close Price' 

#	date:	2018-06-15

#	Author:	conquistadorjd

################################################################################################

import pandas as pd

import numpy as np

from matplotlib import pyplot as plt

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered

df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format

df['Date'] = pd.to_datetime(df['Date'])

# Using matplotlib to add required columns

plt.plot(df['Date'], df['Close Price'])

# Adding labels

plt.xlabel('Date')  

plt.ylabel('Close Price')  

plt.title('Simple time series plot for HDFC') 

# Saving image

plt.savefig('Simple time series plot for HDFC.png')

# In case you dont want to save image but just displya it

#plt.show()

print('*** Program ended ***')

view raw
timeseries_simple.py
hosted with ❤ by GitHub

Here is the sample output

Candlestick chart using Python – pandas matplotlib

As per this link, module matplotlib.finance is deprecated in 2.0 and has been moved to a module called mpl_finance. This is still working when I have motplotlib version 2.1.2. This may stop working in any future releases, however, you can use mpl_finance module to use this feature. Please note that mpl_finance is no longer maintained.

################################################################################################

#	name:	timeseries_OHLC.py

#	desc:	creates OHLC graph

#	date:	2018-06-15

#	Author:	conquistadorjd

################################################################################################

import pandas as pd

# import pandas_datareader as datareader

import matplotlib.pyplot as plt

import datetime

from matplotlib.finance import candlestick_ohlc

# from mpl_finance import candlestick_ohlc

import matplotlib.dates as mdates

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered

df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format

df['Date'] = pd.to_datetime(df['Date'])

df["Date"] = df["Date"].apply(mdates.date2num)

# Creating required data in new DataFrame OHLC

ohlc= df[['Date', 'Open Price', 'High Price', 'Low Price','Close Price']].copy()

# In case you want to check for shorter timespan

# ohlc =ohlc.tail(60)

f1, ax = plt.subplots(figsize = (10,5))

# plot the candlesticks

candlestick_ohlc(ax, ohlc.values, width=.6, colorup='green', colordown='red')

ax.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m'))

# Saving image

plt.savefig('OHLC HDFC.png')

# In case you dont want to save image but just displya it

#plt.show()

print('*** Program ended ***')

view raw
simple2.py
hosted with ❤ by GitHub

Here is the output

Candlestick chart with SMA overlay using Python – pandas matplotlib

################################################################################################

#	name:	timeseries_OHLC_with_SMA.py

#	desc:	creates OHLC graph with overlay of simple moving averages

#	date:	2018-06-15

#	Author:	conquistadorjd

################################################################################################

import pandas as pd

# import pandas_datareader as datareader

import matplotlib.pyplot as plt

import datetime

from matplotlib.finance import candlestick_ohlc

# from mpl_finance import candlestick_ohlc

import matplotlib.dates as mdates

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered

df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format

df['Date'] = pd.to_datetime(df['Date'])

df["Date"] = df["Date"].apply(mdates.date2num)

# Creating required data in new DataFrame OHLC

ohlc= df[['Date', 'Open Price', 'High Price', 'Low Price','Close Price']].copy()

# In case you want to check for shorter timespan

# ohlc =ohlc.tail(60)

ohlc['SMA50'] = ohlc["Close Price"].rolling(50).mean()

f1, ax = plt.subplots(figsize = (10,5))

# plot the candlesticks

candlestick_ohlc(ax, ohlc.values, width=.6, colorup='green', colordown='red')

ax.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m'))

# Creating SMA columns

ohlc['SMA5'] = ohlc["Close Price"].rolling(5).mean()

ohlc['SMA10'] = ohlc["Close Price"].rolling(10).mean()

ohlc['SMA20'] = ohlc["Close Price"].rolling(20).mean()

ohlc['SMA50'] = ohlc["Close Price"].rolling(50).mean()

ohlc['SMA100'] = ohlc["Close Price"].rolling(100).mean()

ohlc['SMA200'] = ohlc["Close Price"].rolling(200).mean()

#Plotting SMA columns

# ax.plot(ohlc['Date'], ohlc['SMA5'], color = 'blue', label = 'SMA5')

# ax.plot(ohlc['Date'], ohlc['SMA10'], color = 'blue', label = 'SMA10')

# ax.plot(ohlc['Date'], ohlc['SMA20'], color = 'blue', label = 'SMA20')

ax.plot(ohlc['Date'], ohlc['SMA50'], color = 'green', label = 'SMA50')

# ax.plot(ohlc.index, df['SMA100'], color = 'blue', label = 'SMA100')

ax.plot(ohlc['Date'], ohlc['SMA200'], color = 'blue', label = 'SMA200')

# Saving image

plt.savefig('OHLC with SMA HDFC.png')

# In case you dont want to save image but just displya it

# ohlc.to_csv('ohlc.csv')

# plt.show()

print('*** Program ended ***')

view raw
simple3.py
hosted with ❤ by GitHub

here is the output

15/06/2018

How to convert daily time series data into weekly and monthly using pandas and python

While working with stock market data, sometime we would like to change our time window of reference. Generally daily prices are available at stock exchenges. Let us see how to conert daily prices into weekly and monthly prices.

You can download daily prices from NSE from this link. We will downoad daily prices for last 24 months. Here is the sample file with which we will work

15-06-2016-TO-14-06-2018HDFCBANKALLN

Please refer to below program to convert daily prices into weekly. Comments in the program will help you understand the logic behind each line.

################################################################################################

#	name:	convert_daily_to_weekly.py

#	desc:	takes inout as daily prices and convert into weekly data

#	date:	2018-06-15

#	Author:	conquistadorjd

################################################################################################

import pandas as pd

import numpy as np

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered

df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format

df['Date'] = pd.to_datetime(df['Date'])

# Getting week number

df['Week_Number'] = df['Date'].dt.week

# Getting year. Weeknum is common across years to we need to create unique index by using year and weeknum

df['Year'] = df['Date'].dt.year

# Grouping based on required values

df2 = df.groupby(['Year','Week_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum'})

# df3 = df.groupby(['Year','Week_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum','Average Price':'avg'})

df2.to_csv('Weekly_OHLC.csv')

print('*** Program ended ***')

view raw
convert_daily_to_weekly.py
hosted with ❤ by GitHub

Once you understand daily to weekly, only small modification is needed to convert this into monthly OHLC data. Here is the script

################################################################################################

#	name:	convert_daily_to_monthly.py

#	desc:	takes inout as daily prices and convert into monthly data

#	date:	2018-06-15

#	Author:	conquistadorjd

################################################################################################

import pandas as pd

import numpy as np

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered

df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format

df['Date'] = pd.to_datetime(df['Date'])

# Getting month number

df['Month_Number'] = df['Date'].dt.month

# Getting year. month is common across years (as if you dont know 🙂 )to we need to create unique index by using year and month

df['Year'] = df['Date'].dt.year

# Grouping based on required values

df2 = df.groupby(['Year','Month_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum'})

# df3 = df.groupby(['Year','Week_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum','Average Price':'avg'})

df2.to_csv('Monthly_OHLC.csv')

print('*** Program ended ***')

view raw
convert_daily_to_monthly.py
hosted with ❤ by GitHub

Here are the output files for your reference.

Monthly_OHLC Weekly_OHLC

Weekly_OHLC

I wasted some time to find ‘Open Price’ for weekly and monthly data. I tried some complex pandas queries and then realized same can be achieved by simply using aggregate function and ‘Open Price‘:‘first.

Please do let me know your feedback.