Python Pandas convert selective columns into rows

My dataset has some information about price and sales for different years. The problem is each year is actually a different column header for price and for sales as well. For example the CSV looks like

Items Price in 2018 Price in 2019 Price in 2020 Sales in 2018 Sales in 2019 Sales in 2020
A 100 120 135 5000 6000 6500
B 110 130 150 2000 4000 4500
C 150 110 175 1000 3000 3000

I want to show it something like this

Items Year Price Sales
A 2018 100 5000
A 2019 120 6000
A 2020 135 6500
B 2018 110 2000
B 2019 130 4000
B 2020 150 4500
C 2018 150 1000
C 2019 110 3000
C 2020 175 3000

I used melt function from Pandas like this df.melt(id_vars = [‘Items’], var_name=”Year”, value_name=”Price”)

But I’m struggling in getting separate columns for Price and Sales as it gives Price and Sales in one column. Thanks

Answer

Let us try pandas wide_to_long

pd.wide_to_long(df, i='Items', j='year', 
                stubnames=['Price', 'Sales'], 
                suffix=r'd+', sep=' in ').sort_index()

              Price Sales
Items year              
A     2018    100   5000
      2019    120   6000
      2020    135   6500
B     2018    110   2000
      2019    130   4000
      2020    150   4500
C     2018    150   1000
      2019    110   3000
      2020    175   3000