coding=utf-8
import requests
from bs4 import BeautifulSoup
import re
import os
import pymongo
import json
import pandas as pd
import numpy as np
import xlrd
import datetime
import pyecharts
def getPriceSoup_table(spiderDay):
soup_table=BeautifulSoup('',"lxml")
for m in range(1,365):
url = "http://www.hfzgncp.com.cn/price.php?class_id=104102103&page=" + str(m) + "#xx"
headers = {'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:61.0)' +
'Gecko/20100101 Firefox/61.0'}
r = requests.get(url,headers=headers)
html = r.text.encode(encoding='utf_8').decode()
soup = BeautifulSoup(html,"lxml")
table = soup.find('div', attrs={'class' :{'price2'}})
soup_table.append(table)
if soup_table == None:
print('!!!!!!!!!!!!!!!!!!!!!!'+str(spiderDay)+'这一天没有水产品价格数据!!!!!!!!!!!!!!!!!!!!!!')
return
else:
print(str(spiderDay)+'这一天有水产品价格数据')
return soup_table
def soup_table2DataFrame(soup_table): #构造dataframe 准备存储表格
list1 = [x.text for x in soup_table.findAll('div', attrs={'class' :{'s1'}})]
list1 = [x.replace('\xa0','') for x in list1]
list2 = [x.text for x in soup_table.findAll('div', attrs={'class' :{'s2'}})]
list2 = [x.replace('\xa0','') for x in list2]
list3 = [x.text for x in soup_table.findAll('div', attrs={'class' :{'s3'}})]
list3 = [x.replace('\xa0','') for x in list3]
list4 = [x.text for x in soup_table.findAll('div', attrs={'class' :{'s4'}})]
list4 = [x.replace('\xa0','') for x in list4]
list5 = [x.text for x in soup_table.findAll('div', attrs={'class' :{'s5'}})]
list5 = [x.replace('\xa0','') for x in list5]
list6 = [x.text for x in soup_table.findAll('div', attrs={'class' :{'s6'}})]
list6 = [x.replace('\xa0','') for x in list6]
list = []
list.append(list1)
list.append(list2)
list.append(list3)
list.append(list4)
list.append(list5)
list.append(list6)
#表的宽度
width1 = len(list1)
print(width1)
df_zanshi = pd.DataFrame(list)
df = df_zanshi.stack().unstack(0)
return df
def getPriceLine(product_name,days):
price_data = []
price_date = []
for i in range(days):
spiderDay = datetime.date.today()- datetime.timedelta(days=i+1)
spiderDayStr =str(spiderDay) #2018-07-11格式
sendtimeStr=re.sub("-","",spiderDayStr) #20180711格式
outputfilePath="D:/xlsx/"+sendtimeStr+".水产品价格xlsx"
if os.path.exists(outputfilePath):
ExcelFile = xlrd.open_workbook(outputfilePath)
sheet = ExcelFile.sheet_by_index(0)
columnIndex = None
rowIndex = None
for j in range(sheet.ncols):
for i in range(sheet.nrows):
if sheet.cell_value(i, j) == '平均价':
columnIndex = j
break
if sheet.cell_value(i, j) == product_name:
rowIndex = i
break
if not (rowIndex == None) and not (columnIndex == None):
print(sheet.cell_value(rowIndex, columnIndex))
price_data.append(sheet.cell_value(rowIndex, columnIndex))
price_date.append(sendtimeStr)
print(price_data)
print(price_date)
if name=="main":
spiderDaynumber = 1
for i in range(spiderDaynumber):
spiderDay = datetime.date.today()- datetime.timedelta(days=i+1)
spiderDayStr =str(spiderDay) #2018-07-11格式
sendtimeStr=re.sub("\-","",spiderDayStr) #20180711格式
soup_table = getPriceSoup_table(spiderDay)
if not soup_table == None:
df = soup_table2DataFrame(soup_table)
print(df)
isExists=os.path.exists('D:/xlsx')
if not isExists:
# 如果不存在则创建目录
os.makedirs('D:/xlsx')
print ('创建D:/xlsx文件夹成功')
#return True
else:
outputfilePath="D:/xlsx/合肥周谷堆大兴水产品价格.xlsx"
df.to_excel(outputfilePath)
