gpt4 book ai didi

python - 合并间隔和时间戳数据帧

转载 作者:行者123 更新时间:2023-12-01 08:41:04 25 4
gpt4 key购买 nike

我有一个包含间隔的表格

dfa = pd.DataFrame({'Start': [0, 101, 666], 'Stop': [100, 200, 1000]})

我有另一个包含时间戳和值的表

dfb = pd.DataFrame({'Timestamp': [102, 145, 113], 'ValueA': [1, 2, 21],
'ValueB': [1, 2, 21]})

我需要创建一个与 dfa 大小相同的数据框,并添加一列,其中包含 ValueA/ValueB 的某些聚合结果,对于 dfbTimestamp 包含在 StartStop 之间的所有行。

所以这里如果将我的聚合定义为

{'ValueA':[np.nanmean,np.nanmin],
'ValueB':[np.nanmax]}

我想要的输出是:

ValueA   ValueA    ValueB
nanmean nanmin nanmax Start Stop

nan nan nan 0 100
8 1 21 101 200
nan nan nan 666 1000

最佳答案

使用mergeassign 创建的辅助列进行交叉连接:

d = {'ValueA':[np.nanmean,np.nanmin],
'ValueB':[np.nanmax]}

df = dfa.assign(A=1).merge(dfb.assign(A=1), on='A', how='outer')

然后按StartStop过滤并按字典聚合:

df = (df[(df.Timestamp >= df.Start) & (df.Timestamp <= df.Stop)]
.groupby(['Start','Stop']).agg(d))

通过 mapjoin 压平 MultiIndex:

df.columns = df.columns.map('_'.join)
print (df)
ValueA_nanmean ValueA_nanmin ValueB_nanmax
Start Stop
101 200 8 1 21

最后join原文:

df = dfa.join(df, on=['Start','Stop'])
print (df)
Start Stop ValueA_nanmean ValueA_nanmin ValueB_nanmax
0 0 100 NaN NaN NaN
1 101 200 8.0 1.0 21.0
2 666 1000 NaN NaN NaN

编辑:

解决方案 cut :

d = {'ValueA':[np.nanmean,np.nanmin],
'ValueB':[np.nanmax]}

#if not default index create it
dfa = dfa.reset_index(drop=True)
print (dfa)
Start Stop
0 0 100
1 101 200
2 666 1000

#add to bins first value of Start
bins = np.insert(dfa['Stop'].values, 0, dfa.loc[0, 'Start'])
print (bins)
[ 0 100 200 1000]

#binning
dfb['id'] = pd.cut(dfb['Timestamp'], bins=bins, labels = dfa.index)
print (dfb)
Timestamp ValueA ValueB id
0 102 1 1 1
1 145 2 2 1
2 113 21 21 1

#aggregate and flatten
df = dfb.groupby('id').agg(d)
df.columns = df.columns.map('_'.join)

#add to dfa
df = pd.concat([dfa, df], axis=1)
print (df)
Start Stop ValueA_nanmean ValueA_nanmin ValueB_nanmax
0 0 100 NaN NaN NaN
1 101 200 8.0 1.0 21.0
2 666 1000 NaN NaN NaN

关于python - 合并间隔和时间戳数据帧,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53494682/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com