我想使用Pandas遍历包含时间戳的xlsx，并获得停机时间

Question

因此，我有一个excel文件，第一列中有日期，连续行中有固定间隔时间戳，最后一列包含平均值。如果平均值低于100，我必须遍历该特定列，找到包含多个连续零的单元格，因为这表示停机时间

找到这些零后，我必须存储它们的时间戳，并获取该日期的停机时间。到目前为止，我知道如何迭代并找到平均值低于零的特定行。我被困在了可以找到2个或更多连续零并存储它们的时间戳的地方

该表的一个示例是：

+---------+------------+------------+-------------+------------+---------+
|  Date   | Timestamp1 | Timestamp2 | TImestamps3 | Timestamp4 | Average |
+---------+------------+------------+-------------+------------+---------+
| 1-10-18 |        100 |        100 |           0 |        100 |      75 |
| 2-10-18 |        100 |          0 |           0 |        100 |      50 |
| 3-10-18 |        100 |          0 |           0 |          0 |      25 |
| 4-10-18 |        100 |        100 |         100 |        100 |     100 |
+---------+------------+------------+-------------+------------+---------+

所以现在我要做的就是把它放到字典里

{2-10-18:'Timestamp2-Timestamp3',3-10-18:'Timestamp2 Tmestamp4}

Answer 1

将计算转移到字典中，因为我无法通过Pandas找到合适的解决方案

  #convert to dictionary
  A = df.set_index('Date').to_dict('index')

  #remove values where 'Average' is less than 100
  B = {key:value for key,value in A.items() if value['Average'] < 100}

  #get rid of the 'Average' key
  #it is not relevant to the solution
  for value in B.values():
     del value['Average']

  from collections import Counter,defaultdict
  d=defaultdict(list)

 #count the number of 0s
 #if it is greater than one, keep
 #and append to the defaultdict d
 for key,value in B.items():
    m = Counter(value.values())
    M = list(value.values())
    #check for consecutive 0s in list
    test_for_consecutive_0 = any(i==j==0 for i,j in zip(M,M[1:]))
    if test_for_consecutive_0  and (m[0]>1):
        d[key].extend([key for key,val in value.items() if val==0])


 #join the values into a string
 result = {key:'-'.join(value) for key,value in d.items()}
 print(result)

 {'2-10-18': 'Timestamp2-TImestamps3',
  '3-10-18': 'Timestamp2-TImestamps3-Timestamp4'}