Why is my data except for the first column converting to NaN?

Question

I've tried various variations of code including trying to reset_index() because maybe there is a multiindex that is causing the error. If you see the print out below you will notice the 'Symbol' index. I'm not sure how to delete that and whether that will fix the error or not.

    tickers = ['AAPL', 'BAC']
    prices_list = []
    for ticker in tickers:
        try:
            prices = dr.DataReader(ticker,'morningstar','01/01/2017')['Close']
            prices = pd.DataFrame(prices)
            prices.columns = [ticker]
            prices_list.append(prices)
        except:
            pass
    prices_df = pd.concat(prices_list,axis=1,copy=False)
    #prices_df.sort_index(inplace=True)
    print(prices_df.head())

Results--->

                     AAPL  BAC
Symbol Date
AAPL   2017-01-02  115.82  NaN
       2017-01-03  116.15  NaN
       2017-01-04  116.02  NaN
       2017-01-05  116.61  NaN
       2017-01-06  117.91  NaN

I'm actually using this basic code to fix this error. I'm pulling data online for the S&P 500.

I think the error is occurring during concatenation, but I'm not sure how to fix that. If you print out prices_list the data is a list of dataframes that includes all the numbers so it's not a problem with the source.

Please read [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). — Mr. T, Feb 16 '18 at 10:23

score 0 · Answer 1 · answered Feb 16 '18 at 10:49

I believe the problem is that each of your DataFrames has a MultiIndex which contains the Symbol name, so that when you attempt to merge the data, something like the following happens (I've used two very small example DataFrames to illustrate):

df1

                     AAPL
Symbol Date              
AAPL   2017-01-02  115.82
       2017-01-03  116.15

df2

                  BAC
Symbol Date              
BAC    2017-01-02  123.82
       2017-01-03  124.15

Attempting to merge this data results in the following:

prices_df = pd.concat([df1, df2],axis=1,copy=False)

                     AAPL     BAC
Symbol Date                      
AAPL   2017-01-02  115.82     NaN
       2017-01-03  116.15     NaN
BAC    2017-01-02     NaN  123.82
       2017-01-03     NaN  124.15

Since Symbol is already represented as a column name, you can simply remove this level from the index of your individual DataFrames, then you should be able to merge as intended:

df1.index = df1.index.droplevel(0)

              AAPL
Date              
2017-01-02  115.82
2017-01-03  116.15

df2.index = df2.index.droplevel(0)

               BAC
Date              
2017-01-02  123.82
2017-01-03  124.15

prices_df = pd.concat([df1, df2],axis=1,copy=False)

              AAPL     BAC
Date                      
2017-01-02  115.82  123.82
2017-01-03  116.15  124.15

Why is my data except for the first column converting to NaN?

1 Answers1