基于重叠和间隔的熊猫数据合并行

Question

我对熊猫没有什么新鲜感，我有一个问题，那就是如何根据熊猫的列值，正确地合并熊猫数据中的行。

我有一个dataframe，df1，它包含以下内容：

df1 = pd.DataFrame(data=[["shoe", 4, 6, 0.110, 10],
                        ["bracelet", 22, 25, 0.115, 40],
                       ["belt", 2, 5, 0.120, 12],
                       ["socks", 1, 3, 0.422, 8],
                       ["scarf", 10, 12, 0.630, 6],
                       ["skirt", 4, 6, 0.9, 5],
                       ["bag", 9, 13, 1.131, 4],
                       ["watch", 1, 4, 1.8, 5],
                       ["sweater", 4, 5, 5.5, 14],
                       ["jeans", 1, 6, 5.6, 14]],
                    columns=['item','low','high','time', 'price'])
df1
    item         low    high    time    price
0   shoe         4      6       0.110   10
1   bracelet     22     25      0.115   40
2   belt         2      5       0.120   12
3   socks        1      3       0.422   8
4   scarf        10     12      0.630   6
5   skirt        4      6       0.9     5
6   bag          9      13      1.131   4
7   watch        1      4       1.8     5
8   sweater      4      5       5.500   14
9   jeans        1      6       5.600   14

我想根据['low', 'high', 'time', 'price']中的值合并这些行，并跟踪item。我想根据1列合并连续行小于time (例如秒)的行。基于此条件，我希望合并行0 -- 7。但是，我只想在['low', 'high']的范围重叠时才实际合并它们。在此基础上，将socks、belt、shoe、skirt和watch合并在一起，因为它们的范围(1,3)、(2,5)、(4,6)、(4,6)、(1,4)和bracelet不与其他行合并。

在即将合并的行中，我希望保留item、time和price作为具有最高price的行。我还想更新low和high，以便完全涵盖合并行的整个范围。最后，我想要两个新列time_min和time_max，它们将指示合并列的总时间范围。如果要合并的行具有相同的price，那么以time最低的行为例(不会有完全相同的time和price的行)。

基于这些规则，我希望得到以下数据，df2

df2 = pd.DataFrame(data=[["belt", 2, 6, 0.120, 12, 0.110, 0.422],
                         ["bracelet", 22, 25, 0.115, 10, 0.115, 0.115],
                         ["scarf", 9, 13, 0.130, 6, 0.630, 1.131],
                         ["sweater", 1, 6, 5.5, 14, 5.5, 5.6]],
                   columns=['item','low','high','time', 'price', 'time_min', 'time_max'])
df2
    item        low high    time    price   time_min    time_max
0   belt        2   6       0.120   12      0.110       1.8
1   bracelet    22  25      0.115   10      0.115       0.115
2   scarf       9   13      0.130   6       0.630       1.131
3   sweater     1   6       5.500   14      5.500       5.600

我查看了由：基于条件的熊猫数据合并提供的Q&A，但在这个问题中，它们只合并在['low', 'high']上(在本例中)，而我需要保留的一些信息丢失了。

Answer 1

下面是一个尝试：

def consolidate(sdf):
    low_high_sorted = sdf[['low', 'high']].sort_values(['low', 'high'])
    grouping = pd.Series(0, index=low_high_sorted.index)
    group, group_max = 0, low_high_sorted.high.iat[0]
    for i, low, high in low_high_sorted.iloc[1:, :].itertuples():
        if low <= group_max:
            group_max = max(group_max, high)
        else:
            group += 1
            group_max = high
        grouping.at[i] = group

    return sdf.groupby(grouping).agg(
        i_max=('price', lambda x: x.idxmax()),
        low=('low', 'min'), high=('high', 'max'), price=('price', 'max'),
        time_min=('time', 'min'), time_max=('time', 'max') 
    )

df2 = (
    df1.groupby(((df1.time - df1.time.shift()) >= 1.).cumsum())
       .apply(consolidate)
       .merge(df1.item, left_on='i_max', right_index=True, how='left')
       .drop(columns='i_max')
       .reset_index(drop=True)
)

结果：

   low  high  price  time_min  time_max      item
0    1     6     12     0.110     1.800      belt
1    9    13      6     0.630     1.131     scarf
2   22    25     40     0.115     0.115  bracelet
3    1     6     14     5.500     5.600   sweater

不幸的是，我无法通过Pandas方法在grouping中构建consolidate系列。

1.步骤：将df1分组以获得由time列确定的组。检查前一行中时间和时间之间的时间差是否大于或等于1.，然后对结果列进行累加(False/True = 0/ 1)：

    time  time_diff_ge_1  cumsum
0  0.110           False       0
1  0.115           False       0
2  0.120           False       0
3  0.422           False       0
4  0.630           False       0
5  0.900           False       0
6  1.131           False       0
7  1.800           False       0
8  5.500            True       1
9  5.600           False       1

(我应该使用df1.time.diff()而不是df1.time - df1.time.shift()。)

2.步骤：第一个分组的.apply-ing consolidate。在它的内部，第二组连接的空隙。若要查找连接:排序low-和high-column，以便允许随后进行线性处理：

   low  high
3    1     3
7    1     4
2    2     5
0    4     6
5    4     6
6    9    13
4   10    12
1   22    25

(实际上，只对low进行排序就足够了：sdf[['low', 'high']].sort_values('low')。)要查找连接组，现在只需检查low是否小于或等于当前组high-max。如果不是这样的话，一个新的连接组就会启动。

   low  high  grouping
3    1     3         0
7    1     4         0
2    2     5         0
0    4     6         0
5    4     6         0
6    9    13         1
4   10    12         1
1   22    25         2

使用pd.Series作为分组有很好的效果:索引将对齐，这实际上使其成为映射索引->分组的值，因此索引的顺序并不重要。请参阅这里

..。如果传递了dict或Series，将使用Series或dict值来确定组(首先对齐序列的值；请参阅.align()方法)。

3.步骤：通过.agg将结果组聚合成一行。agg-logic：

new_column_name=(used_column, aggregating_function)

获得对应于最高item的price：通过.idxmax只获取相应的索引i_max。(methodcaller将是lambda-function：methodcaller('idxmax')的替代品。)

4.步骤：.merge-ing沿着i_max-index和df1.item获得与i_maxes对应的items，然后删除i_max列。