<jupyter_start><jupyter_text>Find the dtype = object columns<jupyter_code>rent.select_dtypes('object').columns
rent.info(null_counts=True)<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2651885 entries, 0 to 2651884
Data columns (total 59 columns):
 #   Column                      Non-Null Count    Dtype  
---  ------                      --------------    -----  
 0   obid                        2651885 non-null  int64  
 1   mietekalt                   2651885 non-null  float64
 2   nebenkosten                 2651885 non-null  object 
 3   heizkosten                  2651885 non-null  object 
 4   baujahr                     1682820 non-null  float64
 5   letzte_modernisierung       2651885 non-null  object 
 6   wohnflaeche                 2651885 non-null  float64
 7   nutzflaeche                 2651885 non-null  object 
 8   etage                       2651885 non-null  object 
 9   anzahletagen                2651885 non-null  object 
 10  zimmeranzahl                2651885 non-null  object 
 11  nebenraeume                 2651885 non-null  object 
 12  schlafzimmer                2651885 non-null  object 
 1[...]<jupyter_text>Createnull_counts=copy of orginal dataframe for cleaning process<jupyter_code>rent_cleaned = rent.copy()<jupyter_output><empty_output><jupyter_text>Map city names to ags_2019 column<jupyter_code>ags_map = {11000000: 'Berlin', 6412000: 'Frankfurt', 2000000: 'Hamburg',
 5111000: 'Düsseldorf', 14612000: 'Dresden', 9162000: 'München',
 5315000: 'Köln', 5112000: 'Duisburg', 5913000: 'Dortmund',
 5113000: 'Essen', 4011000: 'Bremen', 3241001: 'Hannover', 
 8111000: 'Stuttgart', 14713000: 'Leipzig', 9564000: 'Nürnberg'
}
rent_cleaned.loc[:,'City'] = rent_cleaned.ags2019.map(ags_map)
rent_cleaned.ags2019.value_counts()<jupyter_output><empty_output><jupyter_text>Remove strings from object columns which should be np.nan<jupyter_code>nan_values = ['Sonstiges Missing', 'keine Angabe', 'unplausibler Wert geloescht', 'Variable nicht mehr in Einagbemaske vorhanden' ]
rent_cleaned = rent_cleaned.replace(nan_values, np.nan)<jupyter_output><empty_output><jupyter_text>Convert object columns to numeric<jupyter_code>float_columns = ['nebenkosten', 'heizkosten', 'letzte_modernisierung', 'nutzflaeche',
       'etage', 'anzahletagen', 'zimmeranzahl', 'nebenraeume', 'schlafzimmer',
       'badezimmer', 'ev_kennwert', 'click_customer']
rent_cleaned[float_columns] = rent_cleaned[float_columns].astype(float)<jupyter_output><empty_output><jupyter_text>Convert object columns to bool<jupyter_code>rent_cleaned.select_dtypes('object').columns
bool_columns = ['aufzug', 'balkon', 'einbaukueche', 'ev_wwenthalten',
       'foerderung', 'gaestewc', 'garten', 'heizkosten_in_wm_enthalten',
       'keller', 'parkplatz', 'rollstuhlgerecht', 
       'haustier_erlaubt']

rent_cleaned[bool_columns] = rent_cleaned[bool_columns].apply(lambda x: x.map({'Ja':True, 'Nein':False}).astype(bool))<jupyter_output><empty_output><jupyter_text>Convert object columns to date time<jupyter_code>rent_cleaned.adat = pd.to_datetime(rent_cleaned.adat, format='%Ym%m')
rent_cleaned.edat = pd.to_datetime(rent_cleaned.edat, format='%Ym%m')
rent_cleaned.select_dtypes('object').columns<jupyter_output><empty_output><jupyter_text>Drop columns without information<jupyter_code>drop_columns = ['bauphase', 'bef6', 'bef7', 'bef8', 'bef9', 'bef10']
rent_cleaned = rent_cleaned.drop(columns=drop_columns)<jupyter_output><empty_output><jupyter_text>Replace -9 in obejct columns to np.nan<jupyter_code>firing_columns=['bef1', 'bef2', 'bef3', 'bef4', 'bef5']
rent_cleaned[firing_columns] = rent_cleaned[firing_columns].replace('-9', np.nan)<jupyter_output><empty_output><jupyter_text>Create dummy columns from Firing Type columns<jupyter_code># Define Set for unique firing types
firingTypes = set()

