How do you groupby a number of rows offset from the first occurence of a month in a pandas DataFrame?

John Glen 0
Viewed 34

I am trying to offset the groupby boundaries by some number of rows.

For example, given the following dates

-----
1/1
1/2
1/3
...
2/1
2/2
2/3
-----

I would like to get a group with [1/2, 1/3, ... ,2/1, 2/2] which would be an offset of 1.

To add to this, not all the dates are there. For example, given the following dates

-----
1/2
1/4
1/15
...
1/31
2/5
2/10
2/25
-----

I would like to get the group [1/4, 1/15, ... ,1/31, 2/5, 2/10] which would be an offset of 1.

To add to this, I require negative offsets to work too, so given these dates

-----
1/2
1/4
1/15
1/31
2/5
2/10
2/25
2/26
2/28
-----

I would like to get the group [1/4, 1/15, 1/31, 2/5, 2/10, 2/25] which would be an offset of -3.

Is there a way to do this with groupby?

The following puts the boundaries of the groups at the first of the month, but I can not figure out how to move the boundaries by rows.

df.groupby([df["<DATE_GROUP>"].dt.year, df["<DATE_GROUP>"].dt.month], as_index=False)

The .nth() method does not work because it only returns one member from each group, and I require that all the groups be in tact for processing.

TL;DR: I am trying to offset the groupby boundaries by some number of rows.

group-by dataframe pandas python
1 Answers

I came up with a way to do this, but it involves two groupby operations.

The idea is to groupby month and then use the nth() method to create a mask for a second groupby. The following code will return the group in the last example in my post.

df_monthly = df.groupby([df["<DATE_GROUP>"].dt.year, df["<DATE_GROUP>"].dt.month], as_index=False)

mask = df["<DATE_GROUP>"].isin(df_monthly.nth(-3)["<DATE_GROUP>"]).cumsum()

df.groupby(mask, as_index=False)
John Glen0
Related