Sorting on Timestamp broken version 0.11

[rla3rd]

Issue: frame.sort_index uses argsort for a single sort column, but _lexsort_indexer for multi-columns
need to do a transform on datetimens[64] before passing to ``lexsortindexer

In [54]: a.columns
Out[54]: Index([ticker, disclosuredate, txnid], dtype=object)

In [55]: a.values
Out[55]:
array([[A, 2010-03-09 00:00:00, 11110508],
[A, 2010-03-12 00:00:00, 11121853],
[A, 2011-02-15 00:00:00, 12488915],
[A, 2011-03-08 00:00:00, 12563380],
[A, 2011-04-22 00:00:00, 12653015],
[A, 2013-01-28 00:00:00, 15244694]], dtype=object)

In [56]: a.sort(columns=['disclosuredate']).values
Out[56]:
array([[A, 2010-03-09 00:00:00, 11110508],
[A, 2010-03-12 00:00:00, 11121853],
[A, 2011-04-22 00:00:00, 12653015],
[A, 2013-01-28 00:00:00, 15244694],
[A, 2011-03-08 00:00:00, 12563380],
[A, 2011-02-15 00:00:00, 12488915]], dtype=object)

In [57]: pd.version
Out[57]: '0.11.0'

In [58]: import time

In [59]: a['epoch'] = a['disclosuredate'].map(lambda x: time.mktime(x.timetuple()))

In [60]: a.sort(['epoch']).values
Out[60]:
array([[A, 2010-03-09 00:00:00, 11110508, 1268110800.0],
[A, 2010-03-12 00:00:00, 11121853, 1268370000.0],
[A, 2011-02-15 00:00:00, 12488915, 1297746000.0],
[A, 2011-03-08 00:00:00, 12563380, 1299560400.0],
[A, 2011-04-22 00:00:00, 12653015, 1303444800.0],
[A, 2013-01-28 00:00:00, 15244694, 1359349200.0]], dtype=object)

[jreback]

When you do

a['disclosuredate'].map(lambda x: time.mktime(x.timetuple()))

I get (using my example)

In [15]: df['C'].map(lambda x: time.mktime(x.timetuple()))
Out[15]: 
2013-01-01    1357016400
2013-01-02    1357102800
2013-01-03    1357189200
2013-01-04    1357275600
2013-01-05    1357362000
Freq: D, Name: C, dtype: float64

Floats are not dates are very unweidlty to deal with, instead

You must have dtype of datetime64[ns]
you can convert using pd.to_datetime(a['discolsuredate'])

Here's an example

In [6]: df = DataFrame(randn(5,2),columns=list('AB'),index=date_range('20130101',periods=5))

In [7]: df
Out[7]: 
                   A         B
2013-01-01 -0.220543  1.059028
2013-01-02  1.174234  0.277359
2013-01-03  0.185800 -0.144194
2013-01-04 -0.562132 -0.144710
2013-01-05 -0.211064 -1.138927

In [8]: df['C'] = df.index

In [9]: df
Out[9]: 
                   A         B                   C
2013-01-01 -0.220543  1.059028 2013-01-01 00:00:00
2013-01-02  1.174234  0.277359 2013-01-02 00:00:00
2013-01-03  0.185800 -0.144194 2013-01-03 00:00:00
2013-01-04 -0.562132 -0.144710 2013-01-04 00:00:00
2013-01-05 -0.211064 -1.138927 2013-01-05 00:00:00

In [10]: df.info()
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 5 entries, 2013-01-01 00:00:00 to 2013-01-05 00:00:00
Freq: D
Data columns (total 3 columns):
A    5  non-null values
B    5  non-null values
C    5  non-null values
dtypes: datetime64[ns](1), float64(2)

In [12]: df.sort(columns='C',ascending=False)
Out[12]: 
                   A         B                   C
2013-01-05 -0.211064 -1.138927 2013-01-05 00:00:00
2013-01-04 -0.562132 -0.144710 2013-01-04 00:00:00
2013-01-03  0.185800 -0.144194 2013-01-03 00:00:00
2013-01-02  1.174234  0.277359 2013-01-02 00:00:00
2013-01-01 -0.220543  1.059028 2013-01-01 00:00:00

[rla3rd]

This must be an issue with pandas.tslib.Timestamp on import then.

