CODEWITHSUNDEEP
pythonpandas
Deepak
Deepak

Reputation: 470

Is there anyway to reset multi index in pandas?

I want to obtain stock data using pandas_datareader. I have the data, but the index I got is multiIndex. _data.columns

MultiIndex(levels=[['High', 'Low', 'Open', 'Close', 'Volume', 'Adj Close'], ['MSFT']],
           codes=[[0, 1, 2, 3, 4, 5], [0, 0, 0, 0, 0, 0]],
           names=['Attributes', 'Symbols'])




from pandas_datareader import data as pdr
import yfinance
_data = pdr.get_data_yahoo(['MSFT'], start='2019-01-01', end='2019-05-30')

The format I want to get is single index.So that i can use that data to plot

    symbol  date            price
0   MSFT    2000-01-01  39.81
1   MSFT    2000-02-01  36.35
2   MSFT    2000-03-01  43.22
3   MSFT    2000-04-01  28.37
4   MSFT    2000-05-01  25.45

Upvotes: 0

Views: 3193

Answers (4)

Sergey Bushmanov
Sergey Bushmanov

Reputation: 25189

A possible solution to your problem might be:

from pandas_datareader import data as pdr
data = pdr.get_data_yahoo(['MSFT'], start='2019-01-01', end='2019-05-30')
data.columns = data.columns.levels[0]
data['symbol'] = 'MSFT'
data.head()

Attributes  High    Low Open    Close   Volume  Adj Close   symbol
Date                            
2019-01-02  101.750000  98.940002   99.550003   101.120003  35329300.0  100.318642  MSFT
2019-01-03  100.190002  97.199997   100.099998  97.400002   42523600.0  96.628120   MSFT
2019-01-04  102.510002  98.930000   99.720001   101.930000  44060600.0  101.122223  MSFT
2019-01-07  103.269997  100.980003  101.639999  102.059998  35656100.0  101.251190  MSFT
2019-01-08  103.970001  101.709999  103.040001  102.800003  31514400.0  101.985329  MSFT

Upvotes: 1

piRSquared
piRSquared

Reputation: 294198

There are several "Price" Columns to choose from. I chose 'Adj Close'. This is mostly the same as ChrisA's comment.

_data.stack()['Adj Close'].reset_index(name='Price')

          Date Symbols       Price
0   2019-01-02    MSFT  100.318642
1   2019-01-03    MSFT   96.628120
2   2019-01-04    MSFT  101.122223
3   2019-01-07    MSFT  101.251190
4   2019-01-08    MSFT  101.985329
..         ...     ...         ...

Upvotes: 3

Umar.H
Umar.H

Reputation: 23099

I think you need reset_index with melt

 from pandas_datareader import data as pdr
import yfinance
_data = pdr.get_data_yahoo(['MSFT'], start='2019-01-01', end='2019-05-30')
print(_data)


   Attributes   High    Low Open    Close   Volume  Adj Close
Symbols MSFT    MSFT    MSFT    MSFT    MSFT    MSFT
Date                        
2019-01-02  101.750000  98.940002   99.550003   101.120003  35329300.0  100.318642
2019-01-03  100.190002  97.199997   100.099998  97.400002   42523600.0  96.628120
2019-01-04  102.510002  98.930000   99.720001   101.930000  44060600.0  101.122223
2019-01-07  103.269997  100.980003  101.639999  102.059998  35656100.0  101.251190
2019-01-08  103.970001  101.709999  103.040001  102.800003  31514400.0  101.985329

we can reset_index and melt passing date as our id_vars

df = _data.reset_index().melt(id_vars='Date') # You can filter out attributes if you don't need them.
print(df)


 Date   Attributes  Symbols value
0   2019-01-02  High    MSFT    101.750000
1   2019-01-03  High    MSFT    100.190002
2   2019-01-04  High    MSFT    102.510002
3   2019-01-07  High    MSFT    103.269997
4   2019-01-08  High    MSFT    103.970001
5   2019-01-09  High    MSFT    104.879997
6   2019-01-10  High    MSFT    103.750000
7   2019-01-11  High    MSFT    103.440002

Upvotes: 1

Javier Lopez Tomas
Javier Lopez Tomas

Reputation: 2342

You need to use multiindex droplevel https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.droplevel.html

Upvotes: 0

Related Questions