Convert columns into rows data with Pandas

Question

my dataset has some information by location for n dates. The CSV looks like

Country     year2018     year2019        year2020
          saleA saleB    SaleA SaleB     saleA saleB

USA        22   23       323    32        31    65
china      12   12        2     66        66    78

I want my data to be of the form

Country   year      saleA saleB 
             
USA       year2018   22    23    
USA       year2019   323   32
USA       year2020   31    65
china     year2018   12    12
.
.
.

How can I do it using pandas?

I tried using pd.melt but couldn't figured out.

Please provide a reproducible example, your input is not a valid CSV nor a valid DataFrame — mozway
– mozway, Commented Jan 31, 2023 at 21:47

Corralien · Accepted Answer · 2023-01-31 21:55:59Z

2

You can reshape your dataframe with set_index and stack:

out = (df.set_index('Country')
         .rename_axis(columns=['year', None])
         .stack('year').reset_index())

  Country      year  saleA  saleB
0     USA  year2018     22     23
1     USA  year2019    323     32
2     USA  year2020     31     65
3   China  year2018     12     12
4   China  year2019      2     66
5   China  year2020     66     78

Another solution with melt and pivot_table:

>>> out = (df.melt(id_vars='Country', var_name=['year', 'sale'])
             .pivot_table(index=['Country', 'year'], columns='sale', values='value')
             .reset_index())

answered Jan 31, 2023 at 21:55

Corralien

121k8 gold badges44 silver badges69 bronze badges

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

Collectives™ on Stack Overflow

Convert columns into rows data with Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related