1

I have two dataframes. One has the workdays and the stockprice for the Apple-stock. The other one, holds quarterly data on the EPS. However, the list of dates differ, but are in cronological order. I want add the cronological values of the eps frame to the existing price dataframe.

    date    close
0   2020-07-06  373.85
1   2020-07-02  364.11
2   2020-07-01  364.11
3   2020-06-30  364.80
4   2020-06-29  361.78
...     ...     ...
9969    1980-12-18  0.48
9970    1980-12-17  0.46
9971    1980-12-16  0.45
9972    1980-12-15  0.49
9973    1980-12-12  0.51

EPS:

    date        eps
0   2020-03-28  2.59
1   2019-12-28  5.04
2   2019-09-28  3.05
3   2019-06-29  2.22
4   2019-03-30  2.48
...     ...     ...
71  2002-06-29  0.09
72  2002-03-30  0.11
73  2001-12-29  0.11
74  2001-09-29  -0.11
75  2001-06-30  0.17

So my result should look something like this:

            close   eps
date
...         
2020-04-01  240.91  NaN
2020-03-31  254.29  NaN
2020-03-30  254.81  NaN
2020-03-28     NaN  2.59
2020-03-27  247.74  NaN
2020-03-26  258.44  NaN

Notice the value "2020-03-28", which previously only existed in the eps frame, and sits now neatly were it belongs.

However, I can't get it to work. First i thought there must be a simple join, merge or concat that has this function and fits the data right were it belongs, in cronological order, but so far, I couldn't find it.

My failed attempts:

  1. pd.concat([df, eps], axis=0, sort=True) - does simply append the two dataframes
  2. pd.merge_ordered(df, eps, fill_method="ffill", left_by="date") - Simply ignores the eps dates

The goal is to plot this Dataframe with two graphs - One the stock price, and the other one with the eps data.

Martin Müsli
  • 1,031
  • 3
  • 14
  • 26

2 Answers2

1

I think you need:

pd.concat([df.set_index('date'), eps.set_index('date')]).sort_index(ascending=False)
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 1
    NOt quite. For some reason, my new dataframe isn't sorted by index. I just appended the `.sort_index()` to my new dataframe – Martin Müsli Jul 07 '20 at 12:11
0

You can simply sort the concatenated dataframe afterwards by index. Thanks to @jezrael for the tip!

pd.concat([df.set_index('date'), eps.set_index('date')]).sort_index(ascending=False)

For some reason, the sort argument in the concat function doesn't sort my dataframe.

Martin Müsli
  • 1,031
  • 3
  • 14
  • 26
  • for me it sorted it if I changed the sort_index(accending=False) to sort_index(accending=True) – Reut Feb 16 '21 at 17:34