How to merge predicted values back to original DataFrame in Pandas and sklearn

Question

first tip toe into using sklearn with pandas so apologies if this may be a basic question. This is my code:

import pandas as pd
from sklearn.linear_model import LogisticRegression

X = df[predictors]
y = df['Plc']

X_train = X[:int(X.shape[0]*0.7)]
X_test = X[int(X.shape[0]*0.7):]
y_train = y[:int(X.shape[0]*0.7)]
y_test = y[int(X.shape[0]*0.7):]


model = LogisticRegression(max_iter=1000)
model.fit(X_train, y_train)
result = model.score(X_test, y_test)
print("Accuracy: %.3f%%" % (result*100.0))

Now what I am hoping to do is get the predicted values back into the original df so i can have a look at the difference between the actual df['Plc'] column and the predicted values for the y_test.

I have tried this but feel its a) probably not the best way and b) the index numbers aren't lining up as expected.

y_pred = pd.DataFrame()
y_pred['preds'] = model.predict(X_test)
y_test = pd.DataFrame(y_test)
y_test['index1'] = y_test.index
y_test = y_test.reset_index()
y_test = pd.concat([y_test,y_pred],axis=1)
y_test.set_index('index1')
df = df.reset_index()
df_out = pd.merge(df,y_test,how = 'inner',left_index = True, right_index = True)

Any ideas on what I should be doing instead? Thanks!

Answer 1

You can define the preds column in df "on the fly", without creating other dataframes:

import pandas as pd
import numpy as np
from sklearn.linear_model import LogisticRegression

# Generate fake data
df = pd.DataFrame(np.random.rand(1000, 4),
                  columns = list('abcd'))
df['Plc'] = np.random.randint(0,2,1000)

# Split X and y
predictors = list('abcd')
X = df[predictors]
y = df['Plc']

# Split train and test
train_size = int(X.shape[0]*0.7)
X_train = X[:train_size]
X_test = X[train_size:]
y_train = y[:train_size]
y_test = y[train_size:]

# Train the model
model = LogisticRegression(max_iter=1000)
model.fit(X_train, y_train)

# Predict train and test
y_pred_train = model.predict(X_train)
y_pred_test = model.predict(X_test)

Now you have at least two options:

• Stack the prediction and create the column based on the stacked array:

df['preds'] = np.hstack([y_pred_train, y_pred_test])

• Initialize the column and then assign predictions:

df['preds'] = np.nan
df.loc[:train_size-1, 'pred'] = y_pred_train
df.loc[train_size:, 'pred'] = y_pred_test

They yield the same result.

Answer 2

I believe what you want is to merge X_test, y_test and y_pred into the same dataframe (as there's no use to have X_train) here. I think it's easy to use train_test_split with Pandas to keep the indices (though there's a way to use numpy too Scikit-learn train_test_split with indices). I'm going to use iris as toy data here but you get the idea.

from sklearn.datasets import load_iris
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
X, y = load_iris(return_X_y=True)
X = pd.DataFrame(X)
y = pd.Series(y)
### you can use shuffle = False instead of random if it's needed
X_train,X_test,y_train,y_test=train_test_split(X,y,test_size=0.3,random_state=42)

model = LogisticRegression(max_iter=1000)
model.fit(X_train, y_train)
df = X_test.copy()
df['Plc']= y_test
df.reset_index(inplace=True)
df['pred'] = model.predict(X_test)

## then print df, you can remove the index of the original df if you like

if you really want to merge X_train,y_train and have NaN in the pred column, you can merge X_train and y_train the same fashion and use pd.concat to make a single dataframe

df2 = X_train.copy()
df2['Plc'] = y_train
df2.reset_index(inplace=True)
pd.concat([df,df2])

index   0   1   2   3   Plc pred
0   73  6.1 2.8 4.7 1.2 1   1.0
1   18  5.7 3.8 1.7 0.3 0   0.0
2   118 7.7 2.6 6.9 2.3 2   2.0
3   78  6.0 2.9 4.5 1.5 1   1.0
4   76  6.8 2.8 4.8 1.4 1   1.0
... ... ... ... ... ... ... ...
100 71  6.1 2.8 4.0 1.3 1   NaN
101 106 4.9 2.5 4.5 1.7 2   NaN
102 14  5.8 4.0 1.2 0.2 0   NaN
103 92  5.8 2.6 4.0 1.2 1   NaN
104 102 7.1 3.0 5.9 2.1 2   NaN
150 rows × 7 columns

Answer 3

Becase your X_test corresponds to X_test = X[int(X.shape[0]*0.7):], which is the last 30% of your samples, you can add your prediction results at the lower 30% part of your original dataframe:

Z=model.predict(X_test)
df.loc[int(X.shape[0]*0.7):,'predictions']=Z

Here we have a new column called 'prediction's in df. An example if your dataframe is:

df=pd.DataFrame({'predictor1':[0.1,0.3,0.3,0.3,0.5,0.9,0.02,0.8,0.8,0.75],
             'predictor2':[0.1,0.4,0.4,0.5,0.5,0.9,0.02,0.8,0.8,0.75],
        'Plc':np.array([0,1,1,1,1,1,1,0,1,1])})
predictor=['predictor1','predictor2']

It gives you the result:

   predictor1  predictor2  Plc  predictions
0        0.10        0.10    0          NaN
1        0.30        0.40    1          NaN
2        0.30        0.40    1          NaN
3        0.30        0.50    1          NaN
4        0.50        0.50    1          NaN
5        0.90        0.90    1          NaN
6        0.02        0.02    1          NaN
7        0.80        0.80    0          1.0
8        0.80        0.80    1          1.0
9        0.75        0.75    1          1.0

Where Z=[1,1,1] is added to the last 3 samples.