Python pandas linear regression groupby

Question

I am trying to use a linear regression on a group by pandas python dataframe:

This is the dataframe df:

  group      date      value
    A     01-02-2016     16 
    A     01-03-2016     15 
    A     01-04-2016     14 
    A     01-05-2016     17 
    A     01-06-2016     19 
    A     01-07-2016     20 
    B     01-02-2016     16 
    B     01-03-2016     13 
    B     01-04-2016     13 
    C     01-02-2016     16 
    C     01-03-2016     16 

#import standard packages
import pandas as pd
import numpy as np

#import ML packages
from sklearn.linear_model import LinearRegression

#First, let's group the data by group
df_group = df.groupby('group')

#Then, we need to change the date to integer
df['date'] = pd.to_datetime(df['date'])  
df['date_delta'] = (df['date'] - df['date'].min())  / np.timedelta64(1,'D')

Now I want to predict the value for each group for 01-10-2016.

I want to get to a new dataframe like this:

group      01-10-2016
  A      predicted value
  B      predicted value
  C      predicted value

This How to apply OLS from statsmodels to groupby doesn't work

for group in df_group.groups.keys():
      df= df_group.get_group(group)
      X = df['date_delta'] 
      y = df['value']
      model = LinearRegression(y, X)
      results = model.fit(X, y)
      print results.summary()

I get the following error

ValueError: Found arrays with inconsistent numbers of samples: [ 1 52]

DeprecationWarning: Passing 1d arrays as data is deprecated in 0.17 and   willraise ValueError in 0.19. Reshape your data either using X.reshape(-1, 1) if your data has a single feature or X.reshape(1, -1) if it contains a single sample.DeprecationWarning)

UPDATE:

I changed it to

for group in df_group.groups.keys():
      df= df_group.get_group(group)
      X = df[['date_delta']]
      y = df.value
      model = LinearRegression(y, X)
      results = model.fit(X, y)
      print results.summary()

and now I get this error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Turnip

@ayhan - done! thank you – Turnip

You clobbered your df in the loop. – Neocolonialism

I think the problem is your constructor call. You're passing y, an array of values to the constructor's fit_intercept parameter which takes a boolean and you're passing X another array to the copy_X boolean. – Polysaccharide

You want your date_delta to be calculated relative to the min date for whole df? – Neocolonialism

I want to calculate value for each group for a date_delta in the future ( 01-10-2016) – Turnip

Answer 1

New Answer

def model(df, delta):
    y = df[['value']].values
    X = df[['date_delta']].values
    return np.squeeze(LinearRegression().fit(X, y).predict(delta))

def group_predictions(df, date):
    date = pd.to_datetime(date)
    df.date = pd.to_datetime(df.date)

    day = np.timedelta64(1, 'D')
    mn = df.date.min()
    df['date_delta'] = df.date.sub(mn).div(day)

    dd = (date - mn) / day

    return df.groupby('group').apply(model, delta=dd)

demo

group_predictions(df, '01-10-2016')

group
A    22.333333333333332
B     3.500000000000007
C                  16.0
dtype: object

Old Answer

You're using LinearRegression wrong.

• you don't call it with the data and fit with the data. Just call the class like this
  • model = LinearRegression()
• then fit with
  • model.fit(X, y)

But all that does is set value in the object stored in model There is no nice summary method. There probably is one somewhere, but I know the one in statsmodels soooo, see below

---

option 1
use statsmodels instead

from statsmodels.formula.api import ols

for k, g in df_group:
    model = ols('value ~ date_delta', g)
    results = model.fit()
    print(results.summary())

---

                        OLS Regression Results                            
==============================================================================
Dep. Variable:                  value   R-squared:                       0.652
Model:                            OLS   Adj. R-squared:                  0.565
Method:                 Least Squares   F-statistic:                     7.500
Date:                Fri, 06 Jan 2017   Prob (F-statistic):             0.0520
Time:                        10:48:17   Log-Likelihood:                -9.8391
No. Observations:                   6   AIC:                             23.68
Df Residuals:                       4   BIC:                             23.26
Df Model:                           1                                         
Covariance Type:            nonrobust                                         
==============================================================================
                 coef    std err          t      P>|t|      [95.0% Conf. Int.]
------------------------------------------------------------------------------
Intercept     14.3333      1.106     12.965      0.000        11.264    17.403
date_delta     1.0000      0.365      2.739      0.052        -0.014     2.014
==============================================================================
Omnibus:                          nan   Durbin-Watson:                   1.393
Prob(Omnibus):                    nan   Jarque-Bera (JB):                0.461
Skew:                          -0.649   Prob(JB):                        0.794
Kurtosis:                       2.602   Cond. No.                         5.78
==============================================================================

