自从前几天爬了boss直聘7000+职位信息后,一直停滞不前,遇到了几个坑,今天算是重新启动了,感谢亚马逊!
https://geoseis.cn/CoV_tab.html
自适应各种平台显示,新版脚本也格式化成了标签形式。
根据我的爬虫脚本实时监控数据可视化,美国新增病历继续高歌猛进,闭着眼睛都能想到美股或者原油又崩盘了。目前为止161872累计确诊病例,但是不得不说,这个数据应该经得住敲打。
今天我的boss直聘脚本已经突破了1000行,主要还是因为脚本考虑的太周全,超时处理,没有找到相关职位的空页面处理,遇到机器人反爬处理,各种代理设置,反爬策略,多种日志并进(sql和csv),不知不觉就几百上千行了。如果用scrapy这种框架会少很多,不用自己造轮子。

疫情监控脚本可视化
一小部分boss直聘数据

废寝忘食地搞了两天,终于搞明白了几个服务。最近天天想方设法寻找合适的ip代理了,现在发现脚本写的越来越长不仅不方便维护,效率也下来了,有时间得整改下,套用下成熟的框架试试。
昨天发现新大陆,昨天晚上连夜研究,今天终于把脚本写好了,利用亚马逊服务器可以虽然更换ip的功能,调用它的api,用python写了个脚本每个两分钟更换一次ip,其实挺简单,就是release, allocate, attach,核心就三步,扔掉原来ip,分配新ip,新ip分配给服务器。当然,其中还有从远程服务器删除旧ip等过程。
美国服务器的ip更新后自动存到远在日本的服务器(上面架设了redis服务),笔记本配置好redis客户端后python脚本连接日本的服务器redis,获取ip。
看似简单,实际操作中还是挺繁琐,很多坑要踩。当然,最重要的是过程,国外的服务器还是不适合,gfw的过滤明显减慢速度。
这两天还配置了squid服务器,但是不知怎么不太好使,可能跟阿布云代理有关。数据没采集多少,💰 没少花,时间更是大把浪费了。

SQL处理数据-字符串和重复数据处理

mysql重复数据和字符串处理

因为爬取的wikipedia数据不规范,写入MySQL数据库时没能定义好Primary Key和字符类型,后来发现主要是有几个小问题:

  • 地区人口一栏中有不少非数字(县级市比如保定市),城市一栏因为这个原因也是有重复值(后来删掉了县级市,不需要)
  • 香港澳门城镇人口一栏缺失值,城市一栏有中括号

NULL值更新成0

mysql> UPDATE cities_population
-> SET
-> urban_population=0
-> WHERE urban_population IS null;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

删除县级市

mysql> DELETE FROM cities_population WHERE class='县级市';
Query OK, 772 rows affected (0.01 sec)

删除个别城市名称后的一个字符

mysql> UPDATE cities_population
-> SET city=CONCAT(LEFT(city, CHAR_LENGTH(city) -1), '')
-> WHERE city NOT LIKE '%市' AND city NOT LIKE '香港%' AND city NOT LIKE '澳门%';
Query OK, 12 rows affected (0.00 sec)
Rows matched: 12 Changed: 12 Warnings: 0

可以看到香港澳门城市名称里的中括号

mysql> SELECT * FROM cities_population WHERE city LIKE '%[%';
+-------+----------+-----------+------------+------------------+------------------+------------------+-------------+
| index | province | city | class | total_population | admin_population | urban_population | report_time |
+-------+----------+-----------+------------+------------------+------------------+------------------+-------------+
| 378 | 香港 | 香港 [2] | 特别行政区 | 7071576 | 7071576 | NULL | 2011-06-30 |
| 379 | 澳门 | 澳门 [3] | 特别行政区 | 552503 | 552503 | NULL | 2011-08-12 |
| 468 | 香港 | 香港 [5] | 特别行政区 | 6708389 | 6708389 | NULL | 2001-03-14 |
| 469 | 澳门 | 澳门 [6] | 特别行政区 | 435235 | 435235 | NULL | 2001-08-23 |
+-------+----------+-----------+------------+------------------+------------------+------------------+-------------+
4 rows in set (0.00 sec)

去掉中括号:

mysql> UPDATE cities_population
-> SET
-> city=province
-> WHERE city LIKE '%[%';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

检查城市名称重复次数:

