Referring to Previous Row in Calculation

Referring to previous row in calculation

1) for loop Normally one would just use a simple loop for this:

MyData <- data.frame(A = c(5, 10, 15, 20))

MyData$b <- 0
n <- nrow(MyData)
if (n > 1) for(i in 2:n) MyData$b[i] <- ( MyData$A[i] + 13 * MyData$b[i-1] )/ 14
MyData$b[1] <- NA

giving:

> MyData
   A         b
1  5        NA
2 10 0.7142857
3 15 1.7346939
4 20 3.0393586

2) Reduce It would also be possible to use Reduce. One first defines a function f that carries out the body of the loop and then we have Reduce invoke it repeatedly like this:

f <- function(b, A) (A + 13 * b) / 14
MyData$b <- Reduce(f, MyData$A[-1], 0, acc = TRUE)
MyData$b[1] <- NA

giving the same result.

This gives the appearance of being vectorized but in fact if you look at the source of Reduce it does a for loop itself.

3) filter Noting that the form of the problem is a recursive filter with coefficient 13/14 operating on A/14 (but with A[1] replaced with 0) we can write the following. Since filter returns a time series we use c(...) to convert it back to an ordinary vector. This approach actually is vectorized as the filter operation is performed in C.

MyData$b <- c(filter(replace(MyData$A, 1, 0)/14, 13/14, method = "recursive"))
MyData$b[1] <- NA

again giving the same result.

Note: All solutions assume that MyData has at least 1 row.

Reference value of previous row in same column

As per my comment, given your data:

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Col1] = "%T" then [Col2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {
        {"all", each _, type table [Col1=nullable text, Col2=nullable text, Custom=text]}
        })
in
    #"Grouped Rows"

Will => the two grouped tables

If you don't want that first row (the one with the table name) to be in the subgroup table, merely change the aggregation to remove it:

{"all", each Table.RemoveFirstN(_,1), type table [Col1=nullable text, Col2=nullable text, Custom=text]}

Is there a way in Pandas to use previous row value in dataframe.apply when previous value is also calculated in the apply?

First, create the derived value:

df.loc[0, 'C'] = df.loc[0, 'D']

Then iterate through the remaining rows and fill the calculated values:

for i in range(1, len(df)):
    df.loc[i, 'C'] = df.loc[i-1, 'C'] * df.loc[i, 'A'] + df.loc[i, 'B']

  Index_Date   A   B    C    D
0 2015-01-31  10  10   10   10
1 2015-02-01   2   3   23   22
2 2015-02-02  10  60  290  280

SQL calculation with previous row + current row

You seem to want cumulative sums:

select t.*,
       (sum(reconciliation + aves - microa) over (order by date) -
        first_value(aves - microa) over (order by date)
       ) as calculation
from CalcTable t;

Here is a SQL Fiddle.

EDIT:

Based on your comment, you just need to define a group:

select t.*,
       (sum(reconciliation + aves - microa) over (partition by grp order by date) -
        first_value(aves - microa) over (partition by grp order by date)
       ) as calculation
from (select t.*,
             count(nullif(reconciliation, 0)) over (order by date) as grp
      from CalcTable t
     ) t
order by date;

Python - Referring previous row in the calculation of a column in pandas

Code:

import  pandas as pd
df = pd.DataFrame([(94000, 9.9702279, -21.0627081029071, 0, 0, 0),
                (0, 10.1213880586432, -20.7481423282323, 0, 0, 0),
                   (0, 9.6525370469631300, -21.7559382552248, 0, 0, 0)])

df.loc[0, 'd'] = df.loc[0, 5]
for i in range(0, len(df)):
    if i ==0:
        df.loc[i, 'd'] = (df.loc[i, 0] / df.loc[i, 1])

    else:
        df.loc[i, 'd'] =  (df.loc[i - 1, 'd']) + df.loc[i - 1, 2]

print(df)

Output:

       0          1          2  3  4  5            d
0  94000   9.970228 -21.062708  0  0  0  9428.069342
1      0  10.121388 -20.748142  0  0  0  9407.006634
2      0   9.652537 -21.755938  0  0  0  9386.258492

Is there a way to access the previous row value in a SELECT statement?

SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

If you know how to order things but not how to get the previous value given the current one (EG, you want to order alphabetically) then I don't know of a way to do that in standard SQL, but most SQL implementations will have extensions to do it.

Here is a way for SQL server that works if you can order rows such that each one is distinct:

select  rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2 
where t1.Rank = t2.Rank - 1

drop table temp1

If you need to break ties, you can add as many columns as necessary to the ORDER BY.

How to reference to a previous row within a column with Arrayformula in Google Sheets

try:

=INDEX(IF(IFERROR(REGEXMATCH(C3:C; "(?i)DAY")); E3:E; SCAN(E3; H3:H; LAMBDA(x; y; x+y))))

---

Related Topics