# loop over bef1 - bef5 columns
for col in firing_columns:
    # loop over firing types in bef
    for firing in rent_cleaned[col].value_counts().index:
        firingTypes.add(firing)
# Output of firing types
firingTypes

# Create a bef df for each beff column with firing types dummies
# reindex bef df that each df has same columns
# fillna each dummy column with 0 and set astype int
bef = []
for col in firing_columns:
    bef.append(pd.get_dummies(rent_cleaned[col]).reindex(columns=firingTypes).fillna(0).astype(int))

# Sum all bef df and join this to rent_cleaned df
rent_cleaned = rent_cleaned.join(sum(bef))
# Drop no longer used bef columns
rent_cleaned = rent_cleaned.drop(columns=firing_columns)
rent_cleaned.select_dtypes('object')
rent_cleaned.immobilientyp.value_counts()<jupyter_output><empty_output><jupyter_text>Drop immonbilientyp column it is only one value and lieferung column it is only delivery data from Immoscout to rwi-essen<jupyter_code>rent_cleaned = rent_cleaned.drop(columns=['immobilientyp', 'lieferung'])<jupyter_output><empty_output><jupyter_text>Replace -9 in anbieter column to np.nan<jupyter_code>rent_cleaned.anbieter = rent_cleaned.anbieter.replace('-9', np.nan)

object_columns = rent_cleaned.select_dtypes('object').columns
object_columns<jupyter_output><empty_output><jupyter_text>Drop columns nebenraeume because there are only 182 non-null values<jupyter_code>rent_cleaned = rent_cleaned.drop(columns=['nebenraeume'])<jupyter_output><empty_output><jupyter_text>How to deal with the rest of missing values?The columns `['etage', 'anzahletagen', 'zimmeranzahl', 'schlafzimmer', 'badezimmer', 'click_customer']` are set to -100 to seperate the nan values clearly from the rest of the values. For the other missing value columns the decission what is to do follows to the specific use.<jupyter_code>rent_cleaned[['nebenkosten', 'heizkosten', 'baujahr', 'letzte_modernisierung', 
              'nutzflaeche', 'etage', 'anzahletagen', 'zimmeranzahl', 
              'schlafzimmer', 'badezimmer', 'ev_kennwert', 'click_customer']]
fillna_columns = ['etage', 'anzahletagen', 'zimmeranzahl', 'schlafzimmer', 
                  'badezimmer', 'ev_kennwert', 'click_customer']
rent_cleaned[fillna_columns] = rent_cleaned[fillna_columns].fillna(-100)
missing_value_columns = ['nebenkosten', 'heizkosten', 'baujahr', 
                         'letzte_modernisierung', 'nutzflaeche', 
                         'etage', 'anzahletagen', 'zimmeranzahl', 
                         'schlafzimmer', 'badezimmer', 'ev_kennwert', 
                         'click_customer']
rent_cleaned[missing_value_columns].describe()<jupyter_output><empty_output><jupyter_text>Create year column<jupyter_code>rent_cleaned['year'] = rent_cleaned.edat.dt.year


rent_cleaned.info(null_counts=True)<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2651885 entries, 0 to 2651884
Data columns (total 73 columns):
 #   Column                            Non-Null Count    Dtype         
---  ------                            --------------    -----         
 0   obid                              2651885 non-null  int64         
 1   mietekalt                         2651885 non-null  float64       
 2   nebenkosten                       2490467 non-null  float64       
 3   heizkosten                        824489 non-null   float64       
 4   baujahr                           1682820 non-null  float64       
 5   letzte_modernisierung             556602 non-null   float64       
 6   wohnflaeche                       2651885 non-null  float64       
 7   nutzflaeche                       630468 non-null   float64       
 8   etage                             2167254 non-null  float64       
 9   anzahletagen                      1605995 non-null  float64       
 10  zimmeranzahl      [...]<jupyter_text>Pickle the dataframe<jupyter_code>rent_cleaned.to_pickle('data/rent_cleaned.pkl')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1061560210769824, 'fraction_numerical': 0.05102171957331963, 'mean_word_length': 3.4982658959537574, 'pattern_counts': {'":': 0, '<': 65, '<?xml version=': 0, '>': 65, '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': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '516428', 'license': 'MIT', 'n_tokens_mistral': 2922, 'n_tokens_neox': 2611, 'n_words': 530, 'path': 'Campus_Data.ipynb', 'repo_name': 'DigiData-Flo/German_Hoisung_Prices', 'size': '7781'}