0

I have a table with stocks as the columns(over 6000) and prices and dates as the rows. I'm trying to transpose the columns in such a way that the all customers are in one column and their prices are sorted by the dates. Therefore, the customer would be duplicated for each date. I've tried using transpose() and stack() but I'm not getting the desired result. I need help with figuring out how to convert all the stock columns to rows without messing with the date column. I can replicate this in excel using power query but was wondering how to do it in python.

Each column is a different stock so I need to be able to dynamically convert all columns without specify specific column names.

This is how the table currently is:

Date Stock1 Stock2 Stock3
1/2/98 12 1.4 2.2
1/3/98 11.9 1.5 2.4
1/4/98 11.9 1.5 2.5

This is how I'm trying to get the table to look:

Date Stock Price
1/2/98 stock1 12
1/3/98 stock1 11.9
1/4/98 stock1 11.9
1/2/98 stock2 1.4
1/3/98 stock2 1.5
1/4/98 stock2 1.5
1/2/98 stock3 2.2
1/3/98 stock3 2.4
1/4/98 stock3 2.5

Thanks!

feyadej
  • 3
  • 2

1 Answers1

0

This can be accomplished with melt

df.melt(id_vars = ['Date'], value_vars = df.columns.drop('Date').tolist())
ArchAngelPwn
  • 2,891
  • 1
  • 4
  • 17
  • It's useless to provide the `value_vars` in this case (and also to answer the question) – mozway Jun 22 '22 at 17:38
  • I know their was a dup of it out there somewhere probably. So when you do this you don't need to include the value_vars? Is that just because the rest of the df would be automatically defaulted to the value_vars? – ArchAngelPwn Jun 22 '22 at 17:44
  • Yes, all other columns are used – mozway Jun 22 '22 at 18:24