import pandas as pd

data = pd.read_csv('../data/txns_baseline-has10b5-filtered.csv', sep='|', parse_dates=['disclosuredate'])

type(data['disclosuredate'][0])
Out[3]: pandas.tslib.Timestamp

a = data[data['ticker']=='A']

a[['ticker', 'disclosuredate', 'txnid']].sort(['disclosuredate']).values
Out[5]:
array([[A, 2009-11-18 00:00:00, 10826094],
[A, 2009-11-18 00:00:00, 10826175],
[A, 2010-12-20 00:00:00, 12010207],
[A, 2010-03-09 00:00:00, 11110508],
[A, 2011-01-25 00:00:00, 12080828],
[A, 2010-03-12 00:00:00, 11121853],
[A, 2010-11-03 00:00:00, 11896650],
[A, 2010-03-03 00:00:00, 11093189],
[A, 2009-11-17 00:00:00, 10820169],
[A, 2011-07-05 00:00:00, 12803928],
[A, 2012-03-28 00:00:00, 14719198],
[A, 2011-04-22 00:00:00, 12653015],
[A, 2011-07-06 00:00:00, 12812873],
[A, 2011-11-22 00:00:00, 13022405],
[A, 2012-01-25 00:00:00, 14535135],
[A, 2011-05-25 00:00:00, 12740039],
[A, 2011-02-08 00:00:00, 12443239],
[A, 2011-03-23 00:00:00, 12599229],
[A, 2011-08-31 00:00:00, 12901258],
[A, 2011-09-22 00:00:00, 12930155],
[A, 2011-10-24 00:00:00, 12966477],
[A, 2011-07-22 00:00:00, 12835679],
[A, 2012-04-03 00:00:00, 14732867],
[A, 2011-02-02 00:00:00, 12422169],
[A, 2011-02-24 00:00:00, 12521296],
[A, 2013-01-28 00:00:00, 15244694],
[A, 2011-11-18 00:00:00, 13018933],
[A, 2011-11-18 00:00:00, 13018937],
[A, 2011-03-29 00:00:00, 12607129],
[A, 2011-03-08 00:00:00, 12563380],
[A, 2011-02-15 00:00:00, 12488915],
[A, 2011-06-23 00:00:00, 12791504]], dtype=object)

a[['ticker', 'disclosuredate', 'txnid']].values
Out[6]:
array([[A, 2011-09-22 00:00:00, 12930155],
[A, 2010-11-03 00:00:00, 11896650],
[A, 2011-11-22 00:00:00, 13022405],
[A, 2010-12-20 00:00:00, 12010207],
[A, 2011-08-31 00:00:00, 12901258],
[A, 2011-10-24 00:00:00, 12966477],
[A, 2011-11-18 00:00:00, 13018933],
[A, 2011-11-18 00:00:00, 13018937],
[A, 2009-11-18 00:00:00, 10826094],
[A, 2009-11-18 00:00:00, 10826175],
[A, 2009-11-17 00:00:00, 10820169],
[A, 2011-01-25 00:00:00, 12080828],
[A, 2011-07-22 00:00:00, 12835679],
[A, 2011-02-02 00:00:00, 12422169],
[A, 2012-04-03 00:00:00, 14732867],
[A, 2010-03-03 00:00:00, 11093189],
[A, 2010-03-12 00:00:00, 11121853],
[A, 2012-03-28 00:00:00, 14719198],
[A, 2012-01-25 00:00:00, 14535135],
[A, 2010-03-09 00:00:00, 11110508],
[A, 2011-02-15 00:00:00, 12488915],
[A, 2011-02-24 00:00:00, 12521296],
[A, 2011-03-08 00:00:00, 12563380],
[A, 2011-02-08 00:00:00, 12443239],
[A, 2011-04-22 00:00:00, 12653015],
[A, 2011-06-23 00:00:00, 12791504],
[A, 2011-03-23 00:00:00, 12599229],
[A, 2011-07-06 00:00:00, 12812873],
[A, 2011-03-29 00:00:00, 12607129],
[A, 2011-07-05 00:00:00, 12803928],
[A, 2011-05-25 00:00:00, 12740039],
[A, 2013-01-28 00:00:00, 15244694]], dtype=object)

import time

