How to subset a dataset in pandas dataframe?

Question

I have a dataframe which has more than 10 million raws composed of about 30 columns.

The first column is ID

I would like to extract only the first four rows of each ID(they are the newest inputs as it is already sorted)

I am currently using the below code, but unfortunately it is so slow as it takes about two hours to process about 5% of the data and it may take a day or so to process the whole data.

df1 = pd.DataFrame() # an empty dataframe
for i in df.ID:   # df is the dataframe which contains the data
    df2 = df[df["ID"]== i] 
    df2 = df2[0:4] # take the first four rows
    df_f = df1.append(df2)

Are there an effecient way to do the same thing in a shorter time.

Is it guaranteed that there are at least four instances of each ID? — kiliantics
– kiliantics, Commented Dec 6, 2016 at 3:29
Yes there are more than 10 instances for most IDs and I want to get instances from the last four months only and instances are already sorted in a descending order for each ID. — Natheer Alabsi
– Natheer Alabsi, Commented Dec 6, 2016 at 3:31

akuiper · Accepted Answer · 2016-12-06 03:44:47Z

2

You need the head() method:

df.groupby("ID").head(4)

Here is a revised version of your original code with run time testing against groupby().head() method:

def loop():
    df1 = pd.DataFrame() # an empty dataframe
    for i in df.ID.drop_duplicates():   # df is the dataframe which contains the data
        df2 = df[df["ID"]== i] 
        df2 = df2[0:4] # take the first four rows
        df1 = pd.concat([df1, df2])
    return df1

%timeit loop()
# 100 loops, best of 3: 1.99 ms per loop

%timeit df.groupby("ID").head(4)
# 1000 loops, best of 3: 485 µs per loop

edited Dec 6, 2016 at 3:44

answered Dec 6, 2016 at 3:35

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Natheer Alabsi Over a year ago

I used your code: df.groupby("ID").head(4) It solves my problem without using a loop. Thanks so much.

Collectives™ on Stack Overflow

How to subset a dataset in pandas dataframe?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related