Search code examples
pythonpandasyfinance

How to convert a date index to string column in pandas

I am trying to merge an Excel table which I made with data gathered from YFinance. I have tried merging them already but apparently you cannot merge on an index, here is what I tried:

import yfinance
import pandas as pd
Dates=pd.read_csv('r/Example.csv)
Dates.columns = ['Start','End']
Stock = yfinance.Ticker('MSFT')
StockData = Stock.history(period='max')
Dates.merge(StockData, left_on='Start', right_on='Date')

It throws me this error:

ValueError: You are trying to merge on object and datetime64[ns] columns. If you wish to proceed you should use pd.concat

I tried pd.concatand it is not what I need, I need to link up the dates in my CSV with the dates on the yfinance. yFinance automatically makes the index of their history dataframe a 'Date' index, I need to make this index into a column, or at least a string, which I do not know how to do.

Solution

  • You have a couple options here:

    1. To make the index into a column you can use StockData.reset_index()
    2. You can merge on the index using Dates.merge(StockData, left_on='Start', right_index=True) pandas documentation

    You will need them to be the same data type. For that it is likely better to convert the dates in Dates to datetime using Dates['Start'] = pd.to_datetime(Dates['Start']).