<jupyter_start><jupyter_text>Start merging first we merge datasets with same keys together, then we merge the bigger (already merged) datasets together<jupyter_code>from functools import reduce

dfs = [df_dic['diaries_transactions_all'], df_dic['demo_hh'] , df_dic['con_prod_hh_over_trx_yr_mo_usd'], df_dic['edu_hh']]

result_hhids = reduce(lambda df_left, df_right: pd.merge(df_left, df_right, 
                                              right_on = 'hh_ids', left_on = 'hh_ids', 
                                              how='outer'), 
                  dfs)
result_hhids.head()
dfs = [df_dic['con_pur_hh_by_trx_yr_mo_usd'], df_dic['inc_hh_by_trx_yr_mo_usd_incRR'], df_dic['pov_hh_by_trx_yr_mo_usd_excRR'], df_dic['pov_hh_by_trx_yr_mo_usd_incRR'], df_dic['rem_hh_by_trx_yr_mo_usd']]

result_hhids_trxyrmo = reduce(lambda df_left, df_right: pd.merge(df_left, df_right, 
                                              right_on = ['hh_ids', 'trx_yr_mo'], left_on =['hh_ids', 'trx_yr_mo'], 
                                              how= 'outer'),
                  dfs)
result_hhids_trxyrmo.head()
dfs = [df_dic['diaries_wellbeing_ind'], df_dic['diaries_goingson_hh']]

result_hhids_intdate = reduce(lambda df_left, df_right: pd.merge(df_left, df_right, 
                                              right_on = ['hh_ids', 'int_date'], left_on =['hh_ids', 'int_date'], 
                                              how= 'outer'),
                  dfs)

result_hhids_intdate.head()
dfs = [df_dic['demo_ind'], df_dic['edu_ind']]

result_hhids_inds = reduce(lambda df_left, df_right: pd.merge(df_left, df_right, 
                                              right_on = ['hh_ids', 'm_ids'], left_on =['hh_ids', 'm_ids'], 
                                              how= 'outer'),
                  dfs)

result_hhids_inds['m_ids'] = result_hhids_inds['m_ids'].astype(str) # convert to string for merging purposes
result_hhids_inds.head() 
# final part of merging

official_1 = pd.merge(result_hhids, result_hhids_trxyrmo, right_on = ['hh_ids', 'trx_yr_mo'], left_on =['hh_ids', 'trx_yr_mo'], 
                                              how= 'outer')


official_2 = pd.merge(official_1, result_hhids_intdate, right_on = ['hh_ids', 'int_date'], left_on =['hh_ids', 'int_date'], 
                                              how= 'outer')
official= pd.merge(official_2, result_hhids_inds, right_on = ['hh_ids', 'm_ids'], left_on =['hh_ids', 'm_ids_trx'], 
                                              how= 'outer')

official.head()
official[official['hh_ids'] == 'KELDK01'] # check 
official.replace('.', np.NaN) # replace NAs consistently along the whole dataset
official.replace(' ', np.NaN)
empty_cols = [col for col in official.columns if official[col].isnull().all()] # checking for empty columns 

print(empty_cols) # no empty columns
# drop unimportant cols 

official = official.drop(columns = ['trx_yr_mo','first_trx_date_hh', 'last_trx_date_hh', 'tot_hh_daysofobs', 'tot_hh_monthsofobs', 'interview_designation', 'int_date', 'int_month', 'int_year', 'int_yr_mo', 'first_int_date', 'unique_accnts', 'unique_hm_owner', 'tot_acc_daysofobs', 'tot_acc_monthsofobs', 'trx_stdtime_days_hh', 'trx_stdtime_mnths_hh', 'trx_stdtime_days_acc', 'trx_stdtime_mnths_acc', 'trx_family_desc', 'trx_class_desc', 'trx_type_desc', 'trx_mode_code', 'trx_place_incommunity', 'trx_distance_km', 'trx_value_kes', 'trx_inkind_units', 'trx_inkind_value_kes'], axis=1)

# drop trx_class_code column

official = official.drop(columns = ['trx_class_code'], axis = 1) # motivation: conflict with target prediction, see discussion in the report
# drop cols with more than 80% of NaNs

na_perc = official[official.columns[official.isnull().any()]].isnull().sum() * 100 / official.shape[0]
#print(na_perc)

