时间戳

首页 > 解决方案 > 如何使用python将时间序列划分为间隔（不等间隔）并在每个间隔中求和值

python - 如何使用python将时间序列划分为间隔（不等间隔）并在每个间隔中求和值

问题描述

问候大家，

我有两个大表，我从网络模拟中得到它。第一个表如下所示：

name      time    previousTime   nextTime
A         4.22       0.00          4.23
B         4.32       4.22          9.22
A         9.22       4.32          9.23
C         9.32       9.22          10.1
A         10.1       9.23          10.1

第二个表看起来像：

name      time      value 
A         4.11       2
B         4.32       1
A         4.22       0
C         9.32       4
A         4.5        1
A         9.5        8
A         9.6        4

我想要做的是，从第一个表中取出行并在第二个表中检查第二个表中的所有行，其中第二个表中的时间在 previousTime nextTime 或等于 previousTime 或 nextTime 或两者之间。然后将该值作为第一个表中的新列求和。

我期望输出如下：

name      time    previousTime   nextTime    Value 
A         4.22       0.00          4.23        2
B         4.32       4.22          9.22        1
A         9.22       4.23          9.23        1
C         9.32       9.22          10.1        4
A         10.1       9.23          10.1        12

我写了这个 python 代码，但给了我不同的值

import sqlite3
import pandas as pd
import numpy as np
import math
import csv

con = sqlite3.connect("G1.db")
cur = con.cursor()

result =[]
value =[]

q1= cur.execute("SELECT Name,Time,PreviousValue,NextValue from table1 GROUP by SourceName,Time")

rq1 = q1.fetchall()


q2=cur.execute("SELECT Time from table2")

rq2 = q2.fetchall()
print(rq2)


for row in rq1:
      result.append(row)  
print(result[0])
len(rq1)

#start my code
for i in range(len(rq1)):
    for j in range(len(rq2)):
        name = result[i][j]
        T = result[i][j+1]
        P = result[i][j+2]
        N = result[i][j+3]

        print('Name =',name)
        print('P =',P)
        print('T =',T)
        print('N =',N)
        q3= cur.execute("SELECT time,value,sum(value) AS Tsum\
                 from table2\
                 where (Name LIKE '%' || ? || '%' AND (time > ? AND time <= ?)) OR (Name LIKE '%' || ? || '%' AND time == ? ) ",(name,P,N,name,P))

        rq3 = q3.fetchall()
        print (rq3)
        q3len = len(rq3)
        v1 = rq3[j][j+1]
        print('q3 ',rq3[j][j+1])
        value.append(v1)
print (value)
len(value)

任何帮助将不胜感激

标签： pythonsimulation

解决方案

这是给你的完整代码

import pandas as pd

c1 = pd.Series(["A", "B", "A", "C","A"])
c2 = pd.Series([4.22,4.32,9.22,9.32,10.1])
c3 = pd.Series([0.00,4.22,4.32,9.22,9.32])
c4 = pd.Series([4.23,9.22,9.23,10.1,10.1])
c5 = pd.Series([])

Data ={'name':c1, 'time':c2, 'previousTime':c3, 'nextTime': c4, "sum_": c5} # Define Data
table1 = pd.DataFrame(Data)              # Create DataFrame


c1 = pd.Series(["A","B","A","C","A","A","A"])
c2 = pd.Series([4.11,4.32,4.22,9.32,4.5,9.5,9.6])
c3 = pd.Series([2,1,0,4,1,8,4])

Data ={'name':c1, 'time':c2, 'value':c3} # Define Data
table2 = pd.DataFrame(Data)              # Create DataFrame



for idx, row in table1.iterrows():
    counter = 0
    for _, elm_row in table2.iterrows():
        if row.previousTime <= elm_row.time <= row.nextTime:
            counter += 1
    table1.sum_[idx] = int(counter)

==================================================== ================= 你可能需要做一些调整，但这会起作用

import pyodbc
import pandas as pd

con = pyodbc.connect("G1.db")

sql = "SELECT Name,Time,PreviousValue,NextValue from table1 GROUP by SourceName,Time"
table1 = pd.read_sql(sql, con)


sql_ = "SELECT Time from table2"

table2 = pd.read_sql(sql_, con)



result = []

for idx, row in table1.iterrows():
    counter = 0
    for _, elm_row in table2.iterrows():
        if row.previousTime <= elm_row.time <= row.nextTime:
            counter += 1
    result.append(counter)

temp_fr = pd.DataFrame({"sum_": result})

table1.join(temp_fr)

推荐阅读