reshape a pandas dataframe index to columns

Question

Consider the below pandas Series object,

index = list('abcdabcdabcd')
df = pd.Series(np.arange(len(index)), index = index)

My desired output is,

   a  b   c   d
0  0  1   2   3
1  4  5   6   7
2  8  9  10  11

I have put some effort with pd.pivot_table, pd.unstack and probably the solution lies with correct use of one of them. The closest i have reached is

df.reset_index(level = 1).unstack(level = 1)

but this does not gives me the output i my looking for

// here is something even closer to the desired output, but i am not able to handle the index grouping.

df.to_frame().set_index(df1.values, append = True, drop  = False).unstack(level = 0)

     a    b     c     d
0   0.0  NaN   NaN   NaN
1   NaN  1.0   NaN   NaN
2   NaN  NaN   2.0   NaN
3   NaN  NaN   NaN   3.0
4   4.0  NaN   NaN   NaN
5   NaN  5.0   NaN   NaN
6   NaN  NaN   6.0   NaN
7   NaN  NaN   NaN   7.0
8   8.0  NaN   NaN   NaN
9   NaN  9.0   NaN   NaN
10  NaN  NaN  10.0   NaN
11  NaN  NaN   NaN  11.0

root · Accepted Answer · 2017-04-21 19:55:56Z

6

A bit more general solution using cumcount to get new index values, and pivot to do the reshaping:

# Reset the existing index, and construct the new index values.
df = df.reset_index()
df.index = df.groupby('index').cumcount()

# Pivot and remove the column axis name.
df = df.pivot(columns='index', values=0).rename_axis(None, axis=1)

The resulting output:

   a  b   c   d
0  0  1   2   3
1  4  5   6   7
2  8  9  10  11

answered Apr 21, 2017 at 19:55

root

34.1k6 gold badges77 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siraj S. Over a year ago

indeed very thoughtful

juanpa.arrivillaga · Accepted Answer · 2017-04-21 18:36:33Z

-1

Here is a way that will work if the index is always cycling in the same order, and you know the "period" (in this case 4):

>>> pd.DataFrame(df.values.reshape(-1,4), columns=list('abcd'))
   a  b   c   d
0  0  1   2   3
1  4  5   6   7
2  8  9  10  11
>>>

answered Apr 21, 2017 at 18:36

juanpa.arrivillaga

97.6k14 gold badges141 silver badges190 bronze badges

3 Comments

Siraj S. Over a year ago

the index cycling will be in the same order. But, the index length may not necessarily be divisible by 4, for example ['a','b','a','b','a'] cannot be reshaped into a (2,2) array in numpy. However, I guess in pandas, it will be fill NaN values. Hope I am making sense:)

juanpa.arrivillaga Over a year ago

@SirajS. in other words, it might not terminate at the end of a cycle?

Siraj S. Over a year ago

yes. the length of elements maynot be perfectly divisible with the length of the columns. in which case a numpy.reshape() will not work

Collectives™ on Stack Overflow

reshape a pandas dataframe index to columns

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related