CODEWITHSUNDEEP
pythonpandas
Martin
Martin

Reputation: 61

How to reindex a dataframe by its values?

country  doctor  teacher  lawyer
China      41       26      34
USA        25       29      40
Japan      20       26      27

I have a dataframe above, how to convert it to the following one?

age job country
41  doctor  China
25  doctor  USA
20  doctor  Japan
26  teacher China
29  teacher USA
26  teacher Japan
34  lawyer  China
40  lawyer  USA
27  lawyer  Japan

Upvotes: 2

Views: 63

Answers (2)

Anton Protopopov
Anton Protopopov

Reputation: 31672

You could use set_index and unstack for that:

In [65]: df.set_index('country').unstack().reset_index()
Out[65]:
   level_0 country   0
0   doctor   China  41
1   doctor     USA  25
2   doctor   Japan  20
3  teacher   China  26
4  teacher     USA  29
5  teacher   Japan  26
6   lawyer   China  34
7   lawyer     USA  40
8   lawyer   Japan  27

Or if you want to age be index of your result you could add set_index:

In [66]: df.set_index('country').unstack().reset_index().set_index(0)
Out[66]:
    level_0 country
0
41   doctor   China
25   doctor     USA
20   doctor   Japan
26  teacher   China
29  teacher     USA
26  teacher   Japan
34   lawyer   China
40   lawyer     USA
27   lawyer   Japan

With renaming and reordering columns:

res = df.set_index('country').unstack().reset_index()
res.columns = ['job', 'country', 'age']
res = res[['age', 'job', 'country']]

In [73]: res
Out[73]:
   age      job country
0   41   doctor   China
1   25   doctor     USA
2   20   doctor   Japan
3   26  teacher   China
4   29  teacher     USA
5   26  teacher   Japan
6   34   lawyer   China
7   40   lawyer     USA
8   27   lawyer   Japan

Upvotes: 2

Mike Graham
Mike Graham

Reputation: 76713

df.set_index('country').stack()

(Or you might really want df.set_index('country').stack().reset_index().rename(columns={'level_1': 'job', 0: 'age'}) to take the question more literally -- details depend on what you're doing.)

Upvotes: 3

Related Questions