a['epoch'] = a['disclosuredate'].map(lambda x: time.mktime(x.timetuple()))

a[['ticker', 'disclosuredate', 'txnid', 'epoch']].sort(['epoch']).values
Out[9]:
array([[A, 2009-11-17 00:00:00, 10820169, 1258434000.0],
[A, 2009-11-18 00:00:00, 10826094, 1258520400.0],
[A, 2009-11-18 00:00:00, 10826175, 1258520400.0],
[A, 2010-03-03 00:00:00, 11093189, 1267592400.0],
[A, 2010-03-09 00:00:00, 11110508, 1268110800.0],
[A, 2010-03-12 00:00:00, 11121853, 1268370000.0],
[A, 2010-11-03 00:00:00, 11896650, 1288756800.0],
[A, 2010-12-20 00:00:00, 12010207, 1292821200.0],
[A, 2011-01-25 00:00:00, 12080828, 1295931600.0],
[A, 2011-02-02 00:00:00, 12422169, 1296622800.0],
[A, 2011-02-08 00:00:00, 12443239, 1297141200.0],
[A, 2011-02-15 00:00:00, 12488915, 1297746000.0],
[A, 2011-02-24 00:00:00, 12521296, 1298523600.0],
[A, 2011-03-08 00:00:00, 12563380, 1299560400.0],
[A, 2011-03-23 00:00:00, 12599229, 1300852800.0],
[A, 2011-03-29 00:00:00, 12607129, 1301371200.0],
[A, 2011-04-22 00:00:00, 12653015, 1303444800.0],
[A, 2011-05-25 00:00:00, 12740039, 1306296000.0],
[A, 2011-06-23 00:00:00, 12791504, 1308801600.0],
[A, 2011-07-05 00:00:00, 12803928, 1309838400.0],
[A, 2011-07-06 00:00:00, 12812873, 1309924800.0],
[A, 2011-07-22 00:00:00, 12835679, 1311307200.0],
[A, 2011-08-31 00:00:00, 12901258, 1314763200.0],
[A, 2011-09-22 00:00:00, 12930155, 1316664000.0],
[A, 2011-10-24 00:00:00, 12966477, 1319428800.0],
[A, 2011-11-18 00:00:00, 13018933, 1321592400.0],
[A, 2011-11-18 00:00:00, 13018937, 1321592400.0],
[A, 2011-11-22 00:00:00, 13022405, 1321938000.0],
[A, 2012-01-25 00:00:00, 14535135, 1327467600.0],
[A, 2012-03-28 00:00:00, 14719198, 1332907200.0],
[A, 2012-04-03 00:00:00, 14732867, 1333425600.0],
[A, 2013-01-28 00:00:00, 15244694, 1359349200.0]], dtype=object)

[jreback]

show ng the .values is not useful as all of the dtypes are upcasted (this is a fyi), instead show me df.info(),
right after your read the csv, also if you can either include a link to your csv or a small data sample (just paste the csv in)

[rla3rd]

the disclosuredate column turns out to be an integer representation of the date

import pandas as pd

data = pd.read_csv('sample.csv', sep='|', parse_dates=['disclosuredate'])

