Panda dataframe index value as object instead of datetime

Question

I have downloaded some share price data via an API (from Alphavantage):

response = requests.get("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=MSFT&apikey=XXX") data = response.json() df = pd.DataFrame.from_dict(data["Time Series (Daily)"], orient='index')

and converted columns to numeric (df.apply(pd.to_numeric))

This is output in Jupyter - very standard matrix of dates and share prices.

I can't figure out how to manipulate the data (such as multiply df.loc[df['1. open'] > 93, '1. open'] *= 2) which is normally easy. I think there is something wrong with the index data. I have tried df.index = pd.to_datetime(df.index) to get it to timedate format and also tried renaming index. Also, the index column doesn't download if I try to move data frame into a csv file.

I think the issue is that the index data is still a string, but don't understand why it doesn't become datetime with code above.

Error message I get is ('>' not supported between instances of 'str' and 'int') but solutions from other questions don't work either.

Sorry for my long winded question and many thanks for any help!

Note that pd.to_datetime can often fail silently

Josh Friedlander
– Josh Friedlander

2018-09-02 07:51:16 +00:00
Commented Sep 2, 2018 at 7:51 — Josh Friedlander
– Josh Friedlander, Commented Sep 2, 2018 at 7:51

fuglede · Accepted Answer · 2018-09-02 08:08:51Z

1

The name of the column is not 'open' but '1. open'. (Edit: Okay, the question was edited to take care of this, but there's still an 'open' remaining in there.)

Changing this, your example will fail with the error

TypeError: '>' not supported between instances of 'str' and 'int'

which indicates that the columns do not contain numbers but strings (which you could also find through df.info()). You can take care of that by using df.astype(float), or by specifying the desired types when creating the DataFrame, effectively using pd.DataFrame.from_dict(data['Time Series (Daily)'], orient='index', dtype=float).

In the first case,

df = df.astype(float)
df.loc[df['1. open'] > 93, '1. open'] *= 2

would achieve what you want.

edited Sep 2, 2018 at 8:08

answered Sep 2, 2018 at 7:49

fuglede

18.3k3 gold badges62 silver badges107 bronze badges

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

2 Comments

Larsk Over a year ago

thanks a lot - very helpful. you are right re open vs 1. open. After posting question I realised that I had not changed code and amended, but only did in one place. dtype=float worked a charm. Thanks

fuglede Over a year ago

Great! If you found the answer helpful, you can mark it as accepted.

Collectives™ on Stack Overflow

Panda dataframe index value as object instead of datetime

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related