1

I am trying to get some data through the API from quandl but the date column doesn't seem to work the same level as the other columns. E.g. when I use the following code:

data = quandl.get("WIKI/KO", trim_start = "2000-12-12", trim_end =
"2014-12-30", authtoken=quandl.ApiConfig.api_key) 

print(data['Open'])

I end up with the below result

Date

2000-12-12 57.69

2000-12-13 57.75

2000-12-14 56.00

2000-12-15 55.00

2000-12-18 54.00

E.g. date appearing along with the 'Open' column. And when I try to directly include Date like this:

print(data[['Open','Date']]),

it says Date doesn't exist as a column. So I have two questions: (1) How do I make Date an actual column and (2) How do I select only the 'Open' column (and thus not the dates).

Thanks in advance

Community
  • 1
  • 1
MathiasRa
  • 825
  • 2
  • 12
  • 24

1 Answers1

2

Why print(data['Open']) show dates even though Date is not a column:

quandle.get returns a Pandas DataFrame, whose index is a DatetimeIndex. Thus, to access the dates you would use data.index instead of data['Date'].

(1) How do I make Date an actual column

If you wish to make the DatetimeIndex into a column, call reset_index:

data = data.reset_index()
print(data[['Open', 'Date']])

(2) How do I select only the 'Open' column (and thus not the dates)

To obtain a NumPy array of values without the index, use data['Open'].values. (All Pandas Series and DataFrames have Indexs (that's Pandas' raison d'être!), so the only way obtain the values without the index is to convert the Series or DataFrame to a different kind of object, like a NumPy array.)

unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • Thanks. Can I ask you one more question? I would like to insert the price into an SQL table and I have setted up the string correct. But the format still seems to not work. Receiving the following error "pyodbc.ProgrammingError: ('Invalid parameter type. param-index=0 param-type=numpy.ndarray', 'HY105')". How would I have to "manipulate" price so I can insert it into an SQL table? – MathiasRa Oct 16 '17 at 21:33
  • That looks like [this error](https://stackoverflow.com/q/41973933/190597) (found by googling "pyodbc NumPy array Invalid parameter type"). To avoid the error, try converting the NumPy array to a Python list by calling its [`tolist()` method](https://docs.scipy.org/doc/numpy-1.13.0/reference/generated/numpy.ndarray.tolist.html): `data['Open'].values.tolist()`. If that does not solve the problem, please one a new question which shows the table schema, the SQL, and python code you are using. – unutbu Oct 16 '17 at 22:12
  • By comparison with the [other question](https://stackoverflow.com/q/41973933/190597) it appears you might be trying to insert the entire array into a single table record. To resolve that issue -- if it really is the issue -- it would be helpful to see your SQL and python code first. – unutbu Oct 16 '17 at 22:19
  • Not sure how to format it, but I only have one value using the following code: "price = sta[:1] print(price) cur.execute ("Insert into PG (price) values (?)", price)". Also tried "sta = data['Open'].values" but that didn't work either. If I just set price = 55 (as an example), it works. – MathiasRa Oct 16 '17 at 22:47
  • I'm not entirely sure I understand what the situation is. (Such as the value of `price` and `sta` when you call `cur.execute`. And are you getting the same error message with everything you've tried!?.) Please post a **new question** with all the details. – unutbu Oct 17 '17 at 01:13