CODEWITHSUNDEEP
pythonpython-3.xpandas
Sai Kumar
Sai Kumar

Reputation: 715

merge different dataframes and add other columns from base dataframe

I am trying to merge different dataframes.

Assume these two melted dataframes.

melted_dfs[0]=

        Date      Code       delta_7
0   2014-04-01     GWA        0.08
1   2014-04-02     TVV       -0.98

melted_dfs[1] =

       Date       Code       delta_14
0   2014-04-01     GWA         nan
1   2014-04-02     XRP       -1.02

I am looking to merge both the above dataframes along with volume& GR columns from my base dataframe.

base_df = 

     Date         Code      Volume       GR
0   2014-04-01     XRP    74,776.48    482.76
1   2014-04-02     TRR   114,052.96    460.19

I tried to use Python's built in reduce function by converting all dataframes into a list but it throws a error 

abt = reduce(lambda x,y: pd.merge(x,y,on=['Date', 'Code']), feature_dfs)
# feature_dfs is a list which contains all the above dfs.

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

Any help is appreciated. Thanks!

Upvotes: 0

Views: 137

Answers (3)

it's-yer-boy-chet
it's-yer-boy-chet

Reputation: 2017

Try printing the datatypes and index of the DataFrames:

for i, df in enumerate(feature_dfs):
    print 'DataFrame index: {}'.format(str(i))
    print df.info()
    print '-'*72

I would assume that one of the DataFrames is going to show a line like:

Date    X non-null object

Indicating that you don't have a datetime datatype for Date. That DataFrame is the culprit and you will have the index from the print above.

Upvotes: 1

BENY
BENY

Reputation: 323316

This should work , as it stated some of df's date is not datetime format 

feature_dfs=[x.assign(Date=pd.to_datetime(x['Date'])) for x in feature_dfs]

abt = reduce(lambda x,y: pd.merge(x,y,on=['Date', 'Code']), feature_dfs)

Upvotes: 1

ChrisD
ChrisD

Reputation: 967

One of your dataframes in feature_dfs probably has a non-datetime dtype.

Upvotes: 1

Related Questions