# Efficient way to add a value of column to all rows conditioned on another column by group
**URL:** https://discourse.julialang.org/t/efficient-way-to-add-a-value-of-column-to-all-rows-conditioned-on-another-column-by-group/71784
**Category:** Data
**Tags:** dataframes, dataframesmeta
**Created:** [November 19, 2021, 3:00pm UTC](https://discourse.julialang.org/t/efficient-way-to-add-a-value-of-column-to-all-rows-conditioned-on-another-column-by-group/71784 "2021-11-19T15:00:35Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Rahul](https://discourse.julialang.org/u/Rahul)
#### Post date: [November 19, 2021, 3:00pm UTC](https://discourse.julialang.org/t/efficient-way-to-add-a-value-of-column-to-all-rows-conditioned-on-another-column-by-group/71784/1 "2021-11-19T15:00:35Z")
My data has 3 columns (id, time, conc). I am looking for a concise way to add the column (using Chain.jl and DataFramesMeta.jl) as described in the title. Example code is below:
```julia
df = DataFrame(id = sort(repeat([1, 2, 3], 10)),
time = repeat(0:10:90, 3),
conc = rand(30))
@chain df begin
@aside concs_20 = @chain _ begin
@rsubset :time == 20
@select :id :conc_20hr = :conc
end
leftjoin(_, concs_20, on=[:id])
end
```
For example, in R, I can do the task concisely (using the library data.table) as follows:
```julia
df = data.table(id = sort(rep(c(1, 2, 3), 10)),
time = rep(seq(0,90,10), 3),
conc = rnorm(30))
df[, ":="(conc_20hr = conc[time == 20]), by=.(id)]
```
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [November 19, 2021, 3:22pm UTC](https://discourse.julialang.org/t/efficient-way-to-add-a-value-of-column-to-all-rows-conditioned-on-another-column-by-group/71784/2 "2021-11-19T15:22:53Z")
This is an area of active development in DataFramesMeta, and I wish there were a better way of doing it.
The way to do the conditional transformation is
```julia
julia> @chain df begin
@rtransform :conc_20hr = :time == 20 ? :conc : missing
end
```
but it looks like you also want to “spread” the result within the group
```julia
julia> @chain df begin
groupby(:id)
@transform :conc_20hr = first(:conc[:time .== 20])
end
```
---
### Author:  [@bkamins](https://discourse.julialang.org/u/bkamins)
#### Post date: [November 19, 2021, 9:53pm UTC](https://discourse.julialang.org/t/efficient-way-to-add-a-value-of-column-to-all-rows-conditioned-on-another-column-by-group/71784/3 "2021-11-19T21:53:33Z")
The last example matches what OP has written in data.table. I would just make a small substitution of `first` to `only` as it is safer (you are sure there is only one match for `:time .== 20`)
---
### Author:  [@DataFrames](https://discourse.julialang.org/u/DataFrames)
#### Post date: [November 20, 2021, 12:49am UTC](https://discourse.julialang.org/t/efficient-way-to-add-a-value-of-column-to-all-rows-conditioned-on-another-column-by-group/71784/4 "2021-11-20T00:49:31Z")
Actually your `leftjoin` solution might be more efficient (if you don’t (want to) change the `transform` part):
```julia
df = DataFrame(id = sort(repeat(1:300000, 10)),
time = repeat(0:10:90, 300000),
conc = rand(3000000))
@btime transform(groupby(df,1), [:time, :conc]=> (x,y)->first(y[x .== 20]))
75.705 ms (900817 allocations: 302.45 MiB)
@btime leftjoin(df, df[df.time .== 20, [1,3]], on = [:id], makeunique = true)
59.999 ms (345 allocations: 175.26 MiB)
```