Simple linear regression using pandas frame

Question

I want to check trends for multiple objects (SysNr)

I have data covering 3 years (2014,2015,2016)

I consider a large number of variables, but limit this question to one ('res_f_r')

My DataFrame looks something like this.

d = [
    {'RegnskabsAar': 2014, 'SysNr': 1, 'res_f_r': 350000},
    {'RegnskabsAar': 2015, 'SysNr': 1, 'res_f_r': 400000},
    {'RegnskabsAar': 2016, 'SysNr': 1, 'res_f_r': 450000},
    {'RegnskabsAar': 2014, 'SysNr': 2, 'res_f_r': 350000},
    {'RegnskabsAar': 2015, 'SysNr': 2, 'res_f_r': 300000},
    {'RegnskabsAar': 2016, 'SysNr': 2, 'res_f_r': 250000},
]

df = pd.DataFrame(d)



   RegnskabsAar  SysNr  res_f_r
0          2014      1   350000
1          2015      1   400000
2          2016      1   450000
3          2014      2   350000
4          2015      2   300000
5          2016      2   250000

My desire is to do a linear regression for each object (SysNr) and get a return slope and interception

My desired result for the above

   SysNr  intercept  slope
0      1     300000  50000
1      2     400000 -50000

Any ideas?

Answer 1

, (, , , ), np.polyfit (scikit-learn, scipy.stats.linregress,...) groupby :

In [25]: df.groupby("SysNr").apply(lambda g: np.polyfit(g.RegnskabsAar, g.res_f_r, 1))
Out[25]:
SysNr
1    [49999.99999999048, -100349999.99998075]
2    [-49999.99999999045, 101049999.99998072]
dtype: object

:

In [43]: df.groupby("SysNr").apply(
    ...:     lambda g: np.polyfit(g.RegnskabsAar, g.res_f_r, 1)).apply(
    ...:     pd.Series).rename(columns={0:'slope', 1:'intercept'}).reset_index()
Out[43]:
   SysNr    slope     intercept
0      1  50000.0 -1.003500e+08
1      2 -50000.0  1.010500e+08

:

, SysNr: NaNs . , , , , .

, . ( , , , , ) - . , , (, , sysNr 3 -150000.0).

Answer 2

linregress scipy.stats groupby pandas:

from scipy.stats import linregress

# groupby column
grouped = df.groupby('SysNr')

# https://stackoverflow.com/a/14775604/5916727
# apply linear regression to each group
result_df = pd.DataFrame(grouped.apply(lambda x: linregress(x['RegnskabsAar'], x['res_f_r']))).reset_index()

# https://stackoverflow.com/a/29550458/5916727
# expand result to each column
result_df[['slope', 'intercept', 'r_value', 'p_value', 'std_err']] = result_df[0].apply(pd.Series)

# drop initial column with all in one
del result_df[0]

result_df

:

   SysNr    slope    intercept  r_value       p_value  std_err
0      1  50000.0 -100350000.0      1.0  9.003163e-11      0.0
1      2 -50000.0  101050000.0     -1.0  9.003163e-11      0.0