Back

Pandas.DataFrame from History method

We have had some questions regarding the structure of the pandas.DataFrame that we get from a History call:

df = self.History(["SPY","TQQQ"], 1000)

df is a multi-index pandas.DataFrame where the symbols ("SPY" and "TQQQ") are index 0 and date/time is index 1:

self.Log(str(hist.index))
self.Log(str(hist.columns))

For some applications, it is more convenient to have the symbols in the columns:

# Select the closing prices
prices = hist.close

# Get data frame for each symbol
spy = prices.loc['SPY']
tqqq = prices.loc['TQQQ']

# Concat the data frames and define the columns
prices = pd.concat([spy,tqqq],axis = 1)
prices.columns = self.stocks

Please checkout a working example below where we can find the application that needs a different data frame structure. 

Thanks to Yan Xiaowei for the proposed solution.
We welcome alternative solutions.
 

Update Backtest






The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by QuantConnect. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. QuantConnect makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances. All investments involve risk, including loss of principal. You should consult with an investment professional before making any investment decisions.



If your stock list is very long, you can do the conversion in two lines:

# prices is the historical DataFrame with symbols on the first-level index, and self.stocks is your symbol list.

prices = pd.concat([prices.loc[x] for x in self.stocks],axis = 1)
prices.columns = self.stocks

1

LukeI proposed yet another solution (my favorite so far!):

prices = hist.close.unstack(level=0)
0

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by QuantConnect. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. QuantConnect makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances. All investments involve risk, including loss of principal. You should consult with an investment professional before making any investment decisions.


Hi,

@Lukel and @Alexandre Catarino, Thank you for helping me.

Here's another problem I have:

import numpy as np
import pandas as pd

class MyAlgo(QCAlgorithm):

    def Initialize(self):
        self.SetCash(100000)
        self.SetStartDate(2017,7,1)
        self.SetEndDate(2017,9,1)
        # Add assets you'd like to see
        self.AddEquity("AAPL")
        self.AddEquity("AMZN")
        self.AddEquity("UPS")
        self.security_list = ["AAPL", "AMZN", "UPS"]

    def OnData(self, data):
        pass

    def rebalance(self):
        history = self.History(self.security_list, 10, Resolution.Daily)
        price_history = history["close"].unstack(level=0)

        self.Log(str(price_history))

 My logs showed as below:

2017-07-03 10:30:00 :symbol AAPL AMZN UPS time 2017-06-19 145.767708 995.13 110.021668 2017-06-20 144.442909 992.58 108.820602 2017-06-21 145.299546 1001.79 108.403703 2017-06-22 145.060485 1001.34 108.254810 2017-06-23 145.717904 1003.74 109.445950 2017-06-26 145.229820 993.65 110.190413 2017-06-27 143.217720 977.41 108.890085 2017-06-28 145.259703 990.03 109.237501 2017-06-29 145.259703 990.03 109.237501 2017-06-30 143.466742 968.00 109.763588

I was expecting something like below:

                  AAPL    AMZN    UPS
2017-8-1 00:00  100.00  200.00  150.0
2017-8-2 00:00  100.00  200.00  150.0
2017-8-3 00:00  100.00  200.00  150.0

What went wrong in my code?

Thanks. 

0

Hi ~ @Lukel and @Alexandre Catarino, Discard the above writing I posted. I downloaded the logs and now see the below. The logs in the baktest screen made me confused. Thanks a lot.

2017-07-03 10:30:00 symbol AAPL AMZN UPS
time
2017-06-19 145.767708 995.13 110.021668
2017-06-20 144.442909 992.58 108.820602
2017-06-21 145.299546 1001.79 108.403703
2017-06-22 145.060485 1001.34 108.254810
2017-06-23 145.717904 1003.74 109.445950
2017-06-26 145.229820 993.65 110.190413
2017-06-27 143.217720 977.41 108.890085
2017-06-28 145.259703 990.03 109.237501
2017-06-29 145.259703 990.03 109.237501
2017-06-30 143.466742 968.00 109.763588
0

Your question is not clear. Are you talking about the prices in QuantConnect not being round numbers?
In QuantConnect, prices are adjusted by default. while they are raw in Quantopian.
When we want to work with raw prices, we can set the data normalization mode: 

self.AddEquity("AAPL").SetDataNormalizationMode(DataNormalizationMode.Raw)
self.AddEquity("AMZN").SetDataNormalizationMode(DataNormalizationMode.Raw)
self.AddEquity("UPS").SetDataNormalizationMode(DataNormalizationMode.Raw)
0

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by QuantConnect. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. QuantConnect makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances. All investments involve risk, including loss of principal. You should consult with an investment professional before making any investment decisions.


@Alexandre Catarino, Thanks a lot :)

0

Update Backtest





0

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by QuantConnect. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. QuantConnect makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances. All investments involve risk, including loss of principal. You should consult with an investment professional before making any investment decisions.


Loading...

This discussion is closed