How To Transpose Rows Separated With Blank (nan) Data To Multi-column In Python/pandas?

I'm new to python an I want to improve several excel programs I've made using VBA. Like the one below. I have a machine log which is consist of 2 Columns and average of 50,000 Rows

Solution 1:

Input data:

df = pd.read_excel("sample.xlsx", header=None, names=["Operation", "Data"])

>>> df
       Operation                          Data
0    <Operation>                           NaN  # begin 1st group (idx1)
1            NaN  <Timestamp>value</Timestamp>
2            NaN            <Type>value</Type>
3            NaN            <Name>value</Name>
4            NaN         <Action>value</Action
5            NaN            <Data>value</Data>
6   </Operation>                           NaN  # end 1st group (idx2)
7    <Operation>                           NaN  # begin 2nd group (idx1)
8            NaN  <Timestamp>value</Timestamp>
9            NaN            <Type>value</Type>
10           NaN            <Name>value</Name>
11           NaN         <Action>value</Action
12           NaN            <Data>value</Data>
13  </Operation>                           NaN  # end 2nd group (idx2)
14   <Operation>                           NaN  # begin 3rd group (idx1)
15           NaN  <Timestamp>value</Timestamp>
16           NaN            <Type>value</Type>
17           NaN            <Name>value</Name>
18           NaN         <Action>value</Action
19  </Operation>                           NaN  # end 3rd group (idx2)

Comments inside the snippet. Below a one-line version of this code:

data = []
idx1 = df[df["Operation"].eq("<Operation>")].index  # [0, 6, 13]
idx2 = df[df["Operation"].eq("</Operation>")].index  # [7, 14, 19]

for i1, i2 in zip(idx1, idx2):  # [(0, 7), (6, 14), (13, 19)]
    # Getvalues inside the group [(1, 6), (7, 13), (14, 18)]
    df1 = df["Data"].loc[i1+1:i2-1].reset_index(drop=True)
    data.append(df1)

# Concatenate all operations, swap columns androws (.Transpose)
out= pd.concat(data, axis="columns").T.reset_index(drop=True)

# One line
# out= pd.concat([df["Data"].loc[i1+1:i2-1].reset_index(drop=True)
#                      for i1, i2 in zip(df[df["Operation"].eq("<Operation>")].index,
#                                        df[df["Operation"].eq("</Operation>")].index)],
#                 axis="columns").T.reset_index(drop=True)

Output result:

>>> out
                              0                   1                   2                      3                   4
0  <Timestamp>value</Timestamp><Type>value</Type><Name>value</Name><Action>value</Action  <Data>value</Data>
1  <Timestamp>value</Timestamp><Type>value</Type><Name>value</Name><Action>value</Action  <Data>value</Data>
2  <Timestamp>value</Timestamp><Type>value</Type><Name>value</Name><Action>value</Action                 NaN