Convert NumPy arrays to Pandas Dataframe with columns

Question

I want to normalize my both categorical and numeric values.

cols = df.columns.values.tolist()
df_num = df.drop(CAT_COLUMNS, axis=1)
df_num = df_num.as_matrix()
df_num = preprocessing.StandardScaler().fit_transform(df_num)

df.fillna('NA', inplace=True)
df_cat = df.T.to_dict().values()

vec_cat = DictVectorizer( sparse=False )
df_cat = vec_cat.fit_transform(df_cat)

After that I need to combine 2 numpy arrays back to pandas dataframe, but below approach doesn't work for me.

mas = np.hstack((df_num, df_cat))
df = pd.DataFrame(data=mas, columns=cols)

Error Message: ValueError: Shape of passed values is (475, 243), indices imply (83, 243)

One more approach:

columns = df.columns.values.tolist()
for col in columns:
    try:
        if col in CAT_COLUMNS:
            df[col] = pd.get_dummies(df[col])
        else:
            df[col] = df[col].apply(preprocessing.StandardScaler().fit)
    except Exception, err:
        print 'Column: %s and msg=%s' % (col, err.message)

Error Message:

Column: DATE and msg=Singleton array array(1444424400.0) cannot be considered a valid collection. Column: QTR_HR_START and msg=Singleton array array(21600000L, dtype=int64) cannot be considered a valid collection. ...

PS. Is there any way to avoid numpy et all? As example, I want to leverage on pandas_ml library

Answer 1

What you are looking for is pandas.get_dummies(). It will perform one hot encoding on categorical columns, and produce a dataframe as the result. From there you can use pandas.concat([existing_df, new_df],axis=0) to add the new columns to your existing dataframe. This will avoid the use of a numpy array.

An example of how it could be used:

for cat_column in CAT_COLUMNS:
    dummy_df = pd.get_dummies(df[column])

    #Optionally rename columns to indicate categorical feature name
    dummy_df.columns = ["%s_%s" % (cat_column, col) for col in dummy_df.columns]
    df = pd.concat([df, dummy_df], axis=1)