mysql> SELECT COUNT(*) as repetitions,city
-> FROM cities_population
-> GROUP BY city
-> HAVING repetitions > 1;

mysql> SELECT * FROM cities_population
-> WHERE 城市 LIKE '%香港%';
+------+-----------+------------+-----------------+-------------------+-----------------+------------+
| 省市 | 城市 | 层级 | 地区 人口 [1] | 市辖区 人口 [1] | 城镇 人口 [1] | 普查年月日 |
+------+-----------+------------+-----------------+-------------------+-----------------+------------+
| 香港 | 香港 [2] | 特别行政区 | 7071576 | 7071576 | NULL | 2011-06-30 |
+------+-----------+------------+-----------------+-------------------+-----------------+------------+

可以看到县级市:

mysql> SELECT * FROM cities_population LIMIT 10;
+------+----------+--------+-----------------+-------------------+-----------------+------------+
| 省市 | 城市 | 层级 | 地区 人口 [1] | 市辖区 人口 [1] | 城镇 人口 [1] | 普查年月日 |
+------+----------+--------+-----------------+-------------------+-----------------+------------+
| 北京 | 北京市 | 直辖市 | 19612368 | 18827262 | 16446857 | 2010-11-01 |
| 天津 | 天津市 | 直辖市 | 12938693 | 11090783 | 9562255 | 2010-11-01 |
| 河北 | 石家庄市 | 地级市 | 10163788 | 2834942 | 2770344 | 2010-11-01 |
| 河北 | 唐山市 | 地级市 | 7577289 | 3187171 | 2128191 | 2010-11-01 |
| 河北 | 秦皇岛市 | 地级市 | 2987605 | 1029670 | 967877 | 2010-11-01 |
| 河北 | 邯郸市 | 地级市 | 9174683 | 1445338 | 1316674 | 2010-11-01 |
| 河北 | 邢台市 | 地级市 | 7104103 | 670154 | 668765 | 2010-11-01 |
| 河北 | 保定市 | 地级市 | 11194382 | 1138521 | 1038195 | 2010-11-01 |
| 河北 | 定州市 | 县级市 | 保定市 | 1165182 | 482121 | 2010-11-01 |
| 河北 | 张家口市 | 地级市 | 4345485 | 1060605 | 924628 | 2010-11-01 |
+------+----------+--------+-----------------+-------------------+-----------------+------------+

可以看到数据类型问题,即使命令行先创建也不行(可能是因为脚本里写入SQL我用了replace)

mysql> DESC cities_population;
+------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| province | text | YES | | NULL | |
| city | text | YES | | NULL | |
| class | text | YES | | NULL | |
| total_population | text | YES | | NULL | |
| admin_population | bigint(20) | YES | | NULL | |
| urban_population | text | YES | | NULL | |
| report_time | text | YES | | NULL | |
+------------------+------------+------+-----+---------+-------+

处理香港澳门城市名称问题:

mysql> UPDATE cities_population
-> SET city=CONCAT(LEFT(city, CHAR_LENGTH(city) -1), '')
-> WHERE city NOT LIKE '%市' AND city NOT LIKE '香港%' AND city NOT LIKE '澳门%';
Query OK, 12 rows affected (0.00 sec)
Rows matched: 12 Changed: 12 Warnings: 0

修改字符类型:

mysql> DESC cities_population;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| index            | bigint(20)  | YES  | MUL | NULL    |       |
| province         | text        | YES  |     | NULL    |       |
| city             | varchar(20) | NO   |     | NULL    |       |
| class            | text        | YES  |     | NULL    |       |
| total_population | text        | YES  |     | NULL    |       |
| admin_population | bigint(20)  | YES  |     | NULL    |       |
| urban_population | text        | YES  |     | NULL    |       |
| report_time      | text        | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> ALTER TABLE cities_population
    -> MODIFY province VARCHAR(20) NOT NULL,
    -> MODIFY class VARCHAR(20) NOT NULL,
    -> MODIFY total_population FLOAT NOT NULL,
    -> MODIFY urban_population FLOAT NOT NULL,
    -> MODIFY report_time DATE;
Query OK, 878 rows affected, 2 warnings (0.03 sec)
Records: 878  Duplicates: 0  Warnings: 2

