Prashant Kumar
Reputation: 701
Pandas: reorder column based on column name
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]
Upvotes: 0
Views: 113
Answers (1)
Rodalm
Reputation: 5433
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.
Upvotes: 3
Related Questions
- Reordering Pandas Columns based on Column name
- Swap column names with row values in a pandas DataFrame
- Sort pandas dataframe by column specifying custom order
- python reorder a column and the dataframe in a desired order
- Reorder pandas dataframe columns that has columns.name
- Rearrange the columns according to the order present in a data frame in pandas
- Python/Pandas: Sort dataframe columns based on a column name
- How to reorder columns in a Pandas dataframe based on other dataframe columns
- Order columns of a pandas dataframe according to the values in a row
- Reorder dataframe rows based on column values