Consider the following dataframe, where Date
is in the format DD-MM-YYY
:
Date Time Table
01-10-2000 13:00:03 B
01-10-2000 13:00:04 A
01-10-2000 13:00:05 B
01-10-2000 13:00:06 A
01-10-2000 13:00:07 B
01-10-2000 13:00:08 A
How can I 1) group the observations by Table
, 2) sort the rows according to Date
and Time
within each group, 3) show the groups in chronological order according to Date
and Time
of their first observation?
Date Time Table
01-10-2000 13:00:03 B
01-10-2000 13:00:05 B
01-10-2000 13:00:07 B
01-10-2000 13:00:04 A
01-10-2000 13:00:06 A
01-10-2000 13:00:08 A
Input data:
data = {
'Date': ['01-10-2000', '01-10-2000', '01-10-2000', '01-10-2000', '01-10-2000', '01-10-2000'],
'Time': ['13:00:03', '13:00:04', '13:00:05', '13:00:06', '13:00:07', '13:00:08'],
'Table': ['B', 'A', 'B', 'A', 'B', 'A']
}
df = pd.DataFrame(data)
Use groupby.transform
and numpy.lexsort
:
date = pd.to_datetime(df['Date']+' '+df['Time'])
out = df.iloc[np.lexsort([
date,
df['Table'],
date.groupby(df['Table']).transform('min')
])]
Alternatively, using an intermediate column:
date = pd.to_datetime(df['Date']+' '+df['Time'])
out = (df.assign(date=date, min_date=date.groupby(df['Table']).transform('min'))
.sort_values(by=['min_date', 'Table', 'date'])
.drop(columns=['date', 'min_date'])
)
Output:
Date Time Table
0 01-10-2000 13:00:03 B
2 01-10-2000 13:00:05 B
4 01-10-2000 13:00:07 B
1 01-10-2000 13:00:04 A
3 01-10-2000 13:00:06 A
5 01-10-2000 13:00:08 A