data.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 9 entries, 0 to 8
Data columns (total 68 columns):
Unnamed: 0 9 non-null values
ticker 9 non-null values
companyname 9 non-null values
sector 9 non-null values
subsector 9 non-null values
industry_id 9 non-null values
mcap 9 non-null values
mcapgroup 9 non-null values
insidercik 9 non-null values
insidername 9 non-null values
position 9 non-null values
iacc 9 non-null values
txnid 9 non-null values
code 9 non-null values
has_10b5 9 non-null values
value 9 non-null values
shares 9 non-null values
pps 9 non-null values
round_limit_price 9 non-null values
nickel_limit_price 9 non-null values
quarter_limit_price 9 non-null values
fifty_cent_limit_price 9 non-null values
dollar_limit_price 9 non-null values
triggertype 2 non-null values
triggerprice 2 non-null values
round_trigger_price 9 non-null values
nickel_trigger_price 9 non-null values
quarter_trigger_price 9 non-null values
fifty_cent_trigger_price 9 non-null values
dollar_trigger_price 9 non-null values
disclosuredate 9 non-null values
disclosure_t1 9 non-null values
txndate 9 non-null values
option_gain 2 non-null values
option_gain_group 2 non-null values
option_daystoexpire 2 non-null values
option_days_group 2 non-null values
planid 1 non-null values
adoptiondisclosure 1 non-null values
adoption_days 1 non-null values
adoption_days_group 1 non-null values
adoption_change 1 non-null values
adoption_change_group 1 non-null values
first_10b5 1 non-null values
days_between_10b5 8 non-null values
expiring 0 non-null values
sharesinc 0 non-null values
prevshares 0 non-null values
sharesincpct 0 non-null values
sharesinc_change_group 0 non-null values
pricedown 0 non-null values
pricedownpct 0 non-null values
lasthighdate 9 non-null values
lasthighprice 9 non-null values
dayssincelasthigh 9 non-null values
dayssincelasthigh_group 9 non-null values
pregain3m 9 non-null values
pregain1m 9 non-null values
postgain1m 9 non-null values
postgain3m 9 non-null values
postgain6m 9 non-null values
postgain12m 9 non-null values
postgain24m 9 non-null values
relgain1m 9 non-null values
relgain3m 9 non-null values
relgain6m 9 non-null values
relgain12m 9 non-null values
relgain24m 9 non-null values
dtypes: datetime64ns, float64(37), int64(6), object(24)

here is a sample of the csv

