Skip to main content
 首页 » 编程设计

python之Pandas 重采样和插值功能太慢

2024年12月31日6落叶无声

我有一个用例,在该用例中,我对从 10 个 json 对象的列表创建的小型数据框进行了重新采样。数据框中有 10 行 50 列,缺少 20% 的字段。重采样后,我按列插入数据帧,因为我要选择用户定义的插值方法。执行此操作的代码如下:

df = pd.DataFrame(packets_dict) 
df = df.set_index('datetime') 
df = df.resample('60S').first() 
for column in columns_rule: 
    if column in df.columns: 
        # replace value by null if it is out of given min and max values. 
        if 'max_value' in columns_rule[column].keys(): 
            df[column] = df[column].where(df[column] < columns_rule['column']['max_value']) 
        if 'min_value' in columns_rule[column].keys(): 
            df[column] = df[column].where(df[column] > column_rule[column]['min_value']) 
        df[column] = df[column].interpolate(method=linear, limit=3) 

我需要在高速传输的流数据上运行这段代码。但是这段代码执行时间太长。我已经完成了这段代码的分析,通过运行这段代码 990 次显示了以下结果。

resample 耗时 137.347 秒,调用 990 次,一次函数调用耗时 138.79 毫秒。

其中 75.272 耗时数秒,调用 87120 次,一次函数调用耗时 0.864 毫秒。

interpolate 耗时 21.928 秒,调用 43560 次,一次函数调用耗时 0.503 毫秒。

其余代码速度非常快,不会花费太多时间。 这些函数完成 990 次迭代所用的总时间为 234.5 秒,将近 4 分钟,远高于我们要求的速率。我需要优化此代码以将时间减少 20 到 30 倍。有什么方法可以优化这些 pandas 函数,或者我在使用这些函数时可能做错了什么。

我正在使用 Pandas 0.23.0 和 python3。

我已经搜索过了,但找不到任何解决方案。请帮助我提出您的意见和建议。

如果有人想尝试,这里有一个示例数据: 它不是实际数据,因为它无法共享,但我提供的数据主要是 float 、整数和字符串的数量,以及列和行的数量。此外,对于字符串列,我使用 ffill 或 bfill 而不是插值函数。

