Move non empty cells to left in grouped columns pandas
<p>I have a dataframe where there are multiple columns with similar column names. I want the empty cells to be populated with those columns which have data to the right. </p>

<pre><code>Address1     Address2     Address3     Address4     Phone1     Phone2     Phone3     Phone4
ABC          nan          def          nan          9091-XYz   nan        nan        XYZ-ABZ
</code></pre>

<p>Should be column shifted to something like</p>

<pre><code>Address1     Address2     Address3     Address4     Phone1     Phone2     Phone3     Phone4
ABC          def          nan          nan          9091-XYz   XYZ-ABZ    nan        nan 
</code></pre>

<p>There's another <a href="http://stackoverflow.com/questions/32062157/move-non-empty-cells-to-the-left-in-pandas-dataframe">question</a> which solves a similar problem.</p>

<pre><code>pdf = pd.read_csv('Data.txt',sep='\t')

# gets a set of columns removing the numerical part
columns = set(map(lambda x : x.rstrip('0123456789'),pdf.columns))

for col_pattern in columns:
    # get columns with similar names
    current = [col for col in pdf.columns if col_pattern in col]
    coldf= pdf[current]
    # shift columns to the left
</code></pre>

<p>The file <code>Data.txt</code> has columns sorted by column names so all the columns with similar names come together.</p>

<p>Any help with this is appreciated</p>

<p>I had tried adding this to the above code from the link, which ran out of memory :</p>

<pre><code>    newdf=pd.read_csv(StringIO(u''+re.sub(',+',',',df.to_csv()).decode('utf-8')))
    list_.append(newdf)
pd.concat(list_,axis=0).to_csv('test.txt')
</code></pre>

<p><strong><code>pushna</code></strong><br>
Pushes all null values to the end of the series</p>

<p><strong><code>coltype</code></strong><br>
Uses <code>regex</code> to extract the non-numeric prefix from all column names</p>

<pre><code>def pushna(s):
    notnull = s[s.notnull()]
    isnull = s[s.isnull()]
    values = notnull.append(isnull).values
    return pd.Series(values, s.index)

coltype = df.columns.to_series().str.extract(r'(\D*)', expand=False)

df.groupby(coltype, axis=1).apply(lambda df: df.apply(pushna, axis=1))
</code></pre>

<p><a href="http://i.stack.imgur.com/l2Msb.png" rel="nofollow"><img src="http://i.stack.imgur.com/l2Msb.png" alt="enter image description here"></a></p>

python pandas multiple-columns shift