[FIXED] Merging two series with alternating dates into one grouped Pandas dataframe

Issue

Given are two series, like this:

#period1
DATE
2020-06-22    310.62
2020-06-26    300.05
2020-09-23    322.64
2020-10-30    326.54

#period2
DATE
2020-06-23    312.05
2020-09-02    357.70
2020-10-12    352.43
2021-01-25    384.39

These two series are correlated to each other, i.e. they each mark either the beginning or the end of a date period. The first series marks the end of a period1 period, the second series marks the end of period2 period. The end of a period2 period is at the same time also the start of a period1 period, and vice versa.

I've been looking for a way to aggregate these periods as date ranges, but apparently this is not easily possible with Pandas dataframes. Suggestions extremely welcome.

In the easiest case, the output layout should reflect the end dates of periods, which period type it was, and the amount of change between start and stop of the period.

Explicit output:

DATE          CHG       PERIOD
2020-06-22    NaN       1
2020-06-23    1.43      2
2020-06-26    12.0      1
2020-09-02    57.65     2
2020-09-23    35.06     1       
2020-10-12    29.79     2
2020-10-30    25.89     1
2021-01-25    57.85     2

However, if there is any possibility of actually grouping by a date range consisting of start AND stop date, that would be much more favorable

Thank you!

Solution

p1 = pd.DataFrame(data={'Date': ['2020-06-22', '2020-06-26', '2020-09-23', '2020-10-30'], 'val':[310.62, 300.05, 322.64, 326.54]})
p2 = pd.DataFrame(data={'Date': ['2020-06-23', '2020-09-02', '2020-10-12', '2021-01-25'], 'val':[312.05, 357.7, 352.43, 384.39]})

p1['period'] = 1
p2['period'] = 2
df = p1.append(p2).sort_values('Date').reset_index(drop=True)
df['CHG'] = abs(df['val'].diff(periods=1))
df.drop('val', axis=1)

Output:

    Date     period CHG
0   2020-06-22  1   NaN
1   2020-06-23  2   1.43
2   2020-06-26  1   12.00
3   2020-09-02  2   57.65
4   2020-09-23  1   35.06
5   2020-10-12  2   29.79
6   2020-10-30  1   25.89
7   2021-01-25  2   57.85

EDIT: matching the format START - STOP - CHANGE - PERIOD

Starting from the above data frame:

df['Start'] = df.Date.shift(periods=1)
df.rename(columns={'Date': 'Stop'}, inplace=True)
df = df1[['Start', 'Stop', 'CHG', 'period']]
df

Output:

    Start       Stop        CHG     period
0   NaN         2020-06-22  NaN     1
1   2020-06-22  2020-06-23  1.43    2
2   2020-06-23  2020-06-26  12.00   1
3   2020-06-26  2020-09-02  57.65   2
4   2020-09-02  2020-09-23  35.06   1
5   2020-09-23  2020-10-12  29.79   2
6   2020-10-12  2020-10-30  25.89   1
7   2020-10-30  2021-01-25  57.85   2

Answered By - aleirim