|ticker|companyname|sector|subsector|industry_id|mcap|mcapgroup|insidercik|insidername|position|iacc|txnid|code|has_10b5|value|shares|pps|round_limit_price|nickel_limit_price|quarter_limit_price|fifty_cent_limit_price|dollar_limit_price|triggertype|triggerprice|round_trigger_price|nickel_trigger_price|quarter_trigger_price|fifty_cent_trigger_price|dollar_trigger_price|disclosuredate|disclosure_t1|txndate|option_gain|option_gain_group|option_daystoexpire|option_days_group|planid|adoptiondisclosure|adoption_days|adoption_days_group|adoption_change|adoption_change_group|first_10b5|days_between_10b5|expiring|sharesinc|prevshares|sharesincpct|sharesinc_change_group|pricedown|pricedownpct|lasthighdate|lasthighprice|dayssincelasthigh|dayssincelasthigh_group|pregain3m|pregain1m|postgain1m|postgain3m|postgain6m|postgain12m|postgain24m|relgain1m|relgain3m|relgain6m|relgain12m|relgain24m
132761|DDD|3D Systems|Industrial Goods|165.0|1651.0|369034380.0|200000000.0|1105964|Charles W. Hull|Officer|7176375|11846570|S|t|47089.989999999998|9000.0|5.232221|f|f|f|f|f|||f|f|f|f|f|20101004|20101005|20101001||||||||||||18.0||||||||20100810.0|5.2666660000000007|52.0|0-89|38.447971781305121|15.696389093588797|75.095541401273877|95.73248407643311|248.72611464968156|90.191082802547783|359.74522292993635|72.800665576061704|85.484134500828006|229.612628324627|98.42748644919979|343.96402204971002
266330|[PALM]|[Acq] Palm|Technology|183.0|183.0|513518809.06|200000000.0|1134647|Donna L. Dubinsky|Director|2594539|5305372|S|t|32520.0|6000.0|5.4199999999999999|f|f|f|f|f|||f|f|f|f|f|20040211|20040212|20040211||||||||||||7.0||||||||20040209.0|5.4950000000000001|2.0|0-89|-28.356254092992803|-8.6811352253756251|19.469835466179163|61.334552102376591|247.34917733089583|110.3290676416819|225.95978062157221|25.951767941427601|68.451608829533612|262.60710915028301|110.18198115965299|217.944772977898
266329|[PALM]|[Acq] Palm|Technology|183.0|183.0|510722880.88|200000000.0|1134189|Jeffrey C. Hawkins|Officer|2553976|5305409|S|t|32310.0|6000.0|5.3849999999999998|f|f|f|f|f|||f|f|f|f|f|20040126|20040127|20040126||||||||||||4.0||||||||20040122.0|5.6200000000000001|4.0|0-89|-36.235416951294845|-3.7433155080213902|-8.9814814814814827|69.814814814814824|247.22222222222223|144.90740740740739|231.94444444444446|-5.03684905487329|73.761337564857286|259.40441159482401|148.21311051320899|224.05423442951098
347731|FTNT|Fortinet Inc.|Technology|182.0|1821.0|1738396220.0|200000000.0|1475587|Ken Xie|CEO|7168959|11839564|S|t|439150.0|35132.0|12.5|t|t|f|t|f|||f|f|f|f|f|20100928|20100929|20100927||||||||||||6.0||||||||20100921.0|12.52|6.0|0-89|53.345498783454985|25.892634207240945|19.000396667988895|27.647758825862759|241.13447044823485|34.549781832606101|95.834986116620371|13.4962225676085|15.4296789120377|224.29374267363602|29.710013427852999|63.854308161929595
347732|FTNT|Fortinet Inc.|Technology|182.0|1821.0|1738396220.0|200000000.0|1476336|John Whittle|Officer|7168962|11839581|M|t|74746.875|6250.0|11.9595|f|f|f|f|f|||f|f|f|f|f|20100928|20100929|20100927|398.3125|300-400|2221.0|1465+||||||||31.0||||||||20100923.0|12.029999999999999|4.0|0-89|53.345498783454985|25.892634207240945|19.000396667988895|27.647758825862759|241.13447044823485|34.549781832606101|95.834986116620371|13.4962225676085|15.4296789120377|224.29374267363602|29.710013427852999|63.854308161929595
106101|RMBS|Rambus|Technology|186.0|1861.0|1127384709.8399999|200000000.0|1192512|Geoffrey Tate|Director|3692973|6479611|S|t|381821.13|33300.0|11.466100000000001|f|f|f|f|f|||f|f|f|f|f|20050920|20050921|20050920||||||||||||29.0||||||||20050916.0|11.970000000000001|4.0|0-89|-23.776223776223773|-5.2997393570807994|13.486238532110091|48.807339449541281|240.82568807339447|57.981651376146793|70.366972477064223|15.315217379943599|43.311383804533101|231.63997053266601|51.757996646340104|43.7221731758072
56480|GNW|Genworth Financial|Financial|142.0|1420.0|537085993.84000003|200000000.0|1290753|Pamela S. Schutz|Officer|5973693|10057429|S|t|13245.184800000001|8887.0|1.4903999999999999|f|f|f|f|f|pricedown|1.4903999999999999|f|f|f|f|f|20081112|20081113|20081111|||||25726.0|2008-08-14 00:00:00|172.0|101-365|-93.316591928251128|<-50||91.0||||||||20081107.0|4.0899999999999999|4.0|0-89|-90.461346633416454|-70.745697896749533|83.006535947712408|33.986928104575156|239.86928104575165|636.60130718954247|653.59477124183024|96.28021230863709|74.553669342457297|248.221257182963|627.27990525069094|641.73794797561095
266327|[PALM]|[Acq] Palm|Technology|183.0|183.0|506063000.57999998|200000000.0|1134647|Donna L. Dubinsky|Director|2546712|5305411|S|t|32880.0|6000.0|5.4800000000000004|f|f|f|f|f|firstsale|5.4800000000000004|f|f|f|f|f|20040121|20040122|20040121|||||||||||1.0|||||||||20040116.0|6.2450000000000001|5.0|0-89|-37.725877661809776|4.9429657794676807|-12.137681159420289|74.909420289855078|239.31159420289853|151.90217391304347|204.16666666666671|-6.8762619117527404|78.972638490807412|252.04961809141301|155.901211199323|198.05720989204102
263834|PLX|Protalix BioTherapeutics|Healthcare|153.0|153.0|264560481.96000001|200000000.0|1385112|David Aviezer|CEO|6321807|10487387|M|t|340948.0|100000.0|3.4094800000000003|f|f|f|f|f|||f|f|f|f|f|20090512|20090513|20090512|2741.2333333333299|1000+|1671.0|1465+||||||||6.0||||||||20090508.0|3.5099999999999998|4.0|0-89|31.343283582089548|61.467889908256872|7.1022727272727266|73.011363636363626|238.92045454545453|96.022727272727295|84.090909090909065|5.8105819618977499|62.889906146485103|221.64650042939499|74.507400494242589|38.804616436195396

[jreback]

are you doing something different than this?

