<jupyter_start><jupyter_text>Basic EDA using pandas on the selected equity  Preliminary Setup<jupyter_code>%matplotlib inline
import os
import warnings
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
os.chdir(r'D:/DS')
cwd = os.getcwd()
print(cwd)
warnings.filterwarnings('ignore')
data = pd.read_csv(r'MINDTREE.csv')
data.shape
data.head()
data.tail()
data.describe()
#Remove null values to avoid future errors
data.dropna(inplace = True)
#Checking type of Turnover Column since the values are displayed in exponential form with an 'e'
data.dtypes
# Check for NOT 'EQ' values in Series column
data[data.Series != 'EQ']<jupyter_output><empty_output><jupyter_text>Therefore, there are no rows where Series is not 'EQ'<jupyter_code>data.apply(np.max)
data.apply(np.min)<jupyter_output><empty_output><jupyter_text>-------------------------------------------------------------------------------------------------------------The maximum, minimum and mean adjusted close price for the last 90 days is computed.<jupyter_code>data.head()
data.tail()
data['Close Price'].max()
data['Close Price'].min()
data['Close Price'].mean()<jupyter_output><empty_output><jupyter_text>The above (max, min, mean) was for all the dates under consideration. For only past 90 days we have modify the Date values<jupyter_code>data['Date'].dtype
data['Index'] = data.index
data.tail()
# create a separate dataframe only for past 90 days
data90 = data.loc[(data['Index'] >= 403) & (data['Index'] <= 493)]
data90.head()
data90.tail()<jupyter_output><empty_output><jupyter_text>Now, we have a subset of the original data only for the past 90 days<jupyter_code>data90['Close Price'].max()
data90['Close Price'].min()
data90['Close Price'].mean()
data['Date'].dtype
from datetime import datetime
data['Date'] = data['Date'].astype(datetime)
data['Date'].dtype
data['Date'] = pd.to_datetime(data['Date'], errors='coerce')
data['Date'].dtype
np.dtype('datetime64[ns]') == np.dtype('<M8[ns]')
data['Date'].max() - data['Date'].min()<jupyter_output><empty_output><jupyter_text>---------------------------------------------------------------------------------------------------------The monthwise VWAP (Volume Weighted Average Price ) of the stock is calculated.(VWAP = sum(price*volume)/sum(volume)) Key Takeaways regarding VWAPS:1. The volume weighted average price (VWAP) appears as a single line on intraday charts (1 minute, 15 minute, and so on), similar to how a moving average looks.2. A rising VWAP, and/or the price above the VWAP line, means the price is likely in an uptrend.3. A declining VWAP, and/or the price below the VWAP line, means the price is likely in a downtrend.4. Don't rely on VWAP exclusively to determine trend, since it is only showing a historical average, and not what is happening currently or in the future.5. Investors may use VWAP to assess the price they paid for a security throughout the day. At the end of the day, if the price they bought at is higher than the VWAP, then they may have overpaid. If it is less than VWAP, then they purchased shares at a good price for that day.<jupyter_code>data['Date'].max()
data['Month'] = pd.DatetimeIndex(data['Date']).month
data['Year'] = pd.DatetimeIndex(data['Date']).year
data.head(10)
data.tail()<jupyter_output><empty_output><jupyter_text>Now we have separate columns for Year and Month<jupyter_code>data['Month'].min()
data['Month'].max()
data['Year'].min()
data['Year'].max()<jupyter_output><empty_output><jupyter_text>Therefore, the entire dataset is divided into 1st(January) to 12th(December) month and 2017 and 2019.<jupyter_code>data_month = data.groupby(['Month'])
data_year = data.groupby(['Year'])
data_month.head(1)<jupyter_output><empty_output><jupyter_text>Therefore, the data starts from May 2017 and goes on to 2019 Entries from 219 onto 494 are from May 2018 to 2019<jupyter_code>data_year.head(1)<jupyter_output><empty_output><jupyter_text>Entries from 0 - 158 : 2017 Entries from 159 - 404 : 2018 Entries rom 405 - 494 : 2019<jupyter_code>data_month.mean()
data_year.mean()
data_month['Close Price'].mean()
data_year['Close Price'].mean()<jupyter_output><empty_output><jupyter_text>Create new column for product of Close Price and Total Traded Quantity (ie. Volume)<jupyter_code>#data['VWAP'] = (data.groupby('Month')['Close Price'].mean() * data.groupby('Month')['Total Traded Quantity'].mean())
"""def vwap(df):
    q = df['Total Traded Quantity']
    p = df['Close Price']
    return data.assign(vwap =(p * q).cumsum() / q.cumsum())

data.groupby(data['Month'], group_keys = False).apply(vwap)"""
#below wrong, use cumsum
#groupby_month['VWAP'] = (groupby_month['Close Price']*groupby_month['Total Traded Quantity'])/groupby_month['Total Traded Quantity']
#groupby_month.sort_values(['Month'], axis=0, ascending=True, inplace=True) 
data_month
data_jan = data_month.get_group(1)
data_jan.tail()
#data_jan['Price_Vol'] = data_jan.apply(lambda row: (row['Close Price']*row['Total Traded Quantity'], axis = 1))
data_jan.dtypes
data_jan['Price_Vol'] = data_jan['Close Price'] * data_jan['Total Traded Quantity']
data_jan.head()
# Using cumulative sum to calculate VWAP for each day in January
data_jan['VWAP'] = data_jan['Price_Vol'].sum()/data_jan['Total Traded Quantity'].sum()
data_jan.head()<jupyter_output><empty_output><jupyter_text>Approach used in 1.4 : Groupby month in main dataframe and then create new data frame for each month and calculate VWAP for each month individually. January is done in said method. Similar approach can be followed fro the other months.  ----------------------------------------------------------------------------------------------------------Average price over the last N days of the stock price data is computed where N is a user defined parameter. The P/L percentage is calculated over the last N days.<jupyter_code>data.tail()
n = input('Period of time to perform calculation(in days): ')
n = int(n)
# extracting the data for the user given number of days
data_n = data.loc[(data['Index'] >= 493 - n) & (data['Index'] <= 493)]

