1

I'm new to pandas and I'm trying to sort a dataframe that I used melt on.

Here's what melt looks like:

              id  date_time      sold
0     sample_id1   Monday 0         0
1     sample_id2   Monday 0         0
2     sample_id3   Monday 0         0
3     sample_id4   Monday 0         0
4     sample_id5   Monday 0         0
...          ...        ...       ...
7387  sample_id1  Sunday 23         0
7388  sample_id2  Sunday 23         0
7389  sample_id3  Sunday 23         1
7390  sample_id4  Sunday 23         0
7391  sample_id5  Sunday 23         0

What my desired output would look like:

              id  date_time      sold
0     sample_id1   Monday 0         0
1     sample_id1   Monday 1         0
2     sample_id1   Monday 2         0
3     sample_id1   Monday 3         0
4     sample_id1   Monday 4         0
5     sample_id1   Monday 5         0
6     sample_id1   Monday 6         0
7     sample_id1   Monday 7         0
8     sample_id1   Monday 8         0
9     sample_id1   Monday 9         0
...          ...        ...       ...
7387  sample_id5  Sunday 19         0
7388  sample_id5  Sunday 20         0
7389  sample_id5  Sunday 21         1
7390  sample_id5  Sunday 22         0
7391  sample_id5  Sunday 23         0

I tried using sort_values(['id']), but the output mixes the date_time column - looking like this:

              id   date_time     sold
0     sample_id1   Sunday 23        0
1     sample_id1   Saturday 18      0
2     sample_id1   Thursday 1       0
3     sample_id1   Monday 16        36
4     sample_id1   Saturday 19      6

What function from pandas do I need to use to achieve what I want?

tearswep
  • 125
  • 2
  • 13

3 Answers3

1

Use parameter key in DataFrame.sort_values for convert values by dictionary:

d = {'Monday': '0', 'Tuesday': '1', 'Wednesday': '2', 
     'Thursday': '3', 'Friday': '4', 'Saturday': '5', 'Sunday': '6'}

def f(x):
    try:
        return x.replace(d, regex=True).str.split().apply(lambda x: tuple(map(int, x)))
    except:
        return x

df = df.sort_values(['id', 'date_time'], key=f)
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Is there any way to modify the lambda function because at the moment it sorts like this : Monday 0, Tuesday 0, Wednesday 0, Thursday 0, Friday 0 etc instead of Monday 0, Monday 1, Monday 2, Monday 3 etc. – tearswep Mar 29 '22 at 06:53
  • 1
    @tearswep - Can you test now? – jezrael Mar 29 '22 at 07:24
  • Just to make sure I understand: - you use a dictionary to map every day of the week to its corresponding digit - then you apply the f function to the sort_values through the key parameter -because the id cannot be replaced with the dictionary, it returns the id column -when it goes to date_time column, it replaces the days with digits, splits the column, and then applies the lambda function while sorting. Is that correct? – tearswep Mar 29 '22 at 07:43
  • 1
    @tearswep - exactly, also `.apply(lambda x: tuple(map(int, x))` is for convert splitted values to integers tuples for correct order – jezrael Mar 29 '22 at 07:45
0

Not sure about your date_time datatype, but you can always do this to sort based on two columns

sort_values(['id', 'date_time'])
Firefighting Physicist
  • 363
  • 2
  • 4
  • 13
  • 1
    I've tried that as well, but the problem is that it sorts the date_time alphabetically that way. Meaning that instead of getting Monday 0, Monday 1 ... Sunday 22, Sunday 23, I get Friday 0, Friday 1, Friday 10, ... Wednesday 8, Wednesday 9. (because date_time column is alphanumerical) – tearswep Mar 29 '22 at 06:49
  • Take a look at this [link](https://stackoverflow.com/a/54301218/18482459) for an example on custom sorting with a key. Not sure whether you can apply the lambda function to only one of the two colums to sort for or whether you can do it successively. – Firefighting Physicist Mar 29 '22 at 06:54
0

You could try creating two new columns based on your datetime column. The first would represent the day of the week with Monday being 1 and Sunday being 7, and the second would just be the number after the space. Then do a sort based on id and these two new columns. For example:

df[['weekday', 'day_no']] = df['datetime'].str.split(' ', expand=True)
df.loc[df['weekday'] == 'Monday', 'weekday_no'] = 1
df.loc[df['weekday'] == 'Tuesday', 'weekday_no'] = 2
df.loc[df['weekday'] == 'Wednesday', 'weekday_no'] = 3
...

At this point, you can sort on id, weekday_no and and day_no.

Chukwuemeka Chukwuenweniwe
  • 1
  • 1