I want to find out the next occurence of an id and get its number of days.
Consider that I have the following data:
rainfall_record_id ... day
0 0f3fb005-9ea7-4123-851d-4d6f69f126c3 ... 2022-03-23
1 0f3fb005-9ea7-4123-851d-4d6f69f126c3 ... 2022-03-24
2 0f3fb005-9ea7-4123-851d-4d6f69f126c3 ... 2022-03-24
3 0f3fb005-9ea7-4123-851d-4d6f69f126c3 ... 2022-03-25
4 0f3fb005-9ea7-4123-851d-4d6f69f126c3 ... 2022-03-25
... ... ... ...
4055 ac0f3656-e3f4-4a3b-ac5b-c02f93009014 ... 2022-04-19
4056 ac0f3656-e3f4-4a3b-ac5b-c02f93009014 ... 2022-04-19
4057 ac0f3656-e3f4-4a3b-ac5b-c02f93009014 ... 2022-04-19
4058 ac0f3656-e3f4-4a3b-ac5b-c02f93009014 ... 2022-04-19
4059 ac0f3656-e3f4-4a3b-ac5b-c02f93009014 ... 2022-04-19
I want to create a data where I would be able to count how many times the rainfall_record_id occured, how many occurences, its percentage total, and median to the next occurence.
So for example in my data:
To put some context here, there were 500 rainfall_id's that only occured one, which takes up 80% of the whole dataset. And the time to the next occurence is 0, basically because it didnt occur more than once.
Next row, there were 200 rainfall ids that occured twice, which takes up 10%, and the median of the next occurrences,is 4 days which means that all the rainfall ids that occured twice had a median of 4 days before the next fainfall id occured.
My goal is to present it like a chart:
However im not sure how to do that, I do know how to get up to the % of total:
data = rawdata_df.groupby(["day", "rainfall_record_id"]).agg(len)
rainfalldata_df = data.reset_index()
rainfalldata_df = rainfalldata_df.rename(columns={0: 'rainfall_id_count'})
rainfalldata_df = rainfalldata_df.groupby('rainfall_record_id')['rainfall_record_id'].count().reset_index(name='rainfall_id_reoccurence')
rainfalldata_df['% of total'] = rainfalldata_df['rainfall_id_reoccurence'].div(rainfalldata_df['rainfall_id_reoccurence'].sum()).mul(100).astype(int).astype(
I want to present the data of my study this way, I got all the data from a forecasting model, im just having a hard time displaying it. Really need your help.
Additional info:
Median calculation would probably go like this:
rainfall_id_count rainfall_id_reoccurence % of total median
2 4 <not calculated yet> 9.5
This would mean that for four rainfall ids that occured twice, the median is 9.5 days