如何提取DataFrame指定列组合重复行及排查IndexError报错

Problem Background #

I have the following Pandas DataFrame:

import pandas as pd
data = {'Year':[2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018], 'Month':[1,1,1,2,2,3,3,3], 'ID':['A', 'A', 'B', 'A', 'B', 'A', 'B', 'B'], 'Fruit':['Apple', 'Banana', 'Apple', 'Pear', 'Mango', 'Banana', 'Apple', 'Mango']}
df = pd.DataFrame(data, columns=['Year', 'Month', 'ID', 'Fruit'])
df = df.astype(str)

My goal is to extract rows where the combination of Year, Month, and ID is duplicated. First, I used groupby to count occurrences of each combination:

df2 = df.groupby(['Year', 'Month'])['ID'].value_counts().to_frame(name = 'Count').reset_index()
df2 = df2[df2.Count>1]

But when I tried to loop through df2 and match rows in the original DataFrame to build a new DataFrame df_new, I got this error:

---------------------------------------------------------------------------
IndexError Traceback (most recent call last)
<ipython-input-38-7f2d95d71270> in <module>()
 6 temp.reset_index(drop=True, inplace=True)
 7 for j in range(len(temp)):
----> 8 df_new.iloc[count] = temp.iloc[j]
 9 count+=1
10 df_new
...
IndexError: single positional indexer is out-of-bounds

Manually assigning each column works but is too tedious. What's causing this error?

What's Causing the Error? #

The IndexError happens because you're trying to assign a row to df_new.iloc[count] where count exceeds the number of existing rows in df_new.

Chances are you initialized df_new as an empty DataFrame (like df_new = pd.DataFrame(columns=df.columns)), which has 0 rows. When you try to use iloc[0] in the first loop iteration, there's no row at position 0 to assign to—Pandas doesn't automatically expand the DataFrame when using iloc for direct assignment.

Better Solutions (No Loops Needed!) #

Looping through rows in Pandas is inefficient anyway. Here are two cleaner, faster ways to get your desired rows without loops:

1. Use merge to filter matching combinations

First, isolate the duplicate Year/Month/ID combinations from df2, then merge it back with the original DataFrame to keep only those rows:

# Get the duplicate combinations
duplicate_combinations = df2[['Year', 'Month', 'ID']]

# Merge with original df to get matching rows
df_new = df.merge(duplicate_combinations, on=['Year', 'Month', 'ID'], how='inner')

2. Use duplicated() with subset (even simpler!)

You can skip the groupby step entirely by using Pandas' built-in duplicated() method. The keep=False parameter ensures we retain every row that's part of a duplicated combination (not just duplicates after the first occurrence):

# Keep all rows where the Year/Month/ID combination appears more than once
df_new = df[df.duplicated(subset=['Year', 'Month', 'ID'], keep=False)]

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