如何将用空白(Nan)数据分隔的行转置为 python/pandas 中的多列? [英] How to transpose rows separated with blank (Nan) data to multi-column in python/pandas?

问题描述

我是 Python 新手，我想改进我使用 VBA 制作的几个 excel 程序.像下面那个.我有一个机器日志，它由 2 列组成，平均 50,000 行，每组用空格分隔.示例:

样本数据

我想把它转换成每组这个柱状.

输出数据

我不需要第一列，我只需要要转换的第二列.我已经通过 Excel 中的 VBA 完成了它，但是转换 50,000 行需要 2-5 分钟.

我自学 Python 已经有一段时间了，我希望它能通过 Pandas 或 numpy 加快进程.

非常感谢.

解决方案

输入数据:

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>6NaN # 结束第一组 (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

这篇关于如何将用空白(Nan)数据分隔的行转置为 python/pandas 中的多列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！