import openpyxl
import requests
from bs4 import BeautifulSoup
header={"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36"}
wb=openpyxl.Workbook()
ws=wb.active
ws.append(("品牌名","系列名称","价格","销量","级别","能源类型","上市时间"))
def send_http(url):
    res = requests.get(url=url, headers=header)
    http_data=res.json()
    cars=http_data["data"]["list"]
    for car in cars:
        series_name=car["series_name"]
        dealer_name=car["dealer_price"]
        count=car["count"]
        brand_name=car["brand_name"]
        second_http_url=f"https://www.dongchedi.com/auto/params-carIds-x-{car['series_id']}"
        second_res=requests.get(url=second_http_url,headers=header)
        soup=BeautifulSoup(second_res.text)

        jb_element=soup.find("div",attrs={"data-row-anchor":"jb","class":"table_row__yVX1h"})
        jb=jb_element.select(".cell_normal__37nRi")[0].text

        fuel_element = soup.find("div", attrs={"data-row-anchor":"fuel_form","class":"table_row__yVX1h"})
        fuel=fuel_element.select(".cell_normal__37nRi")[0].text

        market_time_element=soup.find("div",attrs={"data-row-anchor":"market_time","class":"table_row__yVX1h"})
        market_time=market_time_element.select(".cell_normal__37nRi")[0].text

        ws.append((brand_name,series_name,dealer_name,count,jb,fuel,market_time))
    pass
for i in range(10):
    url=(f"https://www.dongchedi.com/motor/pc/car/rank_data?aid=1839&app_name=auto_web_pc&city_name=%E7%9F%B3%E5%AE%B6%E5%BA%84&count=10&offset={i*10}&month=&new_energy_type=&"
         f"rank_data_type=11&brand_id=&price=&manufacturer=&outter_detail_type=&nation=0")
    send_http(url)
    print(f"第{i+1}页数据采集完毕")
    pass
wb.save("car.xlsx")

import matplotlib.pyplot as plt
import pandas as pd
from wordcloud import WordCloud
df=pd.read_excel('car.xlsx')
print(f'车辆总数:{df["销量"].sum()}')

max_count_series=df.loc[:, '系列名称']
print(f'销量最多汽车:{max_count_series}')

def mean_fn(x:str):
    _index = x.index("-")
    left_price=float(x[:_index])
    right_price=float(x[(_index+1):x.index("万")])
    return (left_price+right_price)/2
df["平均价格"]=df["价格"].apply(mean_fn)
df["最低价"]=df["价格"].apply(lambda x:float(x[:x.index("-")]))
df["最高价"]=df["价格"].apply(lambda x:float(x[:x.index("-")+1:x.index("万")]))
print(f'汽车平均价格：{round(df["平均价格"].mean(),2)}万')





group_result=df.groupby(["品牌名","级别"],as_index=False).count()
brand_series=group_result["品牌名"]
brand_group=brand_series.groupby(brand_series.values).count()
print(f'车型最多的品牌：{brand_group.sort_values(ascending=False).index[0]}')

ev_sales_df=df.loc[df["能源类型"]=="纯电动",["品牌名","能源类型","销量"]]
ev_sales_percent=ev_sales_df["销量"].sum()/df["销量"].sum()
print(f"电车销售占比:{ev_sales_percent:.2%}")

gas_sales_df=df.loc[df["能源类型"]=="汽油",["品牌名","能源类型","销量"]]
gas_sales_percent=gas_sales_df["销量"].sum()/df["销量"].sum()
print(f"汽车销售占比:{gas_sales_percent:.2%}")


mix_sales_df=df.loc[(df["能源类型"]!="汽油")&(df["能源类型"]!="纯电动"),["品牌名","能源类型","销量"]]
mix_sales_percent=mix_sales_df["销量"].sum()/df["销量"].sum()
print(f"混动销售占比:{mix_sales_percent:.2%}")


#6:统计汽车品牌销量排行榜导出excel
rank_df=df.groupby("品牌名",as_index=False).sum()[["品牌名","销量"]]
rank_df=rank_df.sort_values("销量",ascending=False)

#将rank_df导出为excel
writer=pd.ExcelWriter("car_rank.xlsx")
#将df写入内存中
#index=False,不显示行索引
rank_df.to_excel(writer,sheet_name='Sheet1',index=False)

