Python基于pandas实现json格式转换成dataframe的方法
本文实例讲述了Python基于pandas实现json格式转换成dataframe的方法。分享给大家供大家参考，具体如下：
#-*-coding:utf-8-*-
#!python3
importre
importjson
frombs4importBeautifulSoup
importpandasaspd
importrequests
importos
frompandas.io.jsonimportjson_normalize
classimage_structs():
def__init__(self):
self.picture_url={
"image_id":'',
"picture_url":''
}
classdata_structs():
def__init__(self):
#columns=['title','item_url','id','picture_url','std_desc','description','information','fitment'])
self.info={
"title":'',
"item_url":'',
"id":0,
"picture_url":[],
"std_desc":'',
"description":'',
"information":'',
"fitment":''
}
#"https://waldoch.com/store/catalogsearch/result/index/?cat=0&limit=200&p=1&q=nerf+bar"
#https://waldoch.com/store/new-oem-ford-f-150-f150-5-running-boards-nerf-bar-crew-cab-2015-w-brackets-fl34-16451-ge5fm6.html
defget_item_list(outfile):
result=[]
foriinrange(6):
print(i)
i=str(i+1)
url="https://waldoch.com/store/catalogsearch/result/index/?cat=0&limit=200&p="+i+"&q=nerf+bar"
web=requests.get(url)
soup=BeautifulSoup(web.text,"html.parser")
alink=soup.find_all("a",class_="product-image")
forainalink:
title=a["title"]
item_url=a["href"]
result.append([title,item_url])
df=pd.DataFrame(result,columns=["title","item_url"])
df=df.drop_duplicates()
df["id"]=df.index
df.to_excel(outfile,index=False)
defget_item_info(file,outfile):
DEFAULT_FALSE=""
df=pd.read_excel(file)
foriindf.index:
id=df.loc[i,"id"]
ifos.path.exists(str(int(id))+".xlsx"):
continue
item_url=df.loc[i,"item_url"]
url=item_url
web=requests.get(url)
soup=BeautifulSoup(web.text,"html.parser")
#图片
imglink=soup.find_all("img",class_=re.compile("^gallery-image"))
data=data_structs()
data.info["title"]=df.loc[i,"title"]
data.info["id"]=id
data.info["item_url"]=item_url
forainimglink:
image=image_structs()
image.picture_url["image_id"]=a["id"]
image.picture_url["picture_url"]=a["src"]
print(image.picture_url)
data.info["picture_url"].append(image.picture_url)
print(data.info)
#std_desc
std_desc=soup.find("div",itemprop="description")
try:
strings_desc=[]
foriiinstd_desc.stripped_strings:
strings_desc.append(ii)
strings_desc="\n".join(strings_desc)
except:
strings_desc=DEFAULT_FALSE
#description
try:
desc=soup.find('h2',text="Description")
desc=desc.find_next()
except:
desc=DEFAULT_FALSE
description=desc
#information
try:
information=soup.find("h2",text='Information')
desc=information
desc=desc.find_next()
except:
desc=DEFAULT_FALSE
information=desc
#fitment
try:
fitment=soup.find('h2',text='Fitment')
desc=fitment
desc=desc.find_next()
except:
desc=DEFAULT_FALSE
fitment=desc
data.info["std_desc"]=strings_desc
data.info["description"]=str(description)
data.info["information"]=str(information)
data.info["fitment"]=str(fitment)
print(data.info.keys())
singledf=json_normalize(data.info,"picture_url",['title','item_url','id','std_desc','description','information','fitment'])
singledf.to_excel("test.xlsx",index=False)
exit()
#print(df.ix[i])
df.to_excel(outfile,index=False)
#get_item_list("item_urls.xlsx")
get_item_info("item_urls.xlsx","item_urls_info.xlsx")
这里涉及到的几个Python模块都可以使用pipinstall命令进行安装，如：
pipinstallBeautifulSoup4
pipinstallxlrd
pipinstallopenpyxl
PS：这里再为大家推荐几款比较实用的json在线工具供大家参考使用：
在线
JSON在线格式化工具：
http://tools.jb51.net/code/jsonformat
在线XML/