How To Slice A Row With Duplicate Column Names And Stack That Rows In Order

I have a dataframe as shown in the image and I want to convert it into multiple rows without changing the order. RESP HR SPO2 PULSE 1 46 122 0 0 2 46 122 0 0 3

Solution 1:

One possible solution is use reshape, only necessary modulo of length of columns is 0 (so is possible convert all data to 4 columns DataFrame):

df1 = pd.Dataframe(df.values.reshape(-1, 4), columns=['RESP','HR','SPO2','PULSE'])
df1['RESP1'] = df['RESP'].shift(-1)

General data solution:

a ='46 122 0 0 46 122 0 0 45 122 0 0 45 122 0'.split()
df = pd.DataFrame([a]).astype(int)
print (df)
    012345678910111213140461220046122004512200451220

#flatten values
a = df.values.ravel()
#number ofnew columns
N =4
#array filled by NaNs for possible add NaNs toendoflastrow
arr = np.full(((len(a) -1)//N +1)*N, np.nan)
#fill arrayby flatten values
arr[:len(a)] = a
#reshape tonew DataFrame (lastvalueis NaN)
df1 = pd.DataFrame(arr.reshape((-1, N)), columns=['RESP','HR','SPO2','PULSE'])
#newcolumnwith shifting first col
df1['RESP1'] = df1['RESP'].shift(-1)
print(df1)
   RESP     HR  SPO2  PULSE  RESP1
046.0122.00.00.046.0146.0122.00.00.045.0245.0122.00.00.045.0345.0122.00.0    NaN    NaN

Solution 2:

Here's another way with groupby:

df = pd.DataFrame(np.random.arange(12), columns=list('abcd'*3))

new_df = pd.concat((x.stack().reset_index(drop=True)
                     .rename(k) for k,x in df.groupby(df.columns, axis=1)), 
                    axis=1)

new_df = (new_df.assign(a1=lambda x: x['a'].shift(-1))
                .rename(columns={'a1':'a'})
         )

Output:

ab   c   d    a001234.0145678.02891011  NaN