ValueError: columns overlap but no suffix specified: Index(['Adj. Close'], dtype='object')

Question

I'm trying to build an Algotrader but keep getting the above error (please see subject header). I've tried various workarounds, having researched the issue on Stack Overflow, but I've not been able to resolve the issue. Any help would be much appreciated. I've pasted my code below:

import pandas as pd
import matplotlib.pyplot as plt
import quandl
 
 
# The quandl module allows us to obtain the stock data we require without downloading the corresponding CSV files.
aapl = quandl.get('WIKI/AAPL', start_date='2012-10-01', end_date='2014-01-01')
 
# We can now use .head, .tail methods to seize first and last five elements of the data. Using such methods will allow
# you to see that the data is stepped on a monthly basis atm.
 
data_frame = pd.read_csv('WIKI-FB.csv')
 
# Once you've created the data-frame object, you can extrapolate data from various segmented timelines using slicing.
# data_frame.index will give you the start, range and step of the relevant data set.
 
# At the moment, the index for the dataframe is measured in integers whereas we need to convert it into date-based
# values, thus allowing chronological plotting of the data. parse_dates below enables the conversion. Notice we then sort.
 
df_with_date_index = pd.read_csv('WIKI-FB.csv', index_col= 'Date', parse_dates= True)
df_with_date_index = df_with_date_index.sort_index()
 
df_with_date_index_1 = data_frame.set_index('Date').sort_index()
 
df_with_date_index_1.index = pd.to_datetime(df_with_date_index_1.index)
 
# Once we've created an indexable set of x-y values from the segment of data, we can use the plot method to display
# in our GUI, as in the commented-out code just below.
 
df_with_date_index_1['Adj. Close'].plot()
 
#plt.show()
 
# We now want to create code to operate with multiple stocks. First we specify a range of dates using pandas datetime
# index and assign them to a new empty dataframe. We can then apply this dataframe to any number of given stocks.
 
date_range = pd.date_range('2014-01-03', '2014-04-01')
 
date_range
 
 
df_multi_stock = pd.DataFrame(index=date_range)
 
 
df_fb = pd.read_csv('WIKI-FB.csv', index_col='Date', parse_dates=True, usecols= ['Date', 'Adj. Close'])
 
# Now we combine our two sets of data. We use the how parameter below to tell our program to ignore non-numeric values.
# Inner join is the most common type of join you’ll be working with. It returns a dataframe with only those rows that have
# common characteristics.
 
 
df_multi_stock = df_multi_stock.join(df_fb)
 
#df_multi_stock.head()
 
df_multi_stock = pd.DataFrame(index=date_range)
 
# The addition of the how='inner' method results in the exclusion of non-trading days, which would otherwise produce
# sequences of null values.
df_multi_stock = df_multi_stock.join(df_fb, how='inner')
 
 
df_multi_stock = df_multi_stock.rename(columns={'Adj.Close':'FB'})
 
# plt.show()
# Now we repeat the above steps for our AAPL stocks.
 
df_aapl = pd.read_csv('WIKI-AAPL.csv', index_col='Date', parse_dates=True, usecols= ['Date', 'Adj. Close'])
 
 
 
df_multi_stock = df_multi_stock.rename(columns={'Adj.Close':'AAPL'})
 
df_multi_stock = df_multi_stock.join(df_aapl, how='inner')
 
 
df_multi_stock[['FB', 'AAPL']].plot()
 
plt.show()

Answer 1

I think the reason is you have stored the aapl.csv file in a dataframe named df_aapl and then you have tried to rename the column name for your original df_multi_stock's Adj. Close to AAPL when you have already done that with the name FB. That means currently there is no column with the title Adj. Close. You just have to join the aapl ticker details to df_multi_stock before you rename. Since I do not have access to your csv files nor the full error message, it would be difficult to pin point the problem.

Another approach:

1. Use Quandl to fetch data:

Since you have already imported the quandl module, you can have a better approach since it enables us to obtain the stock data directly without downloading the corresponding CSV files. The entire approach appears to be difficult to read. Therefore, I think you can use quandl.get and pass the ticker symbols as a list using the following line:

data = quandl.get(['WIKI/FB','WIKI/AAPL'])

This would display a combined dataframe of both FB and AAPL in a large dataset. The original quandl.get already sends you the data in the format you need (datetime format for Date column which is also set as index) you can skip the lines for adjusting your csv files. More information on how to retrieve your data can be found on the Quandl Github

2. Filter columns:

Now you can also filter out the columns you need for the analysis and store the original data as follows:

data = data[['WIKI/FB - Adj. Close','WIKI/AAPL - Adj. Close']]

3. Locate and plot:

Now that you already have both the ticker symbols in a single dataframe, you can locate the daterange you need and rename the column titles easily.

data = data.loc['2014-01-03':'2014-04-01']

data = data.rename(columns={'WIKI/FB - Adj. Close':'FB',
                           'WIKI/AAPL - Adj. Close': 'AAPL'})

Finally, you can visualise them using .plot()

Complete code

import pandas as pd
import matplotlib.pyplot as plt
import quandl

data = quandl.get(['WIKI/FB','WIKI/AAPL'])
data = data[['WIKI/FB - Adj. Close','WIKI/AAPL - Adj. Close']]

data = data.loc['2014-01-03':'2014-04-01']
data = data.rename(columns={'WIKI/FB - Adj. Close':'FB',
                           'WIKI/AAPL - Adj. Close': 'AAPL'})

data[['FB', 'AAPL']].plot()