[FIXED] Get the sum of each column, with recursive values in each cell

Issue

Given a parameter p, be any float or integer.

For example, let p=4

time	1	2	3	4	5
Numbers	a1	a1*(0.5)^(1/p)^(2-1)	a1*(0.5)^(1/p)^(2-1)	a1*(0.5)^(1/p)^(3-1)	a1*(0.5)^(1/p)^(4-1)
Numbers	nan	a2	a2*(0.5)^(1/p)^(3-2)	a2*(0.5)^(1/p)^(4-2)	a2*(0.5)^(1/p)^(5-2)
Numbers	nan	nan	a3	a3*(0.5)^(1/p)^(4-3)	a3*(0.5)^(1/p)^(5-3)
Numbers	nan	nan	nan	a4	a4*(0.5)^(1/p)^(5-4)
Number	nan	nan	nan	nan	a5
Final Results	a1	sum of column 2	sum of column 3	sum of column 4	sum of column 5

Numbers like a1,a2,a3,a4,a5,...,at is given, our goal is to find the Final Results. Combining the answer provided by mozway, I wrote the following function which works well. It is a matrix way to solve the problem.

def hl(p,column):
    a = np.arange(len(copy_raw))
    factors = (a[:,None]-a)
    factors = np.where(factors<0, np.nan, factors)
    inter = ((1/2)**(1/p))**factors
    copy_raw[column] = np.nansum(copy_raw[column].to_numpy()*inter, axis=1) 

However, I don't think this method will work well if we are dealing with large dataframe. Are there any better way to fix the problem?

Solution

Assuming your number of rows is not too large, you can achieve this with numpy broadcasting:

First create a 2D array of factors:

a = np.arange(len(df))
factors = (a[:,None]-a)
factors = np.where(factors<0, np.nan, factors)
# array([[ 0., nan, nan, nan, nan],
#        [ 1.,  0., nan, nan, nan],
#        [ 2.,  1.,  0., nan, nan],
#        [ 3.,  2.,  1.,  0., nan],
#        [ 4.,  3.,  2.,  1.,  0.]])

Then map to your data and sum:

df['number2'] = np.nansum(df['number'].to_numpy()*(1/2)**factors, axis=1) 

example output:

   Index       Time  number  number2
0      0  1997-WK01       1   1.0000
1      1  1997-WK02       2   2.5000
2      2  1997-WK03       3   4.2500
3      3  1997-WK04       2   4.1250
4      4  1997-WK05       4   6.0625

intermediate:

df['number'].to_numpy()*(1/2)**factors

# array([[1.    ,    nan,    nan,    nan,    nan],
#        [0.5   , 2.    ,    nan,    nan,    nan],
#        [0.25  , 1.    , 3.    ,    nan,    nan],
#        [0.125 , 0.5   , 1.5   , 2.    ,    nan],
#        [0.0625, 0.25  , 0.75  , 1.    , 4.    ]])

Answered By - mozway