草稿.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "raw count: 20250\n",
      "count after drop duplicates 4881\n",
      "count after drop less than 1000 4756\n",
      "count after drop 40, 50 4573\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "dfs = []\n",
    "for date in pd.date_range('2020-03-07', '2020-04-04'):\n",
    "    try:\n",
    "        df = pd.read_excel('data/chengjiao-北京/%s-北京.xls'%str(date.date()), converters = {'成交价(元/平)':lambda x:float(x),\n",
    "                                                                                 '链家编号':str})\n",
    "        dfs.append(df)\n",
    "    except:\n",
    "        pass\n",
    "df = pd.concat(dfs)\n",
    "df['单价（元/平米）'] = pd.to_numeric(df['单价（元/平米）'], errors='coerse')\n",
    "print('raw count:', len(df))\n",
    "df = df.drop_duplicates(subset=['链家编号'])\n",
    "print('count after drop duplicates', len(df))\n",
    "df = df.loc[df['单价（元/平米）']> 1000]\n",
    "print('count after drop less than 1000', len(df))\n",
    "df = df.loc[~df['产权年限'].str.contains('40')]\n",
    "df = df.loc[~df['产权年限'].str.contains('50')]\n",
    "print('count after drop 40, 50', len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2374"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df.loc[df['成交时间']>'2020-02'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2374"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new = df.loc[df['成交时间']>'2020-02']\n",
    "df_new = df_new.loc[df_new['单价（元/平米）']>5000]\n",
    "len(df_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2020-02-11', '2020-02-12', '2020-02-13', '2020-02-14',\n",
       "       '2020-02-15', '2020-02-16', '2020-02-17', '2020-02-18',\n",
       "       '2020-02-19', '2020-02-20', '2020-02-21', '2020-02-22',\n",
       "       '2020-02-23', '2020-02-24', '2020-02-25', '2020-02-26',\n",
       "       '2020-02-27', '2020-02-28', '2020-02-29', '2020-03-01',\n",
       "       '2020-03-02', '2020-03-03', '2020-03-04', '2020-03-05',\n",
       "       '2020-03-06', '2020-03-07', '2020-03-08', '2020-03-09',\n",
       "       '2020-03-10', '2020-03-11', '2020-03-12', '2020-03-13',\n",
       "       '2020-03-14', '2020-03-15', '2020-03-16', '2020-03-17',\n",
       "       '2020-03-18', '2020-03-19'], dtype=object)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a=df_new['成交时间'].unique()\n",
    "a.sort()\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "成交时间\n",
       "2020-02-11     11\n",
       "2020-02-12      9\n",
       "2020-02-13      6\n",
       "2020-02-14      8\n",
       "2020-02-15      7\n",
       "2020-02-16      8\n",
       "2020-02-17     14\n",
       "2020-02-18     20\n",
       "2020-02-19     21\n",
       "2020-02-20     28\n",
       "2020-02-21     14\n",
       "2020-02-22     23\n",
       "2020-02-23     43\n",
       "2020-02-24     42\n",
       "2020-02-25     53\n",
       "2020-02-26     53\n",
       "2020-02-27     47\n",
       "2020-02-28     47\n",
       "2020-02-29    125\n",
       "2020-03-01     22\n",
       "2020-03-02     59\n",
       "2020-03-03     53\n",
       "2020-03-04     61\n",
       "2020-03-05     52\n",
       "2020-03-06     42\n",
       "2020-03-07     50\n",
       "2020-03-08    102\n",
       "2020-03-09     87\n",
       "2020-03-10     99\n",
       "2020-03-11    108\n",
       "2020-03-12    102\n",
       "2020-03-13    107\n",
       "2020-03-14     97\n",
       "2020-03-15    172\n",
       "2020-03-16    193\n",
       "2020-03-17    114\n",
       "2020-03-18    162\n",
       "2020-03-19    113\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new.groupby('成交时间').size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "所属下辖区\n",
       "东城        71\n",
       "丰台       291\n",
       "亦庄开发区     32\n",
       "大兴       174\n",
       "密云         3\n",
       "平谷         1\n",
       "房山        40\n",
       "昌平       189\n",
       "朝阳       690\n",
       "海淀       296\n",
       "石景山      139\n",
       "西城       173\n",
       "通州       154\n",
       "门头沟       50\n",
       "顺义        71\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new.groupby('所属下辖区').size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "成交时间\n",
       "2020-02-11     2\n",
       "2020-02-12     1\n",
       "2020-02-13     5\n",
       "2020-02-14     1\n",
       "2020-02-15     1\n",
       "2020-02-16     1\n",
       "2020-02-17     2\n",
       "2020-02-18     8\n",
       "2020-02-19     8\n",
       "2020-02-20     8\n",
       "2020-02-21     5\n",
       "2020-02-22    10\n",
       "2020-02-23     9\n",
       "2020-02-24    11\n",
       "2020-02-25    17\n",
       "2020-02-26    12\n",
       "2020-02-27    15\n",
       "2020-02-28    16\n",
       "2020-02-29    38\n",
       "2020-03-01     7\n",
       "2020-03-02    21\n",
       "2020-03-03    11\n",
       "2020-03-04    15\n",
       "2020-03-05    17\n",
       "2020-03-06    11\n",
       "2020-03-07    17\n",
       "2020-03-08    34\n",
       "2020-03-09    29\n",
       "2020-03-10    29\n",
       "2020-03-11    31\n",
       "2020-03-12    27\n",
       "2020-03-13    23\n",
       "2020-03-14    25\n",
       "2020-03-15    44\n",
       "2020-03-16    49\n",
       "2020-03-17    40\n",
       "2020-03-18    50\n",
       "2020-03-19    40\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res=df_new.groupby(['所属下辖区','成交时间']).size()\n",
    "res['朝阳']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<a href=\"http://www.yeshiwei.com/fangjia/北京.html\"><img alt=\"北京\" src=\"http://www.yeshiwei.com/fig/北京/北京.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/上海.html\"><img alt=\"上海\" src=\"http://www.yeshiwei.com/fig/上海/上海.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/深圳.html\"><img alt=\"深圳\" src=\"http://www.yeshiwei.com/fig/深圳/深圳.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/杭州.html\"><img alt=\"杭州\" src=\"http://www.yeshiwei.com/fig/杭州/杭州.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/广州.html\"><img alt=\"广州\" src=\"http://www.yeshiwei.com/fig/广州/广州.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/长沙.html\"><img alt=\"长沙\" src=\"http://www.yeshiwei.com/fig/长沙/长沙.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/厦门.html\"><img alt=\"厦门\" src=\"http://www.yeshiwei.com/fig/厦门/厦门.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/宁波.html\"><img alt=\"宁波\" src=\"http://www.yeshiwei.com/fig/宁波/宁波.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/合肥.html\"><img alt=\"合肥\" src=\"http://www.yeshiwei.com/fig/合肥/合肥.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/成都.html\"><img alt=\"成都\" src=\"http://www.yeshiwei.com/fig/成都/成都.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/重庆.html\"><img alt=\"重庆\" src=\"http://www.yeshiwei.com/fig/重庆/重庆.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/武汉.html\"><img alt=\"武汉\" src=\"http://www.yeshiwei.com/fig/武汉/武汉.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/西安.html\"><img alt=\"西安\" src=\"http://www.yeshiwei.com/fig/西安/西安.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/石家庄.html\"><img alt=\"石家庄\" src=\"http://www.yeshiwei.com/fig/石家庄/石家庄.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/苏州.html\"><img alt=\"苏州\" src=\"http://www.yeshiwei.com/fig/苏州/苏州.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/南京.html\"><img alt=\"南京\" src=\"http://www.yeshiwei.com/fig/南京/南京.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/大连.html\"><img alt=\"大连\" src=\"http://www.yeshiwei.com/fig/大连/大连.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/青岛.html\"><img alt=\"青岛\" src=\"http://www.yeshiwei.com/fig/青岛/青岛.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/无锡.html\"><img alt=\"无锡\" src=\"http://www.yeshiwei.com/fig/无锡/无锡.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/保定.html\"><img alt=\"保定\" src=\"http://www.yeshiwei.com/fig/保定/保定.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/温州.html\"><img alt=\"温州\" src=\"http://www.yeshiwei.com/fig/温州/温州.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/廊坊.html\"><img alt=\"廊坊\" src=\"http://www.yeshiwei.com/fig/廊坊/廊坊.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/东莞.html\"><img alt=\"东莞\" src=\"http://www.yeshiwei.com/fig/东莞/东莞.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/天津.html\"><img alt=\"天津\" src=\"http://www.yeshiwei.com/fig/天津/天津.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/济南.html\"><img alt=\"济南\" src=\"http://www.yeshiwei.com/fig/济南/济南.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/郑州.html\"><img alt=\"郑州\" src=\"http://www.yeshiwei.com/fig/郑州/郑州.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/长春.html\"><img alt=\"长春\" src=\"http://www.yeshiwei.com/fig/长春/长春.png\"></a>\n",
      "<a href=\"http://www.yeshiwei.com/fangjia/济南.html\"><img alt=\"济南\" src=\"http://www.yeshiwei.com/fig/济南/济南.png\"></a>\n"
     ]
    }
   ],
   "source": [
    "import spider.setting as st\n",
    "for city in st.cityList:\n",
    "    print('<a href=\"http://www.yeshiwei.com/fangjia/%s.html\"><img alt=\"%s\" src=\"http://www.yeshiwei.com/fig/%s/%s.png\"></a>'%(city, city, city, city))"
   ]
  },
  {
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}