How to split a dataframe based on consecutive index?
groupby
You can make a perfectly "consecutive" array with
np.arange(10)
array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9])
If I were to subtract this from an index that is monotonically increasing, only those index members that were "consecutive" would show up as equal. This is a clever way to establish a key to group by.
list_of_df = [d for _, d in df.groupby(df.index - np.arange(len(df)))]
And print each one to prove it
print(*list_of_df, sep='\n\n')
Column1 Column2
Index
4464 10.5 12.7
4465 11.3 12.8
4466 10.3 22.8
Column1 Column2
Index
5123 11.3 21.8
5124 10.6 22.4
Column1 Column2
Index
5323 18.6 23.5
np.split
You can use np.flatnonzero
to identify where the differences are not equal to 1
and avoid using cumsum
and groupby
list_of_df = np.split(df, np.flatnonzero(np.diff(df.index) != 1) + 1)
Proof
print(*list_of_df, sep='\n\n')
Column1 Column2
Index
4464 10.5 12.7
4465 11.3 12.8
4466 10.3 22.8
Column1 Column2
Index
5123 11.3 21.8
5124 10.6 22.4
Column1 Column2
Index
5323 18.6 23.5
Here is an alternative:
grouper = (~(pd.Series(df.index).diff() == 1)).cumsum().values
dfs = [dfx for _ , dfx in df.groupby(grouper)]
We use the fact that a continuous difference of 1 equals a sequence (diff == 1).
Full example:
import pandas as pd
data = '''\
Index Column1 Column2
4464 10.5 12.7
4465 11.3 12.8
4466 10.3 22.8
5123 11.3 21.8
5124 10.6 22.4
5323 18.6 23.5
'''
fileobj = pd.compat.StringIO(data)
df = pd.read_csv(fileobj, sep='\s+', index_col='Index')
non_sequence = pd.Series(df.index).diff() != 1
grouper = non_sequence.cumsum().values
dfs = [dfx for _ , dfx in df.groupby(grouper)]
print(dfs[0])
# Column1 Column2
#Index
#4464 10.5 12.7
#4465 11.3 12.8
#4466 10.3 22.8
Another way of seeing it is that we look for non-sequence to groupby, might be more readable:
non_sequence = pd.Series(df.index).diff() != 1
grouper = non_sequence.cumsum().values
dfs = [dfx for _ , dfx in df.groupby(grouper)]