Estimate a linear trend in every row across multiple columns in order to project the next value

I have five columns of historic data which I’d like to find a linear trend across the columns in every row to project the next value in year 2021/22. The historic data is stored in a data frame as follows: –

Index	2016/17	2017/18	2018/19	2019/20	2020/21
0	14.53	13.75	13.03	16.05	15.15
1	14.52	13.74	13.03	16.03	15.14
2	11.92	10.53	10.53	void	void
3	10.87	10.06	10.06	10.05	10.07
4	12.59	10.99	10.99	10.99	11.07
5	12.59	10.99	10.99	10.99	11.00
6	24.56	17.90	17.88	18.02	18.14
7	24.56	17.90	17.88	18.02	18.14
8	24.56	17.90	17.88	18.02	18.14
9	18.40	14.94	14.92	15.02	15.10
10	23.57	23.06	23.09	23.08	22.88

The projected output is then saved in the next column of the same dataframe.

Index	2016/17	2017/18	2018/19	2019/20	2020/21	2021/22
0	14.53	13.75	13.03	16.05	15.15	?
1	14.52	13.74	13.03	16.03	15.14	?
2	11.92	10.53	10.53	void	void	?
3	10.87	10.06	10.06	10.05	10.07	?
4	12.59	10.99	10.99	10.99	11.07	?
5	12.59	10.99	10.99	10.99	11.00	?
6	24.56	17.90	17.88	18.02	18.14	?
7	24.56	17.90	17.88	18.02	18.14	?
8	24.56	17.90	17.88	18.02	18.14	?
9	18.40	14.94	14.92	15.02	15.10	?
10	23.57	23.06	23.09	23.08	22.88	?

I have tried to use numpy.polyfit and scipy.linregress but both seems to give me problems due to having multiple columns instead of one columns. Any help will be greatly appreciated.

Thank you

Answer

Here is a very basic example of how to take the five columns and predict the next one (the 6th) for the first row of the data, using scikit-learn. pip install scikit-learn if you have not got it.

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

data = { '2016/17' : [14.53, 14.52],
'2017/18' : [13.75,13.74],
'2018/19' : [13.03,13.03],
'2019/20' : [16.05,16.03],
'2020/21' : [15.15,15.14]}
df = pd.DataFrame(data)

X = np.array([[1],[2],[3],[4],[5]]) # dummy x values for each of the columns you are going to use as input data
Y = df.iloc[0,:].values.reshape(-1, 1) # work on row 0
 
linear_regressor = LinearRegression()  # create object for the class
linear_regressor.fit(X, Y)  # perform linear regression

prediction = linear_regressor.predict(np.array([[6]]))

Now you will have to take this and get it to work row wise and put the answer into the column you want but the basic machinery is all there.

You will also have to deal with the interesting problems of void. imputation is what you need but it will be problematic if too much data is missing.