输入数据:

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

>>>df运营数据0 NaN # 开始第一组 (idx1)1 NaN<时间戳>值</时间戳>2 NaN <Type>值</Type>3 NaN <名称>值</名称>4 NaN <Action>值</Action5 NaN <Data>值</Data>6操作>NaN # 结束第一组 (idx2)7 <操作>NaN # 开始第二组 (idx1)8 NaN<时间戳>值</时间戳>9 NaN <Type>值</Type>10 NaN <名称>值</名称>11 NaN <Action>值</Action12 NaN <Data>值</Data>13 </操作>NaN # 结束第二组 (idx2)14 <操作>NaN # 开始第三组 (idx1)15 NaN<时间戳>值</时间戳>16 NaN <Type>值</Type>17 NaN<名称>值</名称>18 NaN <Action>value</Action19 </操作>NaN # 结束第 3 组 (idx2)

代码段内的评论.下面是此代码的单行版本:

data = []idx1 = df[df[操作"].eq(")].index # [0, 6, 13]idx2 = df[df["Operation"].eq("")].index # [7, 14, 19]对于 zip(idx1, idx2) 中的 i1, i2: # [(0, 7), (6, 14), (13, 19)]# 获取组内的值 [(1, 6), (7, 13), (14, 18)]df1 = df[数据"].loc[i1+1:i2-1].reset_index(drop=True)数据.附加(df1)# 连接所有操作，交换列和行 (.Transpose)out = pd.concat(data,axis=columns").T.reset_index(drop=True)# 一条线# out = pd.concat([df["Data"].loc[i1+1:i2-1].reset_index(drop=True)# for i1, i2 in zip(df[df["Operation"].eq("")].index,# df[df["Operation"].eq("")].index)],#axis=列".T.reset_index(drop=True)

输出结果:

>>>出去0 1 2 3 40 <时间戳>值</时间戳><类型>值</类型><名称>值</名称><Action>value</Action<Data>value</Data>1 <时间戳>值</时间戳><类型>值</类型><名称>值</名称><Action>value</Action<Data>value</Data>2 <时间戳>值</时间戳><类型>值</类型><名称>值</名称><Action>值</Action NaN

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, every group is separated by spaces. Sample:

Sample Data

and i want to transform it to this columnar per group.

Output Data

I don't need the 1st column, what I only need is the 2nd columns to be transformed. I already made it thru VBA in excel but it took 2-5 mins to transform 50,000 rows.

I've been self learning python for a while and I hope it will speed up the process thru pandas or numpy.

Thanks a lot.

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)]
    # Get values 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 and rows (.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