32

I was wondering if there is a way to groupby consecutive index numbers and move the groups in different columns. Here is an example of the DataFrame I'm using:

                 0
0     19218.965703
1     19247.621650
2     19232.651322
9     19279.216956
10    19330.087371
11    19304.316973

And my idea is to gruoup by sequential index numbers and get something like this:

                 0             1
0     19218.965703  19279.216956    
1     19247.621650  19330.087371
2     19232.651322  19304.316973

Ive been trying to split my data by blocks of 3 and then groupby but I was looking more about something that can be used to group and rearrange sequential index numbers. Thank you!

Improve this question
2
  • #maybe df['v_col'].values.reshape(-1,3).T Commented Sep 1, 2019 at 12:55
  • This is a good way to transpose, however I would like to avoid setting the boundaries (-1, 3) in case I have larger more consecutive idx numbers to group. and the @anky_91 reply is the answer to my question. Thank you! Commented Sep 1, 2019 at 19:44

6 Answers 6

Reset to default
21

Here is one way:

from more_itertools import consecutive_groups
final=pd.concat([df.loc[i].reset_index(drop=True) 
                    for i in consecutive_groups(df.index)],axis=1)
final.columns=range(len(final.columns))
print(final)

              0             1
0  19218.965703  19279.216956
1  19247.621650  19330.087371
2  19232.651322  19304.316973
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Image
I like the more_itertools solution! Thank you. With 3 answers you guys covered all the possible and elegant solutions!!
10

This is a groupby + pivot_table

m = df.index.to_series().diff().ne(1).cumsum()

(df.assign(key=df.groupby(m).cumcount())
    .pivot_table(index='key', columns=m, values=0))


                1             2
key
0    19218.965703  19279.216956
1    19247.621650  19330.087371
2    19232.651322  19304.316973
Improve this answer

Comments

10

Create a new pandas.Series with a new pandas.MultiIndex

a = pd.factorize(df.index - np.arange(len(df)))[0]
b = df.groupby(a).cumcount()

pd.Series(df['0'].to_numpy(), [b, a]).unstack()

              0             1
0  19218.965703  19279.216956
1  19247.621650  19330.087371
2  19232.651322  19304.316973

Similar but with more Numpy

a = pd.factorize(df.index - np.arange(len(df)))[0]
b = df.groupby(a).cumcount()

c = np.empty((b.max() + 1, a.max() + 1), float)
c.fill(np.nan)
c[b, a] = np.ravel(df)
pd.DataFrame(c)

              0             1
0  19218.965703  19279.216956
1  19247.621650  19330.087371
2  19232.651322  19304.316973
Improve this answer

1 Comment

Image
This solution is very clever! The offset relative to np.arange should increase after every "gap" in the sequence, so the offset value should uniquely identify runs of consecutive values. Then pd.factorize will create unique indicators for those unique offsets.
7

One way from pandas groupby 

s=df.index.to_series().diff().ne(1).cumsum()
pd.concat({x: y.reset_index(drop=True) for x, y in df['0'].groupby(s)}, axis=1)

Out[786]: 
              1             2
0  19218.965703  19279.216956
1  19247.621650  19330.087371
2  19232.651322  19304.316973
Improve this answer

Comments

2

I think that you have assumed that the number of observations within each consecutive group will be the same. My approach is:

Prepare the data:

import pandas as pd
import numpy as np

df = pd.DataFrame(data ={'data':[19218.965703 ,19247.621650 ,19232.651322 ,19279.216956 ,19330.087371 ,19304.316973]}, index = [0,1,2,9,10,11] )

And the solution:

df['Group'] = (df.index.to_series()-np.arange(df.shape[0])).rank(method='dense')
df.reset_index(inplace=True)
df['Observations'] = df.groupby(['Group'])['index'].rank()
df.pivot(index='Observations',columns='Group', values='data')

Which returns:

Group                  1.0           2.0
Observations                            
1.0           19218.965703  19279.216956
2.0           19247.621650  19330.087371
3.0           19232.651322  19304.316973
Improve this answer

Comments

1

My way:

df['groups']=list(df.reset_index()['index']-range(0,len(df)))
pd.concat([df[df['groups']==i][['0']].reset_index(drop=True) for i in df['groups'].unique()],axis=1)

              0             0
0  19218.965703  19279.216956
1  19247.621650  19330.087371
2  19232.651322  19304.316973
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.