CODEWITHSUNDEEP
pythonpandastime-series
simar
simar

Reputation: 605

Joining two time series using pandas

I have two time series. Both series have same columns. They have some common dates. One of columns of both series is Close representing closing price. I want to inner join these closing prices. I have used following code. But it is generating error in last line.

dow=pd.read_csv("dow.csv",index_col="Date")
sensex=pd.read_csv("sensex.csv",index_col="Date")
dow_close=dow["Close"]
sensex_close=sensex["Close"]
dow_close.index=pd.to_datetime(dow_close.index)
sensex_close.index=pd.to_datetime(sensex_close.index)
dow_sensex_close=dow_close.join(sensex_close,how='inner')

Upvotes: 0

Views: 69

Answers (2)

jeetkamal
jeetkamal

Reputation: 409

The main problem is your method of sub-setting. Use .loc function for sub-setting. You will get new data frame as data frame not series. Then use .join function along with ls and rs suffix.

dow=pd.read_csv("dow.csv",index_col="Date")
sensex=pd.read_csv("sensex.csv",index_col="Date")
dow_close=dow.loc[:,["Close"]]
sensex_close=sensex.loc[:,["Close"]]
dow_close.index=pd.to_datetime(dow_close.index)
sensex_close.index=pd.to_datetime(sensex_close.index)
dow_sensex_close = dow_close.join(sensex_close,how='inner',lsuffix='_dow', rsuffix='_sensex')
dow_sensex_close.head()

Upvotes: 1

Valdi_Bo
Valdi_Bo

Reputation: 30971

When you invoke join, the other object can be a Series, but the self object must be just a DataFrame.

So the first correction is to run:

dow_close.to_frame().join(sensex_close, ...)

But it is not enough, since you have overlapping columns. To avoid another exception (columns overlap but no suffix specified), you have to specify at least one of lsuffix and rsuffix.

My suggestion is to specify both of them:

dow_sensex_close = dow_close.to_frame().join(sensex_close,
    how='inner', lsuffix='_dow', rsuffix='_sensex')

Upvotes: 1

Related Questions