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

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

CodePudding user response：

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  |