Here is an approach using pandas.DataFrame.shift which will give you the desired output.Follow the below steps along with code:
1.Create a dataframe with some data:
import pandas as pd
datelist = pd.date_range('1/1/2019', periods=100).tolist()
values = np.random.randint(1, 5, 100)
df = pd.DataFrame({'Date': datelist, 'Value': values})
df = df.set_index('Date')
df.head(10)
Date Value
2019-01-01 1
2019-01-02 4
2019-01-03 2
2019-01-04 2
2019-01-05 2
2019-01-06 3
2019-01-07 2
2019-01-08 2
2019-01-09 3
2019-01-10 2
2.Drop the duplicate rows:
df = df.loc[df.Value.shift() != df.Value]
Date Value
2019-01-01 2
2019-01-02 1
2019-01-04 2
2019-01-05 3
2019-01-06 1
3.Reset the index:
df = df.reset_index()
4.Rename the existing columns to be the start columns.
df.columns = ['Start_Date', 'Start_Value']
5.Create end columns by shifting the start columns back one row.
df['End_Date'] = df.Start_Date.shift(-1)
df['End_Value'] = df.Start_Value.shift(-1)
6.Drop the NaNs.
df = df.dropna()
Set the End_Value type to int (if preferred).
df['End_Value'] = df['End_Value'].astype(int)
df.head(10)
Start_Date Start_Value End_Date End_Value
0 2019-01-01 1 2019-01-02 4
1 2019-01-02 4 2019-01-03 2
2 2019-01-03 2 2019-01-06 3
3 2019-01-06 3 2019-01-07 2
4 2019-01-07 2 2019-01-09 3
5 2019-01-09 3 2019-01-10 2
6 2019-01-10 2 2019-01-11 1
7 2019-01-11 1 2019-01-12 2
8 2019-01-12 2 2019-01-15 1
9 2019-01-15 1 2019-01-16 4
7.Finally, create a CSV file from the dataframe:
df.to_csv('trends.csv')
