R语言技术实现：为Dataset B添加与Dataset A区间匹配的Phrase列

Hey there! I totally get why standard merge or match functions weren't working here—those are built for exact value matches, but you need interval-based matching (checking if an onset falls between a start and end time). Let's fix this with pandas, which has perfect tools for this task.

Step 1: Set Up Your Data #

First, let's recreate your datasets in pandas so we can work with them directly:

import pandas as pd

# Dataset A: Phrase with start/end intervals
df_a = pd.DataFrame({
    'phrase': [1, 2, 3, 4],
    'start': [105.476, 107.408, 110.889, 112.557],
    'end': [107.315, 109.443, 112.482, 113.625]
})

# Dataset B: Onset values
df_b = pd.DataFrame({
    'onsets': [105.568, 105.981, 106.117, 106.347, 106.646, 107.149, 107.666,
               107.827, 107.976, 108.128, 108.31, 108.472, 111.015, 111.199,
               111.36, 111.538, 111.72, 111.901, 112.259, 112.44, 112.606,
               112.748, 112.901, 113.046]
})

Step 2: Use pd.cut() for Simple Interval Matching #

The pd.cut() function is made exactly for this scenario—it takes a series of values, bins them into predefined intervals, and maps them to labels. Here's how to implement it:

# Create interval bins from Dataset A's end values, plus edge bins to catch all onsets
bins = [-float('inf')] + df_a['end'].tolist() + [float('inf')]
# Map each bin to the corresponding phrase
labels = df_a['phrase'].tolist()

# Assign the matching Phrase to each onset in Dataset B
df_b['Phrase'] = pd.cut(df_b['onsets'], bins=bins, labels=labels, include_lowest=True)

# Reorder columns to match your desired Dataset C structure
df_c = df_b[['Phrase', 'onsets']]

What this does:

• -inf and inf ensure we don't miss any onsets that fall outside your defined intervals (though all your onsets fit perfectly here).
• include_lowest=True makes sure values equal to the first bin's lower edge are correctly assigned to the first phrase.

Step 3: Verify the Result #

If you print df_c, you'll get exactly the output you wanted:

Phrase   onsets
0        1  105.568
1        1  105.981
2        1  106.117
3        1  106.347
4        1  106.646
5        1  107.149
6        2  107.666  # Correctly maps to phrase 2 (107.666 > 107.315 and <109.443)
7        2  107.827
...
18       3  112.259
19       3  112.44   # Correctly stays in phrase 3 (112.44 <112.482)
20       4  112.606  # Moves to phrase 4 (112.606 >112.482 and <113.625)
...

Alternative: Use merge_asof for More Flexibility #

If your intervals are non-overlapping and sorted (which they are), pd.merge_asof() is another solid option. It matches each onset to the nearest start time that's less than the onset, then we filter out any matches where the onset exceeds the end time:

# Sort datasets (required for merge_asof)
df_a_sorted = df_a.sort_values('start')
df_b_sorted = df_b.sort_values('onsets')

# Merge onsets with the latest start time that's <= onset
df_merged = pd.merge_asof(df_b_sorted, df_a_sorted, left_on='onsets', right_on='start')

# Filter out rows where onset is beyond the matched interval's end
df_c = df_merged[df_merged['onsets'] <= df_merged['end']][['phrase', 'onsets']].rename(columns={'phrase': 'Phrase'})

# Restore original order if needed
df_c = df_c.sort_index()

Why Your Previous Attempts Didn't Work #

• merge only matches exact values, not ranges—so it couldn't link an onset to a start/end interval.
• match functions (like numpy.match) are designed for exact or nearest-exact matches, not interval checks.

内容的提问来源于stack exchange，提问作者Juliano Abramovay