我需要遍历每一行数据来计算每一列类别的时间吗？

Question

我有python中的数据列表，如下表所示。你知道吗

基本上，它是通过观察我们的机器人在迷宫/竞技场中所做的事情而产生的。我们有事件的时间戳，目前时间戳是事件驱动的，不是周期性的。你知道吗

我需要找到一个有效的方式在每个竞技场花费的时间。你知道吗

TimeStamp   Arena
101         Arena A
109         Arena A
112         Arena B
113         Arena A
118         Arena A
120         Arena D
125         Arena D
129         Arena D
138         Arena B
139         Arena B
148         Arena C
149         Arena C
150         Arena B
151         Arena B
159         Arena D
169         Arena D
171         Arena D
172         Arena D
175         Arena B
177         Arena B
180         Arena B
181         Arena A
182         Arena A
189         Arena E
200         Arena E
204         Arena E
208         Arena A
209         Arena A

基本上，我需要把这个放在下面。在每个竞技场花费的总时间。你知道吗

 Arena  TimeStamp
Arena D         32
Arena B         23
Arena E         22
Arena A         16
Arena C         10

我现在就写了一个简单的脚本。你知道吗

import pandas as pd

data = pd.read_csv('arenas_visited.csv')


l = len(data[[1]])
first_arena = data.loc[0, 'Arena']
start_time = data.loc[0, 'TimeStamp']

summary = []

for i in range(0,l):

try:
    next_arena = data.loc[i+1, 'Arena']
except:
    break     

first_arena = data.loc[i, 'Arena']   

if first_arena != next_arena:

    change_time = data.loc[i, 'TimeStamp']
    time_spent = change_time - start_time
    arena = str(data.loc[i, 'Arena'])
    summary.append([arena, time_spent])
    start_time = change_time
    first_arena = data.loc[i+1, 'Arena']   

    if i == l-2:
        if data.loc[i, 'Arena'] != data.loc[i+1, 'Arena']:
            time_spent = 1
            arena = str(data.loc[i+1, 'Arena'])
            print (str(1) + " Spent in " + arena)
            summary.append([arena, time_spent])

else:
    pass

aggregated = pd.DataFrame(summary, columns = ['Arena', 'TimeStamp'])
time_per_arena = aggregated.groupby(['Arena']).sum().sort_values('TimeStamp',  ascending=False).reset_index()
print time_per_arena

基本上，虽然这样做很好。然而，我最终会有数百万行这样的数据，我需要找出一个更快的方法来做到这一点。你知道吗

但是，除了遍历每一行之外，我看不到任何其他方法？你知道吗

是我没有考虑的事情吗？你知道吗

Answer 1

创建时间增量向量，然后对其进行分组和求和：

df['delta'] = df.TimeStamp - df.TimeStamp.shift()

df.groupby('Arena').delta.sum()
Out[62]: 
Arena
Arena_A    21.0
Arena_B    23.0
Arena_C    10.0
Arena_D    32.0
Arena_E    22.0
Name: delta, dtype: float64

Answer 2

Python有很多其他语言无法自动内置的东西。如果您能够：

result = {}
old_arena = None
old_timestamp = 0
# I don't have a lot of experience with panda, so you may need to massage the 
# input to be able to do this
for line in data:
    timestamp, _, arena = line.split()
    if arena == old_arena:
        continue
    timestamp = int(timestamp)
    try:
        result[old_arena] += timestamp - old_timestamp
    except:
        result[old_arena] = timestamp - old_timestamp

    old_arena = arena
    old_timestamp = timestamp

# Process the last interval - if the last one was changed, then
# old_timestamp will equal timestamp and this is fine    
result[old_arena] += int(timestamp) - old_timestamp

这将以O(n)时间和O(n+k)空间复杂度处理整个列表，其中k是竞技场的数量。你知道吗

结果应该是包含以下内容的dict（其中None表示初始时间偏移）：

{'A': 27, 'C': 2, 'B': 26, 'E': 19, 'D': 34, None: 101}

在您的示例数据中：值得注意的是，这提供了到旧的竞技场的转换，这可能不是您想要的。你知道吗

---

如果您想知道过渡到下一个竞技场的位置，可以通过反转遍历来提供：

result = {}
old_arena = None
old_timestamp = 0
# I don't have a lot of experience with panda, so you may need to massage the 
# input to be able to do this
for line in reversed(data):
    timestamp, _, arena = line.split()
    if arena == old_arena:
        continue
    timestamp = int(timestamp)
    try:
        result[old_arena] += old_timestamp - timestamp
    except:
        result[old_arena] = old_timestamp - timestamp

    old_arena = arena
    old_timestamp = timestamp

# Process the last interval - if the last one was changed, then 
# old_timestamp will equal timestamp and this is fine    
result[old_arena] += old_timestamp - int(timestamp)

它给出：

{'A': 21, 'C': 10, 'B': 23, 'E': 22, 'D': 32, None: -209}