mysql> DESC cities_population;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| index            | bigint(20)  | YES  | MUL | NULL    |       |
| province         | varchar(20) | NO   |     | NULL    |       |
| city             | varchar(20) | NO   |     | NULL    |       |
| class            | varchar(20) | NO   |     | NULL    |       |
| total_population | float       | NO   |     | NULL    |       |
| admin_population | bigint(20)  | YES  |     | NULL    |       |
| urban_population | float       | NO   |     | NULL    |       |
| report_time      | date        | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> CREATE TABLE tmp SELECT DISTINCT *  FROM  cities_population;
Query OK, 691 rows affected (0.02 sec)
Records: 691  Duplicates: 0  Warnings: 0

mysql> DROP TABLE cities_population;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE tmp RENAME TO  cities_population;
Query OK, 0 rows affected (0.01 sec)
这样就删除了重复数据:
mysql> ALTER TABLE cities_population
    -> ADD PRIMARY KEY(city,report_time);
Query OK, 691 rows affected (0.02 sec)
Records: 691  Duplicates: 0  Warnings: 0

mysql> DESC cities_population;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| index            | bigint(20)  | YES  |     | NULL    |       |
| province         | varchar(20) | NO   |     | NULL    |       |
| city             | varchar(20) | NO   | PRI | NULL    |       |
| class            | varchar(20) | NO   |     | NULL    |       |
| total_population | float       | NO   |     | NULL    |       |
| admin_population | bigint(20)  | YES  |     | NULL    |       |
| urban_population | float       | NO   |     | NULL    |       |
| report_time      | date        | NO   | PRI | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

肺炎疫情-用数据分析的角度来看我们的医疗

领英也分享过。

能用数据说话咱就不BB!
通过这次数据分析,能清晰地看到我们平时的有些认知和事实是有差距的。三甲医院总数辽宁95,广东89,湖北88,山东82,江苏74,黑龙江70,河南北京60,天津55。按照人均医院,天津稳居第一,北京上海辽宁排名也很靠前,青海这个黑马(西藏因为人少也很有优势)我没去核实数据。
从数据上看,天津北上辽是养老居家圣地,辽宁作为老工业基地的底子现在看来仍旧不可小觑。数据上看东北养老还是不错的,冬天就去海南嘛。如果不差钱,广州应该是个养老的好地方,医疗水平高,环境也不错,要知道老人都是怕冷的。所以说,好好赚钱,争取在广东养老才是正道。这次疫情的焦点湖北省医疗资源一点不差,昨晚(其实昨晚没睡)没来得及做完城市数据可视化,武汉的三甲医院(46家)也是第一排的,所以不是武汉撑不住,任何一个正常的医疗系统都不可能承担这么严重的疫情。最牛的天津也是23万人才有一家三甲医院好嘛!

全国三甲医院数据分析-pyecharts动态图表可视化

1200+三甲医院地理坐标(百度地图API),数据处理和分析,pyecharts数据可视化

https://www.linkedin.com/feed/update/urn:li:activity:6642160429646868480

https://lnkd.in/f78_A3T
Within one or days, I did ETL on 1200+ top hospitals in China. 30000hospitals of which 3000 were tier 3. Only 1200+ tier 3 Top Class.
Data crawling was done a few weeks ago. Recently I put the data into mysql. Then I did data cleaning and processing with MySQL and python. Finally data visualization was done with Pyecharts.
Feel free to review it on my website. It’s in html tabs and easy to view. Very interactive Charts!
To your surprise, Wuhan or Hubei has one of the best medical resources in China. Virus like this is beyond capability of any emergency systems in any countries.
嗯,天快亮了。花了一两天时间把全国1200+三甲医院的数据(全国3万家左右医院,提取出不到3000三级,三甲其实才1200+)提取出来(在我这个链接里你可以看到每家三甲医院的地理位置和电话),处理分析了下,一共12张动态图,做成了html,欢迎大家打开链接观赏下,不看你会后悔的,开心一笑!
大家一定要打开网页看下,手机调成横屏。一共12个标签页,因为这样更方便看图。有时间我再出个简单的分析报告和处理流程,放在自己的网站。你的点击能助力那些想学习数据分析而又找不到敲门的朋友找到我的网站[Shake]如果有朋友懂得运营,欢迎指点迷津,有志于做个高质量的技术博客。
这些漂亮的动态图之后是几十个小时的数据处理,这两天代码敲的都快折了!一共700-800行代码,包括爬虫,导入数据库(mysql里也做了些简单的分析处理),数据清洗,数据处理,数据分析和可视化。用到的工具主要是python,pyecharts,json,mysql。
睡个觉,该起来吃早饭了,忙的一天只吃了一顿饭。

