Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Giving index when joining dataframes
#1
hi all,

I copy the code from Sentdex (https://youtu.be/j0zW_KXyQJ4) to produce the output.

I got the below error:
ValueError: columns overlap but no suffix specified: Index(['Unnamed: 0'], dtype='object')
Any suggestion to change my code?

Here is my code:
import bs4 as bs
import datetime as dt
import os
import pandas as pd
import pandas_datareader.data as web
import pickle
import requests

def save_sp500_tickers():
    resp = requests.get('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')
    soup = bs.BeautifulSoup(resp.text,'lxml')
    table = soup.find('table', {'class':'wikitable'})
    tickers = []
    for row in table.findAll('tr')[1:]:
        ticker = row.findAll('td')[0].text.replace('.','-')
        ticker = ticker[:-1]
        tickers.append(ticker)
        
    with open("sp500tickers.pickle", "wb") as f:
        pickle.dump(tickers, f)
    
    print(tickers)
    return(tickers)
save_sp500_tickers()

def get_data_from_yahoo(reload_sp500=False):
    if reload_sp500:
        tickers = save_sp500_tickers()
    else:
        with open("sp500tickers.pickle", "rb") as f:
            tickers = pickle.load(f)
    if not os.path.exists('stock_dfs'):
        os.makedirs('stock_dfs')
    
    start = dt.datetime(2019,6,8)
    end = dt.datetime.now()
    
    for ticker in tickers:
        print(ticker)
        if not os.path.exists('stock_dfs/{}.csv'.format(ticker)):
            df = web.DataReader(ticker, 'yahoo', start, end)
            df.reset_index(inplace=True)
            df.to_csv('stock_dfs/{}.csv'.format(ticker))
        else:
            print('Already have{}'.format(ticker))

def compile_data():
    with open("sp500tickers.pickle","rb") as f:
        tickers = pickle.load(f)
    
    main_df = pd.DataFrame()

    for count,ticker in enumerate(tickers):
        df = pd.read_csv('stock_dfs/{}.csv'.format(ticker))
        df.set_index('Date',inplace=True)
        
        df.rename(columns = {'Adj Close':ticker}, inplace=True)
        df.drop(['Open','High','Low','Close','Volume'], 1, inplace=True)

        if main_df.empty:
            main_df = df
        else:
            main_df = main_df.join(df, how='outer')
        
        if count & 10 ==0:
            print(count)
        
        print(main_df.head())
        main_df.to_csv('sp500_joined_closed.csv')
    
compile_data()
I run in debug mode in vscode and found in second iteration in the count,ticker enumerate for loop.

Here is what my dataframes look like:
Input dataframe
İmage

Output dataframe: get error in 2nd iteration for ABT.csv
İmage
Quote
#2
i have got the problem solved. i have added index_col when reading csv file.
from
df = pd.read_csv('stock_dfs/{}.csv'.format(ticker))
to
df = pd.read_csv('stock_dfs/{}.csv'.format(ticker), index_col=0)
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Could anyone help me get the jaccard distance between my dataframes please? :) a_real_phoenix 0 247 Jun-27-2019, 06:01 PM
Last Post: a_real_phoenix
  Two dataframes merged Ecniv 10 497 Jun-16-2019, 09:10 PM
Last Post: Ecniv
  Statistical analysis of two dataframes zhl 1 430 Jun-11-2019, 07:26 PM
Last Post: Ecniv
  Interpolate using multiple dataframes Lastwizzle 0 189 May-29-2019, 05:32 PM
Last Post: Lastwizzle
  finding index to given value from dataset gamma 3 205 May-26-2019, 06:26 PM
Last Post: heiner55
  Sum product multiple Dataframes based on column headers. Lastwizzle 0 212 May-21-2019, 04:05 PM
Last Post: Lastwizzle
  Key length (2) exceeds index depth (1) igodspeed 4 378 Mar-14-2019, 12:31 AM
Last Post: scidam
  Select specific index for plot rundesquadrat 1 396 Dec-26-2018, 07:47 AM
Last Post: scidam
  python odeint keeps giving me size of array error kiyoshi7 1 558 Nov-01-2018, 02:03 AM
Last Post: j.crater
  Why can't I merge pandas dataframes learnpython2018 2 622 Sep-23-2018, 05:53 PM
Last Post: learnpython2018

Forum Jump:


Users browsing this thread: 1 Guest(s)