writer._save()
writer.close()

#汽车品牌销量饼状图
#7制作汽车品牌销量饼图，展示各个品牌的销量占比情况。
labels=list(rank_df["品牌名"])[:5]
values=list(rank_df["销量"])[:5]
# 绘制饼状图
#autapct:显示比例
plt.pie(values,labels=labels,autopct="%.2f%%")
#rcParams，r:runtime(运行时)，c:config(配置)
plt.rcParams["font.sans-serif"]="Microsoft YaHei"
#ax.axis('equal')# 确保饼状图为圆形
plt.title( "汽车品牌销量占比",fontsize=16,pad=10)
#loc :location
plt.legend(loc="lower left",bbox_to_anchor=(-0.3,0))
#显示饼图
plt.savefig("car_rank_pie.jpg")
#汽车系列名称词云图
#制作汽车系列名称词云图(按销量生成频率词云图)
df_new=df.set_index("系列名称")
rank_wordcloud_data=df_new["销量"].to_dict()
#生成词云图
wc=WordCloud(font_path='msyh.ttc',#字体路劲background_color='white'，#背景颜色
width=1000,#图宽度
height=600,#图片高度
max_font_size=300,#字体大小
min_font_size=10,#最小字体
max_words=100,#最大词数
font_step=4)#字体变换步长
#根据频率生成词云图需要传入字典)
img=wc.generate_from_frequencies(rank_wordcloud_data)
img.to_file("car_rank_wordcloud.jpg")

#汽车品牌销量柱状图
# 制作汽车品牌销量的柱状图(显示销量前10 的汽车)
#iloc:行号或列号(数字)
#loc:行索引或者列名
labels2=list(rank_df.iloc[:10,:]["品牌名"])
values2=list(rank_df.iloc[:10,:]["销量"])
#print(labels2)
# print(values2)
plt.pie(values2,labels=labels2,autopct="%.2f%%")
plt.rcParams["font.sans-serif"]="Microsoft YaHei"
plt.title("汽车品牌销量Top10")
plt.savefig("car_brand_salts_top10.jpg")

#汽车销量价格占比柱状图
#0-5W
price_0_5_df = df.loc[(df["最低价"] > 0) &(df["最低价"]<=5)]
#5-10w
price_5_10_df = df.loc[(df["最低价"]>5)&(df["最低价"]<= 10)&(df["最高价"]<= 18)]
#10-15w
price_10_15_df = df.loc[(df["最低价"]>10)&(df["最低价"]<=15)&(df["最高价"]<= 15)]
#15-20w
price_15_20_df= df.loc[(df["最低价"]>15)&(df["最低价"]<= 20)&(df["最高价"]<= 20)]
# 20-25w
price_20_25_df= df.loc[(df["最低价"]>20)&(df["最低价"]<= 25)&(df["最高价"]<= 25)]
# 25-30w
price_25_30_df= df.loc[(df["最低价"]>25)&(df["最低价"]<= 30)&(df["最高价"]<= 30)]
# 30-35W
price_30_35_df= df.loc[(df["最低价"]>30)&(df["最低价"]<= 35)&(df["最高价"]<= 35)]
#35-40w
price_35_40_df= df.loc[(df["最低价"]>35)&(df["最低价"]<= 40)&(df["最高价"]<= 40)]
#40-45W
price_40_45_df= df.loc[(df["最低价"]>40)&(df["最低价"]<= 45)&(df["最高价"]<= 45)]
#45-50W
price_45_50_df= df.loc[(df["最低价"]>45)&(df["最低价"]<= 50)&(df["最高价"]<= 50)]

price_counts ={
    "0-5w": len(price_0_5_df),
    "5-10w": len(price_5_10_df),
    "10-15w": len(price_10_15_df),
    "15-20w": len(price_15_20_df),
    "20-25w":len(price_20_25_df),
    "25-30w":len(price_25_30_df),
    "30-35w":len(price_30_35_df),
    "35-40w":len(price_35_40_df),
    "40-45w":len(price_40_45_df),
    "45-50w":len(price_45_50_df)
}
# 绘制桂状图
plt.figure(figsize=(12,6))
plt.bar(price_counts.keys(),price_counts.values())
plt.xlabel('价格区间')
plt.ylabel('汽车数量')
plt.title('汽车销量价格占比柱状图')
plt.show()