全国三甲医院分析-Pyecharts自定义坐标(json_file)

为pyecharts定制json文件,自定义坐标

虽然Pyecharts很多槽点,但是目前还没找到替代品,换新工具也有时间成本(沉没成本?)。之前爬虫爬取的全国三万家医院信息放在了mysql,最近在休假正好有空拿来处理下做个分析。这两天主要是学习下json包的使用,因为pyecharts的自定义坐标用json文件比较方便:

从SQL读取并将关键信息写入新的sql table

def generate_mysql():
    conn = pymysql.connect(
        host='localhost',
        user='username',
        password='passwd',
        port=3306,
        charset = 'UTF8MB4',  
        db = 'hospital_list')
    cursor = conn.cursor()

    sql = 'CREATE TABLE IF NOT EXISTS hospital_coord (hospital VARCHAR(30),\
    provinces VARCHAR(15), cities VARCHAR(15), location VARCHAR(30) ,phone VARCHAR(30), PRIMARY KEY (hospital))'
    # hospital_list是要在数据库中建立的表,用于存放数据
    
    cursor.execute(sql)
    conn.close()
def read_mysql(db):
    Engine = create_engine('mysql+pymysql://user:[email protected]:3306/{0}?charset=UTF8MB4'.format(db))
    con = Engine.connect()
    #提取hospital_name_new, Provinces, Cities, address, phone
    df = pd.read_sql("""SELECT hospital_name_new, Provinces, Cities, address, phone
    FROM top_tier3_hospital ORDER BY Cities ASC """, con)
#    print(type(df['Cities', 'address']))
#    print(df['address'])
    for m in range(0, len(df[ 'address'])):
        df[ 'address'][m] = str(df[ 'address'][m]).split()[0]
        if(len(str(df[ 'address'][m]).split()) > 1):
            print(df[ 'address'])
    
    return df['hospital_name_new'], df['Provinces'], df['Cities'], df[ 'address'], df['phone']
    con.close()
#hospital, provinces, cities, location, address, phone 
        
def write_to_sql(data_frame, db = 'hospital_list'):
    engine = create_engine('mysql+pymysql://user:[email protected]:3306/{0}?charset=UTF8MB4'.format(db))
    try:
        # df = pd.read_csv(df)
        data_frame.to_sql('hospital_coord',con = engine,if_exists='append',index=False)
        # append表示在原有表基础上增加,但该表要有表头(header)
    except Exception as e:
        print(e)       
      

这部分比较熟练了,但是还是有几点需要注意:

  • primary key的使用,防止重复数据
  • 处理不规范数据,比如之前地址栏有两个地址(分号隔开),医院名称也有别称(括号内),个别医院地址找不到返回的无效坐标(0,0)-这个会在后面的程序中带来很多麻烦,比如重复数据,None类型的这种数据会带来意外的问题。
  • SQL命令熟悉了可以方便查看一些数据问题,否则还得写代码,即使是python也很浪费时间,不如命令行来的方便。比如这次产生的None类型数据,重复医院名称,很容易在命令行查到,如果用Python大概率要写代码循环判断。
  • 常用的几个命令:
SELECT * FROM top_tier3_hospital WHERE hospital_name_new LIKE '%枣庄%' and hospital_tier LIKE '三级甲等%';
SELECT COUNT(*) FROM hospital_coord;
SELECT * FROM top_tier3_hospital WHERE address LIKE "%;%";
SELECT * FROM top_tier3_hospital LIMIT 20;

mysql>  SELECT * FROM hospital_list_table
    ->  WHERE hospital_name_new='南京长江医院第三医院' or  hospital_name_new='孝感市人民医院';
数据查重:
mysql>  SELECT COUNT(*) as repetitions, hospital_name_new
    ->  FROM hospital_list_table
    -> GROUP BY hospital_name_new
    ->  HAVING repetitions > 1;

xml转json:

  • 百度API返回的页面没有xml的declaration,但是Python中requests的response却是有的,这个declaration最好去掉:
  • 百度的返回:
