0

I have two dataframes, dframe and dframp. Dframe has 301497 rows in it and dframep has 6080 rows in it. Both dataframes are show below. I want to merge the two such that when dframep is added to dframe the new dataframe puts Nans where dframep does not have any values for that date. I have tried this:

dfall = dframe.merge(dframep, on=['ID','date','instrument','close'], how='outer')

The two merge together but the result is 307577 rows e.g. for the dates that are not in dframep there are no Nan's.

Pulling my hair out so any help would be appreciated. Im guessing it has something to do with indexing and selecting the columns correctly.

enter image description here enter image description here

Thanks

enter image description here

Improve this question

1 Answer 1

Reset to default
1

I can't replicate your problem (nor understand it given your description), but try something like this ? :

dfall = pd.merge(dframe, dframep, how = 'left' ,left_on = ['ID','date','instrument','close'], right_on =['ID','date','instrument','close']

This will keep the rows of dframe, and bring the info that matches from dframp

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

11 Comments

Thanks, this creates dfall but has no rows of dframp in it which to me says maybe the date columns dont match each other for some reason.
with the above code, you are using 4 columns to perform the match, so it might mean that the closing price, or the ID or the instrument etc, don't match. Is there a unique ID to merge the 2 files? Assuming that the column 'ID' acts as an identifier, why don't you perform the merge just on 'ID'?
The matching column will be date, the IDs will also match as they are just a sequential number. Instrument will be different and so too will close in most cases.
Then try the above code with using only the 'date' and the 'id', in left_on and right_on. Let me know if it works.
Sorry I couldn't help anymore. If you were able to share your data I would give it a go.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.