1

I am trying to fetch Adj Close column from multiple csv files.

Sample csv file (same content can be copied to aapl.csv, msft.csv and hcp.csv:

Date    Open    High    Low Close   Volume  Adj Close
10/14/08    116.26  116.4   103.14  104.08  70749800    104.08
10/13/08    104.55  110.53  101.02  110.26  54967000    110.26
10/10/08    85.7    100 85  96.8    79260700    96.8
10/9/08 93.35   95.8    86.6    88.74   57763700    88.74
10/8/08 85.91   96.33   85.68   89.79   78847900    89.79
10/7/08 100.48  101.5   88.95   89.16   67099000    89.16
10/6/08 91.96   98.78   87.54   98.14   75264900    98.14
10/3/08 104 106.5   94.65   97.07   81942800    97.07
10/2/08 108.01  108.79  100 100.1   57477300    100.1
10/1/08 111.92  112.36  107.39  109.12  46303000    109.12
9/30/08 108.25  115 106.3   113.66  58095800    113.66
9/29/08 119.62  119.68  100.59  105.26  93581400    105.26
9/26/08 124.91  129.8   123 128.24  40208700    128.24
9/25/08 129.8   134.79  128.52  131.93  35865600    131.93
9/24/08 127.27  130.95  125.15  128.71  37393400    128.71
9/23/08 131.85  135.8   126.66  126.84  45727300    126.84

My code is:

import pandas as pd
def test_run():
    start_date = '2008-10-01'
    end_date = '2008-10-09'
    dates = pd.date_range(start_date, end_date)
    df1 = pd.DataFrame(index=dates)
    dfSPY = pd.read_csv(
        'aapl.csv',
        index_col='Date',
        parse_dates=True,
        usecols=['Date', 'Adj Close'],
        na_values=['nan'])
    df1 = df1.join(dfSPY, how='inner')
    df1 = df1.rename(columns={'Adj Close':'SPY'})
    symbols = ['aapl', 'msft', 'hcp']
    for sym in symbols:
        df_temp = pd.read_csv(
            '{}.csv'.format(sym),
            index_col='Date',
            parse_dates=True,
            usecols=['Date', 'Adj Close'],
            na_values=['nan'])
        df_temp = df_temp.rename(columns={'Adj Close':sym})
        df1 = df1.join(df_temp, how='left')
    print(df1)
if __name__ == "__main__":
    test_run()

I get error when I run it:

Traceback (most recent call last):
  File "1.py", line 27, in <module>
    test_run()
  File "1.py", line 22, in test_run
    na_values=['nan'])
  File "/anaconda2/lib/python2.7/site-packages/pandas/io/parsers.py", line 678, in parser_f
    return _read(filepath_or_buffer, kwds)
  File "/anaconda2/lib/python2.7/site-packages/pandas/io/parsers.py", line 440, in _read
    parser = TextFileReader(filepath_or_buffer, **kwds)
  File "/anaconda2/lib/python2.7/site-packages/pandas/io/parsers.py", line 787, in __init__
    self._make_engine(self.engine)
  File "/anaconda2/lib/python2.7/site-packages/pandas/io/parsers.py", line 1014, in _make_engine
    self._engine = CParserWrapper(self.f, **self.options)
  File "/anaconda2/lib/python2.7/site-packages/pandas/io/parsers.py", line 1749, in __init__
    _validate_usecols_names(usecols, self.orig_names)
  File "/anaconda2/lib/python2.7/site-packages/pandas/io/parsers.py", line 1134, in _validate_usecols_names
    "columns expected but not found: {missing}".format(missing=missing)
ValueError: Usecols do not match columns, columns expected but not found: ['Adj Close']

I tried referring multiple links but couldn't figure out what I missing here. Thanks in advance.

2
  • 1
    dfSPY worked, but df_temp did not, despite the same structure. Perhaps one of your files does not have the Adj Close field, or it has white space around it that needs to be trimmed. Commented Oct 9, 2018 at 23:32
  • I dint notice there was an extra . in Adj Close column! Thanks for pointing it out. You can post your comment as an answer, I will accept it. Commented Oct 9, 2018 at 23:47

1 Answer 1

1

Given your stack trace of the error (thanks for posting the full trace), you notice that the function is called on line 27 (test_run() at the bottom of your code). The error, however, starts 5 lines up at line 22. This is the initial assignment of df_temp, which is the second time you call the pd.read_csv function. Because it worked the first time for dfSPY with virtually the same arguments, it means that there must be something different with the file format for one or more of the securities. Perhaps one of your files does not have the Adj Close field, or it has white space around it that needs to be trimmed.

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much @ Alexander! Appreciate your quick and crisp answer!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.