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()