<GeocoderSearchResponse>
<status>OK</status>
<result>
<location>
<lat>33.082054</lat>
<lng>107.022983</lng>
</location>
<precise>1</precise>
<confidence>80</confidence>
<level>门址</level>
</result>
</GeocoderSearchResponse>
#定义xml转json的函数
def xmltojson(xmlstr):
    #parse是的xml解析器
    xmlparse = xmltodict.parse(xmlstr)
    #json库dumps()是将dict转化成json格式,loads()是将json转化成dict格式。
    #dumps()方法的ident=1,格式化json
    jsonstr = json.dumps(xmlparse,indent=1)
    return jsonstr
    #print(jsonstr)

百度API调取坐标

这个函数是用来从百度调取具体地点的坐标的,申请API之后就有了这个ak密钥。虽然百度限制宽松,但是我还是限制了使用频率,所以程序会跑300多秒。另外,加上headers和except容错机制,不然肯定会有几个地点让程序崩溃。需要说明的是,ExpatError这个是需要在程序开头import相关的包的,用了捕捉出现的错误,一个健壮的程序必不可少。

容错语句里我傻傻的加上了错误的地方返回(0,0),其实这个后来给我带来了麻烦。我没有重写这段,只是在后来把产生的(0,0)去掉了。不过,不返回一个值我觉得会有问题,给它一个确定的返回值更好,后面也很方便处理掉,因为这个是可控的。

def __get_location1__(hospital, provinces, cities, location_list, address, phone):  
        my_ak = 'yourak_from_baidu'    # 需要自己填写自己的AK
#        print(location_list)
        headers = {
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
            "Accept-Encoding": "gzip, deflate, br",
            "Accept-Language": "zh-CN,zh;q=0.8",
            "Cache-Control": "max-age=0",
            "Connection": "keep-alive",
            "Upgrade-Insecure-Requests": "1",
            "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.86 Safari/537.36"
        }
        try:
            url = 'http://api.map.baidu.com/geocoder?address=' + location_list +'output=json&key=' + my_ak
            print(url)
            response = requests.get(url, headers = headers , timeout = 15)
            txt = response.text
#            print(txt)
            print('\n')
            res_json = txt[42:]
            res_json_ = xmltojson(res_json)
            print(res_json_)
            json_dict = json.loads(res_json_)
#            print(type(json_dict))
            print(json_dict)
            lat = json_dict['GeocoderSearchResponse']['result']['location']['lat']
            lng = json_dict['GeocoderSearchResponse']['result']['location']['lng']
            print(lat, lng)
#            xml_dict = xmltodict.parse(res_json)
            return hospital, provinces, cities, lat, lng, address, phone      
            
        except ExpatError as e:
            print('location {} {} not found. BaiduMap is lost!'.format(cities, location_list))
            print(e)
            #print(response.status_code)
            print(time.strftime('%Y-%m-%d %H:%M:%S'))
#            time.sleep(random.random()*2)
            return 0,0

坐标转换成Geo需要的json文件(自定义坐标)

自定义坐标这块pyecharts官方文档没有详细说明,网上也很少相关的帖子。Geo对json格式要求严格,对输入的data_pair要求也很高,不然不会出图。先说data_pair:

data_pair 我通常用list(zip(list1, list2)),这样你就得到了a list of tuples,比如:

[('重庆三峡中心医院平湖分院', '重庆市', '万州区', '30.836284', '108.409686', '重庆市万州区北山大道862号', '023-58355555,023-58355511'), ('重庆三峡中心医院御安分院', '重庆市', '万州区', '30.844058', '108.425126', '重庆市万州区北山大道1666号', '023-58339601,023-58339553'), ('重庆三峡中心医院百安分院', '重庆市', '万州区', '30.771119', '108.452463', '重庆万州区五桥百安移民新区', '023-58556222')]

通常,你可能不需要这么多,只是[(‘name’, ‘value’), (‘name2’, ‘value2’) ] 就够用了,但是我这里需要在地图上显示医院名称,详细地址和电话。而Geo函数所需要的坐标是自定义json文件中得到的。Pyecharts自带的city_coordinate_json是这个样子的,你的格式也必须处理成这样。这里你会用到json_dumps, json_loads。

{
  "阿城": [
    126.58,
    45.32
  ],
  "阿克苏": [
    80.19,
    41.09
  ],
  "阿勒泰": [
    88.12,
    47.5
  ]
}