CODEWITHSUNDEEP
pythonpandasdataframemulti-index
Thomas Philips
Thomas Philips

Reputation: 1089

Multiply two columns in a pandas MultiIndex dataframe

I have never used multiIndex pandas dataframes, and the Python API for CalcBench, a financial program I use, returned one when I typed the following code:

dataItems = ["ROE", "StockholdersEquity", "SharesOutstandingEndOfPeriod", "EndOfPeriodStockPrice"]             
data = cb.normalized_data(tickers, dataItems, start_year=2021, end_year=2021, period_type='annual')

If I try to explore my dataframe by typing data.info(), I get:

<class 'pandas.core.frame.DataFrame'>
PeriodIndex: 1 entries, 2021 to 2021
Freq: A-DEC
Columns: 1800 entries, ('EndOfPeriodStockPrice', 'A') to ('StockholdersEquity', 'ZTS')
dtypes: float64(1800)

Typing data.columns() gives me:

MultiIndex([('EndOfPeriodStockPrice',    'A'),
            ('EndOfPeriodStockPrice',  'AAL'),
            ('EndOfPeriodStockPrice',  'AAP'),
            ('EndOfPeriodStockPrice', 'AAPL'),
            ('EndOfPeriodStockPrice', 'ABBV'),
            ('EndOfPeriodStockPrice',  'ABC'),
            ('EndOfPeriodStockPrice',  'ABT'),
            ('EndOfPeriodStockPrice',  'ACN'),
            ('EndOfPeriodStockPrice', 'ADBE'),
            ('EndOfPeriodStockPrice',  'ADI'),
            ...
            (   'StockholdersEquity', 'WYNN'),
            (   'StockholdersEquity',  'XEL'),
            (   'StockholdersEquity',  'XOM'),
            (   'StockholdersEquity', 'XRAY'),
            (   'StockholdersEquity',  'XYL'),
            (   'StockholdersEquity',  'YUM'),
            (   'StockholdersEquity',  'ZBH'),
            (   'StockholdersEquity', 'ZBRA'),
            (   'StockholdersEquity', 'ZION'),
            (   'StockholdersEquity',  'ZTS')],
           names=['metric', 'ticker'], length=1800)

I would like to create a new metric MarketCapAtEndOfPeriod in this dataframe for each firm in the year 2021 by multiplying the corresponding entries for EndofPeriodStockPrice and SharesOutstandingEndOfPeriod.

My efforts to do so have gone nowhere, and searching StackOverflow has not helped me solve my problem. Loosely speaking, I'm looking to write something like:

data["MarketCapAtEndOfPeriod"] = data["EndofPeriodStockPrice"] * data["SharesOutstandingEndOfPeriod"]

Upvotes: 1

Views: 374

Answers (1)

andrewkittredge
andrewkittredge

Reputation: 850

market_cap = data.EndOfPeriodStockPrice * data.SharesOutstandingEndOfPeriod
market_cap_df = pd.concat(
    {"MarketCap": market_cap},
    names=["metric"],
    axis=1,
)

data = data.join(market_cap_df)

Upvotes: 3

Related Questions