<jupyter_start><jupyter_text>IntroductionThe aim of this notebook is to analyse and clean up the data, gathered in the previous steps.  Initial setup<jupyter_code>import numpy as np
import pandas as pd

from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"

#show every column
pd.set_option('display.max_columns', None)<jupyter_output><empty_output><jupyter_text>Joining the dataFirst of all, since we have three datasets, let's join them all into one big dataset.<jupyter_code>data_houses = pd.read_csv("data/dataset_houses_with_population.csv")
data_houses['Building type'] = 0
data_apartments = pd.read_csv("data/dataset_apartments_with_population.csv")
data_apartments['Building type'] = 1
data_sharehouses = pd.read_csv("data/dataset_sharehouses_with_population.csv")
data_sharehouses['Building type'] = 2

data = pd.concat([data_houses, data_apartments, data_sharehouses])
data['id'] = np.arange(start = 1, stop = len(data) + 1, step = 1)

data = data.drop(columns="Unnamed: 0")
#data<jupyter_output><empty_output><jupyter_text>Exploratory analysisWe will now do a quick exploratory data analysis.<jupyter_code># Describe the numeric attributes.
data.describe()
data.describe(include=[object])
data.columns<jupyter_output><empty_output><jupyter_text>Cleaning the dataFirst, drop the columns which are useless for predicting the prices. Apart from the obvious ones, drop also the summer/winter costs because there are so few samples.<jupyter_code>data.drop(columns=["Title", "Description", "Link", "Cadastre no.", "Data from realestate book", "Notify about incorrect advertisement", "Kulud suvel/talvel"], inplace=True)

data.describe(include=[object])
data.columns<jupyter_output><empty_output><jupyter_text>Separating features Some features are actually a combination of multiple different features. In order to succesfully train on this data, these features will need to be separated.  "Lisainfo"First, what are the unique feature values in the column "Lisainfo".<jupyter_code>def find_unique_features(dataset, column):
    unique_features = set()

    for entry in dataset[column].unique():
        if not pd.isna(entry):
            features = entry.strip().split(", ")
            #print(features)
            unique_features.update(features)

    return unique_features

unique_features = find_unique_features(data, "Lisainfo")
unique_features<jupyter_output><empty_output><jupyter_text>Extract numeric features from "Lisainfo"<jupyter_code>data_balcony = []
data_balcony_size = []

for i in data["Lisainfo"].values:
    balcony = 0 #Base values
    balcony_size = 0.0
    
    if not pd.isna(i):
        features = i.strip().split(", ")
        
        for feature in features:
            if feature.find("balcony") == 0:
                balcony = 1
                balcony_el = feature.strip().split()
                if len(balcony_el) > 1:
                    balcony_size = float(balcony_el[1])
                    
    data_balcony.append(balcony)
    data_balcony_size.append(balcony_size)

data["Balcony"] = data_balcony
data["Balcony size"] = data_balcony_size
#Probably don't care about that feature
data_dist_from_Tallinn = []

for i in data["Lisainfo"].values:
    dist_from_Tallinn = 0.0
    
    if not pd.isna(i):
        features = i.strip().split(", ")
        
        for feature in features:
            if feature.find("distance from Tallinn") != -1:
                dist_from_Tallinn = float(feature.strip().split()[3])
    data_dist_from_Tallinn.append(dist_from_Tallinn)

data["Distance from Tallinn"] = data_dist_from_Tallinn<jupyter_output><empty_output><jupyter_text>Extract categorical features from "Lisainfo".<jupyter_code>data_addition = []

