虽然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:passwd@localhost: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:passwd@localhost: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
]
}
