Python爬虫实战+数据分析+数据可视化（腾讯新闻实时疫情）.zip

from pymongo import MongoClient import pandas as pd import numpy as np import pymysql # 各省累计确诊人数 def acc_suffer_count(df): # 按照省进行分组 grouped = df.groupby('省')['累计确诊'].sum().reset_index().sort_values('累计确诊',ascending=False) data = [[i['省'],i['累计确诊']] for i in grouped.to_dict(orient="records")] print(data) return data # 各省现有确诊人数 def now_suffer_count(df): grouped = df.groupby(['省'])['现有确诊'].sum().reset_index().sort_values("现有确诊",ascending=False) data = [[i['省'],i['现有确诊']] for i in grouped.to_dict(orient="records")] print(data) return data # # 各省治愈率 def heal_rate(df): # 治愈率 = 治愈人数/累计确诊人数 # 按照省进行分组并求出累计确诊人数和治愈人数 grouped = df.groupby('省')[['累计确诊','治愈人数']].sum().reset_index() grouped['治愈率'] = grouped['治愈人数']/grouped['累计确诊'] # 治愈率保留三位小数 grouped['治愈率'] = grouped['治愈率'].apply(lambda x:round(x,3)) data = [[i['省'],i['治愈率'],i['治愈人数']] for i in grouped.to_dict(orient="records")] print(data) return data # 北上广的疫情情况对比 def four_city_compare(df): # 按照省进行分组 grouped = df.groupby('省').sum() # 计算北上广的治愈率与死亡率 grouped['治愈率'] = grouped['治愈人数']/grouped['累计确诊'] grouped['死亡率'] = grouped['死亡人数']/grouped['累计确诊'] # 处理治愈率和死亡率 保留三位小数 grouped['治愈率'] = grouped['治愈率'].apply(lambda x:round(x,3)) grouped['死亡率'] = grouped['死亡率'].apply(lambda x:round(x,3)) # 取出北上广的疫情数据 cities = grouped.loc[['北京','上海','广东']] data = cities.reset_index() print(data) data = [[i['省'],i['累计确诊'],i['现有确诊'],i['治愈人数'],i['死亡人数'],i['治愈率'],i['死亡率']] for i in data.to_dict(orient="records")] print(data) return data if __name__ == '__main__': client = MongoClient() collection = client['test']['covid_19'] rest = collection.find({},{'_id':0}) df = pd.DataFrame(rest) # 打印基础信息 print(df.info()) print(df.head(1)) # 各省累计确诊人数 # data = acc_suffer_count(df) # 各省现有确诊人数 # data = now_suffer_count(df) # 各省治愈率 # data = heal_rate(df) # 北上广深的疫情情况对比 data = four_city_compare(df) # 初始化数据库连接 将数据存入到mysql中 conn = pymysql.connect(host='localhost',user='root',password='123456',port=3306,database='covid_19',charset='utf8') with conn.cursor() as cursor: # 各省累计确诊人数 # sql = 'insert into db_province_acc_count(province,acc_count) values(%s,%s)' # 各省现有确诊人数 # sql = 'insert into db_province_now_count(province,now_count) values(%s,%s)' # 各省治愈率 sql = 'insert into db_province_heal_rate(province,heal_rate,heal_count) values(%s,%s,%s)' # 北上广的疫情情况对比 # sql = 'insert into db_province_bsg(province,acc_count,now_count,heal_count,dead_count,heal_rate,dead_rate) values(%s,%s,%s,%s,%s,%s,%s)' try: rest = cursor.executemany(sql, data) if rest: print("插入成功") conn.commit() except pymysql.MySQLError as err: print(err) conn.rollback() finally: conn.close()