Melting a pandas dataframe

Question

I have a dataframe like this

    NSW     VIC
0   6718023 5023203
1   6735528 5048207
2   6742690 5061266
3   6766133 5083593
4   6786160 5103965

I want to change it like this

0   6718023  NSW
1   6735528  NSW
2   6742690  NSW
3   6766133  NSW
4   6786160  NSW
5   5023203  VIC
6   5048207  VIC
7   5061266  VIC
8   5083593  VIC
9   5103965  VIC

How can i finish this job?

score 8 · Accepted Answer · answered Sep 03 '17 at 04:03

8

Using melt

 pd.melt(df)
    Out[318]: 
      variable    value
    0      NSW  6718023
    1      NSW  6735528
    2      NSW  6742690
    3      NSW  6766133
    4      NSW  6786160
    5      VIC  5023203
    6      VIC  5048207
    7      VIC  5061266
    8      VIC  5083593
    9      VIC  5103965

answered Sep 03 '17 at 04:03

BENY

317,841
20
164
234

I am wishing you a Merry Christmas! And thank you for all support, small presetn (+5). Good luck! – jezrael Dec 24 '17 at 08:47
@jezrael happy holidays ：-） – BENY Dec 24 '17 at 16:54

score 4 · Answer 2 · answered Sep 03 '17 at 04:01

4

df.stack().reset_index(level=0, drop=True).sort_index()

Output:

NSW    6718023
NSW    6735528
NSW    6742690
NSW    6766133
NSW    6786160
VIC    5023203
VIC    5048207
VIC    5061266
VIC    5083593
VIC    5103965
dtype: int64

answered Sep 03 '17 at 04:01

Scott Boston

147,308
15
139
187

score 4 · Answer 3 · answered Sep 03 '17 at 04:02

Using df.unstack

df = df.unstack().to_frame().reset_index(level=0).iloc[:, [-1, 0]]
df.columns = [0, 1]   # or whatever else you please
df

         0    1
0  6718023  NSW
1  6735528  NSW
2  6742690  NSW
3  6766133  NSW
4  6786160  NSW
0  5023203  VIC
1  5048207  VIC
2  5061266  VIC
3  5083593  VIC
4  5103965  VIC

piRSquared · Answer 4 · 2017-09-03T07:05:01.493

4

FOMO

pd.DataFrame(dict(
    value=df.values.ravel(order='F'),
    variable=df.columns.repeat(len(df))
))

     value variable
0  6718023      NSW
1  6735528      NSW
2  6742690      NSW
3  6766133      NSW
4  6786160      NSW
5  5023203      VIC
6  5048207      VIC
7  5061266      VIC
8  5083593      VIC
9  5103965      VIC

edited Sep 03 '17 at 07:05

answered Sep 03 '17 at 05:56

piRSquared

285,575
57
475
624

For completeness, you could add a `.sort_values('variable')` – cs95 Sep 03 '17 at 06:52
Was supposed to be repeat, not tile. no need to sort (-: – piRSquared Sep 03 '17 at 07:05

Melting a pandas dataframe

4 Answers4

Linked

Related