feature engineering class.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "class feature_engine:\n",
    "    \n",
    "    pd = __import__('pandas')\n",
    "    datetime = __import__('datetime')\n",
    "    mcal = __import__('pandas_market_calendars')\n",
    "    np = __import__('numpy')\n",
    "    random = __import__('random')\n",
    "    \n",
    "    def __init__(self):\n",
    "        print('feature_engine V.0.1 \\nImported pandas,datetime,pandas_market_calendars,numpy packages')\n",
    "        \n",
    "    def calc_price_change(self,tweet_time,financial_data,time_col_name,price_col_name,interval,amount,method='both',\n",
    "                          logdiff=True):\n",
    "        #remove seconds to match financial data format\n",
    "        tweet_time=tweet_time.replace(second=0)\n",
    "        #default baseline is set for five minutes before tweet\n",
    "#         tweet_time_5min_before=tweet_time - self.datetime.timedelta(0,0,0,0,5)\n",
    "        \n",
    "        tweet_price=financial_data[financial_data[time_col_name]==tweet_time][price_col_name]\n",
    "        if len(tweet_price)==0:\n",
    "            return float('NaN')\n",
    "        tweet_price.reset_index(inplace=True,drop=True)\n",
    "\n",
    "        def calculation(financial_data,time_col_name,new_time,price_col_name,tweet_price,direction,logdiff):\n",
    "            \n",
    "            other_price=financial_data[financial_data[time_col_name]==new_time][price_col_name]\n",
    "            other_price.reset_index(inplace=True,drop=True)\n",
    "            if len(other_price)==0:\n",
    "                return float('NaN')\n",
    "            elif logdiff:\n",
    "                if direction=='forward':\n",
    "                    return (self.np.log(other_price.iloc[0]) - self.np.log(tweet_price.iloc[0]))\n",
    "                elif direction=='backward':\n",
    "                    return (self.np.log(tweet_price.iloc[0]) - self.np.log(other_price.iloc[0]))\n",
    "            else:\n",
    "                if direction=='forward':\n",
    "                    return ((other_price.iloc[0]/tweet_price.iloc[0])-1)\n",
    "                elif direction=='backward':\n",
    "                    return ((tweet_price.iloc[0]/other_price.iloc[0])-1)\n",
    "\n",
    "        if (method=='both') or (method=='forward'):  \n",
    "            if interval=='minutes':\n",
    "                time_forward=tweet_time + self.datetime.timedelta(0,0,0,0,amount)\n",
    "            elif interval=='hours':\n",
    "                time_forward=tweet_time + self.datetime.timedelta(0,0,0,0,0,amount)\n",
    "            pct_change_forward=calculation(financial_data,time_col_name,time_forward,price_col_name,tweet_price,'forward',\n",
    "                                          logdiff)\n",
    "\n",
    "        if (method=='both') or (method=='backward'): \n",
    "            if interval=='minutes':\n",
    "                time_backward=tweet_time - self.datetime.timedelta(0,0,0,0,amount)   \n",
    "            elif interval=='hours':\n",
    "                time_backward=tweet_time - self.datetime.timedelta(0,0,0,0,0,amount) \n",
    "            pct_change_backward=calculation(financial_data,time_col_name,time_backward,price_col_name,tweet_price,'backward'\n",
    "                                           ,logdiff)\n",
    "        \n",
    "        if method == 'both':\n",
    "            return self.pd.DataFrame({f'{amount}_{interval}_forward_pct_change':pct_change_forward,\n",
    "                          f'{amount}_{interval}_backward_pct_change':pct_change_backward},index=[0])\n",
    "        elif method=='backward':\n",
    "            return self.pd.DataFrame({f'{amount}_{interval}_backward_pct_change':pct_change_backward},index=[0])\n",
    "        elif method=='forward':\n",
    "            return self.pd.DataFrame({f'{amount}_{interval}_forward_pct_change':pct_change_forward},index=[0])\n",
    "        \n",
    "    def create_pricechg_columns(self,twitter_data,tweet_time_col_name,financial_data,fin_time_col_name,price_col_name,\n",
    "                       interval_amount_dict,method='both',logdiff=True):\n",
    "        from tqdm import tqdm\n",
    "        new_columns=self.pd.DataFrame()\n",
    "        for i, row in tqdm(twitter_data.iterrows()):\n",
    "            temp_df=self.pd.DataFrame()\n",
    "            for interval, amount in interval_amount_dict:\n",
    "                new_df=self.calc_price_change(twitter_data[tweet_time_col_name].loc[i],financial_data,fin_time_col_name,\n",
    "                                                   price_col_name,interval,amount,method,logdiff)\n",
    "                if not isinstance(new_df,float):\n",
    "                    temp_df=self.pd.concat([temp_df,new_df],axis=1)\n",
    "            for item in temp_df.columns:\n",
    "                new_columns.at[i,item] = temp_df.loc[0,item]\n",
    "        return new_columns\n",
    "    \n",
    "    def mean_encoding_tocolumn(self,features_df,categorical_column,target_column):\n",
    "        features_df=features_df[[categorical_column,target_column]]\n",
    "        grouped=features_df.groupby([categorical_column]).mean()\n",
    "        features_df=features_df.merge(grouped,on=categorical_column)\n",
    "        return features_df[f'{target_column}_y']\n",
    "    \n",
    "    def mean_encoding_todict(self,features_df,categorical_column,target_column):\n",
    "        features_df=features_df[[categorical_column,target_column]]\n",
    "        return features_df.groupby([categorical_column]).mean()\n",
    "    \n",
    "    def calculate_sum_volume(self,tweet_time,financial_data,time_col_name,volume_col_name,interval,amount):\n",
    "        from pytz import timezone\n",
    "        #remove seconds to match financial data format\n",
    "        tweet_time=tweet_time.replace(second=0)\n",
    "        \n",
    "        stock_exchg = self.mcal.get_calendar('NYSE', open_time=self.datetime.time(5, 30), close_time=self.datetime.time(12, 0))\n",
    "        daterange = stock_exchg.schedule('2009-05-04','2020-06-10')\n",
    "        try:\n",
    "            market_hours = daterange.loc[tweet_time.replace(hour=0, minute=0, second=0)]\n",
    "        except:\n",
    "            return self.np.nan\n",
    "        if len(market_hours)<2:\n",
    "            return self.np.nan\n",
    "        \n",
    "        def calculation(financial_data,time_col_name,time_forward,time_backward,volume_col_name,tweet_time):\n",
    "            forward_volume_sum=sum(financial_data.loc[(financial_data[time_col_name]<=time_forward) & \n",
    "                                        (financial_data[time_col_name]>=tweet_time)][volume_col_name])\n",
    "            backward_volume_sum=sum(financial_data.loc[(financial_data[time_col_name]<=tweet_time) & \n",
    "                                        (financial_data[time_col_name]>=time_backward)][volume_col_name])\n",
    "            if backward_volume_sum != 0:\n",
    "                return forward_volume_sum/backward_volume_sum-1\n",
    "            else:\n",
    "                return self.np.nan\n",
    "\n",
    "        if interval=='minutes':\n",
    "            time_forward=tweet_time + self.datetime.timedelta(0,0,0,0,amount)\n",
    "            time_backward=tweet_time - self.datetime.timedelta(0,0,0,0,amount)\n",
    "        elif interval=='hours':\n",
    "            time_forward=tweet_time + self.datetime.timedelta(0,0,0,0,0,amount)\n",
    "            time_backward=tweet_time - self.datetime.timedelta(0,0,0,0,0,amount)\n",
    "            \n",
    "        if ((time_forward.replace(tzinfo=timezone('UTC'))<=market_hours['market_close']) & \n",
    "        (time_backward.replace(tzinfo=timezone('UTC'))>=market_hours['market_open'])):\n",
    "            vol_sum=calculation(financial_data,time_col_name,time_forward, time_backward, volume_col_name,tweet_time)\n",
    "        else:\n",
    "            return self.np.nan\n",
    "\n",
    "        return self.pd.DataFrame({f'{amount}_{interval}_forward_vol_sum':vol_sum},index=[0])\n",
    "        \n",
    "    def create_volumesum_columns(self,twitter_data,tweet_time_col_name,financial_data,fin_time_col_name,volume_col_name,\n",
    "                       interval_amount_dict):\n",
    "        from tqdm import tqdm\n",
    "        new_columns=self.pd.DataFrame()\n",
    "        for i, row in tqdm(twitter_data.iterrows()):\n",
    "            temp_df=self.pd.DataFrame()\n",
    "            for interval, amount in interval_amount_dict:\n",
    "                new_df=self.calculate_sum_volume(twitter_data[tweet_time_col_name].loc[i],financial_data,fin_time_col_name,\n",
    "                                                   volume_col_name,interval,amount)\n",
    "                if not isinstance(new_df,float):\n",
    "                    temp_df=self.pd.concat([temp_df,new_df],axis=1)\n",
    "            for item in temp_df.columns:\n",
    "                new_columns.at[i,item] = temp_df.loc[0,item]\n",
    "        return new_columns\n",
    "    \n",
    "    def fill_missing_fin_data(self,ticker_col_name,stock_exchg_name,start_date,end_date,frequency,fin_time_colname,\n",
    "                          financial_data,volume_colname,price_colname):\n",
    "        ticker_name=financial_data.iloc[0][ticker_col_name]\n",
    "        stock_exchg = self.mcal.get_calendar(stock_exchg_name, open_time=self.datetime.time(5, 30), \n",
    "                                             close_time=self.datetime.time(12, 0))\n",
    "        daterange = stock_exchg.schedule(start_date, end_date)\n",
    "        dates=self.pd.DataFrame(self.mcal.date_range(daterange, frequency).tz_convert(None),columns=[fin_time_colname])\n",
    "\n",
    "#         financial_data[fin_time_colname] = self.pd.to_datetime(financial_data[fin_time_colname], utc = True)\n",
    "\n",
    "        financial_data=dates.merge(financial_data,on=fin_time_colname,how='left')\n",
    "        financial_data[ticker_col_name]=ticker_name\n",
    "\n",
    "        financial_data['SYM_SUFFIX'].fillna(0,inplace=True)\n",
    "\n",
    "        financial_data[volume_colname].fillna(0,inplace=True)\n",
    "        financial_data[price_colname].interpolate(inplace=True)\n",
    "        return financial_data\n",
    "    \n",
    "    def token_matrix(self,text_column,financial_topic_words):\n",
    "        \n",
    "        def check_for_word(token_vector,word):\n",
    "            if word in token_vector:\n",
    "                return 1\n",
    "            else:\n",
    "                return 0\n",
    "            \n",
    "        new_df=self.pd.DataFrame()\n",
    "        for topic_word in financial_topic_words:\n",
    "            new_df[topic_word]=text_column.apply(lambda x:check_for_word(x.lower().split(),topic_word))\n",
    "            \n",
    "        return new_df\n",
    "    \n",
    "    def diff_from_meanlog(self,df,date_colname,numeric_colname):\n",
    "        #This function returns the difference of logs between the original value and that month's average\n",
    "        ins_df=df[[date_colname,numeric_colname]].copy().set_index(date_colname,drop=True).astype(int).resample(\"M\").mean()\n",
    "        ins_df[numeric_colname]=self.np.log(ins_df[numeric_colname].replace(0, np.nan))\n",
    "        ins_df['month']=ins_df.index.map(str).str[:7]\n",
    "        df['month']=df[date_colname].map(str).str[:7]\n",
    "        averages=df[[date_colname,'month']].merge(ins_df,on='month',how='left')[numeric_colname]\n",
    "        diff=self.np.log(df[numeric_colname].replace(0, np.nan))-averages\n",
    "        return diff\n",
    "    \n",
    "    def create_random_zero_observations(self, twitter_data, time_column, number_of_random_obs):\n",
    "        max_time = twitter_data[time_column].max()\n",
    "        min_time = twitter_data[time_column].min()\n",
    "        delta = max_time - min_time\n",
    "        int_delta = (delta.days * 24 * 60 * 60) + delta.seconds\n",
    "        created_at = []\n",
    "        for i in range(number_of_random_obs):\n",
    "            random_second = self.random.randrange(int_delta)\n",
    "            new_time = min_time + self.datetime.timedelta(seconds=random_second)\n",
    "            created_at.append(new_time)\n",
    "        new_times_df = pd.DataFrame(created_at)\n",
    "        for column in twitter_data.columns:\n",
    "            new_times_df[column] = 0\n",
    "        new_times_df[time_column] = created_at\n",
    "        new_times_df.drop(0, axis = 1, inplace = True)\n",
    "        return new_times_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "financial_data=pd.read_csv('04052009-10062020-SPYDIA-minutedata.csv',parse_dates=['DATETIME'])\n",
    "twitter_data=pd.read_csv('vp_tweets_reducted_after_nlp.csv',index_col=0,parse_dates=['created_at'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sp500_data=financial_data[financial_data['SYM_ROOT']=='SPY']\n",
    "dow_data=financial_data[financial_data['SYM_ROOT']=='DIA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "vix_data = pd.read_csv('all_vix.csv', parse_dates=['DATETIME'])\n",
    "vix_data.drop(['OPEN', 'HIGH', 'LOW'], axis = 1, inplace = True)\n",
    "vix_data['TICKER'] = 'VIX'\n",
    "vix_data['VOLUME'] = 0\n",
    "vix_data['SYM_SUFFIX'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "feature_engine V.0.1 \n",
      "Imported pandas,datetime,pandas_market_calendars,numpy packages\n"
     ]
    }
   ],
   "source": [
    "feat_eng = feature_engine()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitter_data['retweet_count']=feat_eng.diff_from_meanlog(twitter_data,'created_at','retweet_count')\n",
    "twitter_data['favorite_count']=feat_eng.diff_from_meanlog(twitter_data,'created_at','favorite_count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitter_data = twitter_data.append(feat_eng.create_random_zero_observations(twitter_data, 'created_at', len(twitter_data)), \n",
    "                    ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sp500_data=feat_eng.fill_missing_fin_data('SYM_ROOT','NYSE','2009-05-04','2020-06-10','1min','DATETIME',\n",
    "                          sp500_data,'SIZE','PRICE')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dow_data=feat_eng.fill_missing_fin_data('SYM_ROOT','NYSE','2009-05-04','2020-06-10','1min','DATETIME',\n",
    "                          dow_data,'SIZE','PRICE')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "vix_data=feat_eng.fill_missing_fin_data('TICKER','NYSE','2009-05-04','2020-06-10','1min','DATETIME',\n",
    "                          vix_data,'VOLUME','CLOSE')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets_finance_volume=feat_eng.create_volumesum_columns(twitter_data[['created_at']],'created_at',\n",
    "                                                        dow_data[['DATETIME','SIZE']],'DATETIME','SIZE',\n",
    "                       [('minutes',1),('minutes',5),('minutes',10),('minutes',15),('minutes',30),('hours',1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "11314it [20:45,  9.09it/s]\n"
     ]
    }
   ],
   "source": [
    "tweets_finance=feat_eng.create_pricechg_columns(twitter_data[['created_at']],'created_at',vix_data,\n",
    "                                                'DATETIME','CLOSE',[('minutes',1),('minutes',5),('minutes',10),\n",
    "                                                                    ('minutes',15),('minutes',30),('hours',1),\n",
    "                                                                    ('hours',3)],\n",
    "                                                                    method='forward',logdiff = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitter_data_with_finance=twitter_data.join(tweets_finance)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitter_data_with_finance=twitter_data_with_finance.join(tweets_finance_volume)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# twitter_data_with_finance=twitter_data_with_finance.join(pd.get_dummies(twitter_data_with_finance['source']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitter_data=pd.DataFrame()\n",
    "financial_data=pd.DataFrame()\n",
    "dow_data=pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# twitter_data_with_finance=twitter_data_with_finance.drop(['source'],1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitter_data_with_finance.to_csv('vp_twitter_data_with_vix_for_analysis.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}