How to deal with multi-level column names downloaded with yfinance

D

4

13

I have a list of tickers (tickerStrings) that I have to download all at once. When I try to use Pandas' read_csv it doesn't read the CSV file in the way it does when I download the data from yfinance.

I usually access my data by ticker like this: data['AAPL'] or data['AAPL'].Close, but when I read the data from the CSV file it does not let me do that.

if path.exists(data_file):
    data = pd.read_csv(data_file, low_memory=False)
    data = pd.DataFrame(data)
    print(data.head())
else:
    data = yf.download(tickerStrings, group_by="Ticker", period=prd, interval=intv)
    data.to_csv(data_file)

Here's the print output:

                  Unnamed: 0                 OLN               OLN.1               OLN.2               OLN.3  ...                 W.1                 W.2                 W.3                 W.4     W.5
0                        NaN                Open                High                 Low               Close  ...                High                 Low               Close           Adj Close  Volume
1                   Datetime                 NaN                 NaN                 NaN                 NaN  ...                 NaN                 NaN                 NaN                 NaN     NaN
2  2020-06-25 09:30:00-04:00    11.1899995803833  11.220000267028809  11.010000228881836  11.079999923706055  ...   201.2899932861328   197.3000030517578  197.36000061035156  197.36000061035156  112156
3  2020-06-25 09:45:00-04:00  11.130000114440918  11.260000228881836  11.100000381469727   11.15999984741211  ...  200.48570251464844  196.47999572753906  199.74000549316406  199.74000549316406   83943
4  2020-06-25 10:00:00-04:00  11.170000076293945  11.220000267028809  11.119999885559082  11.170000076293945  ...  200.49000549316406  198.19000244140625   200.4149932861328   200.4149932861328   88771

The error I get when trying to access the data:

Traceback (most recent call last):
File "getdata.py", line 49, in processData
    avg = data[x].Close.mean()
AttributeError: 'Series' object has no attribute 'Close'

Dice answered 27/7, 2020 at 1:41 Comment(0)

D

40

In dealing with financial data from multiple tickers, specifically using yfinance and pandas, the process can be broken down into a few key steps: downloading the data, organizing it in a structured format, and accessing it in a way that aligns with the user's needs. Below, the answer is organized into clear, actionable segments.

Downloading Data for Multiple Tickers

Direct Download and DataFrame Creation

Single Ticker, Single DataFrame Approach:

For individual tickers, the DataFrame downloaded directly from yfinance comes with single-level column names but lacks a ticker column. By iterating over each ticker, adding a ticker column, and then combining these into a single DataFrame, a clear structure for each ticker's data is maintained.

import yfinance as yf
import pandas as pd

tickerStrings = ['AAPL', 'MSFT']
df_list = []
for ticker in tickerStrings:
    data = yf.download(ticker, group_by="Ticker", period='2d')
    data['ticker'] = ticker  # Add ticker column
    df_list.append(data)

# Combine all dataframes into a single dataframe
df = pd.concat(df_list)
df.to_csv('ticker.csv')

Condensed Single DataFrame Approach:

Achieve the same result as above with a one-liner using list comprehension, streamlining the process of fetching and combining data.

# Download 2 days of data for each ticker in tickerStrings, add a 'ticker' column for identification, and concatenate into a single DataFrame with continuous indexing.
df = pd.concat([yf.download(ticker, group_by="Ticker", period='2d').assign(ticker=ticker) for ticker in tickerStrings], ignore_index=True)

Multi-Ticker, Structured DataFrame Approach

When downloading data for multiple tickers simultaneously, yfinance groups data by ticker, resulting in a DataFrame with multi-level column headers. This structure can be reorganized for easier access.

Unstacking Column Levels:

# Define a list of ticker symbols to download
tickerStrings = ['AAPL', 'MSFT']

# Download 2 days of data for each ticker, grouping by 'Ticker' to structure the DataFrame with multi-level columns
df = yf.download(tickerStrings, group_by='Ticker', period='2d')

# Transform the DataFrame: stack the ticker symbols to create a multi-index (Date, Ticker), then reset the 'Ticker' level to turn it into a column
df = df.stack(level=0).rename_axis(['Date', 'Ticker']).reset_index(level=1)

Handling CSV Files with Multi-Level Column Names

To read a CSV file that has been saved with yfinance data (which often includes multi-level column headers), adjustments are necessary to ensure the DataFrame is accessible in the desired format.

Reading and Adjusting Multi-Level Columns:

# Read the CSV file. The file has multi-level headers, hence header=[0, 1].
df = pd.read_csv('test.csv', header=[0, 1])

# Drop the first row as it contains only the Date information in one column, which is redundant after setting the index.
df.drop(index=0, inplace=True)

