0

I have a JSON file, which I then convert to a Pandas dataframe called stocks. The stocks dataframe is in wide format and I'd like to convert it to long format.

Here's what the stocks dataframe looks like after it's ingested and converted from JSON:

    TSLA     MSFT     GE      DELL 
0   993.22   320.72   93.19   57.25

I would like to convert the stocks dataframe into the following format:

     ticker   price
0    TSLA     993.22  
1    MSFT     320.72
2    GE       93.19
3    DELL     57.25

Here is my attempt (which works):

stocks = pd.read_json('stocks.json', lines=True).T.reset_index()
stocks.columns = ['ticker', 'price']

Is there a more Pythonic way to do this? Thanks!

equanimity
  • 2,371
  • 3
  • 29
  • 53

2 Answers2

4

pandas provides the melt function for this job.

pd.melt(stocks, var_name="ticker", value_name="price")

#  ticker   price
#0   TSLA  993.22
#1   MSFT  320.72
#2     GE   93.19
#3   DELL   57.25
Kota Mori
  • 6,510
  • 1
  • 21
  • 25
1

A perhaps more intuitive method than melt would be to transpose and reset the index:

df = df.T.reset_index().set_axis(['ticker', 'price'], axis=1)

Output:

>>> df
  ticker   price
0   TSLA  993.22
1   MSFT  320.72
2     GE   93.19
3   DELL   57.25

Edit: oops, saw that the OP already did that! :)