# Use the non-missing elements of DataFrame A to update DataFrame B

**URL:** <https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658>\
**Category:** Data\
**Tags:** query, dataframes\
**Created:** [July 14, 2021, 8:19pm UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658 "2021-07-14T20:19:13Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![fipelle](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fipelle/32/4772_2.png) [@fipelle](https://discourse.julialang.org/u/fipelle)\
**Post date:** [July 14, 2021, 8:19pm UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/1 "2021-07-14T20:19:13Z")

</div>

Hi, I am trying to find an elegant solution to update a DataFrame (`B`), using the non-missing elements in another DataFrame (`A`) with the same columns.

For instance, with

```julia
using Dates, DataFrames;

# Dates
date_1 = Date("1990-01-31", "yyyy-mm-dd");
date_2 = Date("1990-12-31", "yyyy-mm-dd");

# DataFrames
A = DataFrame(:date=>date_2, :x=>missing, :y=>100);
B = DataFrame(:date=>collect(date_1:Dates.Month(1):date_2), :x=>rand(12), :y=>rand(12));

```

I would like to replace the last element in `B` referring to `y` with 100, while leaving `x` as it is in `B`.

In my actual scenario, which might be too complicated to post here, `A` can have:

1. multiple rows
2. fully observed `x` and `y`
3. random patterns of missing observations in `x` and `y`

and always have dates included in `B`.

---

<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:** [July 14, 2021, 8:45pm UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/2 "2021-07-14T20:45:24Z")

</div>

Why not a `join`?

---

<div class="post-metadata">

**Author:** ![fipelle](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fipelle/32/4772_2.png) [@fipelle](https://discourse.julialang.org/u/fipelle)\
**Post date:** [July 14, 2021, 10:49pm UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/3 "2021-07-14T22:49:25Z")

</div>

That’s what I’d like to do, but I am not sure how to handle the missings with Julia in this case. Can you please elaborate on that?

---

<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:** [July 14, 2021, 10:55pm UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/4 "2021-07-14T22:55:41Z")

</div>

Are you coming from Stata, by chance? This is common in Stata.

I think the best option is to do

```julia
julia> leftjoin(B, A; on = :date, makeunique=true)
12×5 DataFrame
 Row │ date x y x_1 y_1
     │ Date Float64 Float64 Missing Int64?
─────┼───────────────────────────────────────────────────
   1 │ 1990-12-31 0.697569 0.0141314 missing 100
   2 │ 1990-01-31 0.890485 0.735816 missing missing
   3 │ 1990-02-28 0.563947 0.26717 missing missing
   4 │ 1990-03-31 0.692067 0.193784 missing missing
   5 │ 1990-04-30 0.494578 0.904987 missing missing
   6 │ 1990-05-31 0.709388 0.59444 missing missing
   7 │ 1990-06-30 0.91898 0.26661 missing missing
   8 │ 1990-07-31 0.8294 0.638376 missing missing
   9 │ 1990-08-31 0.939963 0.48406 missing missing
  10 │ 1990-09-30 0.116675 0.498974 missing missing
  11 │ 1990-10-31 0.736912 0.5008 missing missing
  12 │ 1990-11-30 0.373643 0.549012 missing missing

```

and then handle the updating after. via `ifelse` or similar.

I brought up an `update` keyword argument on github a while ago, but can’t find the post. It’s something I’d like to see eventually for sure.

---

<div class="post-metadata">

**Author:** ![fipelle](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fipelle/32/4772_2.png) [@fipelle](https://discourse.julialang.org/u/fipelle)\
**Post date:** [July 14, 2021, 11:26pm UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/5 "2021-07-14T23:26:33Z")

</div>

No, I am not coming from Stata. I can do that, but it looks a little more computationally expensive than looping over the rows of `A` and updating `B` inplace - while much easier to implement. It would be very handy to have some built-in function to do that.

Is this `update` keyword argument in production now?

---

<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:** [July 15, 2021, 3:25am UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/6 "2021-07-15T03:25:27Z")

</div>

Yeah I guess it’s worth benchmarking.

No, `update` isn’t being worked on right now. You could bring it up in an issue and see if it can be added.

---

<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:** [July 18, 2021, 8:04am UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/7 "2021-07-18T08:04:56Z")

</div>

in this case you can use this

```julia
joined.y=coalesce.(joined.y_1,joined.y)

```

---

<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:** [July 18, 2021, 8:07am UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/8 "2021-07-18T08:07:28Z")

</div>

other ways to lookup the desidered values:

```julia
transform(B,[:date,:y]=>ByRow((x,y)-> findfirst(==(x),A.date)===nothing ? y : A.y[findfirst(==(x),A.date)])=>:y)

transform(B,[:date,:y]=>ByRow((x,y)-> try A.y[findfirst(==(x),A.date)] catch _ y end)=>:y)

```

but I don’t think they are more efficient than the join function

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [July 18, 2021, 8:20am UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/9 "2021-07-18T08:20:09Z")

</div>

> [@pdeffebach](#):
>
> I brought up an `update` keyword argument on github a while ago, but can’t find the post. It’s something I’d like to see eventually for sure.

Can you just open a new issue then please? I do issue review from time to time and deduplicate such cases, but it is better to open it. As usual - I would appreciate a precise specification of what exactly is requested as I was not sure. In particular the `findfirst` approach above seems not fully correct to me as there might be duplicate entries with the same value in `A.date`. (and I would not concentrate on performance now - rather the API design; later performant implementation would be proposed; as for this case join is O(n+m) and `findfirst` is O(nm) so for large tables join will be much faster even now)

---

<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:** [July 18, 2021, 8:53am UTC](https://discourse.julialang.org/t/use-the-non-missing-elements-of-dataframe-a-to-update-dataframe-b/64658/10 "2021-07-18T08:53:57Z")

</div>

this might handle repeated dates occurrence, although not the performance aspects

`flatten(transform(B,[:date,:y]=>ByRow((x,y)-> findall(==(x),A.date)==[] ? y : A.y[findall(==(x),A.date)])=>:y),:y) `