Overview
Given two datasets D1 and D2, I would like to replace rows in D1 satisfying condition C with the dataset D2. We can assume that the dimensions match up.Something like Select[D1,#<Condition C>&]=D2 As we’ll see in the example below, that approach doesn’t work.
Example datasets
In[]:=
dfLeft=
;dfRight=
;
What I would like to do
Replace Column1 and Column2 values in dfLeft with the corresponding values in dfRight, where Key is equal to A or B.
In[]:=
Select[dfLeft,MemberQ[{"A","B"},#Key]&]=Select[dfRight,MemberQ[{"A","B"},#Key]&]
Out[]=
What I’ve tried
In this simple example, a left-join would work.
In[]:=
dfJoined=JoinAcross[dfLeft,dfRight,"Key"];dfJoined=dfJoined[All,Prepend[#,{"Column1"->If[MemberQ[{"A","B"},#Key],#Column1Right,#Column1],"Column2"->If[MemberQ[{"A","B"},#Key],#Column1Right,#Column2]}]&];dfJoined=dfJoined[All,{"Key","Column1","Column2"}]
Out[]=
In my actual setting, there are over 100 pairs of columns to replace. So I am looking for a more concise solution than the above column-by-column assignment.
In Python
In Python, it is straightforward to assign a dataset to a subset of another dataframe. I am hoping for similar Dataset functionality in Mathematica.
In[]:=
import pandas as pdheaders = ["Key","Column1","Column2"];dfLeft=pd.DataFrame([ ["A",1,600], ["B",2,500], ["C",3,700], ["D",4,650]],columns=headers)dfRight=pd.DataFrame([ ["A","ReplacedNumberA1","ReplacedNumberA2"], ["B","ReplacedNumberB1","ReplacedNumberB2"], ["C",5,10], ["D",20,30]],columns=headers)dfLeft.loc[dfLeft["Key"].isin(["A","B"])] = dfRight.loc[dfRight["Key"].isin(["A","B"])]dfLeft
Out[]=
