CODEWITHSUNDEEP
pythonpandasdataframe
Supawich zaO
Supawich zaO

Reputation: 51

How to combine two dataframe with different sizes of row?

So when I tried to put different size dataframe, it always results in an error.

import pandas as pd
from pandas import Series,DataFrame
import numpy as np

# For reading stock data from yahoo
import pandas_datareader as web

# For time stamps
from datetime import datetime

closing_df = web.DataReader(['AAPL','GOOG','MSFT','AMZN'],'yahoo',start,end)['Adj Close']
#when I do this, it is fine since the size are the same whereas
closing_df = web.DataReader(['AAPL','GOOG','MSFT','AMZN','BTC-USD'],'yahoo',start,end)['Adj Close']
#I always get this error
#ValueError: Index contains duplicate entries, cannot reshape

I tried to have two dataframe, one for the tech company and one for the BTC-USD But when I use join,concat or merge, none it seems to work I want to get all the joint date for both dataset and put it together e.g. if both dataframe has 2010-11-30 then it will be in the dataframe but if only one dataframe contain that date then it will ignore or do not put it in the joint dataframe. Many Thank

Upvotes: 1

Views: 519

Answers (1)

FBruzzesi
FBruzzesi

Reputation: 6475

One workaround is the following

tech = web.DataReader(['AAPL','GOOG','MSFT','AMZN'],'yahoo', start, end)['Adj Close']
btc = web.DataReader('BTC-USD','yahoo', start, end)['Adj Close']

result_df = pd.merge(tech, btc, left_index=True, right_index=True).rename(columns={'Adj Close': 'BTC'})

However by checking single DataFrames it looks like that while tech only has financial days, BTC also has weekends and holidays, thus they retrieve different dates overall. With the above join you will lose BTC data. Maybe it can be better to outer join and then fill down values:

result_df = pd.merge(tech, btc, left_index=True, right_index=True,
                     how='outer').rename(columns={'Adj Close': 'BTC'})
result_df.fillna(method='ffill', inplace=True)

Upvotes: 1

Related Questions