Can't sort while pivoting while day-hour mapping

Question

I am trying labelling time like this

df['day-hour'] = ('Day' + (df['hour'] // 24).add(1).astype(str) +
              ' - ' + (df['hour'] % 24).astype(str))

So, the result will be

customer_id  hour   day-hour
1              10   Day1 - 10
1             123   Day6 - 3
1             489   Day21 - 9
2             230   Day9 - 14

then I try to group df.groupby(['customer_id','day-hour']).size().unstack(fill_value=0)

and the result is

day-hour               Day1 - 10   Day6 - 3   Day21 - 9   Day9 - 14
customer_id
1                              1          1           1           0
2                              0          0           0           1

The output that I expected is sort by actual days like this

day-hour               Day1 - 10   Day6 - 3   Day9 - 14   Day21 - 9
customer_id
1                              1          1           0           1
2                              0          0           1           0

What code that should I change?

Does `df['day-hour'] = ['Day{:02} - {}'.format(*x) for x in zip((df.hour//24).add(1), df.hour%24)]` work? — Zero, Jun 28 '18 at 10:40
as soon as you have strings the [`"Lexicographical sorting"`](https://en.wikipedia.org/wiki/Lexicographical_order) will be used... — MaxU - stand with Ukraine, Jun 28 '18 at 10:42

score 2 · Accepted Answer · answered Jun 28 '18 at 10:43

There are 2 possible solutions - add zeros as pointed @Zero in comments:

df['day-hour'] = ('Day' + (df['hour'] // 24).add(1).astype(str).str.zfill(2) +
              ' - ' + (df['hour'] % 24).astype(str).str.zfill(2) )

Or sorted by custom function with 2 fields:

df = df[sorted(df.columns,key=lambda x: (int(x.split(' - ')[0][3:]), int(x.split(' - ')[1])))]

Better readable:

def f(x):
    a = x.split(' - ')
    return (int(a[0][3:]), int(a[1]))

df = df[sorted(df.columns, key=f)]
print (df)
   Day1 - 10  Day6 - 3  Day9 - 14  Day21 - 9
1          1         1          0          1
2          0         0          1          0

Can't sort while pivoting while day-hour mapping

1 Answers1