# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python [conda env:estate]
#     language: python
#     name: conda-env-estate-py
# ---

# +
import pandas as pd
import re
import matplotlib.pyplot as plt
import numpy as np

from glob import glob

pd.set_option('display.max_columns', 50)
# %matplotlib inline
# -

paths = glob('../data/*.csv')

data = pd.concat([pd.read_csv(path) for path in paths])

data['sub'].values[0]


# +
def extract_price(text):
    price, price_per_sqm, *_ = text.split('zł')
    price = re.sub(r',', '.', price)
    price = re.sub(r'[^0-9.]', '', price)
    
    price_per_sqm = re.sub(r',', '.', price_per_sqm)
    price_per_sqm = re.sub(r'[^0-9.]', '', price_per_sqm)
    return float(price), float(price_per_sqm)

def extract_area(text):
    area = re.sub(r',', '.', text)
    area = re.sub(r'[^0-9.]', '', area)
    return float(area)

def extract_rooms(text):
    rooms = re.sub(r',', '.', text)
    rooms = re.sub(r'[^0-9.]', '', rooms)
    return int(rooms)

def extract_floor(text):
    if not re.search(r'\d', text):
        return None, None
    text = re.sub(r'parter', '1', text)
    text = re.sub(r'poddasze', '0', text)
    floor, number_of_floors = None, None
    if re.search(r'piętro', text):
        if re.search(r'\(z \d+\)', text):
#             try:
            floor, number_of_floors = text.split('z')
#             except:
#                 print("ERROR", text)
            number_of_floors = re.sub(r'[^0-9.]', '', number_of_floors)            
        else:
            floor = text        
        floor = re.sub(r'[^0-9.]', '', floor)
    else:
        number_of_floors = re.sub(r'[^0-9.]', '', text)

    
    floor = int(number_of_floors) if floor == '0' and number_of_floors is not None else floor
    floor = int(floor) if floor is not None else None
    number_of_floors = int(number_of_floors) if number_of_floors is not None else None
        
    return floor, number_of_floors

def extract_main(main_list):
    raw_price = main_list[0]
    raw_area = main_list[1]
    raw_rooms = main_list[2]
    raw_floor = main_list[3]
    
    return [
        *extract_price(raw_price),
        extract_area(raw_area),
        extract_rooms(raw_rooms),
        *extract_floor(raw_floor)
    ]

def extract_sub(text):
    def split_and_strip(text):
        key, value = text.split(':')
        return key.strip(), value.strip()
    return dict(split_and_strip(x) for x in text)

def extract_dotted(text):
    return {x.strip(): True for x in text}


# +
def prepare_lat(data):
    result = pd.DataFrame(data['lat']).reset_index(drop=True)
    result.columns = ['lat']
    return result

def prepare_lon(data):
    result = pd.DataFrame(data['lon']).reset_index(drop=True)
    result.columns = ['lon']
    return result

def prepare_main(data):
    main_columns = ['price', 'price_per_sqm', 'area', 'rooms', 'floor', 'number_of_floors']
    result = pd.DataFrame(data['main'].map(extract_main).tolist(), columns=main_columns).reset_index(drop=True)
    result.columns = main_columns
    return result

def prepare_sub(data):
    pre_data = pd.DataFrame(data['sub'].map(eval).map(extract_sub).tolist())
    columns = pre_data.columns
    result = pre_data.reset_index(drop=True)
    result.columns = columns
    return result

def prepare_dotted(data):
    pre_data = pd.DataFrame(data['dotted'].map(eval).map(extract_dotted).tolist())
    columns = pre_data.columns
    result = pre_data.reset_index(drop=True)
    result.columns = columns
    return result


# -

def prepare_data(data):
    data.coords = data.coords.map(eval)
    data.place = data.place.map(eval)
    data.main = data.main.map(eval)
    data['lat'] = data.coords.map(lambda x: float(x[0][0]))
    data['lon'] = data.coords.map(lambda x: float(x[1][0]))

    main_columns = ['price', 'price_per_sqm', 'area', 'rooms', 'floor', 'number_of_floors']
    dfs = [
        prepare_lat(data),
        prepare_lon(data),
        prepare_sub(data),
        prepare_dotted(data),
        prepare_main(data)
    ]
    final_data = pd.concat(dfs, axis=1)
    final_data['rok budowy']  = final_data['rok budowy'].apply(np.float64)
    final_data['rooms']  = final_data['rooms'].apply(np.float64)
    return final_data


# +
# data.place = data.place.map(lambda x: x[0].split(', '))
# data['len_place'] = data.place.map(len)
# data.sort_values(by='len_place', ascending=False).place

# +
# from itertools import chain
# cur = data.dotted.map(eval).map(extract_dotted)
# dd = list(chain.from_iterable(cur.values))
# -

data = prepare_data(data)

data.columns

data



plt.scatter(data.lat.values, data.lon.values)
plt.show()

plt.hist(data.price.values, bins=50)
plt.show()

plt.hist(data.area.values, bins=100)
plt.show()

plt.hist(data.rooms.values, bins=100)
plt.show()

plt.scatter(data.area.values, data.price.values)
plt.xlim(0, 300)
plt.ylim(0, 2250000)
plt.show()

# # Simple analysis

import xgboost as xgb

model = xgb.XGBRegressor()

mask = np.random.random(len(data)) < 0.8

columns_of_interest = ['rynek', 'area', 'lat', 'lon', 'rok budowy', 'rooms', 'floor', 'number_of_floors']

train_data = pd.get_dummies(data[columns_of_interest + ['price']].loc[mask])

test_data = pd.get_dummies(data[columns_of_interest + ['price']].loc[~mask])

train_data

model.fit(train_data[['rynek_pierwotny', 'area', 'lat', 'lon', 'rok budowy', 'rooms', 'floor', 'number_of_floors']], train_data['price'])

error = model.predict(test_data[['rynek_pierwotny', 'area', 'lat', 'lon', 'rok budowy', 'rooms', 'floor', 'number_of_floors']]) - test_data['price']

error

np.mean(np.abs(error.values))

plt.hist(error, bins=100)
plt.show()

len(data)

data

# !jupyter nbconvert --to script preprocessing.ipynb

# # Histogram, binned groupby

bins = np.array([20, 30, 50, 80, 110])
groups = data.groupby(pd.cut(data.area, bins))
hist = groups.count().area.values 
hist = hist / len(data) * 100
hist

# # Putting into database

# +
import json
from pymongo import MongoClient


host = 'localhost'
port = 30017
db_name = 'test3'
collection_name = 'otodom_offers'

client = MongoClient(host, port)
db = client[db_name]
collection = db[collection_name]
# -

data.columns = [x.replace('.', '_') for x in data.columns]
records = json.loads(data.T.to_json()).values()

collection.insert(records)

len(list(collection.find()))


def get_nearest_neighbors_mean_price_per_sqm(data=data, lat, lon, nb_nearest=20):
    distances = (data.lat - current_lat)**2 + (data.lon - current_lon)**2
    sorted_distances_top = distances.sort_values()[:nb_nearest]
    mean_result = data.ix[sorted_result_top.index]['price_per_sqm'].mean()
    return mean_result


current_lon = data.lon[300]
current_lat = data.lat[300]

current_lon, current_lat







mean_result



StarcoderdataJupyterScriptsDedupFiltered