# Convert the 'Unnamed: 0_level_0', 'Unnamed: 0_level_1' column (which represents dates) to datetime format.
# This assumes the dates are in the 'YYYY-MM-DD' format.
df[('Unnamed: 0_level_0', 'Unnamed: 0_level_1')] = pd.to_datetime(df[('Unnamed: 0_level_0', 'Unnamed: 0_level_1')])

# Set the datetime column as the index of the DataFrame. This makes time series analysis more straightforward.
df.set_index(('Unnamed: 0_level_0', 'Unnamed: 0_level_1'), inplace=True)

# Clear the name of the index to avoid confusion, as it previously referred to the multi-level column names.
df.index.name = None

Flattening Multi-Level Columns for Easier Access

Depending on the initial structure of the DataFrame, multi-level columns many need to be flattened to a single level, adding clarity and simplicity to the dataset.

Flattening and Reorganizing Based on Ticker Level:
- For DataFrames where the ticker symbol is at the top level of the column headers:
```
df.stack(level=0).rename_axis(['Date', 'Ticker']).reset_index(level=1)
```
- If the ticker symbol is at the bottom level:
```
df.stack(level=1).rename_axis(['Date', 'Ticker']).reset_index(level=1)
```

Individual Ticker File Management

For those preferring to manage each ticker's data separately, downloading and saving each ticker's data to individual files can be a straightforward approach.

Downloading and Saving Individual Ticker Data:

for ticker in tickerStrings:
    # Downloads historical market data from Yahoo Finance for the specified ticker.
    # The period ('prd') and interval ('intv') for the data are specified as string variables.
    data = yf.download(ticker, group_by="Ticker", period='prd', interval='intv')

    # Adds a new column named 'ticker' to the DataFrame. This column is filled with the ticker symbol.
    # This step is helpful for identifying the source ticker when multiple DataFrames are combined or analyzed separately.
    data['ticker'] = ticker

    # Saves the DataFrame to a CSV file. The file name is dynamically generated using the ticker symbol,
    # allowing each ticker's data to be saved in a separate file for easy access and identification.
    # For example, if the ticker symbol is 'AAPL', the file will be named 'ticker_AAPL.csv'.
    data.to_csv(f'ticker_{ticker}.csv')

Consolidating Multiple Ticker Files into a Single DataFrame

If data for each ticker is stored in separate files, combining these into a single DataFrame can be accomplished through file reading and concatenation.

Reading Multiple Files into One DataFrame:

# Import the Path class from the pathlib module, which provides object-oriented filesystem paths
from pathlib import Path

# Create a Path object 'p' that represents the directory containing the CSV files
p = Path('path_to_files')

# Use the .glob method to create an iterator over all files in the 'p' directory that match the pattern 'ticker_*.csv'.
# This pattern will match any files that start with 'ticker_' and end with '.csv', which are presumably files containing ticker data.
files = p.glob('ticker_*.csv')

# Read each CSV file matched by the glob pattern into a separate pandas DataFrame, then concatenate all these DataFrames into one.
# The 'ignore_index=True' parameter is used to reindex the new DataFrame, preventing potential index duplication.
# This results in a single DataFrame 'df' that combines all the individual ticker data files into one comprehensive dataset.
df = pd.concat([pd.read_csv(file) for file in files], ignore_index=True)

This structured approach ensures that regardless of the initial data format or how it's stored, you can effectively organize and access financial data for multiple tickers using yfinance and pandas.

Overview of Data Representations

This seciton showcases examples of financial data represented in both multi-level and single-level column formats. These representations are crucial for understanding different data structures and their implications for data analysis in financial contexts.

Multi-Level Column Data

Multi-level column data can be complex but allows for the organization of related data under broader categories. This structure is especially useful for datasets where each entity (e.g., a stock ticker) has multiple attributes (e.g., Open, High, Low, Close prices).

Example: DataFrame with Multi-Level Columns

Below is a sample DataFrame showcasing multi-level column data for two stock tickers, AAPL and MSFT. Each ticker has multiple attributes, such as Open, High, Low, Close, Adjusted Close, and Volume.

                AAPL                                                    MSFT                                
                Open      High       Low     Close Adj Close     Volume Open High Low Close Adj Close Volume
Date                                                                                                        
1980-12-12  0.513393  0.515625  0.513393  0.513393  0.405683  117258400  NaN  NaN NaN   NaN       NaN    NaN
1980-12-15  0.488839  0.488839  0.486607  0.486607  0.384517   43971200  NaN  NaN NaN   NaN       NaN    NaN
1980-12-16  0.453125  0.453125  0.450893  0.450893  0.356296   26432000  NaN  NaN NaN   NaN       NaN    NaN
1980-12-17  0.462054  0.464286  0.462054  0.462054  0.365115   21610400  NaN  NaN NaN   NaN       NaN    NaN
1980-12-18  0.475446  0.477679  0.475446  0.475446  0.375698   18362400  NaN  NaN NaN   NaN       NaN    NaN

