Pandas how to outer merge on datetime column correctly

286
December 16, 2017, at 11:05 AM

I have two dataframes:

resetted.head()
WeightedSentiment   Popularity  Datetime
0   0   2   2012-11-22 11:00:00
1   0   2   2012-11-22 11:30:00
2   0   4   2012-11-22 12:00:00
3   0   2   2012-11-22 15:00:00
4   0   2   2012-11-22 15:30:00
prices.head()
Open    High    Low     Close   Volume  Datetime
46623   236.9392    238.6095    236.5392    238.2094    315177  2012-11-23 10:00:00
46624   238.1894    238.3095    236.7492    237.4993    122132  2012-11-23 10:30:00
46625   237.4793    238.2595    237.1393    238.2094    144457  2012-11-23 11:00:00
46626   238.2094    238.9196    238.1694    238.7695    131733  2012-11-23 11:30:00
46627   238.7695    239.1396    237.9394    238.9496    150386  2012-11-23 12:00:00

And I tried to outer join these two dataframes, but by using pd.merge(prices,resetted,how='outer',on='Datetime') The result is very strange and seems wrong:

Open    High    Low     Close   Volume  Datetime    WeightedSentiment   Popularity
0   236.9392    238.6095    236.5392    238.2094    315177.0    2012-11-23 10:00:00     0.0     20.0
1   238.1894    238.3095    236.7492    237.4993    122132.0    2012-11-23 10:30:00     0.0     12.0
2   237.4793    238.2595    237.1393    238.2094    144457.0    2012-11-23 11:00:00     0.0     12.0
3   238.2094    238.9196    238.1694    238.7695    131733.0    2012-11-23 11:30:00     0.0     2.0
4   238.7695    239.1396    237.9394    238.9496    150386.0    2012-11-23 12:00:00     0.0     12.0
5   238.7995    242.0301    238.0394    241.5900    1183601.0   2012-11-23 12:30:00     0.0     16.0

If I swap the two dataframes' position in the merge function, there will be NaN at head as expected, but the other rows are wrong. I have setup a demo notebook on github.

I'm on pandas 0.21.0

READ ALSO
SMTPAuthenticationError; Username and Password not accepted

SMTPAuthenticationError; Username and Password not accepted

I'm following along with this book and in chapter 18 part of the current assignment involves sending an email via DjangoI have a yearly domain I use for testing, and rather than pay an additional 5$ a month I figured I'd just have the emails forwarded...

352
Iterable Unpacking Evaluation Order

Iterable Unpacking Evaluation Order

I recently answered a question where a user was having trouble because they were appending a multi-dimensional array to another array, and it was brought to my attention in my answer that it is possible to use iterable unpacking to populate an x and y value and assign...

268
Integration of Datastax(Spark, Cassandra, Solr) with a Django website

Integration of Datastax(Spark, Cassandra, Solr) with a Django website

I am looking for options for Integration of Datastax(Spark, Cassandra, Solr) with a Django(python) websiteHas anyone done this implementation and if possible can you please let me know the steps?

222
Cannot access parent for loop from nested for loop

Cannot access parent for loop from nested for loop

I'm trying to compare the value of an index between two arrays of dictionariesTo accomplish this I'm using a nested for loop

158