Python-replacing Scores With Actual Value(transposing)

I have this data that looks like I want to transpose the rows like mentioned in the 'desired output'.I understand that I need to use something similar to melt and cast(used in

Solution 1:

Using the data above

import pandas as pd
from io import StringIO
import itertools

text = u'Time \t          Pressure\tNormal/Abnormal\n11/30/2011 22:50\t74.3\t  0\n11/30/2011 23:00\t74.8\t  1\n11/30/2011 23:10\t77.7\t  1\n11/30/2011 23:30\t74.8\t  0\n11/30/2011 13:00\t80.9\t  0'

df = pd.read_table(StringIO(text))
normal = df.loc[df['Normal/Abnormal'] == 0].as_matrix()
abnormal = df.loc[df['Normal/Abnormal'] == 1].as_matrix()

columns = ["Time", "Normal", "Time", "Abnormal"]
out = []

for nr, ar in itertools.izip_longest(normal, abnormal, fillvalue=['', '']):
    # Concat rows horizontally (i.e. hstack)
    r = list(nr[:2]) + list(ar[:2])
    out.append(r)

df2 = pd.DataFrame(out, columns=columns)

print df2.to_string(index=False)

''' Output
Time  Normal              Time Abnormal
11/30/2011 22:50    74.3  11/30/2011 23:00     74.8
11/30/2011 23:30    74.8  11/30/2011 23:10     77.7
11/30/2011 13:00    80.9
'''

Solution 2:

construct two data frames, 1 for normal & 1 for abnormal and then concat & edit column names

out= pd.concat([
  df[df['Normal/Abnormal'] == k].iloc[:, [0,1]].reset_index(drop=True)
  for k in [0, 1]], axis=1
)
out.columns = ['Time', 'Normal', 'Time', 'Abnormal']
outTime  Normal              Time  Abnormal
011/30/201122:5074.311/30/201123:0074.8111/30/201123:3074.811/30/201123:1077.7211/30/201113:0080.9               NaN       NaN