merge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging Forex Data\n",
    "This notebook takes all .csv files in the specified directory ('path' variable), keeps one column, names it accordingly, merges all date to one data frame. This is then saved as a pickle file for easy access."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "import glob\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = '/home/robert/forex/data/currency'\n",
    "extension = '.csv'\n",
    "tomerge = []\n",
    "import os\n",
    "for root, dirs, files in os.walk(path):\n",
    "    for file in files:\n",
    "        if file.endswith(extension):\n",
    "             tomerge.append(os.path.join(root, file))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['/home/robert/forex/data/currency/HISTDATA_COM_ASCII_USDJPY_M12009/DAT_ASCII_USDJPY_M1_2009.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_USDCAD_M12010/DAT_ASCII_USDCAD_M1_2010.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_EURUSD_M12010/DAT_ASCII_EURUSD_M1_2010.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_USDCAD_M12007/DAT_ASCII_USDCAD_M1_2007.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_USDCAD_M12008/DAT_ASCII_USDCAD_M1_2008.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_USDJPY_M12010/DAT_ASCII_USDJPY_M1_2010.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_GBPUSD_M12008/DAT_ASCII_GBPUSD_M1_2008.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_EURUSD_M12009/DAT_ASCII_EURUSD_M1_2009.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_GBPUSD_M12009/DAT_ASCII_GBPUSD_M1_2009.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_USDJPY_M12008/DAT_ASCII_USDJPY_M1_2008.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_USDJPY_M12007/DAT_ASCII_USDJPY_M1_2007.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_EURUSD_M12008/DAT_ASCII_EURUSD_M1_2008.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_EURUSD_M12007/DAT_ASCII_EURUSD_M1_2007.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_GBPUSD_M12010/DAT_ASCII_GBPUSD_M1_2010.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_USDCAD_M12009/DAT_ASCII_USDCAD_M1_2009.csv', '/home/robert/forex/data/currency/HISTDATA_COM_ASCII_GBPUSD_M12007/DAT_ASCII_GBPUSD_M1_2007.csv']\n"
     ]
    }
   ],
   "source": [
    "print(tomerge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "datatypes = {'one': 'float32',\n",
    "             'two': 'float32', \n",
    "             'three': 'float32',\n",
    "             'four': 'float32',\n",
    "             'five': 'int64'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parser(filename):\n",
    "    folders = filename.split('/')\n",
    "    info = folders[-1].split('.')\n",
    "    mods = info[0].split('_')\n",
    "    return '_'.join(mods[-3:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def dayaverage(df):\n",
    "    return df.groupby([df.index.month, df.index.day]).mean()\n",
    "\n",
    "def houraverage(df):\n",
    "    return df.groupby([df.index.month, df.index.day, df.index.hour]).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def readforex(filename, datatypes, avg=None):\n",
    "    colname = parser(filename)\n",
    "    df = pd.read_csv(filename, header=None, names=[colname, 'two', 'three', 'four', 'five'], sep=',|;|\\t\\ ', engine='python', dtype=np.float32)\n",
    "    df = df[[colname]]\n",
    "    df.index = pd.to_datetime(df.index)\n",
    "    if avg == 'day':\n",
    "        return dayaverage(df)\n",
    "    elif avg == 'hour':\n",
    "        return houraverage(df)\n",
    "    else:\n",
    "        return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "dflist = []\n",
    "for filename in tomerge:\n",
    "    dflist.append(readforex(filename, datatypes))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.concat(dflist, axis=1)\n",
    "outputfile = path + '/forex.pkl'\n",
    "output = open(outputfile, 'wb')\n",
    "pickle.dump(data, output)\n",
    "output.close()"
   ]
  },
  {
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}