Warnings:
[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.
                            OLS Regression Results                            
==============================================================================
Dep. Variable:                  value   R-squared:                       0.750
Model:                            OLS   Adj. R-squared:                  0.500
Method:                 Least Squares   F-statistic:                     3.000
Date:                Fri, 06 Jan 2017   Prob (F-statistic):              0.333
Time:                        10:48:17   Log-Likelihood:                -3.2171
No. Observations:                   3   AIC:                             10.43
Df Residuals:                       1   BIC:                             8.631
Df Model:                           1                                         
Covariance Type:            nonrobust                                         
==============================================================================
                 coef    std err          t      P>|t|      [95.0% Conf. Int.]
------------------------------------------------------------------------------
Intercept     15.5000      1.118     13.864      0.046         1.294    29.706
date_delta    -1.5000      0.866     -1.732      0.333       -12.504     9.504
==============================================================================
Omnibus:                          nan   Durbin-Watson:                   3.000
Prob(Omnibus):                    nan   Jarque-Bera (JB):                0.531
Skew:                          -0.707   Prob(JB):                        0.767
Kurtosis:                       1.500   Cond. No.                         2.92
==============================================================================

Warnings:
[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.
                            OLS Regression Results                            
==============================================================================
Dep. Variable:                  value   R-squared:                        -inf
Model:                            OLS   Adj. R-squared:                   -inf
Method:                 Least Squares   F-statistic:                    -0.000
Date:                Fri, 06 Jan 2017   Prob (F-statistic):                nan
Time:                        10:48:17   Log-Likelihood:                 63.481
No. Observations:                   2   AIC:                            -123.0
Df Residuals:                       0   BIC:                            -125.6
Df Model:                           1                                         
Covariance Type:            nonrobust                                         
==============================================================================
                 coef    std err          t      P>|t|      [95.0% Conf. Int.]
------------------------------------------------------------------------------
Intercept     16.0000        inf          0        nan           nan       nan
date_delta -3.553e-15        inf         -0        nan           nan       nan
==============================================================================
Omnibus:                          nan   Durbin-Watson:                   0.400
Prob(Omnibus):                    nan   Jarque-Bera (JB):                0.333
Skew:                           0.000   Prob(JB):                        0.846
Kurtosis:                       1.000   Cond. No.                         2.62
==============================================================================

Neocolonialism

thank you @piRSquared; is there a way to do it with linear regression? I am trying to create a dataframe with the predicted values by group for a date in the future. with the OLS summary method, I would have to go in manually find the formula for each group and calculate it for 01-10-2016 – Turnip

thank you very much; I have one more question, so I fully understand how you resolved this issue, if my date would be in the format "2016-01-10" in the data set; how would it change the code? – Turnip

I get this error TypeError: ufunc subtract cannot use operands with types dtype('<M8[ns]') and dtype('O') – Turnip

@Turnip I've updated post to ensure df.date is in fact datetime just in case it isn't passed that way. Also, look at the documentation for pd.to_datetime you may want to use thedayfirst parameter: pd.to_datetime(dayfirst=True) – Neocolonialism

thank you @Neocolonialism - unfortunately I am getting the same error, when I run group_predictions(df, '01-10-2016') ; it points towards this line df['date_delta'] = df.date.sub(mn).div(day) – Turnip

Answer 2

This might be a late response but I post the answer anyway should someone encounters the same problem. Actually, everything that was shown was correct except for the regression block. Here are the two problems with the implementation:

• Please note that the model.fit(X, y) gets an input X{array-like, sparse matrix} of shape (n_samples, n_features) for X. So both inputs for model.fit(X, y) should be 2D. You can easily convert the 1D series to 2D by the reshape(-1, 1) command.

• The second problem is the regression fitting process itself: y and X are not the input of model = LinearRegression(y, X) but rather the input of `model.fit(X, y)'.

Here is the modification to the regression block:

for group in df_group.groups.keys():
      df= df_group.get_group(group)
      X = np.array(df[['date_delta']]).reshape(-1, 1) # note that series does not have reshape function, thus you need to convert to array
      y = np.array(df.value).reshape(-1, 1) 
      model = LinearRegression()  # <--- this does not accept (X, y)
      results = model.fit(X, y)
      print results.summary()

Jemison

Answer 3

As a newbie I cannot comment so I will write it as a new answer. To solve an error:

Runtime Error: ValueError : Expected 2D array, got scalar array instead

you need to reshape delta value in line:

return np.squeeze(LinearRegression().fit(X, y).predict(np.array(delta).reshape(1, -1)))

Credit stays for you piRSquared

Intestine