# DataFrames: conditional probabilities

**URL:** <https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [April 10, 2024, 8:54pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805 "2024-04-10T20:54:18Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![hendri54](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/hendri54/32/9621_2.png) [@hendri54](https://discourse.julialang.org/u/hendri54)\
**Post date:** [April 10, 2024, 8:54pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/1 "2024-04-10T20:54:18Z")

</div>

I have two DataFrames. One gives Prob(Z | Y), the other Prob(Y | X).  
I would like to compute Prob(Z | X). How?

M(non)WE:

```julia
using DataFrames, Random

dfZY = DataFrame(
    z = repeat([1,2]; outer = 3),
    y = repeat([1,2,3]; inner = 2),
    probZY = [0.2, 0.8, 0.3, 0.7, 0.6, 0.4]
    )

dfYX = DataFrame(
    y = repeat([1,2,3]; outer = 2),
    x = repeat([1,2]; inner = 3),
    probYX = [0.2, 0.3, 0.5, 0.5, 0.3, 0.2]
    )

# dfZX should be Prob(Z|X) = sum_Y Prob(Z|Y) Prob(Y|X)

```

Thank you for suggestions.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [April 10, 2024, 9:16pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/3 "2024-04-10T21:16:11Z")

</div>

Here’s a DataFramesMeta.jl solution which I think is what you want

```julia
julia> @chain dfZY begin
           leftjoin(dfYX, on = :y)
           @by [:x, :z] :probZX = sum(:probZY .* :probYX)
       end
4×3 DataFrame
 Row │ x z probZX  
     │ Int64? Int64 Float64 
─────┼────────────────────────
   1 │ 1 1 0.43
   2 │ 1 2 0.57
   3 │ 2 1 0.31
   4 │ 2 2 0.69

```

---

<div class="post-metadata">

**Author:** ![hendri54](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/hendri54/32/9621_2.png) [@hendri54](https://discourse.julialang.org/u/hendri54)\
**Post date:** [April 10, 2024, 9:21pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/4 "2024-04-10T21:21:24Z")

</div>

Thank you, but the result should be “2 x 2” with entries such as Prob(z = 2 | x = 1) and Prob(z = 1 | x = 2)

---

<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:** [April 10, 2024, 9:23pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/5 "2024-04-10T21:23:32Z")

</div>

This seems better:

```julia
julia> @chain dfYX begin
           innerjoin(dfZY; on=:y)
           groupby([:z,:x])
           combine([:probYX, :probZY] => ((yx,zy)->sum(yx.*zy)) => :probZX)
       end
4×3 DataFrame
 Row │ z x probZX  
     │ Int64 Int64 Float64 
─────┼───────────────────────
   1 │ 1 1 0.43
   2 │ 1 2 0.31
   3 │ 2 1 0.57
   4 │ 2 2 0.69

```

---

<div class="post-metadata">

**Author:** ![jar1](https://avatars.discourse-cdn.com/v4/letter/j/c0e974/32.png) [@jar1](https://discourse.julialang.org/u/jar1)\
**Post date:** [April 10, 2024, 9:25pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/6 "2024-04-10T21:25:21Z")

</div>

```julia
julia> @chain dfYX begin
           innerjoin(dfZY; on=:y)
           groupby([:x, :z])
           combine([:probYX, :probZY] => sum∘(.*) => :probZX)
       end
4×3 DataFrame
 Row │ x z probZX  
     │ Int64 Int64 Float64 
─────┼───────────────────────
   1 │ 1 1 0.43
   2 │ 1 2 0.57
   3 │ 2 1 0.31
   4 │ 2 2 0.69

```

---

<div class="post-metadata">

**Author:** ![hendri54](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/hendri54/32/9621_2.png) [@hendri54](https://discourse.julialang.org/u/hendri54)\
**Post date:** [April 10, 2024, 9:28pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/7 "2024-04-10T21:28:14Z")

</div>

That looks right (though it will take me a bit of time to figure it out).  
Thanks!

---

<div class="post-metadata">

**Author:** ![bertschi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bertschi/32/33462_2.png) [@bertschi](https://discourse.julialang.org/u/bertschi)\
**Post date:** [April 10, 2024, 9:34pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/8 "2024-04-10T21:34:00Z")

</div>

Yes, this seems correct.  
Basically, you want to multiply the transition matrices p(Z|Y) and p(Y|X). Viewing the data frame as a matrix in coordinate format, one can quickly check:

```julia-repl
julia> using SparseArrays

julia> pZX = sparse(dfZY.z, dfZY.y, dfZY.probZY) * sparse(dfYX.y, dfYX.x, dfYX.probYX)
2×2 SparseMatrixCSC{Float64, Int64} with 4 stored entries:
 0.43 0.31
 0.57 0.69

julia> DataFrame([:z, :x, :probZX] .=> findnz(pZX))
4×3 DataFrame
 Row │ z x probZX  
     │ Int64 Int64 Float64 
─────┼───────────────────────
   1 │ 1 1 0.43
   2 │ 2 1 0.57
   3 │ 1 2 0.31
   4 │ 2 2 0.69

```

---

<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:** [April 10, 2024, 9:39pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/9 "2024-04-10T21:39:35Z")

</div>

Yep, matrix multiplication is nice for this question. Initially I tried to get to the matrices without the `sparse` trick which depends a bit on the values of X,Y,Z. It went something like:

```julia
julia> using NamedArrays

julia> uYX = unstack(dfYX, :x, :y, :probYX);

julia> naYX = NamedArray(Matrix(uYX[:,2:end]),(uYX.x, names(uYX)[2:end]),(:X,:Y));

julia> uZY = unstack(dfZY, :y, :z, :probZY);

julia> naZY = NamedArray(Matrix(uZY[:,2:end]),(uZY.y, names(uZY)[2:end]),(:Y,:Z));

julia> naYX*naZY
2×2 Named Matrix{Union{Missing, Float64}}
X ╲ Z │ 1 2
──────┼───────────
1 │ 0.43 0.57
2 │ 0.31 0.69

```

It’s always useful to remember `unstack` and friends.

---

<div class="post-metadata">

**Author:** ![bertschi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bertschi/32/33462_2.png) [@bertschi](https://discourse.julialang.org/u/bertschi)\
**Post date:** [April 10, 2024, 9:53pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/10 "2024-04-10T21:53:49Z")

</div>

Had seen this trick somewhere on the [J](https://www.jsoftware.com/#/) page where it had been used to implement stack. Working with indices can be quite cool and there are some neat identities, e.g., the rank of each element in a vector can be obtained as `sortperm ∘ sortperm`.  
Ideally, I would like to have a data notation that is somewhat independent on how its stored, i.e., long or wide. E.g., in [TensorCast](https://github.com/mcabbott/TensorCast.jl) the matrix multiplication would be expressed as

```julia
@reduce pZX[z,x] := sum(y) pZY[z,y] * pYX[y,x]

```

just imagine that something similar would work on data frames

```julia
@combine dfZX[:probZX | :z, :x] := sum(:y) dfZY[:probZY | :y, :z] * dfYX[:probYX | :x, :y]

```

and compile into `join`, `groupby`, `combine` etc.

---

<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:** [April 10, 2024, 10:30pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/11 "2024-04-10T22:30:11Z")

</div>

Seems to me `unstack` is a bit stuck in the matrix/pivot-table world and hasn’t advanced to the tensor world. More precisely,  
`unstack` takes one `colkey` variable which turns into an additional dimension, when it should be able to accept several `colkey`s and make an `N+1` dimensional Array like type. Perhaps even directly into a NamedArray.  
Perhaps the syntax should follow `read` which takes a “sink” type.

(This message can be moved to a different thread)

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [April 10, 2024, 10:47pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/12 "2024-04-10T22:47:13Z")

</div>

Sorry! I’ve edited my post with the correct outcome

---

<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:** [April 11, 2024, 7:19pm UTC](https://discourse.julialang.org/t/dataframes-conditional-probabilities/112805/13 "2024-04-11T19:19:30Z")

</div>

> [@Dan](#):
>
> the `sparse` trick which depends a bit on the values of X,Y,Z.

you can use a function like this

`gg(df,col)=groupindices(groupby(df,col))`

to get the “indexes” to use in the transition matrices construction

```julia

gg(df,col)=groupindices(groupby(df,col))

function trnsmatr(df)
    df[:,1]=gg(df,1)
    df[:,2]=gg(df,2)
    sdf=sort(df,[2,1])
    n=nrow(unique(df,1))
    reshape(sdf[:,3],n,:)
end

trnsmatr(dfZY) * trnsmatr(dfYX)

```