Take union of two columns, Python + Pandas
<p>I have a df arranged like follows:</p>

<pre><code>   x    y    z
0  a   jj  Nan
1  b   ii   mm
2  c   kk   nn
3  d   ii  NaN
4  e  Nan   oo
5  f   jj   mm
6  g  Nan   nn
</code></pre>

<p>The desired output is:</p>

<pre><code>   x    y    z   w
0  a   jj  Nan   a
1  b   ii   mm   a
2  c   kk   nn   c
3  d   ii  NaN   a
4  e  Nan   oo   e
5  f   jj   mm   a
6  g  Nan   nn   c
</code></pre>

<p>The logic is </p>

<ol>
<li><p>to take union of column y &amp; z : <em><code>ii == jj</code> since in index 1 and 5, they both have <code>mm</code> in column z</em></p></li>
<li><p>group this union : <em>index 0,1,3,5 are a group, index 2,6 are another group</em></p></li>
<li><p>within the group, randomly take one cell in column x and assign it to column w for the whole group</p></li>
</ol>

<p>I have no clue at all about this problem. 
Can somebody help me?</p>

<p><strong>EDITNOTE:</strong></p>

<p>I was first post a perfectly sorted column y and column z like follows:</p>

<pre><code>   x    y    z   w
0  a   ii  NaN   a
1  b   ii   mm   a
2  c   jj   mm   a
3  d   jj  Nan   a
4  e   kk   nn   e
5  f  Nan   nn   e
6  g  Nan   oo   g
</code></pre>

<p>For this case, piRSquared's solution works perfect. </p>

<p><strong>EDITNOTE2:</strong> </p>

<p>Nickil Maveli's solution works perfect for my problem. However, I noted that there's a situation that the solution can not handle, that is :</p>

<pre><code>   x   y   z
0  a  ii  mm
1  b  ii  nn
2  c  jj  nn
3  d  jj  oo
4  e  kk  oo
</code></pre>

<p>By Nickil Maveli's solution, the result would be like follows:</p>

<pre><code>   0   1   2  w
0  a  ii  mm  a
1  b  ii  mm  a
2  c  jj  nn  c
3  d  jj  nn  c
4  e  kk  oo  e
</code></pre>

<p>However, the desired output should be w = ['a', 'a', 'a', 'a', 'a']. </p>

<p>Make all null strings as <code>NaN</code> values by replacing them. Next, group them according to 'y' and fill all the missing values with the value corresponding to it's first valid index present in 'z'. </p>

<p>Then, perform groupby operation on 'z', by applying sum which aggregates all the values present in 'x' together. Slice it accordingly to fill all the values in that group with that particular value(Here, slice=0).</p>

<p>Convert it to a dictionary to create the mapping and finally assign it back to a new column, 'w' as shown:</p>

<pre><code>df_new = df.replace('Nan', np.NaN)
df_new['z'] = df_new.groupby('y')['z'].transform(lambda x: x.loc[x.first_valid_index()])
df['w'] = df_new['z'].map(df_new.groupby('z')['x'].apply(lambda x: x.sum()[0]).to_dict())
df
</code></pre>

<p><a href="http://i.stack.imgur.com/V6RLm.png" rel="nofollow"><img src="http://i.stack.imgur.com/V6RLm.png" alt="Image"></a></p>

python pandas