1

I have a pivot table where the rows are sorted by date, then columns are in alphabetical order.

I would like to see the column order differently. For example if location is showing a value in the first row then I would like that to be the first column.

I basically tried every version of the code below using sort_values:

df1 = pd.pivot_table(df1, values = 'num', index='date', columns = 'location_name',aggfunc = "sum")
result = df1.sort_values(('date'), ascending=False)

// +-----------+---+---+---+---+
// |   date    | a | b | c | d |
// +-----------+---+---+---+---+
// | 7/31/2021 |   |   | 1 |   |
// | 8/1/2021  |   |   | 1 |   |
// | 8/2/2021  |   |   | 1 |   |
// | 8/3/2021  |   |   |   | 2 |
// | 8/4/2021  |   |   |   | 2 |
// | 8/5/2021  |   |   |   | 2 |
// | 8/6/2021  |   |   |   | 2 |
// | 8/7/2021  |   |   |   | 2 |
// | 8/8/2021  | 3 |   |   |   |
// | 8/9/2021  | 3 |   |   |   |
// | 8/10/2021 | 3 |   |   |   |
// +-----------+---+---+---+---+

So the order of the columns above should be c,d,a,b in that order with the date order staying the same.

Any ideas on the best way to do this?

Cam
  • 17
  • 6
  • 1
    `results= results[['date', 'c','d','a','b']]` – ThePyGuy Jul 21 '21 at 21:12
  • Does this answer your question? [How to change the order of DataFrame columns?](https://stackoverflow.com/questions/13148429/how-to-change-the-order-of-dataframe-columns) – Henry Ecker Jul 21 '21 at 21:20
  • I think the question isn't just reordering the columns, but rather, about how `list('cdab')` would be determined – ifly6 Jul 21 '21 at 21:26
  • These answers sparked some ideas. Think I'll order the df before the pivot and create a list of the columns based on that order then after the pivot reorder the table on that list. – Cam Jul 21 '21 at 21:34

1 Answers1

1

Try:

cols = df.iloc[:, 1:].apply(pd.Series.first_valid_index).sort_values().index
df = df[['date', *cols.tolist()]]

>>> df
         date    c    d    a   b
0   7/31/2021  1.0  NaN  NaN NaN
1    8/1/2021  1.0  NaN  NaN NaN
2    8/2/2021  1.0  NaN  NaN NaN
3    8/3/2021  NaN  2.0  NaN NaN
4    8/4/2021  NaN  2.0  NaN NaN
5    8/5/2021  NaN  2.0  NaN NaN
6    8/6/2021  NaN  2.0  NaN NaN
7    8/7/2021  NaN  2.0  NaN NaN
8    8/8/2021  NaN  NaN  3.0 NaN
9    8/9/2021  NaN  NaN  3.0 NaN
10  8/10/2021  NaN  NaN  3.0 NaN
Corralien
  • 109,409
  • 8
  • 28
  • 52
  • This worked for me but first I need to Pivot the df how I wanted it set up: `df1 = pd.pivot_table(df1, values = 'num', index='date', columns = 'location_name',aggfunc = "sum")` Then reset the index to make it a df again: `df1= df1.reset_index()` Finally ran your two lines above and gave me your output above. Thanks! – Cam Jul 22 '21 at 14:52