I have a dataframe with a column containing ids and other column containing numbers:
df1 = {'ID':[400, 400, 400, 400, 400, 400, 500, 500, 500, 500],
'Number':[1, 2, 3, 4, 8, 9, 22, 23, 26, 27]}
You may note that each Id has their correponding series of consecutive numbers in the column "Number". For example:
Id 400 contains a series of length 4 {1, 2, 3, 4} and another of length 2 {8, 9}
I´d like to obtain for each Id, the average length of their corresponding series. In this example:
df2 = {'ID':[400, 500], 'avg_length':[3, 2]}
Any ideas will be much appreciated!
Here is one way, uses groupby twice,
Option 2: Without using apply twice, still uses tmp column created earlier