packets_dict = [ 
{'datetime':"2018-08-01 22:05:40",'field1':12.4,'field2':'aab','field3':1234,'field4':12.4,'field5':'aab','field6':1234,'field7':12.4,'field8':'aab','field9':1234,'field10':12.4,'field11':'aab','field12':1234,'field13':12.4,'field14':'aab','field15':1234,'field16':12.4,'field17':'aab','field18':1234,'field19':12.4,'field20':'aab','field21':1234,'field22':12.4,'field23':'aab','field24':1234,'field25':12.4,'field26':'aab','field27':1234,'field28':12.4,'field29':'aab','field30':1234,'field31':12.4,'field32':'aab','field33':1234,'field34':12.4,'field35':'aab','field36':1234,'field37':12.4,'field38':'aab','field39':1234,'field40':12.4,'field41':'aab','field42':1234,'field43':12.4,'field44':'aab','field45':1234,'field46':12.4,'field47':'aab','field48':1234,'field49':12.4}, 
{'datetime':"2018-08-01 22:06:41",'field10':12.4,'field11':'aab','field12':1234,'field13':12.4,'field14':'aab','field15':1234,'field16':12.4,'field17':'aab','field18':1234,'field19':12.4,'field20':'aab','field21':1234,'field22':12.4,'field23':'aab','field24':1234,'field25':12.4,'field26':'aab','field27':1234,'field28':12.4,'field29':'aab','field30':1234,'field31':12.4,'field32':'aab','field33':1234,'field34':12.4,'field35':'aab','field36':1234,'field37':12.4,'field38':'aab','field39':1234,'field40':12.4,'field41':'aab','field42':1234,'field43':12.4,'field44':'aab','field45':1234,'field46':12.4,'field47':'aab','field48':1234,'field49':12.4}, 
{'datetime':"2018-08-01 22:07:42",'field1':12.4,'field2':'aab','field3':1234,'field4':12.4,'field5':'aab','field6':1234,'field7':12.4,'field8':'aab','field9':1234,'field10':12.4,'field11':'aab','field12':1234,'field13':12.4,'field14':'aab','field15':1234,'field16':12.4,'field17':'aab','field18':1234,'field19':12.4,'field20':'aab','field21':1234,'field22':12.4,'field23':'aab','field24':1234,'field25':12.4,'field26':'aab','field27':1234,'field28':12.4,'field29':'aab','field30':1234,'field31':12.4,'field32':'aab','field33':1234,'field34':12.4,'field35':'aab','field36':1234,'field37':12.4,'field38':'aab','field39':1234,'field40':12.4,'field41':'aab','field42':1234,'field43':12.4,'field44':'aab','field45':1234,'field46':12.4,'field47':'aab','field48':1234,'field49':12.4}, 
{'datetime':"2018-08-01 22:08:44",'field1':12.4,'field2':'aab','field3':1234,'field4':12.4,'field5':'aab','field6':1234,'field7':12.4,'field8':'aab','field21':1234,'field22':12.4,'field23':'aab','field24':1234,'field25':12.4,'field26':'aab','field27':1234,'field28':12.4,'field29':'aab','field30':1234,'field31':12.4,'field32':'aab','field33':1234,'field34':12.4,'field35':'aab','field36':1234,'field37':12.4,'field38':'aab','field39':1234,'field40':12.4,'field41':'aab','field42':1234,'field43':12.4,'field44':'aab','field45':1234,'field46':12.4,'field47':'aab','field48':1234,'field49':12.4}, 
{'datetime':"2018-08-01 22:09:46",'field1':12.4,'field2':'aab','field3':1234,'field4':12.4,'field5':'aab','field6':1234,'field7':12.4,'field8':'aab','field9':1234,'field10':12.4,'field11':'aab','field12':1234,'field13':12.4,'field14':'aab','field15':1234,'field16':12.4,'field17':'aab','field18':1234,'field25':12.4,'field26':'aab','field27':1234,'field28':12.4,'field29':'aab','field30':1234,'field31':12.4,'field32':'aab','field33':1234,'field34':12.4,'field35':'aab','field36':1234,'field37':12.4,'field38':'aab','field39':1234,'field40':12.4,'field41':'aab','field42':1234,'field43':12.4,'field44':'aab','field45':1234,'field46':12.4,'field47':'aab','field48':1234,'field49':12.4}, 
{'datetime':"2018-08-01 22:10:49",'field1':12.4,'field2':'aab','field3':1234,'field4':12.4,'field5':'aab','field6':1234,'field7':12.4,'field8':'aab','field9':1234,'field10':12.4,'field11':'aab','field12':1234,'field13':12.4,'field14':'aab','field15':1234,'field16':12.4,'field17':'aab','field18':1234,'field19':12.4,'field20':'aab','field21':1234,'field22':12.4,'field23':'aab','field24':1234,'field25':12.4,'field26':'aab','field27':1234,'field28':12.4,'field41':'aab','field42':1234,'field43':12.4,'field44':'aab','field45':1234,'field46':12.4,'field47':'aab','field48':1234,'field49':12.4}, 
{'datetime':"2018-08-01 22:11:50",'field1':12.4,'field2':'aab','field3':1234,'field4':12.4,'field5':'aab','field6':1234,'field7':12.4,'field8':'aab','field9':1234,'field10':12.4,'field11':'aab','field12':1234,'field13':12.4,'field14':'aab','field15':1234,'field16':12.4,'field17':'aab','field18':1234,'field19':12.4,'field20':'aab','field21':1234,'field22':12.4,'field23':'aab','field24':1234,'field25':12.4,'field26':'aab','field27':1234,'field28':12.4,'field29':'aab','field30':1234,'field31':12.4,'field32':'aab','field33':1234,'field34':12.4,'field35':'aab','field36':1234,'field37':12.4,'field48':1234,'field49':12.4}, 
{'datetime':"2018-08-01 22:12:54",'field1':12.4,'field2':'aab','field3':1234,'field4':12.4,'field5':'aab','field6':1234,'field7':12.4,'field8':'aab','field9':1234,'field10':12.4,'field11':'aab','field12':1234,'field13':12.4,'field14':'aab','field15':1234,'field16':12.4,'field17':'aab','field18':1234,'field19':12.4,'field20':'aab','field21':1234,'field22':12.4,'field23':'aab','field24':1234,'field25':12.4,'field26':'aab','field27':1234,'field28':12.4,'field38':'aab','field39':1234,'field40':12.4,'field41':'aab','field42':1234,'field43':12.4,'field44':'aab','field45':1234,'field46':12.4,'field47':'aab','field48':1234,'field49':12.4}, 
{'datetime':"2018-08-01 22:15:55",'field1':12.4,'field2':'aab','field3':1234,'field4':12.4,'field5':'aab','field6':1234,'field7':12.4,'field8':'aab','field9':1234,'field10':12.4,'field11':'aab','field12':1234,'field13':12.4,'field14':'aab','field15':1234,'field16':12.4,'field17':'aab','field18':1234,'field19':12.4,'field20':'aab','field21':1234,'field22':12.4,'field23':'aab','field24':1234,'field25':12.4,'field26':'aab','field27':1234,'field28':12.4,'field29':'aab','field30':1234,'field31':12.4,'field32':'aab','field33':1234,'field34':12.4,'field35':'aab','field36':1234,'field37':12.4,'field38':'aab','field39':1234,'field40':12.4,'field41':'aab','field42':1234,'field43':12.4,'field44':'aab','field45':1234,'field46':12.4,'field47':'aab','field48':1234,'field49':12.4}, 
{'datetime':"2018-08-01 22:16:01",'field1':12.4,'field2':'aab','field3':1234,'field4':12.4,'field5':'aab','field6':1234,'field7':12.4,'field8':'aab','field28':12.4,'field29':'aab','field30':1234,'field31':12.4,'field32':'aab','field33':1234,'field34':12.4,'field35':'aab','field36':1234,'field37':12.4,'field38':'aab','field39':1234,'field40':12.4,'field41':'aab','field42':1234,'field43':12.4,'field44':'aab','field45':1234,'field46':12.4,'field47':'aab','field48':1234,'field49':12.4} 
] 
 
