72 lines
3.1 KiB
Python
72 lines
3.1 KiB
Python
import pandas as pd
|
|
from common import configs as cfg
|
|
|
|
import common
|
|
|
|
mongo_uri = 'mongodb://developer:G00IDCDEV12385@192.168.0.136:28017/?connectTimeoutMS=60000&socketTimeoutMS=10000000'
|
|
HOUSE_RENT = common.get_mongodb_collection("mongodb://192.168.0.105:27018/", "vip_site", "esf_houseRent")
|
|
DISTRICT_STAT_RENT = common.get_mongodb_collection(cfg.MONGO_URI, "fangjia", "district_stat_rent")
|
|
DISTRICT_RENT = common.get_mongodb_collection(mongo_uri, "fangjia_rent", "district_rent")
|
|
|
|
|
|
def find_room_rent(city, region, name, room):
|
|
stat = DISTRICT_STAT_RENT.find_one({"city": city, "region": region, "district_name": name, "house_type": room,
|
|
"end_month_date": common.parse_date("2019-03-31")})
|
|
if stat:
|
|
return stat["avg_price"]
|
|
else:
|
|
return None
|
|
|
|
|
|
def find_sq_rent(city, region, name, room):
|
|
stat = DISTRICT_RENT.find_one({"city": city, "region": region, "name": name, "room": room, "date": 201902})
|
|
if stat:
|
|
return stat["rent_price"]
|
|
else:
|
|
return None
|
|
|
|
|
|
def find_rent_house(city, region, alias, room):
|
|
fields_filter = {"address": 0, "_id": 0, "block": 0, "c_date": 0, "title": 0, "m_date": 0, "rent_type": 0}
|
|
houses = pd.DataFrame(list(HOUSE_RENT.find({"city": city, "region": region, "rent_type": "整租",
|
|
"district_name": {"$in": alias}, "room": room, "publish_date": {"$gt": common.parse_date("2019-01-01")}}, fields_filter)))
|
|
if len(houses) == 0:
|
|
return houses
|
|
houses["avg_price"] = houses["total_price"] / houses["area"]
|
|
houses["avg_price"] = houses["avg_price"].map(int)
|
|
houses.drop_duplicates(keep='first', inplace=True)
|
|
houses.sort_values(by="area", inplace=True)
|
|
return houses
|
|
|
|
|
|
regions = ["朝阳", "海淀", "丰台", "西城", "通州", "石景山", "东城"]
|
|
|
|
|
|
def process():
|
|
all_house = pd.DataFrame()
|
|
for region in regions:
|
|
for seaweed in common.get_seaweed("北京", region):
|
|
names = [seaweed["name"]]
|
|
if "alias" in seaweed and seaweed["alias"] is not None:
|
|
names += seaweed["alias"]
|
|
for room in range(1, 3):
|
|
houses = find_rent_house(seaweed["city"], seaweed["region"], names, room)
|
|
if len(houses) > 1:
|
|
all_house = all_house.append(houses)
|
|
all_house["publish_date"] = all_house["publish_date"].apply(lambda x: common.format_date(x, '%Y%m%d'))
|
|
for col in ["direction", "fitment", "floor_info", "hall", "height", "toilet", "avg_price", "publish_date"]:
|
|
del all_house[col]
|
|
excel_data = []
|
|
for index, row in all_house.iterrows():
|
|
row = dict(row)
|
|
row["户型租金"] = find_room_rent(row["city"], row["region"], row["district_name"], row["room"])
|
|
row["平米租金"] = find_sq_rent(row["city"], row["region"], row["district_name"], row["room"])
|
|
print(row)
|
|
excel_data.append(row)
|
|
excel_data = pd.DataFrame(excel_data)
|
|
excel_data = excel_data.dropna(how="any")
|
|
excel_data.to_excel("D:\\建行户型房源数据.xlsx", index=False)
|
|
|
|
|
|
process()
|