我有以下内容:
import pandas as pd
ls = [(1,2,10,20,5),
(3,4,30,40,10),
(5,6,50,60,20)]
df_ = pd.DataFrame({'col1': [1.1, 3.5, 5.4, 4.1],
'col2': [11, 35, 44, 41]})
我想在df_中创建一个新列，该列将根据以下规则创建:
判断ls的每个数组:
生成的框架应该是这样的:
df_ = pd.DataFrame({'col1': [1.1, 3.5, 5.4, 4.1],
'col2': [11, 35, 54, 41],
'result': [5, 10, None, None]})
我怎么能做到呢？
一个有效的 Select 是使用janitor的conditional_join:
# pip install pyjanitor
import janitor
out = df_.conditional_join(
pd.DataFrame(ls)
.rename(columns={4: 'result'})
.astype({0: df_['col1'].dtype, 1: df_['col1'].dtype,
2: df_['col2'].dtype, 3: df_['col2'].dtype,}),
('col1', 0, '>='), ('col1', 1, '<='),
('col2', 2, '>='), ('col2', 3, '<='),
right_columns=['result'], how='left'
)
或者使用MultiIndex为IntervalIndex:
import numpy as np
a = np.array(ls).T
idx = (pd.MultiIndex
.from_arrays([pd.IntervalIndex.from_arrays(a[0], a[1]),
pd.IntervalIndex.from_arrays(a[2], a[3])])
)
df_['result'] = (pd.Series(a[4], index=idx)
.reindex(zip(df_['col1'], df_['col2']))
.values
)
输出:
col1 col2 result
0 1.1 11 5.0
1 3.5 35 10.0
2 5.4 44 NaN
3 4.1 41 NaN