In [31]: df = pd.read_csv('ts.csv',sep='|',parse_dates=['disclosuredate'])

In [32]: df.get_dtype_counts()
Out[32]: 
datetime64[ns]     1
float64           37
int64              6
object            24
dtype: int64

In [33]: df.sort(columns='disclosuredate').loc[:,['ticker','disclosuredate']]
Out[33]: 
   ticker      disclosuredate
7  [PALM] 2004-01-21 00:00:00
2  [PALM] 2004-01-26 00:00:00
1  [PALM] 2004-02-11 00:00:00
5    RMBS 2005-09-20 00:00:00
6     GNW 2008-11-12 00:00:00
8     PLX 2009-05-12 00:00:00
3    FTNT 2010-09-28 00:00:00
4    FTNT 2010-09-28 00:00:00
0     DDD 2010-10-04 00:00:00

[rla3rd]

yes im not appending .loc[:,['ticker','disclosuredate']] after the sort

In [1]: import pandas as pd

In [2]: data = pd.read_csv('sample.csv', sep='|', parse_dates=['disclosuredate'])

In [3]: data.get_dtype_counts()
Out[3]:
datetime64[ns] 1
float64 37
int64 6
object 24
dtype: int64

In [4]: data.sort(columns='disclosuredate').loc[:,['ticker','disclosuredate']]
Out[4]:
ticker disclosuredate
7 [PALM] 2004-01-21 00:00:00
2 [PALM] 2004-01-26 00:00:00
1 [PALM] 2004-02-11 00:00:00
5 RMBS 2005-09-20 00:00:00
6 GNW 2008-11-12 00:00:00
8 PLX 2009-05-12 00:00:00
3 FTNT 2010-09-28 00:00:00
4 FTNT 2010-09-28 00:00:00
0 DDD 2010-10-04 00:00:00

In [5]: data[['ticker', 'disclosuredate']].sort(columns=['disclosuredate'])
Out[5]:
ticker disclosuredate
1 [PALM] 2004-02-11 00:00:00
7 [PALM] 2004-01-21 00:00:00
2 [PALM] 2004-01-26 00:00:00
5 RMBS 2005-09-20 00:00:00
0 DDD 2010-10-04 00:00:00
8 PLX 2009-05-12 00:00:00
6 GNW 2008-11-12 00:00:00
3 FTNT 2010-09-28 00:00:00
4 FTNT 2010-09-28 00:00:00

[rla3rd]

In [8]: data.sort(columns=['disclosuredate']).tail(1)['ticker']
Out[8]:
4 FTNT
Name: ticker, dtype: object

In [9]: data.sort(columns='disclosuredate').loc[:,['ticker','disclosuredate']].tail(1)['ticker']
Out[9]:
0 DDD
Name: ticker, dtype: object

[jreback]

ok...this is a bug, but very odd

the difference is that I am specifying a list when it doesn't work (bottom), but a single column name when it does

these are handled by different sorters (the single argument uses argsort which works fine with datetime64[ns])
marking the list of datetime columns as a bug...thanks...good catch!

In [41]: df[['ticker', 'disclosuredate']].sort(columns='disclosuredate')
Out[41]: 
   ticker      disclosuredate
7  [PALM] 2004-01-21 00:00:00
2  [PALM] 2004-01-26 00:00:00
1  [PALM] 2004-02-11 00:00:00
5    RMBS 2005-09-20 00:00:00
6     GNW 2008-11-12 00:00:00
8     PLX 2009-05-12 00:00:00
3    FTNT 2010-09-28 00:00:00
4    FTNT 2010-09-28 00:00:00
0     DDD 2010-10-04 00:00:00

In [42]: df[['ticker', 'disclosuredate']].sort(columns=['disclosuredate'])
Out[42]: 
   ticker      disclosuredate
1  [PALM] 2004-02-11 00:00:00
7  [PALM] 2004-01-21 00:00:00
2  [PALM] 2004-01-26 00:00:00
5    RMBS 2005-09-20 00:00:00
0     DDD 2010-10-04 00:00:00
8     PLX 2009-05-12 00:00:00
6     GNW 2008-11-12 00:00:00
3    FTNT 2010-09-28 00:00:00
4    FTNT 2010-09-28 00:00:00

[jreback]

thanks for the report, this is merged into master if you would like to try