Copying set of specific columns between Pandas dfs where specific values match
<p>I am sure this is going to be a 'doh' moment, but I am having a difficult time trying to copy a set of columns between dataframes where the value of a specific column in df1 is also found in df2.</p>

<p>A simplified version of df1 looks like this:
<a href="http://i.stack.imgur.com/oVzMm.png" rel="nofollow"><img src="http://i.stack.imgur.com/oVzMm.png" alt="df1"></a></p>

<p>A simplified version of df2 looks like this:
<a href="http://i.stack.imgur.com/mSycT.png" rel="nofollow"><img src="http://i.stack.imgur.com/mSycT.png" alt="df2"></a></p>

<p>From here I'm building a list of columns (cols) that does not include the 'p_people_id' field from df1 and creating those fields in df2 and assigning a nan value.
cols=<a href="http://i.stack.imgur.com/zVeq9.png" rel="nofollow"><img src="http://i.stack.imgur.com/zVeq9.png" alt="columns from df1 != p_people_id"></a>
After which df2 looks like this:
<a href="http://i.stack.imgur.com/PrtY7.png" rel="nofollow"><img src="http://i.stack.imgur.com/PrtY7.png" alt="df2 w appended cols"></a>
Working with these dfs I'm trying to look find all instances where df2.a_people_id == df1.p_people_id and assign the values of df1[cols] to the df2 instance.</p>

<p>Finding the instance using .loc is simple enough.  I've managed to be able to select the cols I want to target using .loc as well  </p>

<pre><code>df2.loc[df2['a_people_id']==df1['p_people_id'][0],np.array(cols)]  
</code></pre>

<p>This works fine and returns: <a href="http://i.stack.imgur.com/dBis5.png" rel="nofollow"><img src="http://i.stack.imgur.com/dBis5.png" alt="enter image description here"></a>  </p>

<p>But, if I try something like this to set/assign those specific columns where the id field in df1 matches the id field in df2:   </p>

<pre><code>df2.loc[df2['a_people_id']==df1['p_people_id'][0],np.array(cols)]=df1.loc[df1['p_people_id']==df1['p_people_id'][0],np.array(cols)]
</code></pre>

<p>Nothing happens and I'm not sure why. <a href="http://i.stack.imgur.com/NYVTM.png" rel="nofollow"><img src="http://i.stack.imgur.com/NYVTM.png" alt="df2 assignment results"></a></p>

<p>I've attempted to utilize .ix, .loc, .iloc, .where, .select, .set in various ways, but this has to be one of those areas where I'm "just not doin it right."  I can post other examples where I've managed to get the syntax right to find [cols] where the specific id matches and no error or 'view vs copy' warning is printed, but no assignment happens either.  Where am I going wrong here?</p>

<p>If I'm not mistaken, I think you're looking for a join operation. </p>

<p>In particular, this statement in your description: </p>

<pre><code>df2.loc[df2['a_people_id']==df1['p_people_id'][0],np.array(cols)] 
</code></pre>

<p>Means "look in <code>df2</code> for all rows where <code>p_people_id</code> matches the first <code>p_people_id</code> and for those rows, select the <code>cols</code> columns. </p>

<p>This produces a set of rows and a columns (6 in your example, all containing NA's) and, if I get things correctly, you then want to put those 6 lines together with the corresponding line in df1, plus do that for all <code>p_people_id</code> in <code>df1</code>. </p>

<p>If my assumption above is correct, then this can be done with a simple join. Like so: </p>

<pre><code>pd.merge(left=df1, left_on="p_people_id",
         right=df2, right_on="p_people_id")
</code></pre>

<p>If there are 1000 different p_people_id in df1 and each of them had 6 lines in df2, the above statement would produce a dataframe with 6000 rows.</p>

<p>You can then select the desired columns in the result. </p>

python pandas dataframe