Convert Pandas DataFrame columns to rows

Question

I have the following dict which I converted to dataframe

players_info = {'Afghanistan': {'Asghar Stanikzai': 809.0,
  'Mohammad Nabi': 851.0,
  'Mohammad Shahzad': 1713.0,
  'Najibullah Zadran': 643.0,
  'Samiullah Shenwari': 774.0},
 'Australia': {'AJ Finch': 1082.0,
  'CL White': 988.0,
  'DA Warner': 1691.0,
  'GJ Maxwell': 822.0,
  'SR Watson': 1465.0},
 'England': {'AD Hales': 1340.0,
  'EJG Morgan': 1577.0,
  'JC Buttler': 985.0,
  'KP Pietersen': 1176.0,
  'LJ Wright': 759.0}}

pd.DataFrame(players_info)

The resulting output is

But I want the columns to be mapped with rows like the following

Player            Team           Score
Mohammad Nabi     Afghanistan    851.0
Mohammad Shahzad  Afghanistan   1713.0  
Najibullah Zadran Afghanistan    643.0  
JC Buttler        England        985.0
KP Pietersen      England       1176.0
LJ Wright         England        759.0

I tried reset_index but it is not working as I want. How can I do that ?

harpan · Accepted Answer · 2018-06-08 20:03:31Z

6

You need:

df = df.stack().reset_index()
df.columns=['Player', 'Team', 'Score']

Output of df.head(5):

      Player    Team    Score
0   AD Hales    Score   1340.0
1   AJ Finch    Team    1082.0
2   Asghar Stanikzai    Player  809.0
3   CL White    Team    988.0
4   DA Warner   Team    1691.0

answered Jun 8, 2018 at 20:03

harpan

8,6412 gold badges22 silver badges40 bronze badges

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

Comments

cs95 · Accepted Answer · 2018-06-08 21:00:53Z

Let's take a stab at this using melt. Should be pretty fast.

df.rename_axis('Player').reset_index().melt('Player').dropna()

                Player     variable   value
2     Asghar Stanikzai  Afghanistan   809.0
10       Mohammad Nabi  Afghanistan   851.0
11    Mohammad Shahzad  Afghanistan  1713.0
12   Najibullah Zadran  Afghanistan   643.0
14  Samiullah Shenwari  Afghanistan   774.0
16            AJ Finch    Australia  1082.0
18            CL White    Australia   988.0
19           DA Warner    Australia  1691.0
21          GJ Maxwell    Australia   822.0
28           SR Watson    Australia  1465.0
30            AD Hales      England  1340.0
35          EJG Morgan      England  1577.0
37          JC Buttler      England   985.0
38        KP Pietersen      England  1176.0
39           LJ Wright      England   759.0

Collectives™ on Stack Overflow

Convert Pandas DataFrame columns to rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related