Pandas: How to Unstack Every Third Index(row) and widen the DataFrame?

user_13607357 0
Viewed 41

I have the following sample DataFrame (wherein the real data set, the pattern repeats for many rows). I wish to move the 'Category' 'Section' 'Level' 'Result' from rows to column labels.

The row following are the features of the above, and are

Input Code is representative of the following:

df = pd.DataFrame({'name': ['Joe Bloggs', 'Category',
...                                   'A', 'Jane Doe', 'Category',
...                                   'B'],
...                    'Date': ['2020-06-19' , 'Section', '1', '2020-06-19',
...                            'Section', '2'],
...                    'Time': ["09:00:00", "Level", "First", "16:30:06",
...                                 "Level", "Third"],
...                   'type': ["Login", "Result",
...                          "Pass", "Logout",
...                          "Result", "Fail"]})

Desired Output is as follows:

Desired Output is

Many thanks

pandas python
1 Answers

Try:

df=pd.concat([\
    df.iloc[0::3].reset_index(drop=True), \
    pd.DataFrame(\
        df.iloc[2::3].to_numpy(), \
        columns=df.iloc[1].to_numpy()\
    )], axis=1\
)

Outputs:

         name        Date      Time  ... Section  Level Result
0  Joe Bloggs  2020-06-19  09:00:00  ...       1  First   Pass
1    Jane Doe  2020-06-19  16:30:06  ...       2  Third   Fail
user_116101860
Related