如何使用python将时间序列划分为区间（不等于区间）和每个区间的和值

Question

问候大家

我有两张大桌子，我是从网络模拟得到的。 第一个表如下所示：

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)

任何帮助都将不胜感激

Answer 1

这是你的完整代码

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)