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)
1
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
# Sort by Date and Time
df['DateTime'] = pd.to_datetime(df['Date'] + ' ' + df['Time'], format='%d-%m-%Y %H:%M:%S')
df_sorted = df.sort_values('DateTime')
# Find the first occurrence of each Table group
first_occurrences = df_sorted.groupby('Table', as_index=False).first()
# Sort Table groups according to the Date and Time of their first occurrence
table_order = first_occurrences.sort_values('DateTime')['Table']
# Create categorical version of 'Table' specifying the order of the categories
df_sorted['Table_cat'] = pd.Categorical(df_sorted['Table'], categories=table_order, ordered=True)
# Sort the dataframe
result = df_sorted.sort_values(['Table_cat', 'DateTime'])
result = result.reset_index(drop=True).drop('Table_cat', axis=1)
1
import numpy as np
import pandas as pd
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)
'''
Date Time Table
0 01-10-2000 13:00:03 B
1 01-10-2000 13:00:04 A
2 01-10-2000 13:00:05 B
3 01-10-2000 13:00:06 A
4 01-10-2000 13:00:07 B
5 01-10-2000 13:00:08 A
'''
res = df.iloc[
np.lexsort(
(pd.to_datetime(df['Date'] + ' ' + df['Time']), df['Table'])
)#lex
].reset_index(drop=True)
print(res)
'''
Date Time Table
0 01-10-2000 13:00:04 A
1 01-10-2000 13:00:06 A
2 01-10-2000 13:00:08 A
3 01-10-2000 13:00:03 B
4 01-10-2000 13:00:05 B
5 01-10-2000 13:00:07 B
'''