<jupyter_start><jupyter_text>1. Create the sample dataset with reduced number of variables (510)<jupyter_code># # Use a smaller data set to save time
# df = pd.read_csv('PHBsample14_sss.csv', low_memory=False)

# # drop the unnecessary columns due to csv import
# df.drop('Unnamed: 0', axis=1, inplace=True)
# Load the full dataset
pandas2ri.activate()
readRDS = r['readRDS']
t0 = time()
rdf = readRDS('/data/capstone_data/ClusterData_allPHB_allvendorvars_editedcolumns.rds')
df = pandas2ri.ri2py_dataframe(rdf)
t1 = time()
df[8,].score_C4
print(df.shape)
print((t1-t0)/60)
df_vars = pd.read_csv('/home/capsops/mandy/selectedVariables.csv', encoding = 'latin1')
# df_vars.drop('Unnamed: 0', axis=1, inplace=True)
df_vars.shape
len(df_vars.loc[df_vars.Delete == 0, ].Variable.values)
columnMask = [True if x in df_vars.loc[df_vars.Delete == 0, ].Variable.values else False for x in df.columns.values]

df = df.iloc[:, columnMask]
df.shape<jupyter_output><empty_output><jupyter_text>how was the selectedVariables determined?1. manually deleted over 200 irrelevant features; deleted features with > 50% missing values2. run pandas_profiling to deleted over 200 correlated features3. delete variables with the value '-2147483648'4. delete variable 'CEN_bg_GINIIncome' since it's constant5. replace the value of 9992 in variable 'i24aunf1_C4' with NA6. rerun pandas_profiling to detect correlated variables, found the following 4: 'First.Mortgage.Amount.in.1000s.of.Dollars_C3', 'Target.Home.Market.Value_C3', 'Total.Mortgage.Amounts.in.1000s.of.Dollars_C3', 'WDModelFilterIn'7. add 'PolNum_UW' back8. delete the features with > 50% missing values9. delete Match1,3,4 (indicator variables -- if all C4 are NaN then Match4 is N, otherwise Y)10. delete 'Target.Narrow.Band.Income.Indicator_C3' -- categorization of variable 'Target.Narrow.Band.Income_num'  Codes used in the above steps:  Replace strange values with NA-2147483648: appears in multiple variables df.replace(-2147483648, np.NaN, inplace = True)9992: i24aunf1_C4df.i24aunf1_C4.replace('9992.0', np.NaN, inplace = True) Remove the columns with more than 50% missing valuespercNA = df.isnull().sum()/df.shape[0]columnMask = percNA > 0.5df = df.loc[:, -columnMask]df.head(0).to_csv('selectedVariables.csv')  2. replace the -2147483648 value with NaN<jupyter_code>df.replace(-2147483648, np.NaN, inplace = True)<jupyter_output>/home/capsops/.conda/envs/capsenvs/lib/python3.6/site-packages/pandas/core/missing.py:50: RuntimeWarning: invalid value encountered in equal
  mask = arr == x<jupyter_text>3. Encode the following boolean variables<jupyter_code>for col in ['JointInd', 'GMDBInd', 'EligibleInd', 'FirstEligQInd', 'UtilizationInd']:
    df.loc[:, col].replace(1.0, 1, inplace = True)
    df.loc[:, col].replace(0.0, -1, inplace = True)
df.loc[:, 'Qual'].replace('Q', 1, inplace = True)
df.loc[:, 'Qual'].replace('N', -1, inplace = True)
df.loc[:, 'OriginalOwner_C1'].replace('Y', 1, inplace = True)
df.loc[:, 'OriginalOwner_C1'].replace('N', -1, inplace = True)<jupyter_output><empty_output><jupyter_text>4. Change the values in "Percent" columns into values between 0 and 1<jupyter_code>df_vars.loc[(df_vars.Delete == 0)&(df_vars.DataMunging == 'divide by 100'), ].Variable.values
for col in df_vars.loc[(df_vars.Delete == 0)&(df_vars.DataMunging == 'divide by 100'), ].Variable.values:
    df.loc[:, col] = df.loc[:, col]/100<jupyter_output><empty_output><jupyter_text>5. reorder the features by type (PolicyNum + Boolean + Categorical + Positive Integer variables + Positive Numeric + Percentage Numeric + Real Value Numeric)<jupyter_code># columnTypes = df.dtypes
# columnUniqueCounts = df.T.apply(lambda x: x.nunique(), axis=1)
# columnSummary = pd.concat((columnTypes, columnUniqueCounts), axis = 1)
# columnSummary.columns = ['columnType', 'uniqueCounts']

# columnSummary.loc[(columnSummary.columnType == 'float64') & (columnSummary.uniqueCounts < 10), ]
columnMask = np.concatenate(
    [
        df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'PK'), ].Variable.values
      , df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'boolean'), ].Variable.values
      , df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'categorical'), ].Variable.values
      , df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'positive integer'), ].Variable.values
      , df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'positive numeric'), ].Variable.values
      , df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'numeric between 0 and 1'), ].Variable.values
      , df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'real value numeric'), ].Variable.values
    ]
)
df = df.loc[:, columnMask]
[
    len(df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'PK'), ].Variable.values)
  , len(df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'boolean'), ].Variable.values)
  , len(df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'categorical'), ].Variable.values)
  , len(df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'positive integer'), ].Variable.values)
  , len(df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'positive numeric'), ].Variable.values)
  , len(df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'numeric between 0 and 1'), ].Variable.values)
  , len(df_vars.loc[(df_vars.Delete == 0)&(df_vars.Type == 'real value numeric'), ].Variable.values)
]
# PK: 1
# Boolean: 2:8
# Categorical: 9:20
# Positive Integer: 21:135
# Positive Numeric: 136:187
# Percentage Numeric: 188:500
# Real Value Numeric: 501:510<jupyter_output><empty_output><jupyter_text>6. Export cleaned data to RDS file<jupyter_code>df.shape
df.head()
t0 = time()
df.to_csv("allDataCleaned.csv", compression='gzip')
t1 = time()
print((t1-t0)/60/60)<jupyter_output>3.1714428997702067
{'fraction_non_alphanumeric': 0.15829846582984658, 'fraction_numerical': 0.044630404463040445, 'mean_word_length': 5.475169300225733, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 26, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '273863', 'license': 'MIT', 'n_tokens_mistral': 2221, 'n_tokens_neox': 2057, 'n_words': 448, 'path': 'dataMunging/DataMunging-Python.ipynb', 'repo_name': 'mbzhuang/CustomerSegmentationforVAProducts', 'size': '5736'}