for i in data["Lisainfo"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("parking") != -1:
                current = ' '.join(feature.strip().split()[1:-1])
    data_addition.append(current)

data["Parking"] = data_addition
data_addition = []

for i in data["Lisainfo"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("wall") != -1:
                current = ' '.join(feature.strip().split()[1:])
    data_addition.append(current)

data["Wall"] = data_addition
data_addition = []

for i in data["Lisainfo"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("roof") != -1:
                current = ' '.join(feature.strip().split()[1:-1])
    data_addition.append(current)

data["Roof"] = data_addition<jupyter_output><empty_output><jupyter_text>Remove the now separated features from 'unique features' "Lisainfo"<jupyter_code>filtered_unique_features = [ x for x in unique_features if "balcony" not in x and 
                                                            "distance from Tallinn" not in x and 
                                                            "parking" not in x and
                                                            "wall" not in x and 
                                                            "roof" not in x ]<jupyter_output><empty_output><jupyter_text>Separate the remaining features<jupyter_code>def separate(dataset, uniques, column):
    for unique_feature in uniques:
        data_addition = []

        for i in data[column].values:
            current = False

            if not pd.isna(i):
                features = i.strip().split(", ")

                for feature in features:
                    if feature.find(unique_feature) != -1:
                        current = True
            data_addition.append(current)

        dataset[unique_feature] = data_addition
        
separate(data, filtered_unique_features, "Lisainfo")
data.drop(columns=["Lisainfo"], inplace=True)<jupyter_output><empty_output><jupyter_text>"Kitchen"Find unique features in "Kitchen"<jupyter_code>unique_features = find_unique_features(data, "Kitchen")
unique_features<jupyter_output><empty_output><jupyter_text>Extract numeric features from "Kitchen"<jupyter_code>#Probably don't care about that feature, too few entries
data_kitchen_size = []

for i in data["Kitchen"].values:
    kitchen_size = np.nan
    
    if not pd.isna(i):
        features = i.strip().split(", ")
        
        for feature in features:
            if feature.find("m²") != -1:
                kitchen_size = float(feature.strip().split()[1])
    data_kitchen_size.append(kitchen_size)

data["Kitchen size"] = data_kitchen_size<jupyter_output><empty_output><jupyter_text>Extract categorical features from "Kitchen"<jupyter_code>data_addition = []

for i in data["Kitchen"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("heated with firewood") != -1:
                current = ' '.join(feature.strip().split()[1:])
            elif feature.find("shove") != -1 or feature.find("stove") != -1 or feature.find("pliit") != -1:
                current = feature.strip().split()[0]
            
    data_addition.append(current)

data["Stove type"] = data_addition<jupyter_output><empty_output><jupyter_text>Separate the remaining features<jupyter_code>separate(data, {'kitchen furniture','open kitchen','refridgerator'}, "Kitchen")
data.drop(columns=["Kitchen"], inplace=True)<jupyter_output><empty_output><jupyter_text>"Sanitary arrangemenets"Find unique features in "Sanitary arrangements"<jupyter_code>unique_features = find_unique_features(data, "Sanitary arrangements")
unique_features<jupyter_output><empty_output><jupyter_text>Extract features from "Sanitary arrangements"<jupyter_code>separate(data, unique_features, "Sanitary arrangements")
data.drop(columns=["Sanitary arrangements"], inplace=True)<jupyter_output><empty_output><jupyter_text>"Heating and ventilation"Find unique features in "Heating and ventilation"<jupyter_code>unique_features = find_unique_features(data, "Heating and ventilation")
unique_features<jupyter_output><empty_output><jupyter_text>Extract categorical features from "Heating and ventilation"<jupyter_code>data_addition = []

for i in data["Heating and ventilation"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("heating") != -1:
                current = feature.strip().split()[0]
            elif feature.find("õhksoojuspump") != -1:
                current = "Air source heat pump"
    data_addition.append(current)

data["Heating type"] = data_addition<jupyter_output><empty_output><jupyter_text>Remove the now separated features from 'unique features'<jupyter_code>filtered_unique_features = [ x for x in unique_features if "heating" not in x and 
                                                            "õhksoojuspump" not in x ]<jupyter_output><empty_output><jupyter_text>Separate the remaining features<jupyter_code>separate(data, filtered_unique_features, "Heating and ventilation")
data.drop(columns=["Heating and ventilation"], inplace=True)<jupyter_output><empty_output><jupyter_text>"Communications and security"Find unique features in "Communications and security"<jupyter_code>unique_features = find_unique_features(data, "Communications and security")
unique_features<jupyter_output><empty_output><jupyter_text>Extract features from "Communications and security"<jupyter_code>separate(data, unique_features, "Communications and security")
data.drop(columns=["Communications and security"], inplace=True)<jupyter_output><empty_output><jupyter_text>"Neighbourhood"Find unique features in "Neighbourhood"<jupyter_code>unique_features = find_unique_features(data, "Neighbourhood")
unique_features
filtered_unique_features = [ x for x in unique_features if "sea" not in x and 
                                                            "meri" not in x and
                                                            "river" not in x and
                                                            "lake" not in x and
                                                            "tiik" not in x and
                                                            "Tiik" not in x and
                                                            "Oja" not in x and
                                                            "oja" not in x and
                                                            "karjäär" not in x and
                                                            "карьер" not in x and
                                                            "water body" not in x and
                                                            "beach length" not in x and
                                                            "laht" not in x and
                                                            "rand" not in x and
                                                            "jõgi" not in x and
                                                            "järv" not in x and
                                                            "море" not in x ]
filtered_unique_features<jupyter_output><empty_output><jupyter_text>Extract features from "Neighbourhood"<jupyter_code>data_addition = []

for i in data["Neighbourhood"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("condition") != -1:
                current = feature.strip().split()[2]
    data_addition.append(current)

data["Road condition"] = data_addition
data_addition = []

for i in data["Neighbourhood"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("condition") == -1:
                if feature.find("roads") != -1:
                    current = feature.strip().split()[1]
    data_addition.append(current)

data["Road type"] = data_addition
data_addition = []

for i in data["Neighbourhood"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("surrounding") != -1:
                current = ' '.join(feature.strip().split()[2:])
    data_addition.append(current)

data["Surrounding buildings"] = data_addition
data_addition = []

for i in data["Neighbourhood"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("location") != -1:
                if feature.find("city") != -1:
                    current = ' '.join(feature.strip().split()[0:-1])
                else:
                    current = ' '.join(feature.strip().split()[1:-1])
    data_addition.append(current)

data["Location type"] = data_addition
data_addition = []

for i in data["Neighbourhood"].values:
    current = np.nan

    if not pd.isna(i):
        features = i.strip().split(", ")

        for feature in features:
            if feature.find("neighbours") != -1:
                current = ' '.join(feature.strip().split()[1:])
                
    data_addition.append(current)

data["Nearby neighbours"] = data_addition
def separate_synonyms(dataset, uniques, column):
    for unique_features in uniques:
        data_addition = []

        for i in data[column].values:
            current = False

            if not pd.isna(i):
                features = i.strip().split(", ")

                for feature in features:
                    for unique_feature in unique_features:
                        if feature.find(unique_feature) != -1:
                            current = True
            data_addition.append(current)
        dataset[unique_features[0]] = data_addition
    

separate_synonyms(data, 
                  [['forest'], ['sea', 'meri', 'laht', 'море', 'rand'], 
                   ['river', 'jõgi'], ['lake', 'järv'], 
                   ['stream', 'oja', 'Oja'], 
                   ['pond', 'tiik', 'Tiik']], 
                  "Neighbourhood")

data.drop(columns=["Neighbourhood"], inplace=True)<jupyter_output><empty_output><jupyter_text>"This floor/Number of floors"Separate 'This floor/Number of floors' column<jupyter_code>data_addition_this = []
data_addition_numberof = []

for i in data["This floor/Number of floors"].values:
    current_this = np.nan
    current_numberof = np.nan
    
    if not pd.isna(i):
        features = i.strip().split("/")
        if len(features) == 2:
            current_this = features[0]
            current_numberof = features[1]
        
    data_addition_this.append(current_this)
    data_addition_numberof.append(current_numberof)

data["This floor"] = data_addition_this
data["Total number of floors"] = data_addition_numberof

data.drop(columns=["This floor/Number of floors"], inplace=True)<jupyter_output><empty_output><jupyter_text>"Location"Separate the location into latitude and longitude.<jupyter_code>data[["Latitude", "Longitude"]] = data.Location.str.split(",", expand=True)
data["Latitude"] = data["Latitude"].astype(float)
data["Longitude"] = data["Longitude"].astype(float)
data.drop(columns=["Location"], inplace=True)
data.values[:][0]<jupyter_output><empty_output><jupyter_text>Simple data type conversionsRemove the m2 from the area field to convert them to numeric.<jupyter_code>data["Total area"] = data['Total area'].astype('str').map(lambda x: x.split()[0]).astype('float64') #remove m2
data["Ground area"] = data['Ground area'].astype('str').map(lambda x: x.split()[0]).astype('float64') #remove m2<jupyter_output><empty_output><jupyter_text>Convert the "Energy mark" into a number.<jupyter_code>def remap_column(data, column, mapping):
    data[column] = data[column].map(mapping)
data["Energy mark"].unique()
# data["Energy mark"] = data["Energy mark"].map({'C': 2, 'B': 1, np.nan: 0, '-': 0, 'Missing': 0, 'A': 0, 'H': 7, 'E': 4, 'D': 3, 'G': 6, 'F': 5})
remap_column(data, "Energy mark", {'C': 2, 'B': 1, np.nan: 8, '-': 8, 'Missing': 8, 'A': 0, 'H': 7, 'E': 4, 'D': 3, 'G': 6, 'F': 5})
data["Energy mark"].unique()
data["Condition"].unique()
remap_column(data, "Condition", {'all brand-new': 6, 'Good condition': 5, 'ready': 5, 'needs renovating': 0, 'satisfactory': 1, np.nan: 1, 'sanitary renovation needed': 2, 'renovated': 4,'sanitary renovation done': 3})
data["Condition"].unique()
data["Readiness"].unique()
remap_column(data, "Readiness", {'ready': 4, 'roofed box': 2, np.nan: 4, 'box with doors and windows': 3, 'roofless box': 1, 'foundation': 0})
data["Readiness"].unique()
data["Ownership"].unique()
data = pd.get_dummies(data, columns=["Ownership"])<jupyter_output><empty_output><jupyter_text>Dealing with NaN values<jupyter_code># Create a function to assist in removing invalid values.
def fill_na_with_number(data, column, number):
    return data[column].fillna(value=number, inplace=True)
    
def fill_na_with_mode(data, column):
    return data[column].fillna(value=data[column].mode().values[0], inplace=True)

def fill_na_with_mean(data, column):
    return data[column].fillna(value=data[column].mean(), inplace=True)<jupyter_output><empty_output><jupyter_text>We have no use for any data, that doesn't have price<jupyter_code>data = data[data['Price'].notna()]<jupyter_output><empty_output><jupyter_text>Before dealing with NaN values, it needs to be determined, what are the problematic columns<jupyter_code>def get_percent_of_na(data, column):
    return round(data[column].isna().sum() / len(data) * 100, 2)

bad_columns = []
for column in data.columns:
    percent = get_percent_of_na(data, column)
    #print(percent, "%  >>    ", column)
    if percent > 30:
        print(percent, "%  >>    ", column)
        bad_columns.append(column)<jupyter_output>64.36 %  >>     Number of floors
72.14 %  >>     Ground area
77.77 %  >>     Bedrooms
88.69 %  >>     Register number
92.76 %  >>     Additional information
57.06 %  >>     Parking
82.08 %  >>     Wall
81.38 %  >>     Roof
92.41 %  >>     Kitchen size
53.29 %  >>     Stove type
69.35 %  >>     Road condition
93.58 %  >>     Road type
71.24 %  >>     Surrounding buildings
71.81 %  >>     Location type
79.87 %  >>     Nearby neighbours
38.51 %  >>     This floor
38.51 %  >>     Total number of floors<jupyter_text>Now, remove the problematic data entries or fill it with a certain value<jupyter_code>bad_columns.remove('Ground area')
fill_na_with_number(data, 'Ground area', 0) #this is better to be filled with zeros than remove

bad_columns.remove('Number of floors')
fill_na_with_number(data, 'Number of floors', 1) #this is better to be filled with ones than remove
bad_columns.remove('This floor')
fill_na_with_number(data, 'This floor', 1) #this is better to be filled with ones than remove
bad_columns.remove('Total number of floors')
fill_na_with_number(data, 'Total number of floors', 1) #this is better to be filled with ones than remove

data = data.drop(columns=bad_columns)
for column in data.columns:
    percent = get_percent_of_na(data, column)
    #print(percent, "%  >>    ", column)
    if percent > 0:
        print(percent, "%  >>    ", column)
#data.dtypes
data[["Rooms", "Total area", "Built in year", "Heating type"]].describe(include=[float, int, object, bool])
fill_na_with_mode(data, "Rooms")
fill_na_with_mean(data, "Total area")
fill_na_with_mode(data, "Built in year")
fill_na_with_mode(data, "Heating type")
for column in data.columns:
    percent = get_percent_of_na(data, column)
    #print(percent, "%  >>    ", column)
    if percent > 0:
        print(percent, "%  >>    ", column)<jupyter_output><empty_output><jupyter_text>Final analysis<jupyter_code>data

data.dtypes.unique()
print("The number of NaN values in this dataset is: ", sum(data.isna().sum()))
import seaborn as sns

sns.pairplot(data, 
             x_vars=data.columns,
             y_vars='Price', 
             kind='scatter')
data.corr(method ='pearson')
# Find the highest abs correlations.
pairwise_correlations = data.corr(method ='spearman').abs()
pairwise_correlations.values[np.triu_indices_from(pairwise_correlations, 0)] = np.nan
top_correlations = pairwise_correlations["Price"].sort_values(ascending=False).head(10)
top_correlations
import matplotlib.pyplot as plt
import seaborn as sns

sns.set_context("poster")
plt.rc("font", size=14)

fig, ax = plt.subplots(figsize=(30, 10))
sns.barplot(x=top_correlations.index, y=top_correlations, color="tab:blue")
plt.ylabel("Correlation to price")

plt.savefig("Correlation.pdf")
data.Condition
data_original = data.copy()
remap_column(data, "Condition", {6: 'All brand-new', 5: 'Good condition', 0: 'Needs renovating', 1: 'Satisfactory', 2: 'Sanitary renovation needed', 4: 'Renovated', 3: 'Sanitary renovation done'})
data["Condition"].unique()
import matplotlib.ticker as ticker

data.Condition
sns.set_context("poster")
plt.rc("font", size=14)

fig, ax = plt.subplots(figsize=(30, 10))
# sns.catplot(data=data, kind="violin", x="Condition", y="Price", split=True)
sns.violinplot(x=data.Condition, y=data.Price)
plt.ylim((0, 1000000))
plt.yticks([0, 200000, 400000, 600000, 800000, 1000000])
# plt.ticklabel_format(style="plain")
ax.yaxis.set_major_formatter(ticker.EngFormatter())

plt.savefig("Condition.pdf")<jupyter_output><empty_output><jupyter_text>Save the cleaned data<jupyter_code>data.to_csv("data/cleaned_data.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12605663079126056, 'fraction_numerical': 0.011455494480114554, 'mean_word_length': 3.0664913598797896, 'pattern_counts': {'":': 0, '<': 132, '<?xml version=': 0, '>': 182, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 42, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '33', 'dir': 'jupyter-structured-clean-dedup', 'id': '640286', 'license': 'MIT', 'n_tokens_mistral': 6867, 'n_tokens_neox': 6497, 'n_words': 1547, 'path': 'data_analysis.ipynb', 'repo_name': 'Jyrijoul/real_estate_prediction', 'size': '21649'}