nas_list = []
for i in na_perc.keys():
  if na_perc[i] > 80.0:
    nas_list.append(i)

official = official.drop(columns = nas_list) # DROP MOSTLY EMPTY COLUMNS

official.shape
perc = 80.0
min_count =  int(((100-perc)/100)*official.shape[1] + 1) # DROP MOSTLY EMPTY ROWS
official = official.dropna(axis=0, 
                    thresh=min_count) 

official.shape
%cd /content/drive/MyDrive/GRAD-C24_Machine_Learning/MLProject_KenyaFinancial
DATA_PATH = "/content/drive/MyDrive/GRAD-C24_Machine_Learning/MLProject_KenyaFinancial/MLProject_KenyaFinancial/clean_data/official.csv"
os.getcwd()

official = pd.read_csv(DATA_PATH)<jupyter_output>/content/drive/MyDrive/GRAD-C24_Machine_Learning/MLProject_KenyaFinancial<jupyter_text>Drop NaN values for the target value<jupyter_code>official = official[official['trx_mode_desc'].notna()] # drop na rows for trx_mode_desc
for column in official.columns: # convert them into the same object
  if official[column].dtype == "object":
    official[column] = official[column].astype("|S")<jupyter_output><empty_output><jupyter_text>Treat important variables that have inconsistent values: 'trx_outlet' and 'trx_purpose'<jupyter_code>Tol = []
for tol in official["trx_outlet"]:
    Tol.append(tol.decode('UTF-8'))
#!pip install stop-words

from stop_words import get_stop_words
sw = get_stop_words('english')
sw.extend(['large', 'small', 'kenya', 'kenyan', 'place', 'person'])
whitelist = set('abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ')

Tol1 = []
for val in Tol:
    val1 = ''.join(filter(whitelist.__contains__, val))
    val1 = val1.lower()
    val1 = ' '.join([word for word in val1.split() if word not in sw])
    Tol1.append(val1)
singlewords= []
for sentence in Tol1:
  firstword = sentence.split(sep=None, maxsplit=1)
  try:
    singlewords.append(firstword[0])
  except:
    if type(firstword) != 'list':
          singlewords.append(firstword)
    else:
      singlewords.append('')


official["trx_outlet1"] = singlewords
official["trx_outlet1"].astype(str).nunique()

Tol = []
for tol in official["trx_prx_purpose"]:
    Tol.append(tol.decode('UTF-8'))
###############
sw = get_stop_words('english')
sw.extend(['large', 'small', 'kenya', 'kenyan', 'place', 'person'])
whitelist = set('abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ')
##############
Tol1 = []
for val in Tol:
    val1 = ''.join(filter(whitelist.__contains__, val))
    val1 = val1.lower()
    val1 = ' '.join([word for word in val1.split() if word not in sw])
    Tol1.append(val1)
##############
singlewords= []
for sentence in Tol1:
  firstword = sentence.split(sep=None, maxsplit=1)
  try:
    singlewords.append(firstword[0])
  except:
    if type(firstword) != 'list':
          singlewords.append(firstword)
    else:
      singlewords.append('')
#############
official["trx_purpose1"] = singlewords

official["trx_purpose1"].astype(str).nunique()<jupyter_output><empty_output><jupyter_text>Drop old columns and add new ones<jupyter_code>official = official.drop(['trx_outlet', 'trx_prx_purpose'], axis = 1)<jupyter_output><empty_output><jupyter_text>Merged dataframe is ready to be split into train and test!<jupyter_code>os.getcwd()
os.makedirs('clean_data', exist_ok=True)  # make dataset available as csv for machine learning preprocessing
compression_opts = dict(method='zip',
                        archive_name='official.csv')
official.to_csv('clean_data/official.zip', index=False,
          compression=compression_opts)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13802083333333334, 'fraction_numerical': 0.008771929824561403, 'mean_word_length': 3.9404197698036563, 'pattern_counts': {'":': 1, '<': 20, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 22, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '279074', 'license': 'MIT', 'n_tokens_mistral': 2650, 'n_tokens_neox': 2540, 'n_words': 474, 'path': 'code/Cleaning.ipynb', 'repo_name': 'lauramenicacci/ML_project', 'size': '7296'}