# A lagged table-join for finding the last ten rows that meets a criterion, for each row

**URL:** <https://discourse.julialang.org/t/a-lagged-table-join-for-finding-the-last-ten-rows-that-meets-a-criterion-for-each-row/108352>\
**Category:** Data\
**Tags:** dataframes, dataframesmeta\
**Created:** [January 4, 2024, 5:04pm UTC](https://discourse.julialang.org/t/a-lagged-table-join-for-finding-the-last-ten-rows-that-meets-a-criterion-for-each-row/108352 "2024-01-04T17:04:08Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Cong](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/cong/32/202895_2.png) [@Cong](https://discourse.julialang.org/u/Cong)\
**Post date:** [January 4, 2024, 5:04pm UTC](https://discourse.julialang.org/t/a-lagged-table-join-for-finding-the-last-ten-rows-that-meets-a-criterion-for-each-row/108352/1 "2024-01-04T17:04:08Z")

</div>

I have a dataframe like below. I am trying to add 6 columns to it: `Out1Bac1, Out1Bac2, Out1Bac3, Out2Bac1, Out2Bac2, Out2Bac3`.  
For each row, find the last row whose `abschoice` equals the `CP1` of the current row, and get the `unitReward` of that row to fill into the `Out1Bac1` column of the current row; then find the second last row whose `abschoice` equals the `CP1` of the current row, and get the `unitReward` of that row to fill into the `Out1Bac2` column of the current row; then find the third last row whose `abschoice` equals the `CP1` of the current row, and get the `unitReward` of that row to fill into the `Out1Bac3` column of the current row. and the same for `CP2` and `Out2Bac1, Out2Bac2, Out2Bac3`.  
195×4 DataFrame  
Row │ CP1 CP2 abschoice unitReward  
│ String String String? Int16  
─────┼───────────────────────────────────────  
1 │ A D D 4  
2 │ A D D 3  
3 │ A A A 2  
4 │ A D D 0  
5 │ A D D 3  
6 │ A D D 6  
7 │ A B A 3  
8 │ A A A 1  
9 │ A B A 2  
10 │ A B A 2  
Currently I am trying to do this (in a chain, so don’t worry about specifying the df or adding !):  
`@rtransform(:Out2Bac1 = lag(:abschoice, 1) .== :CP2 ? lag(:unitReward, 1) : lag(:abschoice, 2) .== :CP2 ? lag(:unitReward, 1) : missing)`  
but  
first: Julia complains that `no method matching lag(::String, ::Int64)`,  
second (the real problem): this syntax requires me to manually write out how many rows I am willing to search back, one by one, which is pretty ugly if I want to search, say, 30 rows back,  
third (the real tricky problem): this syntax doesn’t allow me to find the second last or the third last row that satisfies that criterion easily. So maybe a better way is to find at once the last 3 rows whose `abschoice` == `CP1` of the current row, then fill the 3 `unitReward` values from that 3 rows, into the 3 columns `Out1Bac1, Out1Bac2, Out1Bac3` for the current row. — I have no clue how to do that – sounds like I need a `join` together with `lag`??

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [January 4, 2024, 6:19pm UTC](https://discourse.julialang.org/t/a-lagged-table-join-for-finding-the-last-ten-rows-that-meets-a-criterion-for-each-row/108352/2 "2024-01-04T18:19:14Z")

</div>

First,

> [@Cong](#):
>
> Julia complains that `no method matching lag(::String, ::Int64)`,

seems to be because of `@rtransform` which transforms row by row. `lag` wants to work on a vector. You can see this in the error message which complains `lag` received a `String` as first argument instead of a `Vector{String}`.

As to the broader question… that’s a little more complicated. It would also be nice if you gave a mini-example with the desired output DataFrame (although it is somewhat comprehensible even in the current post).

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [January 4, 2024, 6:58pm UTC](https://discourse.julialang.org/t/a-lagged-table-join-for-finding-the-last-ten-rows-that-meets-a-criterion-for-each-row/108352/3 "2024-01-04T18:58:56Z")

</div>

Again, IIUC the question, this is one way:

```julia
function populateOutBac!(df)
    d = Dict{eltype(df.abschoice),Vector{eltype(df.unitReward)}}()
    df.Out1Bac1 = allowmissing(similar(df.unitReward))
    df.Out1Bac2 = allowmissing(similar(df.unitReward))
    df.Out1Bac3 = allowmissing(similar(df.unitReward))
    df.Out2Bac1 = allowmissing(similar(df.unitReward))
    df.Out2Bac2 = allowmissing(similar(df.unitReward))
    df.Out2Bac3 = allowmissing(similar(df.unitReward))
    for r in eachrow(df)
        w = get!(()->(eltype(df.unitReward)[]), d, r.CP1)
        z = get!(()->(eltype(df.unitReward)[]), d, r.CP2)
        r.Out1Bac1, r.Out1Bac2, r.Out1Bac3 = get.(Ref(w), 1:3, missing)
        r.Out2Bac1, r.Out2Bac2, r.Out2Bac3 = get.(Ref(z), 1:3, missing)
        v = get!(()->(eltype(df.unitReward)[]), d, r.abschoice)
        if length(v) == 3
            circshift!(v, 1)
            v[1] = r.unitReward
        else
            pushfirst!(v, r.unitReward)
        end
    end
end

```

which gives for the partial DataFrame in the question:

```julia
10×10 DataFrame
 Row │ CP1 CP2 abschoice unitReward Out1Bac1 Out1Bac2 Out1Bac3 Out2Bac1 Out2Bac2 Out2Bac3 
     │ String1 String1 String1 Int64 Int64? Int64? Int64? Int64? Int64? Int64?   
─────┼─────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ A D D 4 missing missing missing missing missing missing 
   2 │ A D D 3 missing missing missing 4 missing missing 
   3 │ A A A 2 missing missing missing missing missing missing 
   4 │ A D D 0 2 missing missing 3 4 missing 
   5 │ A D D 3 2 missing missing 0 3 4
   6 │ A D D 6 2 missing missing 3 0 3
   7 │ A B A 3 2 missing missing missing missing missing 
   8 │ A A A 1 3 2 missing 3 2 missing 
   9 │ A B A 2 1 3 2 missing missing missing 
  10 │ A B A 2 2 1 3 missing missing missing 

```

Any good?

---

<div class="post-metadata">

**Author:** ![Cong](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/cong/32/202895_2.png) [@Cong](https://discourse.julialang.org/u/Cong)\
**Post date:** [January 4, 2024, 7:49pm UTC](https://discourse.julialang.org/t/a-lagged-table-join-for-finding-the-last-ten-rows-that-meets-a-criterion-for-each-row/108352/4 "2024-01-04T19:49:45Z")

</div>

This is the desired output dataframe – which is exactly what you got!:

 ![image](https://global.discourse-cdn.com/julialang/original/3X/e/9/e9082f571f752394c822d5138d9c37f93bf0471c.png)  
and the thing I want to do is: e.g. for the 5th row, CP1 == A, I go back to find the rows that have abschoice == A: row 3. then get the corresponding unitReward from the row: 2. then fill it in the 5th row: fill 2 in `CP1Bac1` ; couldn’t find more rows that have abschoice ==A, so fill missing in `CP1Bac2` , missing in `CP1Bac3`; CP2 == D for the 5th row, so I go back and find row 4,2,1 that have abschoice == D, the unitReward 0,3,4. so fill them in Out2Back1, Out2Back2, Out2Back3 respectively.

---

<div class="post-metadata">

**Author:** ![Cong](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/cong/32/202895_2.png) [@Cong](https://discourse.julialang.org/u/Cong)\
**Post date:** [January 4, 2024, 8:25pm UTC](https://discourse.julialang.org/t/a-lagged-table-join-for-finding-the-last-ten-rows-that-meets-a-criterion-for-each-row/108352/5 "2024-01-04T20:25:12Z")

</div>

Thank you so much!! This is exactly what I wanted. Could you explain your code a bit? d = Dict() creates an empty dictionary of certain data type. but I couldn’t tell where you actually fill the dictionary with the vector of df.abschoice and df.unitReward.

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [January 4, 2024, 8:36pm UTC](https://discourse.julialang.org/t/a-lagged-table-join-for-finding-the-last-ten-rows-that-meets-a-criterion-for-each-row/108352/6 "2024-01-04T20:36:24Z")

</div>

Glad to be of assistance. Some more explanation to the code:

```julia
function populateOutBac!(df)
# Define a dictionary to hold the recent `unitReward` for each 
# choice:
    d = Dict{eltype(df.abschoice),Vector{eltype(df.unitReward)}}()
# Add the missing columns to the DataFrame, with type as 
# reward and allowing `missing` values:
    df.Out1Bac1 = allowmissing(similar(df.unitReward))
    df.Out1Bac2 = allowmissing(similar(df.unitReward))
    df.Out1Bac3 = allowmissing(similar(df.unitReward))
    df.Out2Bac1 = allowmissing(similar(df.unitReward))
    df.Out2Bac2 = allowmissing(similar(df.unitReward))
    df.Out2Bac3 = allowmissing(similar(df.unitReward))
# Go over each row:
    for r in eachrow(df)
# Get the recent rewards for CP1 and CP2 stored in Dict `d` (if 
# there isn't an entry, generate an empty vector for that value:
        w = get!(()->(eltype(df.unitReward)[]), d, r.CP1)
        z = get!(()->(eltype(df.unitReward)[]), d, r.CP2)
# Use broadcasting and default value feature of `get` to get 
# previous value or `missing`:
        r.Out1Bac1, r.Out1Bac2, r.Out1Bac3 = get.(Ref(w), 1:3, missing)
        r.Out2Bac1, r.Out2Bac2, r.Out2Bac3 = get.(Ref(z), 1:3, missing)
# Push the new reward into the Dict `d`, by first getting the right 
# entry:
        v = get!(()->(eltype(df.unitReward)[]), d, r.abschoice)
# If entry already has 3 previous values, cycle and overwrite the 
# oldest (now in the first position):
        if length(v) == 3
            circshift!(v, 1)
            v[1] = r.unitReward
        else
            pushfirst!(v, r.unitReward)
        end
    end
end

```

That’s it.  
There should be prettier ways of doing it, and maybe someone will chime in. But to help this happen, it helps to have cut-and-paste option for testing:

```julia
iob = IOBuffer("""1 │ A D D 4
       2 │ A D D 3
       3 │ A A A 2
       4 │ A D D 0
       5 │ A D D 3
       6 │ A D D 6
       7 │ A B A 3
       8 │ A A A 1
       9 │ A B A 2
       10 │ A B A 2""");
df = CSV.read(iob, DataFrame; 
  header=["rownum", "tmp", "CP1", "CP2", "abschoice", "unitReward"]);
select!(df, Not([:tmp, :rownum]))

```

to create `df` for processing with above `populateOutBac!(df)` or some other way.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [January 4, 2024, 10:54pm UTC](https://discourse.julialang.org/t/a-lagged-table-join-for-finding-the-last-ten-rows-that-meets-a-criterion-for-each-row/108352/7 "2024-01-04T22:54:45Z")

</div>

This isn’t very elegant but, perhaps, it’s easier to follow

```julia
df= CSV.read("tablej.csv",DataFrame)
insertcols!(df,1,:row=>1:nrow(df))

function tabjoin(r,cp1)
    v=findall(==(cp1),df[1:r-1,4])
    coal= length(v)==0 ? [missing,missing,missing] :
    length(v)==1 ? [missing,missing, df[v[1],5]] :
    length(v)==2 ? [missing, df[v[1],5],df[v[2],5]] :
    df[v[end-2:end],5]
    (;zip([:O1B1,:O1B2,:O1B3],reverse(coal))...)
end

df1=transform(df, [1,2]=>ByRow(tabjoin)=>AsTable)

```