Reverse row and colums in a pandas Dataframe

user_3901852 0
Viewed 132

I'm tryng to reverse this but I can't figure out how. I'm starting from

>>> d = {'col1': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C'], 'col2': [1, 2, 3, 4, 5, 6, 7, 7]}
>>> df = pd.DataFrame(data=d)
>>> df
  col1  col2
0    A     1
1    A     2
2    A     3
3    B     4
4    B     5
5    B     6
6    C     7
7    C     7

And I want to obtain:

col1  new_1  new_2  new_3
0    A      1      2      3
1    B      4      5      6
2    C      7      7  empty

where there are new_x columns based on max number of times a col1 item is repeated. It seems to be a pretty standard transpose, but I can't find a solution.

Sorry if duplicated.

Thx Sirius

pandas python
3 Answers

use .cumcount() and .unstack() after setting your indices.

cumcount() here groups by your target column and applies a sequential count along the index, this allows us to unstack() it and create your new pivoted structure.

the rest of the code is to obtain your target dataframe, you could also do this with pivot and crosstab.

df1 = df.set_index([df.groupby('col1').cumcount() + 1,
                    df['col1']]).drop('col1',1)\
                    .unstack(0)\
                    .droplevel(0,1)\
                    .add_prefix('new_')\
                    .fillna('empty')\
                    .reset_index()

Or with pivot:

(df.assign(k=df.groupby("col1").cumcount()+1).pivot('col1','k','col2')
   .add_prefix("col_").reset_index())

  col1  new_1  new_2  new_3
0    A    1.0    2.0    3.0
1    B    4.0    5.0    6.0
2    C    7.0    7.0  empty
user_93751020

It's not a one-liner but maybe a bit simpler / easier to follow.

First, aggregate to one lists column:

df_ = pd.DataFrame(df.groupby('col1').col2.agg(list))

which gives

           col2
col1           
A     [1, 2, 3]
B     [4, 5, 6]
C        [7, 7]

Then, build a new DataFrame from these lists:

df2 = (pd.DataFrame(df_.col2.tolist(), index=df_.index).add_prefix('new_')
                                                       .reset_index())

which gives

  col1  new_0  new_1  new_2
0    A      1      2    3.0
1    B      4      5    6.0
2    C      7      7    NaN

Please note that:

  • I interpreted empty as an empty cell, not the 'empty' string
  • NaN is always seen as a float, that's why values in this column were cast by pandas to floats
user_47864400 
 d = {'col1': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C'], 'col2': [1, 2, 3, 4, 5, 6, 7, 7]}
 df = pd.DataFrame(data=d)
 print(df)
 print(df.pivot_table(index='col1',columns=df.index, values='col2').fillna(0))

output:

        0    1    2    3    4    5    6    7
  col1                                        
  A     1.0  2.0  3.0  0.0  0.0  0.0  0.0  0.0
  B     0.0  0.0  0.0  4.0  5.0  6.0  0.0  0.0
  C     0.0  0.0  0.0  0.0  0.0  0.0  7.0  7.0
user_40011770
Related