I have a data frame that looks like this
Now the column name was converted from date format to %b %Y format and it is from Jan 2019 to Oct-2021. I want to reorder the column names based on the date and not alphabatically.
How do I do that? I used the following line but it does not seem to work
table.sort_index(axis='columns', level='Date')
Edit: Here is the output from print(table.head(10).to_dict(orient='list'))
{('AADT', 'Apr 2019'): [4180.722222222223, 18075.733333333334, 1304536.8333333333, 62335.3, 1039.3333333333333, 9797.733333333334, 62949.36666666667, 527094.5333333333, 7893.0625, 10071.620689655172], ('AADT', 'Apr 2020'): [31081.166666666668, 17484.366666666665, 753747.0, 36726.03333333333, nan, 9451.0, 24169.966666666667, 292953.73333333334, 8537.1, 5484.466666666666], ('AADT', 'Apr 2021'): [2231.7272727272725, 21075.266666666666, 1109779.4666666666, 71246.8, nan, 10868.48275862069, 25227.2, 418751.43333333335, nan, 10701.863636363636], ('AADT', 'Aug 2019'): [2904.2903225806454, 20891.645161290322, 1466448.1935483871, 50817.6129032258, 1342.6666666666667, 8048.612903225807, 55199.54838709677, 609280.2903225806, 8958.258064516129, 10431.483870967742], ('AADT', 'Aug 2020'): [10822.379310344828, 12799.1, 679384.7741935484, 38622.54838709677, 1655.5, 5720.4838709677415, 12387.90322580645, 252135.5806451613, 9662.724137931034, 12194.724137931034], ('AADT', 'Aug 2021'): [3887.3225806451615, 12065.483870967742, 940915.0645161291, 59411.48387096774, 1863.5652173913043, 4154.129032258064, 23908.516129032258, 383140.6129032258, 17013.612903225807, 17311.483870967742], ('AADT', 'Dec 2019'): [nan, 29175.0, 1504749.322580645, 73835.83870967742, nan, 7232.142857142857, 61149.25, 584672.7096774194, nan, 8295.739130434782], ('AADT', 'Dec 2020'): [13414.380952380952, 13985.129032258064, 691473.6129032258, 33008.12903225807, nan, 10286.807692307691, 5184.032258064516, 256402.03225806452, 13108.444444444445, 9272.064516129032], ('AADT', 'Feb 2019'): [3646.4285714285716, 15604.5, 1044447.1428571428, 46783.96428571428, nan, 7491.285714285715, 19002.428571428572, 391334.5, 6558.107142857143, 6011.964285714285], ('AADT', 'Feb 2020'): [7531.482758620689, 18667.241379310344, 1107406.2068965517, 53251.24137931035, nan, 6785.379310344828, 20535.96551724138, 409618.5172413793, 8480.586206896553, 9712.620689655172], ('AADT', 'Feb 2021'): [2889.1428571428573, 13605.75, 1027667.6428571428, 66543.21428571429, nan, 11329.857142857143, 16417.25, 387610.75, 17206.2, 12769.392857142857], ('AADT', 'Jan 2019'): [3825.8709677419356, 16945.354838709678, 1043053.9677419355, 43416.3870967742, nan, 7369.064516129032, 17330.064516129034, 390853.5483870968, 9425.709677419354, 7200.580645161291], ('AADT', 'Jan 2020'): [2249.451612903226, 21037.225806451614, 1062939.0, 56543.54838709677, nan, 7452.225806451613, 21468.709677419356, 395211.0, 8022.967741935484, 8072.5161290322585], ('AADT', 'Jan 2021'): [2171.714285714286, 13143.032258064517, 872891.9354838709, 50554.645161290326, nan, 7621.740740740741, 8855.290322580646, 331211.67741935485, 10596.066666666668, 14136.161290322581], ('AADT', 'Jul 2019'): [2939.36, 18458.827586206895, 1490796.0, 64612.83870967742, 3319.1785714285716, 7838.290322580645, 64218.87096774193, 595895.3548387097, 11158.933333333332, 10345.967741935483], ('AADT', 'Jul 2020'): [8153.653846153846, 17704.423076923078, 696114.2903225806, 44171.967741935485, 1810.5172413793102, 5099.896551724138, 15129.129032258064, 258649.74193548388, 9227.23076923077, 10859.423076923076], ('AADT', 'Jul 2021'): [2941.3870967741937, 14229.064516129032, 1016602.3870967742, 55530.58064516129, 1884.4516129032259, 4855.870967741936, 31988.870967741936, 425938.12903225806, 16346.0, 18524.612903225807], ('AADT', 'Jun 2019'): [3332.0344827586205, 15839.033333333333, 1372660.5, 61044.5, 3884.7241379310344, 8082.433333333333, 60063.96666666667, 569774.2, 14599.333333333334, 12828.5], ('AADT', 'Jun 2020'): [6245.888888888889, 15431.518518518518, 825799.3, 43901.5, 1867.7333333333333, 8874.068965517241, 21052.633333333335, 302394.7, 10843.037037037036, 9797.111111111111], ('AADT', 'Jun 2021'): [3416.4615384615386, 58768.5, 907133.0, 50799.166666666664, 1987.8, 4894.8, 22486.766666666666, 393780.26666666666, nan, 17403.958333333332], ('AADT', 'Mar 2019'): [3190.3870967741937, 17057.709677419356, 1185417.2258064516, 54922.096774193546, nan, 9740.612903225807, 29554.74193548387, 456925.3870967742, 8201.37037037037, 9367.838709677419], ('AADT', 'Mar 2020'): [19192.516129032258, 17663.935483870966, 830373.1612903225, 55640.58064516129, nan, 9049.0, 21661.967741935485, 316354.29032258067, 8921.838709677419, 8638.612903225807], ('AADT', 'Mar 2021'): [1871.4347826086957, 17696.4, 1143384.5161290322, 75820.4, nan, 11566.3, 19212.833333333332, 430195.5806451613, nan, 14244.333333333334], ('AADT', 'May 2019'): [3238.0, 13838.032258064517, 1332669.935483871, 56042.77419354839, 2129.6875, 9247.548387096775, 56724.709677419356, 552480.3225806452, 11927.806451612903, 15388.41935483871], ('AADT', 'May 2020'): [15637.58064516129, 17504.322580645163, 855213.2903225806, 48857.903225806454, 1828.3636363636363, 10741.548387096775, 26456.83870967742, 319674.70967741933, 14051.032258064517, 10396.193548387097], ('AADT', 'May 2021'): [3893.6666666666665, 13900.129032258064, 903837.4193548387, 56877.16129032258, 1921.6, 6080.0, 29688.09677419355, 378961.32258064515, nan, 15112.0], ('AADT', 'Nov 2019'): [nan, 22217.85714285714, 1503773.3333333333, 66929.6, nan, 9282.6, 82848.0, 571871.2333333333, nan, 7841.555555555556], ('AADT', 'Nov 2020'): [24571.65, 20366.758620689656, 691888.9666666667, 33084.26666666667, nan, 14329.310344827587, 7821.266666666666, 264809.7, 15502.521739130434, 14558.172413793103], ('AADT', 'Oct 2019'): [1925.4444444444443, 22052.47619047619, 1498169.3870967743, 54511.93548387097, nan, 9876.9, 73759.85714285714, 587274.3548387097, 10594.777777777777, 9077.333333333334], ('AADT', 'Oct 2020'): [22728.129032258064, 22289.16129032258, 670292.2903225806, 32471.032258064515, nan, 9804.483870967742, 6325.580645161291, 250591.5806451613, 10681.37037037037, 10965.645161290322], ('AADT', 'Oct 2021'): [3264.7, 22671.533333333333, 1001493.1, 61869.86666666667, nan, 4570.413793103448, 28537.5, 374477.4, 12551.366666666667, 14900.2], ('AADT', 'Sep 2019'): [4031.9666666666667, 24609.266666666666, 1453336.2333333334, 54464.63333333333, nan, 9714.9, 57786.4, 631562.7, 11894.933333333332, 13868.266666666666], ('AADT', 'Sep 2020'): [30643.608695652172, 17740.076923076922, 709198.5333333333, 35773.6, nan, 7293.814814814815, 10384.933333333332, 256959.9, 9393.421052631578, 12787.739130434782], ('AADT', 'Sep 2021'): [2784.2, 14646.133333333333, 980093.1, 60560.36666666667, nan, 4127.1, 25578.133333333335, 366471.1, 14351.533333333333, 10283.966666666667]}
Here is the output from print(table.head(5))
AADT \
Date Apr 2019
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 4.180722e 03
Aleutians West Census Area 2016.0 1.807573e 04
Anchorage Municipality 2020.0 1.304537e 06
Bethel Census Area 2050.0 6.233530e 04
Bristol Bay Borough 2060.0 1.039333e 03
\
Date Apr 2020
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 31081.166667
Aleutians West Census Area 2016.0 17484.366667
Anchorage Municipality 2020.0 753747.000000
Bethel Census Area 2050.0 36726.033333
Bristol Bay Borough 2060.0 NaN
\
Date Apr 2021
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 2.231727e 03
Aleutians West Census Area 2016.0 2.107527e 04
Anchorage Municipality 2020.0 1.109779e 06
Bethel Census Area 2050.0 7.124680e 04
Bristol Bay Borough 2060.0 NaN
\
Date Aug 2019
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 2.904290e 03
Aleutians West Census Area 2016.0 2.089165e 04
Anchorage Municipality 2020.0 1.466448e 06
Bethel Census Area 2050.0 5.081761e 04
Bristol Bay Borough 2060.0 1.342667e 03
\
Date Aug 2020
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 10822.379310
Aleutians West Census Area 2016.0 12799.100000
Anchorage Municipality 2020.0 679384.774194
Bethel Census Area 2050.0 38622.548387
Bristol Bay Borough 2060.0 1655.500000
\
Date Aug 2021
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 3887.322581
Aleutians West Census Area 2016.0 12065.483871
Anchorage Municipality 2020.0 940915.064516
Bethel Census Area 2050.0 59411.483871
Bristol Bay Borough 2060.0 1863.565217
\
Date Dec 2019
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 NaN
Aleutians West Census Area 2016.0 2.917500e 04
Anchorage Municipality 2020.0 1.504749e 06
Bethel Census Area 2050.0 7.383584e 04
Bristol Bay Borough 2060.0 NaN
\
Date Dec 2020
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 13414.380952
Aleutians West Census Area 2016.0 13985.129032
Anchorage Municipality 2020.0 691473.612903
Bethel Census Area 2050.0 33008.129032
Bristol Bay Borough 2060.0 NaN
\
Date Feb 2019
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 3.646429e 03
Aleutians West Census Area 2016.0 1.560450e 04
Anchorage Municipality 2020.0 1.044447e 06
Bethel Census Area 2050.0 4.678396e 04
Bristol Bay Borough 2060.0 NaN
\
Date Feb 2020
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 7.531483e 03
Aleutians West Census Area 2016.0 1.866724e 04
Anchorage Municipality 2020.0 1.107406e 06
Bethel Census Area 2050.0 5.325124e 04
Bristol Bay Borough 2060.0 NaN
... \
Date ...
State Postal Code State FIPS County Name County FIPS ...
AK 2.0 Aleutians East Borough 2013.0 ...
Aleutians West Census Area 2016.0 ...
Anchorage Municipality 2020.0 ...
Bethel Census Area 2050.0 ...
Bristol Bay Borough 2060.0 ...
\
Date May 2020
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 15637.580645
Aleutians West Census Area 2016.0 17504.322581
Anchorage Municipality 2020.0 855213.290323
Bethel Census Area 2050.0 48857.903226
Bristol Bay Borough 2060.0 1828.363636
\
Date May 2021
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 3893.666667
Aleutians West Census Area 2016.0 13900.129032
Anchorage Municipality 2020.0 903837.419355
Bethel Census Area 2050.0 56877.161290
Bristol Bay Borough 2060.0 1921.600000
\
Date Nov 2019
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 NaN
Aleutians West Census Area 2016.0 2.221786e 04
Anchorage Municipality 2020.0 1.503773e 06
Bethel Census Area 2050.0 6.692960e 04
Bristol Bay Borough 2060.0 NaN
\
Date Nov 2020
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 24571.650000
Aleutians West Census Area 2016.0 20366.758621
Anchorage Municipality 2020.0 691888.966667
Bethel Census Area 2050.0 33084.266667
Bristol Bay Borough 2060.0 NaN
\
Date Oct 2019
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 1.925444e 03
Aleutians West Census Area 2016.0 2.205248e 04
Anchorage Municipality 2020.0 1.498169e 06
Bethel Census Area 2050.0 5.451194e 04
Bristol Bay Borough 2060.0 NaN
\
Date Oct 2020
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 22728.129032
Aleutians West Census Area 2016.0 22289.161290
Anchorage Municipality 2020.0 670292.290323
Bethel Census Area 2050.0 32471.032258
Bristol Bay Borough 2060.0 NaN
\
Date Oct 2021
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 3.264700e 03
Aleutians West Census Area 2016.0 2.267153e 04
Anchorage Municipality 2020.0 1.001493e 06
Bethel Census Area 2050.0 6.186987e 04
Bristol Bay Borough 2060.0 NaN
\
Date Sep 2019
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 4.031967e 03
Aleutians West Census Area 2016.0 2.460927e 04
Anchorage Municipality 2020.0 1.453336e 06
Bethel Census Area 2050.0 5.446463e 04
Bristol Bay Borough 2060.0 NaN
\
Date Sep 2020
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 30643.608696
Aleutians West Census Area 2016.0 17740.076923
Anchorage Municipality 2020.0 709198.533333
Bethel Census Area 2050.0 35773.600000
Bristol Bay Borough 2060.0 NaN
Date Sep 2021
State Postal Code State FIPS County Name County FIPS
AK 2.0 Aleutians East Borough 2013.0 2784.200000
Aleutians West Census Area 2016.0 14646.133333
Anchorage Municipality 2020.0 980093.100000
Bethel Census Area 2050.0 60560.366667
Bristol Bay Borough 2060.0 NaN
[5 rows x 34 columns]
CodePudding user response：
Assuming the column level 'Date' is datetime-like in the beginning, the trick is to sort the 'Date' level as you did (table.sort_index(axis='columns', level='Date')) and only then convert that level to string format %b %Y.
Another option, if the 'Date' level is in the string format %b %Y from the beginning, is to pass a sorting function to the key parameter.
table = table.sort_index(axis='columns', level='Date',
key= lambda dates: pd.to_datetime(dates, format='%b %Y'))
From the DateFrame.sort_index docs:
key : callable, optional
If not None, apply the key function to the index values before sorting. This is similar to the key argument in the builtin sorted() function, with the notable difference that this key function should be vectorized. It should expect an Index and return an Index of the same shape. For MultiIndex inputs, the key is applied per level.