Chunk_TPV_filtrado_to-DB.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Al igual que con el fichero de tarjetas se hace un chunk para filtrar y limpiar los datos con los comercios que \n",
    "# se utilizaran por sector y codigos postales del proyecto, por el tamaño del fichero y al hacer la concatenacion no se \n",
    "#tiene sufuciente memoria, por tal motivo se realiza el mismo proceso para pasar el datframe filtrado a una base de datos\n",
    "#postgresql, se utiliza la misma libreria odo para optimizar el proceso de conversión, por otro lado se utiliza \n",
    "#postgresql porque es mas rapida que otras bases de datos como Mysql o sqlite3 en el proceso de conversion\n",
    "#podeis mirar en el siguiente enlace http://odo.pydata.org/en/latest/perf.html\n",
    "#La encriptación de las tarjetas se mantiene oculto por motivos de privacidad y sensibilidad del dato##"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Jose\\Miniconda3\\lib\\site-packages\\odo\\backends\\pandas.py:94: FutureWarning: pandas.tslib is deprecated and will be removed in a future version.\n",
      "You can access NaTType as type(pandas.NaT)\n",
      "  @convert.register((pd.Timestamp, pd.Timedelta), (pd.tslib.NaTType, type(None)))\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from odo import odo,chunks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "comercio_propio=pd.read_csv('C:/Users/Jose/Documents/Master Data Science/TFM/Comercios_propios.txt',sep=';',quotechar='\"'\\\n",
    "                           ,dtype={'Cod_comercio':str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Chunk Num 0\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Jose\\Miniconda3\\lib\\site-packages\\ipykernel\\__main__.py:7: FutureWarning: currently extract(expand=None) means expand=False (return Index/Series/DataFrame) but in a future version of pandas this will be changed to expand=True (return DataFrame)\n",
      "C:\\Users\\Jose\\Miniconda3\\lib\\site-packages\\ipykernel\\__main__.py:8: FutureWarning: currently extract(expand=None) means expand=False (return Index/Series/DataFrame) but in a future version of pandas this will be changed to expand=True (return DataFrame)\n",
      "C:\\Users\\Jose\\Miniconda3\\lib\\site-packages\\ipykernel\\__main__.py:9: FutureWarning: currently extract(expand=None) means expand=False (return Index/Series/DataFrame) but in a future version of pandas this will be changed to expand=True (return DataFrame)\n",
      "C:\\Users\\Jose\\Miniconda3\\lib\\site-packages\\ipykernel\\__main__.py:36: FutureWarning: currently extract(expand=None) means expand=False (return Index/Series/DataFrame) but in a future version of pandas this will be changed to expand=True (return DataFrame)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Chunk Num 1\n",
      "Chunk Num 2\n",
      "Chunk Num 3\n",
      "Chunk Num 4\n",
      "Chunk Num 5\n",
      "Chunk Num 6\n",
      "Chunk Num 7\n",
      "Chunk Num 8\n",
      "Chunk Num 9\n",
      "Chunk Num 10\n",
      "Chunk Num 11\n",
      "Chunk Num 12\n",
      "Chunk Num 13\n",
      "Chunk Num 14\n",
      "Chunk Num 15\n",
      "Chunk Num 16\n",
      "Chunk Num 17\n",
      "Chunk Num 18\n",
      "Chunk Num 19\n",
      "Chunk Num 20\n",
      "Chunk Num 21\n",
      "Chunk Num 22\n",
      "Chunk Num 23\n",
      "Chunk Num 24\n",
      "Chunk Num 25\n",
      "Chunk Num 26\n",
      "Chunk Num 27\n",
      "Chunk Num 28\n",
      "Chunk Num 29\n",
      "Chunk Num 30\n",
      "Chunk Num 31\n",
      "Chunk Num 32\n",
      "Chunk Num 33\n",
      "Chunk Num 34\n",
      "Chunk Num 35\n",
      "Chunk Num 36\n",
      "Chunk Num 37\n",
      "Chunk Num 38\n",
      "Chunk Num 39\n",
      "Chunk Num 40\n",
      "Chunk Num 41\n",
      "Chunk Num 42\n",
      "Chunk Num 43\n",
      "Chunk Num 44\n",
      "Chunk Num 45\n",
      "Chunk Num 46\n",
      "Chunk Num 47\n",
      "Chunk Num 48\n",
      "Chunk Num 49\n",
      "Chunk Num 50\n",
      "Chunk Num 51\n",
      "Chunk Num 52\n",
      "Chunk Num 53\n",
      "Chunk Num 54\n",
      "Chunk Num 55\n",
      "Chunk Num 56\n",
      "Chunk Num 57\n",
      "Chunk Num 58\n",
      "Chunk Num 59\n",
      "Chunk Num 60\n",
      "Chunk Num 61\n",
      "Chunk Num 62\n",
      "Chunk Num 63\n",
      "Chunk Num 64\n",
      "Chunk Num 65\n",
      "Chunk Num 66\n",
      "Chunk Num 67\n",
      "Chunk Num 68\n",
      "Chunk Num 69\n",
      "Chunk Num 70\n",
      "Chunk Num 71\n",
      "Chunk Num 72\n",
      "Chunk Num 73\n",
      "Chunk Num 74\n",
      "Chunk Num 75\n",
      "Chunk Num 76\n",
      "Chunk Num 77\n",
      "Chunk Num 78\n",
      "Chunk Num 79\n",
      "Chunk Num 80\n",
      "Chunk Num 81\n",
      "Chunk Num 82\n",
      "Chunk Num 83\n",
      "Chunk Num 84\n",
      "Chunk Num 85\n",
      "Chunk Num 86\n",
      "Chunk Num 87\n",
      "Chunk Num 88\n",
      "Chunk Num 89\n",
      "Chunk Num 90\n",
      "Chunk Num 91\n",
      "Chunk Num 92\n",
      "Chunk Num 93\n",
      "Chunk Num 94\n",
      "Chunk Num 95\n",
      "Chunk Num 96\n",
      "Chunk Num 97\n",
      "Chunk Num 98\n",
      "Chunk Num 99\n",
      "Chunk Num 100\n",
      "Chunk Num 101\n",
      "Chunk Num 102\n",
      "Chunk Num 103\n",
      "Chunk Num 104\n",
      "Chunk Num 105\n",
      "Chunk Num 106\n",
      "Chunk Num 107\n",
      "Chunk Num 108\n",
      "Chunk Num 109\n",
      "Chunk Num 110\n",
      "Chunk Num 111\n",
      "Chunk Num 112\n",
      "Chunk Num 113\n",
      "Chunk Num 114\n",
      "Chunk Num 115\n",
      "Chunk Num 116\n",
      "Chunk Num 117\n",
      "Chunk Num 118\n",
      "Chunk Num 119\n",
      "Chunk Num 120\n",
      "Chunk Num 121\n",
      "Chunk Num 122\n",
      "Chunk Num 123\n",
      "Chunk Num 124\n",
      "Chunk Num 125\n",
      "Wall time: 3h 36min 6s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "tpvs=pd.read_csv('C:/Users/Jose/Documents/Master Data Science/TFM/TPVs.txt',sep=';',decimal=','\\\n",
    "                    ,names=[\"Fecha\",\"Horas\",\"Cod_comercio\",\"Tarjeta\",\"Importe\"]\\\n",
    "                    ,dtype={'Fecha':str,'Horas':str,'Tarjeta':str,'Cod_comercio':str},low_memory=False,chunksize=1000000)\n",
    "all_chunk_results=pd.DataFrame()\n",
    "for (i, chunk) in enumerate(tpvs):\n",
    "    print(\"Chunk Num %d\"%i)\n",
    "    chunk.Fecha=chunk.Fecha.str.replace('^(\\ufeff)','')\n",
    "    b=chunk.Horas\n",
    "    h=b.str.extract('^(\\d{2})')\n",
    "    ms=b.str.extract('(\\d{4})$')\n",
    "    m=ms.str.extract('^(\\d{2})')\n",
    "    s='00'\n",
    "    time=h+\":\"+m+\":\"+s+\".\"+\"000\"\n",
    "    Date=chunk.Fecha+\" \"+time\n",
    "    Date=pd.to_datetime(Date, format=\"%Y-%m-%d %H:%M:%S.%f\")\n",
    "    Date_hour=pd.to_datetime(Date.dt.date) + pd.to_timedelta(Date.dt.hour, unit='H')\n",
    "    Hora=pd.to_datetime(Date_hour,format=\"%H:%M\").dt.time\n",
    "    Dia=Date_hour.dt.date\n",
    "    Dia_semana=Date_hour.dt.dayofweek\n",
    "    days={0:'Lun',1:'Mar',2:'Mie',3:'Jue',4:'Vie',5:'Sab',6:'Dom'}\n",
    "    Dia_semana=Dia_semana.apply(lambda x:days[x])\n",
    "    Mes=Date.apply(lambda x: x.month)\n",
    "    month={1:'Ene',2:'Feb',3:'Mar',4:'Abr',5:'May',6:'Jun',7:'Jul',8:'Ago',9:'Sep',10:'Oct',11:'Nov',12:'Dic'}\n",
    "    Mes=Mes.apply(lambda x:month[x])\n",
    "    g=Date.dt.year\n",
    "    t=chunk.Tarjeta\n",
    "    char=\"0123456789\"\n",
    "    encrip=\"XXXXXXXXXX\"\n",
    "    trans = str.maketrans(char, encrip)\n",
    "    t1=t.str.translate(trans)\n",
    "    chunk['Hora']=Hora\n",
    "    chunk['Dia']=Dia\n",
    "    chunk['Dia_semana']=Dia_semana\n",
    "    chunk['Cod_tarjeta']=t1\n",
    "    chunk['Mes']=Mes\n",
    "    chunk['Año']=g\n",
    "    chunk['Cod_comercio']=chunk.Cod_comercio.str.strip()\n",
    "    r=t1.str.extract('^(\\w{6})')\n",
    "    chunk['Bin']=r\n",
    "    chunk.drop('Tarjeta', axis=1, inplace=True)\n",
    "    chunk.drop('Fecha', axis=1, inplace=True)\n",
    "    chunk.drop('Horas', axis=1, inplace=True)\n",
    "    chunk1=pd.merge(chunk,comercio_propio,on='Cod_comercio',how='inner')\n",
    "    odo(chunk1,'postgresql://postgres:jose@localhost:5432/TFM::Ope_tvp',if_exists='append')\n",
    "    "
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}