<jupyter_start><jupyter_text>flats-in-cracow data wrangling  Imports<jupyter_code>import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import unidecode

from collections import Counter
from IPython.display import display
from sklearn.impute import KNNImputer
from pylab import rcParams
from pathlib import Path<jupyter_output><empty_output><jupyter_text>Setup<jupyter_code># Create directory for images 
Path("img").mkdir(parents=True, exist_ok=True)

# Set default figure size
rcParams['figure.figsize'] = (4, 4)

# Tell pandas how to display floats
pd.options.display.float_format = "{:,.2f}".format<jupyter_output><empty_output><jupyter_text>Goal I scraped listings of properties for sale in Cracow. We would like to create a model to predict flat prices.  Data source Data has been scraped from a website with listings. The data has undergone small transformations along the way. The goal of these transformations was to get the data into a usable state not to check it's validity.  Data loading<jupyter_code>path = '../flats-data/raw_data.csv'
data = pd.read_csv(path, lineterminator='\n')
data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 61307 entries, 0 to 61306
Data columns (total 24 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   Date         61137 non-null  object 
 1   City         47144 non-null  object 
 2   District     33916 non-null  object 
 3   Amount       61077 non-null  float64
 4   Currency     61077 non-null  object 
 5   Property     60720 non-null  object 
 6   Seller       60970 non-null  object 
 7   Area         60819 non-null  float64
 8   Rooms        60102 non-null  float64
 9   Bathrooms    39187 non-null  float64
 10  Parking      26408 non-null  object 
 11  Garden       61307 non-null  bool   
 12  Balcony      61307 non-null  bool   
 13  Terrace      61307 non-null  bool   
 14  Basement     61307 non-null  bool   
 15  New          61307 non-null  bool   
 16  Block        61307 non-null  bool   
 17  Townhouse    61307 non-null  bool   
 18  Apartment    61307 non-null  bool   
 19  Bus [...]<jupyter_text>First we sort the data in from newest to oldest, forcing rows with missing `Date` values to be last.<jupyter_code>data = data.sort_values(by='Date', 
                        ascending=False, 
                        na_position='last', 
                        ignore_index=True)<jupyter_output><empty_output><jupyter_text>Next we assume that the `Title` column uniquely identifies a listing.<jupyter_code>data = data.drop_duplicates(['Title'], keep='first')<jupyter_output><empty_output><jupyter_text>After this the shape of the data is:<jupyter_code>print(data.shape)<jupyter_output>(10604, 24)<jupyter_text>Data exploration We check for missing values that we will have to deal with.<jupyter_code>missing = data.isnull().sum(axis=0)
missing.name = 'Missing'
missing = missing.to_frame()
missing = missing[missing['Missing'] > 0]
missing.sort_values('Missing', ascending=False)<jupyter_output><empty_output><jupyter_text>Check numeric columns We see that we have 24 columns at our disposal. We inspect the numeric columns to see what we are dealing with. In the `Amount` column we note there is a property for sale that costs 1PLN, clearly a erroneous value. Next we note that the enourmous maximum in the `Amount` column. That is quite a lot of money and could be considered a potential outlier.The maximum and minimum of the `Area` column also indicate the existance of outliers. These values are clearly too large. The data will need to undergo a filtering process.<jupyter_code>data.describe()<jupyter_output><empty_output><jupyter_text>Check binary columns We inspect the data to see if binary columns are properly populated and check for imbalances.<jupyter_code>binary = data.select_dtypes(bool).columns.to_list()

for col in binary:
    tmp = data[[col, 'Amount']]
    tmp = tmp.fillna('NaN')
    tmp = tmp.groupby(col, as_index=False)
    tmp = tmp.count()
    tmp = tmp.rename(columns={'Amount': 'Count'})
    tmp = tmp.sort_values('Count', ascending=False)
    tmp = tmp.reset_index(drop=True)
    display(tmp)<jupyter_output><empty_output><jupyter_text>Check categorical columns We inspect categorical columns to assert that they contain "valid" values. Most of these columns were generated by a script during the scraping and etl phase of the project.<jupyter_code>categorical = data.select_dtypes('object').columns
categorical = categorical.to_list()
omit = ['Title', 'Link', 'Description', 'Date']

for col in categorical:
    if col not in omit:
        tmp = data[['Amount', col]].copy()
        tmp = tmp.fillna('NaN')
        tmp = tmp.groupby(col, as_index=False)
        tmp = tmp.count()
        tmp = tmp.rename(columns={'Amount': 'Count'})
        tmp = tmp.sort_values('Count', ascending=False)
        tmp = tmp.reset_index(drop=True)
        display(tmp)<jupyter_output><empty_output><jupyter_text>Data cleaning We assume that if we know the district, the `City` is `kraków`.<jupyter_code>mask = (data['City'].isna() == True) & (data['District'].isna() == False)
data.loc[mask, 'City'] = 'kraków'<jupyter_output><empty_output><jupyter_text>We extract more `Parking` information from the property description.<jupyter_code>def extract_parking(x):
    if ('garaż' in x or 'garaz' in x or 'parking' in x) and 'podziemny' in x:
        return 'covered'
    elif ('garaż' in x or 'garaz' in x) and 'podziemny' not in x:
        return 'garage'
    elif 'parking' in x and 'podziemny' not in x:
        return 'street'
    else:
        return 'no parking'
mask = (data['Parking'].isna() == True) & (data['Description'].isna() == False)
data.loc[mask, ['Parking', 'Description']] = data.loc[mask, 'Description'].apply(extract_parking)
mask = data['Parking'].isna() == True
data.loc[mask, 'Parking'] = 'no parking'<jupyter_output><empty_output><jupyter_text>We confirm that we have dealt with all the `NaN`s in the `Parking` column.<jupyter_code>print(data['Parking'].isna().sum())
text = data[data['Description'].isna() == False].copy()
text = text['Description'].to_list()
text = ' '.join(text)
text = text.split(' ')
text = [x for x in text if x.isalpha()]
text = [x for x in text if len(x) > 3]<jupyter_output><empty_output><jupyter_text>Filtering Next we filter the data according to these rules:<jupyter_code>data = data[data['City'] == 'kraków']
data = data[data['Currency'] == 'pln']
data = data[data['Property'] == 'flat']
data = data[(data['Amount'] >= data['Amount'].quantile(0.025))]
data = data[(data['Amount'] <= data['Amount'].quantile(0.975))]
data = data[(data['Area'] >= data['Area'].quantile(0.01))]
data = data[(data['Area'] <= data['Area'].quantile(0.99))]
data = data[data['District'] != 'unknown']
data = data[data['District'].isna() == False]
data = data[data['Seller'].isna() == False]
data = data[data['Description'].isna() == False]
data = data.reset_index(drop=True)
data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 4659 entries, 0 to 4658
Data columns (total 24 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   Date         4659 non-null   object 
 1   City         4659 non-null   object 
 2   District     4659 non-null   object 
 3   Amount       4659 non-null   float64
 4   Currency     4659 non-null   object 
 5   Property     4659 non-null   object 
 6   Seller       4659 non-null   object 
 7   Area         4659 non-null   float64
 8   Rooms        4602 non-null   float64
 9   Bathrooms    2266 non-null   float64
 10  Parking      4659 non-null   object 
 11  Garden       4659 non-null   bool   
 12  Balcony      4659 non-null   bool   
 13  Terrace      4659 non-null   bool   
 14  Basement     4659 non-null   bool   
 15  New          4659 non-null   bool   
 16  Block        4659 non-null   bool   
 17  Townhouse    4659 non-null   bool   
 18  Apartment    4659 non-null   bool   
 19  Bus st[...]<jupyter_text>Impute missing values The next step is to fill in missing values for numeric columns `Amount` `Area` `Rooms` and `Bathrooms`. We use the `KNNImputer` to accomplish this.<jupyter_code>numeric = list(data.select_dtypes('number').columns)
mask = (data['Bathrooms'].isna() == True | data['Rooms'].isna())
missing = data[numeric]

imputer = KNNImputer(n_neighbors=5)
imputer.fit(missing)

missing = imputer.transform(missing)
missing = pd.DataFrame(missing, columns=numeric)

for col in numeric:
    data[col] = missing[col]
    
for col in numeric:
    data[col] = data[col].apply(lambda x: round(x))    
print(data.shape)<jupyter_output>(4659, 24)<jupyter_text>Text data<jupyter_code>data_text = data[['Amount', 'Description']].copy()
data_text['Description'] = data_text['Description'].apply(unidecode.unidecode)
text = data_text[data_text['Description'].isna() == False].copy()
text = text['Description'].to_list()
text = ' '.join(text)
text = text.split(' ')
text = [x for x in text if x.isalpha()]
text = [x for x in text if len(x) > 3]
unigrams_cnt = Counter(text)
bigrams = [(text[i], text[i+1]) for i in range(0, len(text)-1)]
bigrams_cnt = Counter(bigrams)
# bigrams_cnt.most_common(50)
trigrams = [(text[i], text[i+1], text[i+2]) for i in range(0, len(text)-2)]
trigrams_cnt = Counter(trigrams)
# trigrams_cnt.most_common(50)
for x in unigrams_cnt.most_common(100):
    if x[1] > 25:
        string = x[0]
        name = 'uni_' + x[0]
        data_text[name] = data_text['Description'].str.contains(string).astype(int)
for x in bigrams_cnt.most_common(100):
    if x[1] > 25:
        string = ' '.join(x[0])
        name = 'bi_'+'_'.join(x[0])    
        data_text[name] = data_text['Description'].str.contains(string).astype(int)    
for x in trigrams_cnt.most_common(100):
    if x[1] > 25:
        string = ' '.join(x[0])
        name = 'tri_'+'_'.join(x[0])    
        data_text[name] = data_text['Description'].str.contains(string).astype(int)    
cols = [x for x in data_text.columns if 'uni_' in x or 'bi_' in x or 'tri_' in x]
corrs = data_text[cols].corrwith(data_text['Amount']).to_frame()
corrs = corrs.reset_index()
corrs.columns = ['N-Gram', 'Correlation']
corrs['Correlation'] = np.abs(corrs['Correlation'])
corrs = corrs.sort_values('Correlation', ascending=False)
print(corrs.head(75).to_string())<jupyter_output>N-Gram  Correlation
12                             uni_covered         0.11
209      tri_mieszkania_przynalezy_piwnica         0.10
127           bi_przyjemnosc_zaprezentowac         0.09
63                             uni_piwnica         0.09
124                  bi_przynalezy_piwnica         0.08
101               bi_mieszkania_przynalezy         0.08
238   tri_przyjemnosc_zaprezentowac_oferte         0.08
188                bi_zaprezentowac_oferte         0.08
37                               uni_bloku         0.07
240     tri_zaprezentowac_oferte_sprzedazy         0.07
87                           uni_miejskiej         0.07
24                          uni_przynalezy         0.07
291   tri_przystanki_komunikacji_miejskiej         0.07
62                              uni_garazu         0.07
181                    bi_oferte_sprzedazy         0.07
79                              uni_pokoju         0.06
149                     bi_sieci_miejskiej      [...]<jupyter_text>Save data Verify that there are no `NaN`s in data.<jupyter_code>data.isnull().sum().sum()<jupyter_output><empty_output><jupyter_text>Remove columns that will not be used further.<jupyter_code>data = data.drop(['Title', 
                  'Description', 
                  'Link', 
                  'Property', 
                  'City', 
                  'Currency', 
                  'Date'], axis=1)<jupyter_output><empty_output><jupyter_text>Take a last peek at the data.<jupyter_code>data.head()
data.describe()<jupyter_output><empty_output><jupyter_text>Save it for further analysis.<jupyter_code>data.to_csv('../flats-data/cleaned_data.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11915247195679268, 'fraction_numerical': 0.03647694225176568, 'mean_word_length': 3.1762664816099933, 'pattern_counts': {'":': 0, '<': 80, '<?xml version=': 0, '>': 86, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '362158', 'license': 'MIT', 'n_tokens_mistral': 4288, 'n_tokens_neox': 3912, 'n_words': 1071, 'path': 'flats-notebooks/00_Data_Wrangling.ipynb', 'repo_name': 'besiobu/flats-in-cracow', 'size': '12035'}