def avg_price(n):   
    ap = data_n['Close Price'].sum()/n
    print("Average price for the given time period: ", ap)

    
def pl_percentage(n):
    pl_df = data_n.iloc[[0, -1]]
    pl_df.head()
    a = pl_df['Close Price']
    a =a.pct_change()
    a = a.iloc[-1] * 100
    print("PL Percentage change for the given time period: ", a, "%")
    
avg_price(n)
pl_percentage(n)<jupyter_output>Period of time to perform calculation(in days): 7
Average price for the given time period:  1118.3857142857144
PL Percentage change for the given time period:  -0.2343590788669192 %<jupyter_text>Period of time to perform calculation(in days): 7Average price for the given time period:  1118.3857142857144PL Percentage change for the given time period:  -0.2343590788669192 %Period of time to perform calculation(in days): 14Average price for the given time period:  1048.1928571428573PL Percentage change for the given time period:  1.0944759938048554 %Period of time to perform calculation(in days): 30Average price for the given time period:  999.3366666666665PL Percentage change for the given time period:  3.008942661756975 %Period of time to perform calculation(in days): 90Average price for the given time period:  928.2133333333335PL Percentage change for the given time period:  13.308644832773986 %Period of time to perform calculation(in days): 180Average price for the given time period:  937.8138888888889PL Percentage change for the given time period:  -0.38661104893681353 %Period of time to perform calculation(in days): 365Average price for the given time period:  881.9361643835617PL Percentage change for the given time period:  91.67971808927173 %<jupyter_code>data.head()<jupyter_output><empty_output><jupyter_text>Calculating % Change using pct_change() function<jupyter_code>data['Day_Perc_Change'] = data[['Close Price']].pct_change()*100
data.head()<jupyter_output><empty_output><jupyter_text>Removing first row to avoid NaN Value<jupyter_code>data = data.iloc[1:]
data.head()<jupyter_output><empty_output><jupyter_text>------------------------------------------------------------------------------------------------------------'Trend' column:    'Slight or No change' for 'Day_Perc_Change' in between -0.5 and 0.5    'Slight positive' for 'Day_Perc_Change' in between 0.5 and 1    'Slight negative' for 'Day_Perc_Change' in between -0.5 and -1    'Positive' for 'Day_Perc_Change' in between 1 and 3    'Negative' for 'Day_Perc_Change' in between -1 and -3    'Among top gainers' for 'Day_Perc_Change' in between 3 and 7    'Among top losers' for 'Day_Perc_Change' in between -3 and -7    'Bull run' for 'Day_Perc_Change' >7    'Bear drop' for 'Day_Perc_Change' <-7<jupyter_code>def T(data):
    if((data['Day_Perc_Change'] >= -0.5)&(data['Day_Perc_Change'] <= 0.5)):
        return 'Slight or No change'
    elif((data['Day_Perc_Change']>= 0.5)&(data['Day_Perc_Change']<= 1)):
        return 'Slight Positive'
    elif((data['Day_Perc_Change']<= -0.5)&(data['Day_Perc_Change']>= -1)):
        return 'Slight Negative'
    elif((data['Day_Perc_Change']>= 1)&(data['Day_Perc_Change']<= 3)):
        return 'Positive'
    elif((data['Day_Perc_Change']<= -1)&(data['Day_Perc_Change']>= -3)):
        return 'Negative'
    elif((data['Day_Perc_Change']>= 3)&(data['Day_Perc_Change']<= 7)):
        return 'Among top gainers'
    elif((data['Day_Perc_Change']<= -3)&(data['Day_Perc_Change']>= -7)):
        return 'Among top losers'
    elif(data['Day_Perc_Change']> 7):
        return 'Bull run'
    elif(data['Day_Perc_Change']<-7):
        return 'Bear drop'
    else:
        return 'Undefined'
    
data['Trend'] = data.apply(T, axis=1)
data.head()<jupyter_output><empty_output><jupyter_text>Now the main dataframe contain column with metadata based on Daily % change in closing prices of the given security.  --------------------------------------------------------------------------------------------------------Average and median values for each trend according to total volume is computed.<jupyter_code>groupby_trend = data['Total Traded Quantity'].groupby(data['Trend'])
data['Total Traded Quantity'].groupby(data['Trend']).describe()
groupby_trend.mean()
groupby_trend.median()
data.to_csv(r'D:/DS/mindtree_updated.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17035248672139064, 'fraction_numerical': 0.03969097054563013, 'mean_word_length': 4.152238805970149, 'pattern_counts': {'":': 0, '<': 80, '<?xml version=': 0, '>': 79, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '543250', 'license': 'MIT', 'n_tokens_mistral': 3538, 'n_tokens_neox': 3213, 'n_words': 998, 'path': '1 - Exploratory Data Analysis.ipynb', 'repo_name': 'atheesh1998/Analysis-on-Securitized-Assets', 'size': '10355'}