clear_data_1.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": [
    "userFeature = pd.read_csv('./userFeature.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature = pd.read_table(\"./ad_static_feature.out\",header=None)\n",
    "adStaticFeature_data = []\n",
    "with open('./ad_static_feature.out','r') as f:\n",
    "    attributeName = ['aid','createTime','auid','pid','pType','aIndustryId','size']\n",
    "    cnt = 0\n",
    "    for i, line in enumerate(f):\n",
    "        i = i + 1\n",
    "        line = line.strip().split('\\t')\n",
    "        adStaticFeature_dict = {}\n",
    "        \n",
    "        if (line[1]=='0') or (len(line)<7):#creat time is 0 and size is null is dirty data\n",
    "            continue\n",
    "        if ',' in line[5]:# mutilty aIndustryId is dirty data \n",
    "            continue\n",
    "            \n",
    "        j = 0\n",
    "        for each in line:\n",
    "            each_list = each.split(',')\n",
    "            adStaticFeature_dict[attributeName[j]] = ' '.join(each_list[0:])\n",
    "#             adStaticFeature_dict[attributeName[j]] = each\n",
    "            j += 1\n",
    "        adStaticFeature_data.append(adStaticFeature_dict)\n",
    "        # print(adStaticFeature_data)\n",
    "        if i % 100000 == 0:\n",
    "            print(i)\n",
    "        if i % 1000000 == 0:\n",
    "            ad_static_feature = pd.DataFrame(adStaticFeature_data)\n",
    "            ad_static_feature.to_csv('./adStaticFeature_' + str(cnt) + '.csv', index=False)\n",
    "            cnt += 1\n",
    "            del adStaticFeature_data,ad_static_feature\n",
    "            adStaticFeature_data = []\n",
    "    ad_static_feature = pd.DataFrame(adStaticFeature_data)\n",
    "    ad_static_feature.to_csv('./adStaticFeature_' + str(cnt) + '.csv', index=False)\n",
    "    del adStaticFeature_data,ad_static_feature\n",
    "    ad_static_feature = pd.concat([pd.read_csv('./adStaticFeature_' + str(k) + '.csv', low_memory=False) for k in range(cnt+1)]).reset_index(drop=True)\n",
    "    ad_static_feature.to_csv('./adStaticFeature.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_test=pd.read_csv('./adStaticFeature.csv',header=0) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "497669"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.aid = df_test.aid.astype(np.int64)#modify all the data format\n",
    "len(df_test.aid.unique())\n",
    "ad_operation = pd.read_table(\"./ad_operation.dat\",header=None)\n",
    "### time cost about 10 mins\n",
    "not_contain=[]\n",
    "def set_contain(big, small):\n",
    "    count = 0\n",
    "    for t in small:\n",
    "        if t not in big:\n",
    "            count += 1\n",
    "#             print(t, \"is not contained.\")\n",
    "            not_contain.append(t)\n",
    "#     print(\"{} are not contained.\".format(count))\n",
    "set_contain(df_test.aid.unique(), ad_operation[0].unique())\n",
    "# print(not_contain)\n",
    "# print(len(not_contain))\n",
    "adOperation_data = []\n",
    "df_test=pd.read_csv('./adStaticFeature.csv',header=0) \n",
    "# df_test.aid = df_test.aid.astype(np.str)\n",
    "len(df_test.aid.unique())\n",
    "with open('./ad_operation.dat','r') as f:\n",
    "    attributeName = ['aid','updateTime','operationType','changeType','newValue']\n",
    "    cnt = 0\n",
    "    for i, line in enumerate(f):\n",
    "        i = i + 1\n",
    "        line = line.strip().split('\\t')\n",
    "        adOperation_dict = {}\n",
    "        j = 0\n",
    "        print(i)\n",
    "        if int(line[0]) in not_contain:#the aid if is not appear in ad static ad feature is dirty data\n",
    "            continue\n",
    "        if line[1] == '20190230000000':#the 2.30 is not correct\n",
    "            continue\n",
    "            \n",
    "        for each in line:\n",
    "            adOperation_dict[attributeName[j]] = each\n",
    "            j += 1\n",
    "        adOperation_data.append(adOperation_dict)\n",
    "        # print(adOperation_data)\n",
    "        if i % 100000 == 0:\n",
    "            print(i)\n",
    "        if i % 1000000 == 0:\n",
    "            ad_operation = pd.DataFrame(adOperation_data)\n",
    "            ad_operation.to_csv('./adOperation_' + str(cnt) + '.csv', index=False)\n",
    "            cnt += 1\n",
    "            del adOperation_data,ad_operation\n",
    "            adOperation_data = []\n",
    "    ad_operation = pd.DataFrame(adOperation_data)\n",
    "    ad_operation=ad_operation[(ad_operation[1]%1000000)==0]\n",
    "    ad_operation.to_csv('./adOperation_' + str(cnt) + '.csv', index=False)\n",
    "    del adOperation_data,ad_operation\n",
    "    ad_operation = pd.concat([pd.read_csv('./adOperation_' + str(k) + '.csv', low_memory=False) for k in range(cnt+1)]).reset_index(drop=True)\n",
    "    ad_operation.to_csv('./adOperation.csv', index=False)\n",
    "# df_test.aid = df_test.aid.astype(np.int64)\n",
    "df_ad_operation=pd.read_csv('./adOperation.csv',header=0) \n",
    "df_ad_operation.info()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_exposure = pd.read_table(\"./totalExposureLog.out\",header=None)\n",
    "ad_exposure.head()\n",
    "# ad_exposure.info()\n",
    "ad_exposure.info()\n",
    "#time cost 40+mins\n",
    "adExposure_data = []\n",
    "with open('./totalExposureLog.out','r') as f:\n",
    "    attributeName = ['aRequestId','requestTime','adLocationId','uid','aid','size','adPrice','pctr','equality_ecpm','totalEcpm']\n",
    "    cnt = 0\n",
    "    for i, line in enumerate(f):\n",
    "        i = i + 1\n",
    "        line = line.strip().split('\\t')\n",
    "        adExposure_dict = {}\n",
    "        j = 0\n",
    "        for each in line:\n",
    "            each_list = each.split(',')\n",
    "            adExposure_dict[attributeName[j]] = ' '.join(each_list[0:])\n",
    "            j += 1\n",
    "        adExposure_data.append(adExposure_dict)\n",
    "        if i % 100000 == 0:\n",
    "            print(i)\n",
    "        if i % 1000000 == 0:\n",
    "            ad_exposure = pd.DataFrame(adExposure_data)\n",
    "            ad_exposure.to_csv('./adExposure_' + str(cnt) + '.csv', index=False)\n",
    "            cnt += 1\n",
    "            del adExposure_data,ad_exposure\n",
    "            adExposure_data = []\n",
    "    ad_exposure = pd.DataFrame(adExposure_data)\n",
    "    ad_exposure.to_csv('./adExposure_' + str(cnt) + '.csv', index=False)\n",
    "    del adExposure_data,ad_exposure\n",
    "    ad_exposure = pd.concat([pd.read_csv('./adExposure_' + str(k) + '.csv', low_memory=False) for k in range(cnt+1)]).reset_index(drop=True)\n",
    "    ad_exposure.to_csv('./adExposure.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adExposure = pd.read_csv('./adExposure.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adExposure = adExposure.drop_duplicates(['aRequestId','adLocationId','aid'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adStaticFeature_aid = pd.read_csv('./adStaticFeature.csv',header=0)\n",
    "adStaticFeature_aid = adStaticFeature_aid[['aid']]\n",
    "adStaticFeature_aid = adStaticFeature_aid.drop_duplicates('aid',keep='first', inplace=False)\n",
    "adOpration_aid = pd.read_csv('./adOperation.csv',header=0)\n",
    "adOpration_aid = adOpration_aid[['aid']]\n",
    "adOpration_aid = adOpration_aid.drop_duplicates('aid',keep='first', inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adExposure = adExposure[adExposure['aid'].isin(adOpration_aid['aid'].values)]\n",
    "adExposure = adExposure[adExposure['aid'].isin(adStaticFeature_aid['aid'].values)]\n",
    "adExposure.to_csv('./adExposureNew.csv', index=False)\n",
    "adExposure = pd.read_csv('./adExposureNew.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "userFeature_data = []\n",
    "with open('./user_data','r') as f:\n",
    "    attributeName = ['uid','age','gender','area','status','education','consuptionAbility','device','work','connectionType','behavior']\n",
    "    cnt = 0\n",
    "    for i, line in enumerate(f):\n",
    "        i = i + 1\n",
    "        line = line.strip().split('\\t')\n",
    "        userFeature_dict = {}\n",
    "        j = 0\n",
    "        for each in line:\n",
    "            each_list = each.split(',')\n",
    "            userFeature_dict[attributeName[j]] = ' '.join(each_list[0:])\n",
    "            j += 1\n",
    "        userFeature_data.append(userFeature_dict)\n",
    "        if i % 100000 == 0:\n",
    "            print(i)\n",
    "        if i % 1000000 == 0:\n",
    "            user_feature = pd.DataFrame(userFeature_data)\n",
    "            user_feature.to_csv('/Users/hanmin/Desktop/CSV/userFeature_' + str(cnt) + '.csv', index=False)\n",
    "            cnt += 1\n",
    "            del userFeature_data,user_feature\n",
    "            userFeature_data = []\n",
    "    user_feature = pd.DataFrame(userFeature_data)\n",
    "    user_feature.to_csv('./userFeature_' + str(cnt) + '.csv', index=False)\n",
    "    del userFeature_data,user_feature\n",
    "    user_feature = pd.concat([pd.read_csv('./userFeature_' + str(k) + '.csv', low_memory=False) for k in range(cnt+1)]).reset_index(drop=True)\n",
    "    user_feature.to_csv('./userFeature.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "testSample_data = []\n",
    "with open('./test_sample.dat','r') as f:\n",
    "    attributeName = ['tid','aid','createTime','size','aIndustryId','pType','pid','auid','exposureTime','crowd','price']\n",
    "    cnt = 0\n",
    "    for i, line in enumerate(f):\n",
    "        i = i + 1\n",
    "        line = line.strip().split('\\t')\n",
    "        testSample_dict = {}\n",
    "        j = 0\n",
    "        for each in line:\n",
    "            testSample_dict[attributeName[j]] = each\n",
    "            j += 1\n",
    "        testSample_data.append(testSample_dict)\n",
    "        if i % 100000 == 0:\n",
    "            print(i)\n",
    "        if i % 1000000 == 0:\n",
    "            test_sample = pd.DataFrame(testSample_data)\n",
    "            test_sample.to_csv('./testSample_' + str(cnt) + '.csv', index=False)\n",
    "            cnt += 1\n",
    "            testSample_data = []\n",
    "    test_sample = pd.DataFrame(testSample_data)\n",
    "    test_sample.to_csv('./testSample_' + str(cnt) + '.csv', index=False)\n",
    "    del testSample_data,test_sample\n",
    "    test_sample = pd.concat([pd.read_csv('./testSample_' + str(k) + '.csv', low_memory=False) for k in range(cnt+1)]).reset_index(drop=True)\n",
    "    test_sample.to_csv('./testSample.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "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": [
    "\n"
   ]
  },
  {
   "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": {
    "scrolled": true
   },
   "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": []
  },
  {
   "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": 10,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": []
  },
  {
   "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": []
  },
  {
   "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": 21,
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}