{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#数据读入\n",
    "data_lm = pd.read_table('0430.txt',encoding='GBK')\n",
    "data_tm = pd.read_table('0520.txt',encoding='GBK')\n",
    "kuandai = pd.concat([data_lm,data_tm])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#创建中间表\n",
    "kd_tm = kuandai.loc[kuandai['入网时间']//100 == 201805]#本月数据\n",
    "kd_lm = kuandai.loc[(kuandai['入网时间']//100 == 201804)&(kuandai['入网时间'] % 100 <= 20)]#上月数据\n",
    "ec_tm = kd_tm.loc[kd_tm['十六大渠道'] == '电子渠道']#本月电子渠道数据\n",
    "ec_lm = kd_lm.loc[kd_lm['十六大渠道'] == '电子渠道']#上月电子渠道数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#计算当月宽带业务发展量\n",
    "rst = ec_tm.groupby('分公司')['统计值'].sum()\n",
    "rst.name = '发展量'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "分公司\n",
       "东莞    0.889671\n",
       "中山    1.392544\n",
       "云浮    0.250000\n",
       "佛山    1.091286\n",
       "广州    1.034507\n",
       "惠州    1.142857\n",
       "揭阳         NaN\n",
       "梅州    1.038462\n",
       "汕头    0.588235\n",
       "汕尾    0.583333\n",
       "江门    0.578313\n",
       "河源    0.400000\n",
       "深圳    0.967887\n",
       "清远    1.684211\n",
       "湛江    1.444444\n",
       "潮州    0.175676\n",
       "珠海    0.882937\n",
       "肇庆    1.260870\n",
       "茂名    0.888889\n",
       "阳江    0.800000\n",
       "韶关    0.725962\n",
       "Name: 环比, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算发展量环比\n",
    "rst_huanbi = rst / ec_lm.groupby('分公司')['统计值'].sum()\n",
    "rst_huanbi.name = \"环比\"\n",
    "rst_huanbi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "分公司\n",
       "东莞    0.268549\n",
       "中山    0.091144\n",
       "云浮    0.000397\n",
       "佛山    0.012738\n",
       "广州    0.030841\n",
       "惠州    0.075696\n",
       "揭阳    0.000659\n",
       "梅州    0.004713\n",
       "汕头    0.001327\n",
       "汕尾    0.002309\n",
       "江门    0.006345\n",
       "河源    0.000546\n",
       "深圳    0.085430\n",
       "清远    0.004692\n",
       "湛江    0.002500\n",
       "潮州    0.006424\n",
       "珠海    0.073724\n",
       "肇庆    0.005881\n",
       "茂名    0.001870\n",
       "阳江    0.000576\n",
       "韶关    0.024946\n",
       "Name: 渠道占比, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算渠道占比\n",
    "rst_zhanbi = rst / kd_tm.groupby('分公司')['统计值'].sum()\n",
    "rst_zhanbi.name = \"渠道占比\"\n",
    "rst_zhanbi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>发展量</th>\n",
       "      <th>环比</th>\n",
       "      <th>渠道占比</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>分公司</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>东莞</th>\n",
       "      <td>6193.0</td>\n",
       "      <td>0.889671</td>\n",
       "      <td>0.268549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>中山</th>\n",
       "      <td>1270.0</td>\n",
       "      <td>1.392544</td>\n",
       "      <td>0.091144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云浮</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.000397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>佛山</th>\n",
       "      <td>263.0</td>\n",
       "      <td>1.091286</td>\n",
       "      <td>0.012738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广州</th>\n",
       "      <td>1469.0</td>\n",
       "      <td>1.034507</td>\n",
       "      <td>0.030841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>惠州</th>\n",
       "      <td>840.0</td>\n",
       "      <td>1.142857</td>\n",
       "      <td>0.075696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>揭阳</th>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>梅州</th>\n",
       "      <td>27.0</td>\n",
       "      <td>1.038462</td>\n",
       "      <td>0.004713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>汕头</th>\n",
       "      <td>10.0</td>\n",
       "      <td>0.588235</td>\n",
       "      <td>0.001327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>汕尾</th>\n",
       "      <td>7.0</td>\n",
       "      <td>0.583333</td>\n",
       "      <td>0.002309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江门</th>\n",
       "      <td>48.0</td>\n",
       "      <td>0.578313</td>\n",
       "      <td>0.006345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河源</th>\n",
       "      <td>2.0</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.000546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>深圳</th>\n",
       "      <td>3647.0</td>\n",
       "      <td>0.967887</td>\n",
       "      <td>0.085430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>清远</th>\n",
       "      <td>32.0</td>\n",
       "      <td>1.684211</td>\n",
       "      <td>0.004692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湛江</th>\n",
       "      <td>13.0</td>\n",
       "      <td>1.444444</td>\n",
       "      <td>0.002500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>潮州</th>\n",
       "      <td>39.0</td>\n",
       "      <td>0.175676</td>\n",
       "      <td>0.006424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>珠海</th>\n",
       "      <td>445.0</td>\n",
       "      <td>0.882937</td>\n",
       "      <td>0.073724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>肇庆</th>\n",
       "      <td>29.0</td>\n",
       "      <td>1.260870</td>\n",
       "      <td>0.005881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>茂名</th>\n",
       "      <td>8.0</td>\n",
       "      <td>0.888889</td>\n",
       "      <td>0.001870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>阳江</th>\n",
       "      <td>4.0</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>0.000576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>韶关</th>\n",
       "      <td>151.0</td>\n",
       "      <td>0.725962</td>\n",
       "      <td>0.024946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        发展量        环比      渠道占比\n",
       "分公司                            \n",
       "东莞   6193.0  0.889671  0.268549\n",
       "中山   1270.0  1.392544  0.091144\n",
       "云浮      1.0  0.250000  0.000397\n",
       "佛山    263.0  1.091286  0.012738\n",
       "广州   1469.0  1.034507  0.030841\n",
       "惠州    840.0  1.142857  0.075696\n",
       "揭阳      4.0       NaN  0.000659\n",
       "梅州     27.0  1.038462  0.004713\n",
       "汕头     10.0  0.588235  0.001327\n",
       "汕尾      7.0  0.583333  0.002309\n",
       "江门     48.0  0.578313  0.006345\n",
       "河源      2.0  0.400000  0.000546\n",
       "深圳   3647.0  0.967887  0.085430\n",
       "清远     32.0  1.684211  0.004692\n",
       "湛江     13.0  1.444444  0.002500\n",
       "潮州     39.0  0.175676  0.006424\n",
       "珠海    445.0  0.882937  0.073724\n",
       "肇庆     29.0  1.260870  0.005881\n",
       "茂名      8.0  0.888889  0.001870\n",
       "阳江      4.0  0.800000  0.000576\n",
       "韶关    151.0  0.725962  0.024946"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#合并各字段\n",
    "result = pd.concat([rst,rst_huanbi,rst_zhanbi],axis=1)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>发展量</th>\n",
       "      <th>环比</th>\n",
       "      <th>渠道占比</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>分公司</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>深圳</th>\n",
       "      <td>3647.0</td>\n",
       "      <td>0.967887</td>\n",
       "      <td>0.085430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广州</th>\n",
       "      <td>1469.0</td>\n",
       "      <td>1.034507</td>\n",
       "      <td>0.030841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>佛山</th>\n",
       "      <td>263.0</td>\n",
       "      <td>1.091286</td>\n",
       "      <td>0.012738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>东莞</th>\n",
       "      <td>6193.0</td>\n",
       "      <td>0.889671</td>\n",
       "      <td>0.268549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>中山</th>\n",
       "      <td>1270.0</td>\n",
       "      <td>1.392544</td>\n",
       "      <td>0.091144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>惠州</th>\n",
       "      <td>840.0</td>\n",
       "      <td>1.142857</td>\n",
       "      <td>0.075696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江门</th>\n",
       "      <td>48.0</td>\n",
       "      <td>0.578313</td>\n",
       "      <td>0.006345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>珠海</th>\n",
       "      <td>445.0</td>\n",
       "      <td>0.882937</td>\n",
       "      <td>0.073724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>汕头</th>\n",
       "      <td>10.0</td>\n",
       "      <td>0.588235</td>\n",
       "      <td>0.001327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>揭阳</th>\n",
       "      <td>4.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>潮州</th>\n",
       "      <td>39.0</td>\n",
       "      <td>0.175676</td>\n",
       "      <td>0.006424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>汕尾</th>\n",
       "      <td>7.0</td>\n",
       "      <td>0.583333</td>\n",
       "      <td>0.002309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湛江</th>\n",
       "      <td>13.0</td>\n",
       "      <td>1.444444</td>\n",
       "      <td>0.002500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>茂名</th>\n",
       "      <td>8.0</td>\n",
       "      <td>0.888889</td>\n",
       "      <td>0.001870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>阳江</th>\n",
       "      <td>4.0</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>0.000576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云浮</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.000397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>肇庆</th>\n",
       "      <td>29.0</td>\n",
       "      <td>1.260870</td>\n",
       "      <td>0.005881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>梅州</th>\n",
       "      <td>27.0</td>\n",
       "      <td>1.038462</td>\n",
       "      <td>0.004713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>清远</th>\n",
       "      <td>32.0</td>\n",
       "      <td>1.684211</td>\n",
       "      <td>0.004692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河源</th>\n",
       "      <td>2.0</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.000546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>韶关</th>\n",
       "      <td>151.0</td>\n",
       "      <td>0.725962</td>\n",
       "      <td>0.024946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        发展量        环比      渠道占比\n",
       "分公司                            \n",
       "深圳   3647.0  0.967887  0.085430\n",
       "广州   1469.0  1.034507  0.030841\n",
       "佛山    263.0  1.091286  0.012738\n",
       "东莞   6193.0  0.889671  0.268549\n",
       "中山   1270.0  1.392544  0.091144\n",
       "惠州    840.0  1.142857  0.075696\n",
       "江门     48.0  0.578313  0.006345\n",
       "珠海    445.0  0.882937  0.073724\n",
       "汕头     10.0  0.588235  0.001327\n",
       "揭阳      4.0  0.000000  0.000659\n",
       "潮州     39.0  0.175676  0.006424\n",
       "汕尾      7.0  0.583333  0.002309\n",
       "湛江     13.0  1.444444  0.002500\n",
       "茂名      8.0  0.888889  0.001870\n",
       "阳江      4.0  0.800000  0.000576\n",
       "云浮      1.0  0.250000  0.000397\n",
       "肇庆     29.0  1.260870  0.005881\n",
       "梅州     27.0  1.038462  0.004713\n",
       "清远     32.0  1.684211  0.004692\n",
       "河源      2.0  0.400000  0.000546\n",
       "韶关    151.0  0.725962  0.024946"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#地市排序\n",
    "dishi = ['深圳','广州','佛山','东莞',\n",
    "         '中山','惠州','江门','珠海',\n",
    "         '汕头','揭阳','潮州','汕尾',\n",
    "         '湛江','茂名','阳江','云浮',\n",
    "         '肇庆','梅州','清远','河源','韶关']\n",
    "result.reindex(index=dishi).fillna(0)"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}