I have a dataframe with 8 columns and ~0.8 million rows. I want to find the mode of every 50 rows of a specific column (e.g. Column 5) in a separate dataframe. My approach looks like this.
for i in range(1, len(data['Column5'])-1) :
splitdata = (data['Column5'][i:(i+49)])
mode_pressure[j] = splitdata.mode()
i = i+50
j = j+1
But I get "'int' object does not support item assignment" error. My df looks like the below
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8
0 612458 6715209 671598606 101043 -56 224 16560
1 612458 6715210 671598706 101038 -264 256 16696
2 612458 6715211 671598806 101038 -144 192 16528
3 612458 6715212 671598906 101043 -136 200 16576
4 612458 6715213 671599006 101037 -232 104 16576
5 612458 6715214 671599106 101038 -88 264 16904
6 612458 6715215 671599206 101040 -200 176 16808
7 612458 6715212 671598906 101043 -136 200 16576
8 612458 6715213 671599006 101037 -232 104 16576
9 612458 6715214 671599106 101040 -88 264 16904
10 612458 6715215 671599206 101040 -200 176 16808
Output: (assume mode of 5 values)
df_mode : 101038, 101048
I have written the same function in R. And R returns the latest (last) mode value as a single output for every set of 50.
i=1
j=1
while(i<=length(data$Column5)-1) {
splitdata<-data$Column5[i:(i+49)]
mode_value[j] = modeest::mfv(splitdata)
i=i+50
j=j+1
}
I think need groupby by numpy arange for more general solution, e.g. working nice with DatetimeIndex with floor division:
df = df.groupby(np.arange(len(df)) // 50)['Col5'].apply(lambda x: x.mode())
There is possible multiple values, so possible solutions are Multiindex:
df = df.groupby(np.arange(len(df)) // 5)['Col5'].apply(lambda x: x.mode())
print (df)
0 0 101038
1 101043
1 0 101040
2 0 101040
Name: Col5, dtype: int64
Or lists:
df = df.groupby(np.arange(len(df)) // 5)['Col5'].apply(lambda x: x.mode().tolist())
print (df)
0 [101038, 101043]
1 [101040]
2 [101040]
Name: Col5, dtype: object