columns_rule = { 
'field1':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field3':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field4':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field6':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field7':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field9':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field10':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field12':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field13':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field15':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field16':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field18':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field19':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field21':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field22':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field24':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field25':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field26':'aab', 
'field27':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field28':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field30':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field31':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field33':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field34':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field36':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field37':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field39':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field40':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field42':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field43':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field45':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field46':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field48':{ 
    'max_value':999, 
    'min_value':0 
}, 
'field49':{ 
    'max_value':999, 
    'min_value':0 
} 
}

请您参考如下方法:

想法是在循环中提取 dict 的值,然后使用所有匹配列的 DataFrame 并使用 instaed resample Grouper使用 groupby:

df = df.set_index('datetime').groupby(pd.Grouper(freq='60S')).first() 
 
dmin, dmax = {}, {} 
cmin, cmax = [],[] 
for column in columns_rule: 
    if column in df.columns: 
        if 'max_value' in columns_rule[column]: 
            dmax[column] = columns_rule[column]['max_value'] 
            cmax.append(column) 
        if 'min_value' in columns_rule[column]: 
            dmin[column] = columns_rule[column]['min_value'] 
            cmin.append(column) 
 
m1 = df[cmax].lt(pd.Series(dmax)) 
m2 = df[cmin].gt(pd.Series(dmin)) 
 
cols = np.union1d(cmin, cmax) 
df[cmax] = np.where(m1, df[cmax], np.nan) 
df[cmin] = np.where(m2, df[cmin], np.nan) 
df[cols] = df[cols].interpolate(method='linear', limit=3)