org table problems with sorting of some column

org table problems with sorting of some column

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1069 bytes --]



* Problem 1

I imported this column from a CVS file, so the date format was already chosen.
 
|       Date |
|------------|
| 05.05.2020 |
| 07.01.2021 |
| 07.01.2021 |
| 07.05.2020 |
| 09.03.2021 |
| 12.05.2020 |
| 13.02.2020 |
| 13.03.2020 |
| 13.03.2020 |
| 14.05.2020 |
| 15.02.2021 |
| 15.02.2021 |
| 17.02.2020 |
| 17.04.2020 |
| 20.04.2020 |
| 22.04.2020 |
| 23.06.2020 |
| 24.02.2021 |
| 24.04.2020 |
| 25.09.2020 |
| 25.11.2020 |

I'd like to sort these dates, but when I do that numerically it does not work as expected, any idea what to do.


* Problem 2
I'd like to order the column below descending 

|   Income |
|----------|
|   837.00 |
|   681.71 |
|   664.16 |
|   499.46 |
|   316.64 |
|   258.30 |
|   258.30 |
|   258.30 |
|   187.52 |
|   173.15 |
|   170.87 |
|   163.50 |
|   127.76 |
|   117.00 |
|    94.80 |
|    91.00 |
| 2.852.21 |
| 2.401.74 |
| 1.912.16 |
| 1.735.14 |
| 1.480.87 |
| 1.407.73 |

But again it does not work.

Any idea what to do?

regards

Uwe Brauer 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: org table problems with sorting of some column

[Tim Cross]

Uwe Brauer <oub@mat.ucm.es> writes:

> [[S/MIME Signed Part:Undecided]]
>
>
> * Problem 1
>
> I imported this column from a CVS file, so the date format was already chosen.
>  
> |       Date |
> |------------|
> | 05.05.2020 |
> | 07.01.2021 |
> | 07.01.2021 |
> | 07.05.2020 |
> | 09.03.2021 |
> | 12.05.2020 |
> | 13.02.2020 |
> | 13.03.2020 |
> | 13.03.2020 |
> | 14.05.2020 |
> | 15.02.2021 |
> | 15.02.2021 |
> | 17.02.2020 |
> | 17.04.2020 |
> | 20.04.2020 |
> | 22.04.2020 |
> | 23.06.2020 |
> | 24.02.2021 |
> | 24.04.2020 |
> | 25.09.2020 |
> | 25.11.2020 |
>
> I'd like to sort these dates, but when I do that numerically it does not work as expected, any idea what to do.
>
>
> * Problem 2
> I'd like to order the column below descending 
>
> |   Income |
> |----------|
> |   837.00 |
> |   681.71 |
> |   664.16 |
> |   499.46 |
> |   316.64 |
> |   258.30 |
> |   258.30 |
> |   258.30 |
> |   187.52 |
> |   173.15 |
> |   170.87 |
> |   163.50 |
> |   127.76 |
> |   117.00 |
> |    94.80 |
> |    91.00 |
> | 2.852.21 |
> | 2.401.74 |
> | 1.912.16 |
> | 1.735.14 |
> | 1.480.87 |
> | 1.407.73 |
>
> But again it does not work.
>
> Any idea what to do?
>
> regards
>
> Uwe Brauer 
>
> [[End of S/MIME Signed Part]]

When reporting a problem, it would really help if you made it clear what
you have tried and how it didn't work. From your description above, it
is impossible to know if you actually tried using the built-in table
sorting command and if you did, how you used it (what input, how you
tried calling it etc). You also don't provide any details on what did
not work - was it an error, was the sorting wrong, did nothing happen at
all? There is also no details regarding your Emacs version or the org
version you are running. 

For problem 1, I suspect you will need to either change the date format
or tell org what the date format is. It won't work trying to sort
numerically as those values are not really numerical values. The date
format you currently have I think is ambiguous - in Europe it would
represent dd-mm-yyyy, but in the US it would represent mm-dd-yyyy. Best
solution would be to convert the values into an ISO format i.e.
yyyy-mm-dd.

For problem 2, I suspect the issue is because all your values are not
numerical. The value 2.852.21 might be composed of numbers, but that is
not a numerical value (what is the value of 1 + 2.852.21?). Org will
interpret those values as strings and will sort them alphabetically.

The table sorting command in the manual states

‘C-c ^’ (‘org-table-sort-lines’)
     Sort the table lines in the region.  The position of point
     indicates the column to be used for sorting, and the range of lines
     is the range between the nearest horizontal separator lines, or the
     entire table.  If point is before the first column, you are
     prompted for the sorting column.  If there is an active region, the
     mark specifies the first line and the sorting column, while point
     should be in the last line to be included into the sorting.  The
     command prompts for the sorting type, alphabetically, numerically,
     or by time.  You can sort in normal or reverse order.  You can also
     supply your own key extraction and comparison functions.  When
     called with a prefix argument, alphabetic sorting is
     case-sensitive.

Note the part on providing key extraction and comparison functions. If
you cannot convert your data to data formats org is able to recognise,
then you will need to provide functions to provide the key comparison
logic i.e. how to sort 2.99 and 2.852.21 etc.

Re: org table problems with sorting of some column

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 3407 bytes --]

>>> "TC" == Tim Cross <theophilusx@gmail.com> writes:

> Uwe Brauer <oub@mat.ucm.es> writes:

>> [[S/MIME Signed Part:Undecided]]
>> 
>> 
>> * Problem 1
>> 
>> I imported this column from a CVS file, so the date format was already chosen.
>> 
>> |       Date |
>> |------------|
>> | 05.05.2020 |
>> | 07.01.2021 |
>> | 07.01.2021 |
>> | 07.05.2020 |
>> | 09.03.2021 |
>> | 12.05.2020 |
>> | 13.02.2020 |
>> | 13.03.2020 |
>> | 13.03.2020 |
>> | 14.05.2020 |
>> | 15.02.2021 |
>> | 15.02.2021 |
>> | 17.02.2020 |
>> | 17.04.2020 |
>> | 20.04.2020 |
>> | 22.04.2020 |
>> | 23.06.2020 |
>> | 24.02.2021 |
>> | 24.04.2020 |
>> | 25.09.2020 |
>> | 25.11.2020 |
>> 
>> I'd like to sort these dates, but when I do that numerically it does not work as expected, any idea what to do.
>> 
>> 
>> * Problem 2
>> I'd like to order the column below descending 
>> 
>> |   Income |
>> |----------|
>> |   837.00 |
>> |   681.71 |
>> |   664.16 |
>> |   499.46 |
>> |   316.64 |
>> |   258.30 |
>> |   258.30 |
>> |   258.30 |
>> |   187.52 |
>> |   173.15 |
>> |   170.87 |
>> |   163.50 |
>> |   127.76 |
>> |   117.00 |
>> |    94.80 |
>> |    91.00 |
>> | 2.852.21 |
>> | 2.401.74 |
>> | 1.912.16 |
>> | 1.735.14 |
>> | 1.480.87 |
>> | 1.407.73 |
>> 
>> But again it does not work.
>> 
>> Any idea what to do?
>> 
>> regards
>> 
>> Uwe Brauer 
>> 
>> [[End of S/MIME Signed Part]]

> When reporting a problem, it would really help if you made it clear what
> you have tried and how it didn't work. From your description above, it
> is impossible to know if you actually tried using the built-in table
> sorting command and if you did, how you used it (what input, how you
> tried calling it etc). You also don't provide any details on what did
> not work - was it an error, was the sorting wrong, did nothing happen at
> all? There is also no details regarding your Emacs version or the org
> version you are running. 


Point taken. I am using 
GNU Emacs master 846ff294206c922da
org master commit e2fa3c4c4046b

> For problem 1, I suspect you will need to either change the date format
> or tell org what the date format is. It won't work trying to sort
> numerically as those values are not really numerical values. The date
> format you currently have I think is ambiguous - in Europe it would
> represent dd-mm-yyyy, but in the US it would represent mm-dd-yyyy. Best
> solution would be to convert the values into an ISO format i.e.
> yyyy-mm-dd.


It is a European format: dd.mm.yyyy 

So the most convenient way would be to tell org to use that format,
since changing the format to iso, would require to use a sophisticated
reg-expression.

I had a quick look but I cannot find a way to tell org to use the
European dd.mm.yyyy  format.




> For problem 2, I suspect the issue is because all your values are not
> numerical. The value 2.852.21 might be composed of numbers, but that is
> not a numerical value (what is the value of 1 + 2.852.21?). Org will
> interpret those values as strings and will sort them alphabetically.

Right, sorry 

| 2.852.21 |
| 2.401.74 |
| 1.912.16 |
| 1.735.14 |
| 1.480.87 |
| 1.407.73 |

Does not make much sense, I replace it by 

| 2852.21 |
| 2401.74 |
| 1912.16 |
| 1735.14 |
| 1480.87 |
| 1407.73 |

And everything worked as expected.

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: org table problems with sorting of some column

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 892 bytes --]

>>> "TC" == Tim Cross <theophilusx@gmail.com> writes:

> Uwe Brauer <oub@mat.ucm.es> writes:


> For problem 1, I suspect you will need to either change the date format
> or tell org what the date format is. It won't work trying to sort
> numerically as those values are not really numerical values. The date
> format you currently have I think is ambiguous - in Europe it would
> represent dd-mm-yyyy, but in the US it would represent mm-dd-yyyy. Best
> solution would be to convert the values into an ISO format i.e.
> yyyy-mm-dd.

I just checked 
I have the setting 
,----
| org-time-stamp-custom-formats is a variable defined in ‘org.el’.
| Its value is (" %d.%m.%Y " . " %d.%m.%Y %a %H:%M ")
`----

But this as I understand only effect via 
‘org-display-custom-times’ the representation. 

It does not help me to recognise  28.04.2020 as a European date.

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: org table problems with sorting of some column

[Tim Cross]

Uwe Brauer <oub@mat.ucm.es> writes:

> [[S/MIME Signed Part:Undecided]]
>>>> "TC" == Tim Cross <theophilusx@gmail.com> writes:
>
>> Uwe Brauer <oub@mat.ucm.es> writes:
>
>
>> For problem 1, I suspect you will need to either change the date format
>> or tell org what the date format is. It won't work trying to sort
>> numerically as those values are not really numerical values. The date
>> format you currently have I think is ambiguous - in Europe it would
>> represent dd-mm-yyyy, but in the US it would represent mm-dd-yyyy. Best
>> solution would be to convert the values into an ISO format i.e.
>> yyyy-mm-dd.
>
> I just checked 
> I have the setting 
> ,----
> | org-time-stamp-custom-formats is a variable defined in ‘org.el’.
> | Its value is (" %d.%m.%Y " . " %d.%m.%Y %a %H:%M ")
> `----
>
> But this as I understand only effect via 
> ‘org-display-custom-times’ the representation. 
>
> It does not help me to recognise  28.04.2020 as a European date.
>
> [[End of S/MIME Signed Part]]

I suspect org is actually passing off the sorting/comparison to calc
(based on nothing but a guess of course). I would look into how calc
determines the format for date/time calculations and see if that can
help.