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>In the general case this is a set consolidation/connected components problem.  While if we assume certain things about your data we can solve a reduced case, it's just a bit of bookkeeping to do the whole thing.</p>

<p>scipy has a connected components function we can use if we do some preparation:</p>

<pre><code>import scipy.sparse

def via_cc(df_in):
    df = df_in.copy()

    # work with ranked version
    dfr = df[["y","z"]].rank(method='dense')
    # give nans their own temporary rank
    dfr = dfr.fillna(dfr.max().fillna(0) + dfr.isnull().cumsum(axis=0))
    # don't let y and z get mixed up; have separate nodes per column
    dfr["z"] += dfr["y"].max() 

    # build the adjacency matrix
    size = int(dfr.max().max()) + 1
    m = scipy.sparse.coo_matrix(([1]*len(dfr), (dfr.y, dfr.z)),
                                (size, size))

    # do the work to find the groups
    _, cc = scipy.sparse.csgraph.connected_components(m)

    # get the group codes
    group = pd.Series(cc[dfr["y"].astype(int).values], index=dfr.index)
    # fill in w from x appropriately
    df["w"] = df["x"].groupby(group).transform(min)

    return df
</code></pre>

<p>which gives me</p>

<pre><code>In [230]: via_cc(df0)
Out[230]: 
   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

In [231]: via_cc(df1)
Out[231]: 
   x   y   z  w
0  a  ii  mm  a
1  b  ii  nn  a
2  c  jj  nn  a
3  d  jj  oo  a
4  e  kk  oo  a
</code></pre>

<p>If you have a set consolidation recipe around, like the one <a href="https://rosettacode.org/wiki/Set_consolidation#Python:_Iterative" rel="nofollow">here</a>, you can simplify some of the above at the cost of an external function.</p>

<p>(Aside: note that in my df0, the "Nan"s are really NaNs.  If you have a <em>string</em> "Nan" (note how it's different from NaN), then the code will think it's just another string and will assume that you want all "Nan"s to be in the same group.)</p>

python pandas