NaN values when new column added to pandas DataFrame

Question

I'm trying to generate a new column in a pandas DataFrame that equals values in another pandas DataFrame. When I attempt to create the new column I just get NaNs for the new column values.

First I use an API call to get some data, and the 'mydata' DataFrame is one column of data indexed by dates

mydata = Quandl.get(["YAHOO/INDEX_MXX.4"],                     trim_start="2001-04-01", trim_end="2014-03-31",                     collapse="monthly") 

The next DataFrame I get from a CSV with the following code, and it contains many columns of data with the same number of rows as 'mydata'

DWDATA = pandas.DataFrame.from_csv("filename",                                    header=0,                                    sep=',',                                    index_col=0,                                    parse_dates=True,                                    infer_datetime_format=True) 

I then try to generate the new column like this:

DWDATA['MXX'] = mydata.iloc[:,0] 

Again, I just get NaN values. Can someone help me understand why it's doing this and how to resolve? From what I've read it looks like I might have something wrong with my indexes. The indexes are dates in each DataFrame, but 'mydata' have end-of-month dates while 'DWDATA' has beginning-of-month dates.

Answer 1

Because the indexes are not exactly equal, NaNs will result. Either one or both of the indexes must be changed to match. Example:

mydata = mydata.set_index(DWDATA.index) 

The above will change the index of the 'mydata' DataFrame to match the index of the 'DWDATA' DataFrame.

Since the number of rows are exactly equal for the two DataFrames, you can also just pass the values of 'mydata' to the new 'DWDATA' column:

DWDATA['MXX'] = mydata.iloc[:,0].values