CODEWITHSUNDEEP
pythonpandasdataframepivot-table
Siraj S.
Siraj S.

Reputation: 3751

reshape a pandas dataframe index to columns

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

Upvotes: 2

Views: 3438

Answers (2)

root
root

Reputation: 33793

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

Upvotes: 6

juanpa.arrivillaga
juanpa.arrivillaga

Reputation: 95948

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
>>>

Upvotes: -1

Related Questions