Can't reshape (pivot) pandas DF

Question

So I have this DF:

In [130]: dfAbr
Out[130]: 
           ip ospfArea router_name
0     1.1.1.1  0.0.0.2   Router1-1
1     1.1.1.2  0.0.0.2   Router1-2
140   5.5.5.1  0.0.0.5   Router5-1
141   5.5.5.2  0.0.0.5   Router5-2

I'd like to reshape that into a 2-row dataframe, such as:

I've been playing with both stack/unstack and pivot functions but couldn't go that far.

For example, dfAbr1 = pd.DataFrame(dfAbr.set_index('ospfArea').stack()).reset_index(), renaming column names, produces the following:

In [151]: dfAbr1
Out[151]: 
  ospfArea       level1     level2
0  0.0.0.2           ip    1.1.1.1
1  0.0.0.2  router_name  Router1-1
2  0.0.0.2           ip    1.1.1.2
3  0.0.0.2  router_name  Router1-2
4  0.0.0.5           ip    5.5.5.1
5  0.0.0.5  router_name  Router5-1
6  0.0.0.5           ip    5.5.5.2
7  0.0.0.5  router_name  Router5-2

From there, I wanted to pivot it, like this: dfAbr1.pivot(index='ospfArea', columns='level1',values='level2'), but couln't do it as I get Index contains duplicate entries, cannot reshape. I believe this is because under level1 I have duplicate values...

Any other way of doing this?

Thanks!

score 8 · Answer 1 · answered Dec 20 '19 at 14:29

Create MultiIndex by DataFrame.set_index with counter column by GroupBy.cumcount, reshape by DataFrame.unstack and flatten Multiindex by map with format:

df = df.set_index(['ospfArea', df.groupby('ospfArea').cumcount().add(1)]).unstack()
df.columns = df.columns.map('{0[0]}{0[1]}'.format)
print (df)
              ip1      ip2 router_name1 router_name2
ospfArea                                            
0.0.0.2   1.1.1.1  1.1.1.2    Router1-1    Router1-2
0.0.0.5   5.5.5.1  5.5.5.2    Router5-1    Router5-2

score 2 · Accepted Answer · answered Dec 20 '19 at 14:34

2

Solution with pivot, also refer to this question

new_df = (df.assign(col=df.groupby('ospfArea').cumcount())
   .pivot(index='ospfArea', columns='col')
)

new_df.columns = [f'{x[0]}{x[1]+1}' for x in new_df.columns]

Output:

              ip1      ip2 router_name1 router_name2
ospfArea                                            
0.0.0.2   1.1.1.1  1.1.1.2    Router1-1    Router1-2
0.0.0.5   5.5.5.1  5.5.5.2    Router5-1    Router5-2

answered Dec 20 '19 at 14:34

Quang Hoang

146,074
10
56
74

I liked your idea. One comment though: I did `df['col'] = df.groupby('ospfArea').cumcount()` ... why did you use `assign` instead? – Lucas Aimaretto Dec 20 '19 at 17:07
1

It's just easier to chain (and does not alter your original data). – Quang Hoang Dec 20 '19 at 17:09

Can't reshape (pivot) pandas DF

2 Answers2