[FIXED] Python - How can you calculate values from two columns into its own column in each row?

Issue

I'm having a problem with calculating results from certain columns and appending it to a separate column.

For an example, I'm trying to calculate the EV Ratio by dividing b / a and appending those results to col_e (EV/Ratio).

I believe one of the problems is calculating e, there are going to be tickers that do not have numbers which appends None or 'NaN'.

I know in Yfinance you can get the ev/ratio, but the point is to do other calculations in the future that is not provided by Yfinance.

Thanks!

start = time.time()
col_a = []  
col_b = []  
col_c = []  
col_d = []  
col_e = []

print('Loading Data... Please wait for results')

def do_something(tickers):
    print('---', tickers, '---')
    all_info = yf.Ticker(tickers).info
    try:
        a = all_info.get('ebitda')
        b = all_info.get('enterpriseValue')
        c = all_info.get('trailingPE')
        d = all_info.get('sector')
        e = (float(b)/float(a))
    except:
        None

    col_a.append(a)  
    col_b.append(b)  
    col_c.append(c)  
    col_d.append(d)  
    col_e.append(e)   
    return
with concurrent.futures.ThreadPoolExecutor() as executer:
    executer.map(do_something, tickers)
        

# Dataframe Set Up
pd.set_option("display.max_rows", None)    
df = pd.DataFrame({
    'Ticker': tickers,
    'Ebitda': col_a,  
    'EnterpriseValue' :col_b,  
    'PE Ratio': col_c,  
    'Sector': col_d,
    'EV/Ratio': col_e
})
print(df.dropna())
print('It took', time.time()-start, 'seconds.')

Normal Output without EV Ratio calculation

   Ticker        Ebitda  EnterpriseValue    PE Ratio              Sector
0       A -6.810958e+07     8.829677e+07         NaN  Consumer Defensive
1      AA           NaN     7.848941e+08         NaN         Real Estate
2     AAC -2.015600e+07     1.971329e+08    1.006808  Financial Services
3    AACG  8.132960e+08     1.228469e+09    9.518116   Consumer Cyclical
4   AACIU -2.217800e+07     3.088700e+08         NaN          Technology
5    AADI           NaN     2.441449e+09   60.060978  Financial Services
6    AAIC           NaN              NaN         NaN                None

Desired Output (xxx represents the ratio)

   Ticker        Ebitda  EnterpriseValue    PE Ratio              Sector    EV/Ratio
0       A -6.810958e+07     8.829677e+07         NaN  Consumer Defensive    xxx
1      AA           NaN     7.848941e+08         NaN         Real Estate    xxx
2     AAC -2.015600e+07     1.971329e+08    1.006808  Financial Services    xxx
3    AACG  8.132960e+08     1.228469e+09    9.518116   Consumer Cyclical    xxx
4   AACIU -2.217800e+07     3.088700e+08         NaN          Technology    xxx
5    AADI           NaN     2.441449e+09   60.060978  Financial Services    xxx
6    AAIC           NaN              NaN         NaN                None    xxx

Output Using .dropna()

0       A  1.178790e+08     3.501286e+09   56.152172         Industrials
2     AAC -2.015600e+07     1.971329e+08    1.006808  Financial Services
5    AADI  1.762000e+09     5.311271e+10   61.948406          Healthcare
6    AAIC  8.132960e+08     1.228469e+09    9.518116   Consumer Cyclical
12   AAOI  1.239876e+09     1.609877e+10   25.678375   Consumer Cyclical
15   AAPL  1.891750e+08     3.665276e+09  137.295380         Real Estate
17    AAT  2.305600e+07     1.175756e+08    7.616487  Financial Services
19    AAU  1.109350e+11     2.489513e+12   33.713196          Technology
20   AAWW  8.486470e+08     4.013744e+09    5.528439         Industrials
22    ABB  3.621000e+09     7.813488e+10   13.841295         Industrials
24    ABC  2.767000e+10     2.758342e+11   40.154480          Healthcare

Solution

import yfinance as yf
import pandas as pd

tickers = ['MSFT', 'AAPL', 'TSLA']

def create_df(tickers):
    all_info = []
    for each_ticker in tickers:
        all_info.append(yf.Ticker(each_ticker).info)
        
    df = pd.DataFrame.from_records(all_info)
    df = df[['symbol','ebitda', 'enterpriseValue', 'trailingPE', 'sector']]
    df.dropna(inplace=True)
    df['EV/Ratio'] = df['enterpriseValue'] / df['ebitda']
    return df
    
df = create_df(tickers)
df.head()

|    | symbol   |       ebitda |   enterpriseValue |   trailingPE | sector            |   EV/Ratio |
|---:|:---------|-------------:|------------------:|-------------:|:------------------|-----------:|
|  0 | MSFT     |  85745000448 |     2476438192128 |      37.6686 | Technology        |    28.8814 |
|  1 | AAPL     | 120233000960 |     2534678462464 |      26.7362 | Technology        |    21.0814 |
|  2 | TSLA     |   7266999808 |     1033331408896 |     337.498  | Consumer Cyclical |   142.195  |

With multi-threading:

It is not recommended to add row by row to a DataFrame. We instead add each records to a list, and then create a DataFrame from that list.

all_info = []
def create_df(ticker):
    all_info.append(yf.Ticker(ticker).info)
    
with ThreadPoolExecutor(max_workers=2) as executor:
    futures = [executor.submit(create_df, x) for x in tickers]

df = pd.DataFrame.from_records(all_info)
df = df[['symbol','ebitda', 'enterpriseValue', 'trailingPE', 'sector']]
df.dropna(inplace=True)
df['EV/Ratio'] = df['enterpriseValue'] / df['ebitda']

Answered By - iudeen