Example: CSV Format of Multi-Level Columns

Representing the above DataFrame in CSV format poses a unique challenge, as shown below. The multi-level structure is flattened into two header rows followed by the data rows.

,AAPL,AAPL,AAPL,AAPL,AAPL,AAPL,MSFT,MSFT,MSFT,MSFT,MSFT,MSFT
,Open,High,Low,Close,Adj Close,Volume,Open,High,Low,Close,Adj Close,Volume
Date,,,,,,,,,,,,
1980-12-12,0.5133928656578064,0.515625,0.5133928656578064,0.5133928656578064,0.40568336844444275,117258400,,,,,,
1980-12-15,0.4888392984867096,0.4888392984867096,0.4866071343421936,0.4866071343421936,0.3845173120498657,43971200,,,,,,
1980-12-16,0.453125,0.453125,0.4508928656578064,0.4508928656578064,0.3562958240509033,26432000,,,,,,

Single-Level Column Data

For datasets where each entity shares a uniform set of attributes, single-level column data structures are ideal. This simpler format facilitates easier data manipulation and analysis, making it a common choice for many applications.

Example: DataFrame with Single-Level Columns

Below is a sample DataFrame displaying single-level column data for the MSFT stock ticker. It includes attributes such as Open, High, Low, Close, Adjusted Close, and Volume, alongside the ticker symbol for each entry. This format is straightforward, enabling direct access to each attribute of the stock data.

                Open      High       Low     Close  Adj Close      Volume ticker
Date                                                                            
1986-03-13  0.088542  0.101562  0.088542  0.097222   0.062205  1031788800   MSFT
1986-03-14  0.097222  0.102431  0.097222  0.100694   0.064427   308160000   MSFT
1986-03-17  0.100694  0.103299  0.100694  0.102431   0.065537   133171200   MSFT
1986-03-18  0.102431  0.103299  0.098958  0.099826   0.063871    67766400   MSFT
1986-03-19  0.099826  0.100694  0.097222  0.098090   0.062760    47894400   MSFT

Example: CSV Format of Single-Level Columns

When single-level column data is exported to a CSV format, it results in a straightforward, easily readable file. Each row corresponds to a specific date, and each column header directly represents an attribute of the stock data. This simplicity enhances the CSV's usability for both humans and software applications.

Date,Open,High,Low,Close,Adj Close,Volume,ticker
1986-03-13,0.0885416641831398,0.1015625,0.0885416641831398,0.0972222238779068,0.0622050017118454,1031788800,MSFT
1986-03-14,0.0972222238779068,0.1024305522441864,0.0972222238779068,0.1006944477558136,0.06442664563655853,308160000,MSFT
1986-03-17,0.1006944477558136,0.1032986119389534,0.1006944477558136,0.1024305522441864,0.0655374601483345,133171200,MSFT
1986-03-18,0.1024305522441864,0.1032986119389534,0.0989583358168602,0.0998263880610466,0.06387123465538025,67766400,MSFT
1986-03-19,0.0998263880610466,0.1006944477558136,0.0972222238779068,0.0980902761220932,0.06276042759418488,47894400,MSFT

This section exemplifies how single-level column data is organized, providing an intuitive and accessible way to work with financial datasets. Whether in DataFrame or CSV format, single-level data structures support efficient data processing and analysis tasks.

Disband answered 27/7, 2020 at 2:18 Comment(0)

T

6

To turn it into a dict of d[ticker]=df:

df = yf.download(tickers, group_by="ticker")
d = {idx: gp.xs(idx, level=0, axis=1) for idx, gp in df.groupby(level=0, axis=1)}

Thoroughwort answered 7/4, 2021 at 16:9 Comment(0)

S

2

Another option which maintains the pandas dataframe but drops the data you don't need is to change the column index from a multiindex to a single index. Since you only care about the 'Close' column, the first step will be throwing the other ones out:

df = yf.download(...)
df = df[['Close']]

This is great but leaves each column with a multiindex which looks like (Close/AAPL) or (Close/MSFT) etc. What you really want is just the ticker.

df.columns = [col[1] for col in df.columns]

Now if you want to split the dataframe into separate ones for each column you can do this with list comprehension.

separated = [df.iloc[:,i] for i in range(len(df.columns))]

Schism answered 15/3, 2021 at 16:12 Comment(0)

S

1

Use the below line to write and read the CSV file. They will be in the exact format as you downloaded from the yfinance API.

To write to a file

data.to_csv('file_loc')

To read the file

data = pd.read_csv('file_loc', header=[0, 1], index_col=[0])

Sverre answered 28/10, 2021 at 18:11 Comment(0)