2
$\begingroup$

I used the below code for downloading stock data from yahoo finance:-

import yfinance as yf
import datetime

    stocks = ["AXISBANK.NS", "HDFCBANK.NS", "ICICIBANK.NS" ,"INDUSINDBK.NS",
    "KOTAKBANK.NS",
    "SBIN.NS",
    "YESBANK.NS"]
    start = datetime.datetime(2018,1,1)
    end = datetime.datetime(2019,7,17)
    data = yf.download(stocks, start=start, end=end)
    data

I get the data in a below manner:-

enter image description here

I saved the data using panda:-

import pandas as pd

df = pd.DataFrame(data) 

# saving the dataframe 
df.to_csv('BANKING STOCK.csv')

I got the data in this format:- enter image description here

But I ant my data in this format:-

enter image description here

Because this format is more convenient for analyzing data through SQL. How can I change the format of the data?

$\endgroup$
3
$\begingroup$

Use:

data.stack().reset_index().rename(index=str, columns={"level_1": "Symbol"}).sort_values(['Symbol','Date'])

Output:

           Date       Symbol  Adj Close  ...     Low    Open     Volume
0    2018-01-01  AXISBANK.NS     564.80  ...  560.50  563.80    6943234
7    2018-01-02  AXISBANK.NS     558.81  ...  556.35  567.00    6292268
14   2018-01-03  AXISBANK.NS     559.75  ...  555.75  561.30    3990149
21   2018-01-04  AXISBANK.NS     559.21  ...  558.00  564.85    2766664
28   2018-01-05  AXISBANK.NS     562.55  ...  556.70  561.05    3249056
35   2018-01-08  AXISBANK.NS     564.75  ...  562.45  565.80    3770339
42   2018-01-09  AXISBANK.NS     564.60  ...  563.00  564.00    4607504
49   2018-01-10  AXISBANK.NS     562.10  ...  560.45  566.15    2411625

The basic operation is stack that stacks the column headers into a multiindex. This multiindex is then converted to columns (reset_index()) which then get proper names. Finally the data are sorted by Symbol and Date as required.


Remark: You don't need df = pd.DataFrame(data), yf.download already returns a DataFrame.

$\endgroup$
  • $\begingroup$ Sorry for late accepting @Stef. It will be more convenient for me if you describe your code. $\endgroup$ – coding_ninza Jul 19 '19 at 14:47
  • $\begingroup$ @coding_ninza No problem. I added some explanations who the code works. $\endgroup$ – Stef Jul 19 '19 at 14:57
  • $\begingroup$ Than you very much @Stef $\endgroup$ – coding_ninza Jul 20 '19 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.