I'm trying to order this dataframe in quarterly order using the list sortTo as reference to put it into a table.
import pandas as pd
# Sample DataFrame
data = {'QuarterYear': ["Q1 2024", "Q2 2024", "Q3 2023", 'Q3 2024', "Q4 2023", "Q4 2024"], 'data1': [5, 6, 2, 1, 10, 3], 'data2': [12, 4, 2, 7, 2, 9]}
sortTo = ["Q3 2023", "Q4 2023", "Q1 2024", 'Q2 2024', "Q3 2024", "Q4 2024"]
df = pd.DataFrame(data)
df.reindex(sortTo)
I've tried re-index, sort_values to no avail. I cannot use np.sort as the quarters are not numerical.
Current output:
QuarterYear data1 data2
0 Q1 2024 5 12
1 Q2 2024 6 4
2 Q3 2023 2 2
3 Q3 2024 1 7
4 Q4 2023 10 2
5 Q4 2024 3 9
Code
use key parameter for custom sorting.
out = df.sort_values(
'QuarterYear',
key=lambda x: x.map({k: n for n, k in enumerate(sortTo)})
)
out:
QuarterYear data1 data2
2 Q3 2023 2 2
4 Q4 2023 10 2
0 Q1 2024 5 12
1 Q2 2024 6 4
3 Q3 2024 1 7
5 Q4 2024 3 9
If your data consists of years and quarters, the following code should do the sorting you need without sortTo.
out = df.sort_values(
'QuarterYear',
key=lambda x: x.str.replace(r'(Q\d) (\d+)', r'\2 \1', regex=True)
)