Extending a subset table in pandas

Question

I want to test some trading, math, python, through yahoo finance. I can retrieve some data. I want to add some calculations based on open and closing prices. I can get the data using the below code.

import numpy as np
import math
import pandas as pd
import yfinance as yf
ticker = 'EURUSD=X,BTC-USD'
data = yf.download(tickers= ticker, period = '30d', interval = '1h')
closedata = data[['Open','Close']]
closedata

This results in the open and close data per ticker:

                    Open                    Close
                    BTC-USD    EURUSD=X     BTC-USD  EURUSD=X
2022-09-09 00:00:00 NaN        1.000600     NaN      1.001202
2022-09-09 01:00:00 NaN        1.001202     NaN      1.005126
2022-09-09 02:00:00 NaN        1.004823     NaN      1.005328
2022-09-09 03:00:00 NaN        1.005530     NaN      1.007151
2022-09-09 04:00:00 NaN        1.007049     NaN      1.007151
... ... ... ... ...
2022-10-20 11:00:00 19215.98   0.978474     19216.63 0.980488
2022-10-20 12:00:00 19213.79   0.980777     19222.87 0.981643
2022-10-20 12:20:00 19224.61   NaN          19224.61 NaN

I'd like to add 3 formula columns per price ticker (BTCx and Eurx)
To get a table structure alike the following table header from here :

newtable = BTC-USD open|EuroUSD=X open|BTC-USD close|euroSD=X close|BTC-1|BTC2|BTC3|Eur1|Eur2|Eur3

How to achieve that?
Or maybe it's wrong and I'm thinking too flat and simple here.
Panda seems quite capable and maybe more structured like the below is possible? (extending it)

 Open      Close     Results1  results2  results3
 [tickers],[tickers],[tickers],[tickers],[tickers]

If the above is possible it's even nicer, though how to add the result columns then?

Does this answer your question? [How to deal with multi-level column names downloaded with yfinance](https://stackoverflow.com/questions/63107594/how-to-deal-with-multi-level-column-names-downloaded-with-yfinance) — ozacha, Oct 20 '22 at 14:56
tried it but no, it does do some manupilation but not as in my Q — Peter, Oct 20 '22 at 20:28

KarelZe · Accepted Answer · 2022-10-27T17:56:40.000

What you want to do is flatten a MultiIndex. To obtain the naming you want, given by BTC-USD open|EuroUSD=X open|BTC-USD close|euroSD=X close|... you can do:

closedata.columns = (
    closedata.columns.get_level_values(1) + " " + closedata.columns.get_level_values(0)
)

Output:

                           BTC-USD Open  EURUSD=X Open  BTC-USD Close  \
2022-09-15 23:00:00+00:00           NaN       0.999600            NaN   
2022-09-16 00:00:00+00:00           NaN       0.999001            NaN   
2022-09-16 01:00:00+00:00           NaN       1.000901            NaN   

                           EURUSD=X Close  
2022-09-15 23:00:00+00:00        0.999001  
2022-09-16 00:00:00+00:00        1.000901  
2022-09-16 01:00:00+00:00        1.000100

Pandas also provides a method MultiIndex.to_flat_index(). However, you can not specify the order with this approach.

To manually add a column to a data frame with a MultiIndex you can do:

closedata.loc[('MSFT', 'Close')] = 100

or assign any series.

Extending a subset table in pandas

1 Answers1