pandas: 'join' failing to compile
<p>I'm having a bit of a problem trying to get my code to compile. Looks like the line with <code>main_df = df</code> is causing a failure, and I don't quite understand why.</p>

<p>Any help is much appreciated.</p>

<pre><code>import quandl
import pandas as pd

# API key was removed
api_key = 'X'
fiddy_states = pd.read_html('https://simple.wikipedia.org/wiki/List_of_U.S._states',flavor='html5lib')

main_df = pd.DataFrame()

for abbv in fiddy_states[0][0][1:]:
    query = "FMAC/HPI_"+str(abbv)
    df = quandl.get(query, authtoken=api_key)

    if main_df.empty:
        main_df = df
    else:
        main_df = main_df.join(df)

print(main_df.head())
</code></pre>

<p>I get this error:</p>

<blockquote>
  <p>Traceback (most recent call last):
    File "C:/Users/Dave/Documents/Python Files/helloworld.py", line 17, in 
      main_df = main_df.join(df)</p>
  
  <p>File "C:\Python35\lib\site-packages\pandas\core\frame.py", line 4385, in join
      rsuffix=rsuffix, sort=sort)</p>
  
  <p>File "C:\Python35\lib\site-packages\pandas\core\frame.py", line 4399, in _join_compat
      suffixes=(lsuffix, rsuffix), sort=sort)</p>
  
  <p>File "C:\Python35\lib\site-packages\pandas\tools\merge.py", line 39, in merge
      return op.get_result()</p>
  
  <p>File "C:\Python35\lib\site-packages\pandas\tools\merge.py", line 223, in get_result
      rdata.items, rsuf)</p>
  
  <p>File "C:\Python35\lib\site-packages\pandas\core\internals.py", line 4445, in items_overlap_with_suffix
      to_rename)
  ValueError: columns overlap but no suffix specified: Index(['Value'], dtype='object')</p>
</blockquote>

<p>You can pass a list of codes to the <code>quandl.get</code> function, then you get a dataframe back with data for each code in a column. Code: </p>

<pre><code>import quandl
import pandas as pd

fiddy_states = pd.read_html('https://simple.wikipedia.org/wiki/List_of_U.S._states', flavor='html5lib')
data = quandl.get(["FMAC/HPI_"+s for s in fiddy_states[0][0][1:]])
</code></pre>

python pandas join quandl