Python数据分析实例

###第一步getValue（）
爬取链家关于武汉房价信息
###第二步setMySQL（）
存入数据库
###第三步getMySQL（）
从数据库中取出数据
###第四步setPrice（）
绘成有关数据图

# -*- coding:utf-8 -*-
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.wait import WebDriverWait
import time
import pymysql
import pandas as pd
from pyecharts.charts import Scatter,Map
from pyecharts import options as opts
from pyecharts.commons.utils import JsCode
from pyecharts.globals import ThemeType
from pyecharts.faker import Faker

def setMySQL():
    db = pymysql.connect(host='localhost', user='root', password='123456', port=3306)
    cursor = db.cursor()
    cursor.execute('select version()')
    cursor.execute('create database lianjia default character set utf8')
    db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='lianjia', autocommit=True)
    cursor = db.cursor()
    # 创建lianjia数据表
    cursor.execute('create table if not exists lianjia('
                   'name varchar(100) PRIMARY KEY, '
                   'region varchar(100), '
                   'area varchar(100), '
                   'price VARCHAR(100)) default character set="utf8" ')
    # 测试语句
    # cursor.execute('insert into lianjia(name, region, area, price) values("1","2","3","4")')
    for item in lis_values:
        print(type(item))#查看数据类型
        sql = 'insert into lianjia(name, region, area, price) values(%s, %s, %s, %s)'
        try:
            cursor.execute(sql, (tuple(item)))
            db.commit()
            print(item[0] + '入库成功。')
        except:
            db.rollback()
            print(item[0] + '保存失败。')
    db.close()


def getValue():
    value=[]
    browser = webdriver.Chrome()
    url="https://wh.lianjia.com/?utm_source=baidu&utm_medium=pinzhuan&utm_term=biaoti&utm_content=biaotimiaoshu&utm_campaign=wywuhan"
    browser.get(url)
    link = browser.find_element_by_xpath('/html/body/div[6]/div/div[1]/p/a').get_attribute('href')
    browser.get(link)
    # body > div.resblock - list - container.clearfix > ul.resblock - list - wrapper > li: nth - child(1)
    # body > div.resblock - list - container.clearfix > ul.resblock - list - wrapper > li: nth - child(2)
    for i in range(0, 10):
        # body > div.resblock - list - container.clearfix > ul.resblock - list - wrapper > li: nth - child(1)
        # body > div.resblock - list - container.clearfix > ul.resblock - list - wrapper > li: nth - child(2)
        # body > div.resblock - list - container.clearfix > ul.resblock - list - wrapper > li: nth - child(1)
        # body > div.resblock - list - container.clearfix > ul.resblock - list - wrapper
        # lis_house = browser.find_elements_by_css_selector('body > div.resblock - list - container.clearfix > '
        #                                                   'ul.resblock - list - wrapper > li')
        lis_house = browser.find_elements_by_class_name('resblock-list')
        for item in lis_house:
            name = item.find_element_by_class_name('name').text
            region = item.find_element_by_css_selector('div > div.resblock-location > span:nth-child(1)').text
            area = item.find_element_by_css_selector('div > div.resblock-area > span').text
            price = item.find_element_by_css_selector('div > div.resblock-price > div.main-price > span.number').text + '元/平方'
            value.append([name, region, area, price])
        browser.find_element_by_class_name('next').click()
        time.sleep(1)
    return value

def getMySQL():
    db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='lianjia', autocommit=True)
    cursor = db.cursor()
    cursor.execute('select * from lianjia')
    results = cursor.fetchall()
    return results

def setPrice():
    result=[[item[0],item[1],item[2],item[3]] for item in results]
    result_c = [[item[2], round(int(item[3].split('元')[0]), 2)] for item in result if item[3] != '价格待定元/平方']
    df = pd.DataFrame(
        result_c,
        columns=['area', 'price']
    )
    # print(df)
    c = (
        Scatter()
        .add_xaxis(df['area'])
        .add_yaxis('散点图',df['price'])
        .set_global_opts(
            title_opts=opts.TitleOpts(title='房价——面积'),
            visualmap_opts=opts.VisualMapOpts(
                max_=150,
                min_=0
            ),
            tooltip_opts=opts.TooltipOpts(
                is_show=True
            ),
            xaxis_opts=opts.AxisOpts(name='面积'),
            yaxis_opts=opts.AxisOpts(name='房价')
        )
        .set_series_opts(
            label_opts=opts.LabelOpts(
                is_show=False
            )
        )
        .render('武汉链家散点图.html')
    )

    result_s = [[item[1]+'区',int(item[3].split('元')[0])]for item in result if item[3]!='价格待定元/平方']
    print(result_s)
    df_s = pd.DataFrame(result_s, columns=['address', 'price'])
    result_s = df_s.groupby('address')['price'].mean().reset_index()
    result_s=[[value['address'],round(value['price'], 2)]for key, value in result_s.iterrows()]
    s = (
        Map()
        .add('武汉各区均价', result_s, '武汉')
        .set_global_opts(
            title_opts=opts.TitleOpts(title='武汉各区域均价'),
            visualmap_opts=opts.VisualMapOpts(
                is_show=True,
                min_=1000,
                max_=100000
            ),
            tooltip_opts=opts.TooltipOpts(
                is_show=True,
                formatter='{c}元'
            )
        )
        .render('武汉各区域均价.html')
    )




if __name__ == '__main__':
    lis_values = getValue()
    setMySQL()
    results = getMySQL()
    setPrice()

此代码实用性不大，主要用来练习，代码中散点图绘制有问题，原因是传入的数据没有处理好，可以自己尝试修改绘图。