CODEWITHSUNDEEP
pythonpandasdataframesplitbins
junglekim
junglekim

Reputation: 29

Pandas splitting rows by certain cumsum

Is there a way to split certain dataframe row so that I can make a group of rows with certain cumsum? In this example I want to split rows that makes cumsum 20

my data

timestamp             counts    cumsum
'2015-01-01 03:45:14' 4         4  
'2015-01-01 03:45:14' 2         6
'2015-01-01 03:45:14' 1         7
'2015-01-01 03:45:15' 12        19
'2015-01-01 03:45:15' 8         27   <--split
'2015-01-01 03:45:15' 8         35
'2015-01-01 03:45:15' 2         37
'2015-01-01 03:45:16' 26        63   <--split(twice)
'2015-01-01 03:45:17' 3         66
'2015-01-01 03:45:17' 8         71
'2015-01-01 03:45:19' 11        82   <--split
'2015-01-01 03:45:20' 8         90
'2015-01-01 03:45:21' 1         91

I want my dataframe to be like this

my data

timestamp             counts    cumsum
'2015-01-01 03:45:14' 4         4  
'2015-01-01 03:45:14' 2         6
'2015-01-01 03:45:14' 1         7
'2015-01-01 03:45:15' 12        19
'2015-01-01 03:45:15' 1         20   <--split  20
'2015-01-01 03:45:15' 7         27   <--split
'2015-01-01 03:45:15' 8         35
'2015-01-01 03:45:15' 2         37
'2015-01-01 03:45:16' 3         40   <--split  40
'2015-01-01 03:45:16' 20        60   <--split  60
'2015-01-01 03:45:16' 3         63   <--split
'2015-01-01 03:45:17' 3         66
'2015-01-01 03:45:17' 8         71
'2015-01-01 03:45:19' 9         80   <--split  80
'2015-01-01 03:45:19' 2         82   <--split
'2015-01-01 03:45:20' 8         90
'2015-01-01 03:45:21' 1         91

Upvotes: 3

Views: 799

Answers (1)

Ben.T
Ben.T

Reputation: 29635

you can do it by creating a dataframe with the values (20-40-60-80...) you want to add and pd.concat with the original df. Then drop_duplicates on the column cumsum in case you already have the values 20-40-60... in the original dataframe (thanks to @jezrael comment), sort_values this column and reset_index. I understand you want to bfill the timestamps column and use diff on the column cumsum to recalculate the column counts.

val_split = 20
df_ = (pd.concat([df, 
                 pd.DataFrame({'cumsum':range(val_split, df['cumsum'].max(), val_split)})])
         .drop_duplicates('cumsum')
         .sort_values('cumsum')
         .reset_index(drop=True)
      )
df_['timestamp'] = df_['timestamp'].bfill()
df_['counts'] = df_['cumsum'].diff().fillna(df_['counts'])
print (df_)
                timestamp  counts  cumsum
0   '2015-01-01 03:45:14'     4.0       4
1   '2015-01-01 03:45:14'     2.0       6
2   '2015-01-01 03:45:14'     1.0       7
3   '2015-01-01 03:45:15'    12.0      19
4   '2015-01-01 03:45:15'     1.0      20
5   '2015-01-01 03:45:15'     7.0      27
6   '2015-01-01 03:45:15'     8.0      35
7   '2015-01-01 03:45:15'     2.0      37
8   '2015-01-01 03:45:16'     3.0      40
9   '2015-01-01 03:45:16'    20.0      60
10  '2015-01-01 03:45:16'     3.0      63
11  '2015-01-01 03:45:17'     3.0      66
12  '2015-01-01 03:45:17'     5.0      71
13  '2015-01-01 03:45:19'     9.0      80
14  '2015-01-01 03:45:19'     2.0      82
15  '2015-01-01 03:45:20'     8.0      90
16  '2015-01-01 03:45:21'     1.0      91

Upvotes: 6

Related Questions