How to get key and value in well format and n/a values at the end in pandas
<p>Sort the data in ascending order and the keys which are not present need to be printed in the last.</p>

<p>Please suggest a solution and also suggest if any modifications is required.</p>

<p><strong>Input.txt-</strong></p>

<pre><code>3=1388|4=1388|5=M|8=157.75|9=88929|1021=1500|854=n|388=157.75|394=157.75|474=157.75|1584=88929|444=20160713|459=93000546718000|461=7|55=93000552181000|22=89020|400=157.75|361=0.73|981=0|16=1468416600.6006|18=1468416600.6006|362=0.46
3=1388|4=1388|5=M|8=157.73|9=100|1021=0|854=p|394=157.73|474=157.749977558|1584=89029|444=20160713|459=93001362639104|461=26142|55=93001362849000|22=89120|361=0.71|981=0|16=1468416601.372|18=1468416601.372|362=0.45
</code></pre>

<p><strong>Program-Code-</strong></p>

<pre><code>import pandas as pd
import numpy as np 
from operator import itemgetter   
df = pd.read_csv("C:\",index_col=None, names=['text'])
s = df.text.str.split('|')
ds =[dict(w.split('=',1 ) for w in x) for x in s]
p = pd.DataFrame.from_records(ds)
p1 = p.replace(np.nan,'n/a', regex=True)
st = p1.stack(level=0,dropna=False)
dfs = [g for i, g in st.groupby(level=0)]
dfs_length = len(dfs)
i = 0
while i &lt; len(dfs):    
    print '\nindex[%d]'%i
    for (_,k),v in dfs[i].iteritems():
        print k,'\t',v
    i = i + 1
</code></pre>

<p><strong>OUTPUT (I got):</strong></p>

<pre><code>index[0]
1021      1500      
1584      88929     
16        1468416600.6006
18        1468416600.6006
22        89020     
3         1388      
361       0.73      
362       0.46     
388       157.75    
394       157.75    
4         1388      
400       157.75    
444       20160713  
459       93000546718000
461       7         
474       157.75    
5         M       
55        93000552181000
8         157.75    
854       n         
9         88929     
981       0         

index[1]
1021      0         
1584      89029     
16        1468416601.372
18        1468416601.372
22        89120     
3         1388      
361       0.71      
362       0.45     
388       n/a       
394       157.73    
4         1388      
400       n/a       
444       20160713  
459       93001362639104
461       26142     
474       157.749977558
5         IBM       
55        93001362849000
8         157.73    
854       p         
9         100       
981       0         
</code></pre>

<p><strong>EXPECTED OUTPUT</strong></p>

<pre><code>index[0]
3         1388
4         1388
5         M
8         157.75
9         88929
16        1468416600.6006
18        1468416600.6006
22        89020
55        93000552181000
361       0.73
388       157.75
394       157.75
400       157.75
444       20160714
459       93000546718000
461       7
474       157.75
854       n
981       0
1021      1500
1584      88929

index[1]
3         1388 
4         1388 
5         M 
8         157.73 
9         100      
16        1468416601.372
18        1468416601.372
22        89120 
55        9300136284900 
361       0.71      
362       0.45  
394       157.73 
444       20160713  
459       93001362639104
461       26142     
474       157.749977558 
854       p   
981       0    
1021      0         
1584      89029          
388       n/a       
400       n/a       
</code></pre>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.stack.html" rel="nofollow"><code>stack</code></a> for creating <code>Series</code>, which is splited by <code>=</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.split.html" rel="nofollow"><code>split</code></a> and converted to <code>DataFrame</code>. Then first column is casted to <code>int</code>, overwrite index by first column by <code>set_index</code> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_index.html" rel="nofollow"><code>sort_index</code></a>:</p>

<pre><code>temp=u"""3=1388|4=1388|5=M|8=157.75|9=88929|1021=1500|854=n|388=157.75|394=157.75|474=157.75|1584=88929|444=20160713|459=93000546718000|461=7|55=93000552181000|22=89020|400=157.75|361=0.73|981=0|16=1468416600.6006|18=1468416600.6006|"""
#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp), sep='|',index_col=None, header=None)
</code></pre>



<pre><code>df1 = df.stack().str.split('=', expand=True)
df1.iloc[:,0] = df1.iloc[:,0].astype(int)
df1 = df1.set_index(0).sort_index()
print (df1)
                    1
0                    
3                1388
4                1388
5                   M
8              157.75
9               88929
16    1468416600.6006
18    1468416600.6006
22              89020
55     93000552181000
361              0.73
388            157.75
394            157.75
400            157.75
444          20160713
459    93000546718000
461                 7
474            157.75
854                 n
981                 0
1021             1500
1584            88929
</code></pre>

<p>Another solution with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_values.html" rel="nofollow"><code>sort_values</code></a>:</p>

<pre><code>df1= df.stack().str.split('=', expand=True)
df1.columns = ['a','b']
df1['a'] = df1['a'].astype(int)
df1 = df1.reset_index(drop=True).sort_values('a')
print (df1)
       a                b
0      3             1388
1      4             1388
2      5                M
3      8           157.75
4      9            88929
19    16  1468416600.6006
20    18  1468416600.6006
15    22            89020
14    55   93000552181000
17   361             0.73
7    388           157.75
8    394           157.75
16   400           157.75
11   444         20160713
12   459   93000546718000
13   461                7
9    474           157.75
6    854                n
18   981                0
5   1021             1500
10  1584            88929
</code></pre>

python pandas