Stack dataframe (python)

Question

I'm trying to stack a dataframe in python by means of using the function stack() but something is not working properly.

My dataframe has the following structure:

>              BE       BG       CZ      ...
> AT_CPA_A02   0.0706   0        0.3879  ... 
> BE_CPA_A02   38.8601  0.0001   0.0233  ...      
> BG_CPA_A02   0        95.2664  0.      ...
>     ...        ...      ...      ...

And what I'm looking for is:

>              Country Val.
> AT_CPA_A02   BE      0.0706   
> AT_CPA_A02   BG      0
> AT_CPA_A02   CZ      0.3879
> ...
> BE_CPA_A02   BE      38.8601   
> BE_CPA_A02   BG      0.0001
> BE_CPA_A02   CZ      0.0233 
> ...

Despite of this, while using the function stack() in df = df.stack() the results is:

>              Country BE.      BG.     CZ
> AT_CPA_A02   BE      0.0706   NA.     NA.
>              BG      NA.      0.      NA.
>              CZ      NA.      NA.     0.3879.   
> ...
> BE_CPA_A02.  BE.     38.8601  NA.     NA. 
>              BG.     NA.      0.0001  NA
>              CZ.     NA.      NA.     0.0233
> ...

While using the function with some example data it works perfectly...

Could someone help me on that? Many thanks in advance

Did @wwnde or I answer your question? Please accept what you think is the best answer by clicking the checkmark next to the solution. — David Erickson
– David Erickson, Commented Oct 9, 2020 at 14:38
Unfortunately not... none of the answers solved the problem... can I do something to be more explicit while explaining my dataset? (such as uploading dataframe) — edusanlla
– edusanlla, Commented Oct 9, 2020 at 16:50

IoaTzimas · Accepted Answer · 2020-10-09 10:51:10Z

1

One option is to use .melt. The way I'm doing it, you would have to reset the index first and then set it back:

df = df.reset_index().melt(id_vars='index', var_name='Country', value_name='Val.').set_index('index')
df
Out[1]: 
           Country     Val.
index                      
AT_CPA_A02      BE   0.0706
BE_CPA_A02      BE  38.8601
BG_CPA_A02      BE   0.0000
AT_CPA_A02      BG   0.0000
BE_CPA_A02      BG   0.0001
BG_CPA_A02      BG  95.2664
AT_CPA_A02      CZ   0.3879
BE_CPA_A02      CZ   0.0233
BG_CPA_A02      CZ   0.0000

edited Oct 9, 2020 at 10:51

IoaTzimas

10.7k2 gold badges15 silver badges32 bronze badges

answered Oct 9, 2020 at 10:50

David Erickson

16.7k2 gold badges21 silver badges37 bronze badges

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

Comments

wwnde · Accepted Answer · 2020-10-09 11:04:24Z

1

You were on the right track. Please Try:

df.stack().reset_index(level=1).rename(columns={'level_1':'Country',0:'Val' })



            Country      Val
AT_CPA_A02      BE   0.0706
AT_CPA_A02      BG   0.0000
AT_CPA_A02      CZ   0.3879
BE_CPA_A02      BE  38.8601
BE_CPA_A02      BG   0.0001
BE_CPA_A02      CZ   0.0233
BG_CPA_A02      BE   0.0000
BG_CPA_A02      BG  95.2664
BG_CPA_A02      CZ   0.0000

answered Oct 9, 2020 at 11:04

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

Collectives™ on Stack Overflow

Stack dataframe (python)

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