python + pandas

dgn2 · Post by **dgn2** » Mon Nov 19, 2012 6:43 pm

I started using pandas (the most recent version) about 1 hour ago, so I am still trying to figure out how to do the most basic things. Is there a fast way to parse a timestamp of the format HH:MM:SS.MS using pandas.to_datetime()?

Here is a sample of a few lines of the data file I am working with:

2012/11/01,17:53:01.621,1.22010,1000000,1.22040,1000000

2012/11/01,17:54:59.812,1.22009,500000,1.22040,1000000

2012/11/01,17:54:59.833,1.22009,500000,1.22043,500000

2012/11/01,17:55:00.468,1.22008,500000,1.22043,500000

2012/11/01,17:55:03.853,1.22009,500000,1.22060,500000

2012/11/01,17:57:29.565,1.22010,1000000,1.22060,500000

2012/11/01,17:57:30.236,1.22010,1000000,1.22085,500000

columnNames=['sessionDate','sessionTime','bidPrice','bidSize','askPrice','askSize']

Basically, I want to load a file with multiple sessions and downsample to hourly OHLC for each session. The sessionDate column is not a calendar day, but rather a session date. So, for example quotes between 17:05 and 23:59 on Sunday 2012/11/11 are found under the date Monday 2012/11/12 and so on. Is there an easy way to do this using pandas? There doesn't seem to be any functionality for defining a session, but I am probably missing something.

I can load the file via

data=pd.read_csv(fileName,names=columnNames)

and extract the data via

sessionDate=data['sessionDate']

sessionTime=data['sessionTime']

bidPrice=data['bidPrice']

bidSize=data['bidSize']

askPrice=data['askPrice']

askSize=data['askSize']

spread=askPrice-bidPrice

but I haven't been able figure out how to parse the timestamp, create a time series object and downsample yet.

Any suggestions?

briant57 · Post by **briant57** » Mon Nov 19, 2012 7:16 pm

I am not a Pandas pro, but will offer up some initial advice. Asking questions here https://groups.google.com/forum/?fromgroups#!forum/pydata usually gets quick answers.

data=pd.read_csv(fileName,names=columnNames,parse_dates={'Date': [0, 1]})

This will give you a new column labeled 'Date' taking the date from 'sessionDate' and time from 'sessionTime' in pandas datetime format. (columns 0 and 1 of your dataframe).

Resampling could be done in this way for mid price.

ts=(data['askPrice']+data['bidPrice'])/2

ts.resample('60Min', how='ohlc')

I am unsure of a clear way to convert the session times for the early hours.

Brian

signalseeker · Post by **signalseeker** » Mon Nov 19, 2012 7:39 pm

df["timestmp"] = df.sessionDate + 'T' + df.sessionTime

df["timestamp"] = df.timestmp.astype("M8[ms]")

and then you can do df.set_index("timestamp") etc

dgn2 · Post by **dgn2** » Mon Nov 19, 2012 7:59 pm

Thanks signalseeker. I knew there had to be an easy way!

dgn2 · Post by **dgn2** » Mon Nov 19, 2012 10:32 pm

This turned out to be very straight-forward:

# load the data into memory (example line: 2012/11/01,18:00:02.197,32.22,25000,0,0)

columnNames=['sessionDate','sessionTime','bidPrice','bidSize','askPrice','askSize']

data=pd.read_csv(fileName,names=columnNames,parse_dates={'sessionDateTime': [0, 1]})

data.set_index("sessionDateTime")

# index to get the data

sessionDateTime=data['sessionDateTime']

bidPrice=data['bidPrice']

askPrice=data['askPrice']

# create the time series object

bidPriceTs=Series(bidPrice,index=sessionDateTime)

askPriceTs=Series(askPrice,index=sessionDateTime)

# down-sample the data to open,high,low,close,nTicks

bidPrice_60min=bidPriceTs.resample('60Min', how='ohlc')

askPrice_60min=bidPriceTs.resample('60Min', how='ohlc')

# write hourly data

bidPrice_60min.to_csv('C:/data/bid_60min.csv')

askPrice_60min.to_csv('C:/data/ask_60min.csv')

I am sure there are better ways to do this using groupby, but for now this works for me. Thank you both for your help.

Scotty · Post by **Scotty** » Wed Nov 21, 2012 3:44 am

dgn2

you like?

I find a good way to work out how to do things is to follow Wes and the ipython notebooks he writes. Lots of examples.

See

http://wesmckinney.com/blog/?p=301

Tradenator · Post by **Tradenator** » Fri Jan 04, 2013 6:30 pm

I'm in "DLL Hell", can someone please help? Specifically, I'm trying to use py2exe to compile a script into an executable. I have it working for a basic 'hello world' script as per the tutorial, but the moment I try to import any of a number of packages into this basic script (without actually using them yet) the setup crashes before it can complete. If anyone has successfully done this, would it be possible for you to post a copy of a setup file that worked? I'm on python 2.7.3 with pandas 0.10.0, matplotlib 1.2.0, and numpy 1.6.2 but I would think that any recent versions of packages would suffice.

Again, I can get a basic script to compile, but the moment I import pandas, or numpy, or matplotlib it all goes to hell in a handbasket. Thanks in advance.

tristanreid · Post by **tristanreid** » Fri Jan 04, 2013 6:54 pm

Hey Tradenator,

Which version of py2exe are you using? Note that if you download the 'latest' from sourceforge it points to Python 2.6. There is a version there that supports Python 2.7 though.

I'm sure you've also seen: http://www.py2exe.org/index.cgi/WorkingWithVariousPackagesAndModules

For Numpy, the guy at the attached link got the setup script to run, but the resultant exe fails. You may want to use his setup.py to see if it works for you, and modify from there.

http://stackoverflow.com/questions/4895153/py2exe-and-numpy-not-getting-along

Sorry to not be able to help more specifically - these bookmarks are all from my toying around with that about 6 months ago...I didn't go any further, I used a different approach for what I was doing.

-t.

ax · Post by ax » Fri Jan 04, 2013 10:28 pm

I haven't used py2exe but if you're trying to distribute an application without code take a look at cython. It pretty simple; here's a setup.py example http://pastebin.com/iZGXAHLP

Tradenator · Post by **Tradenator** » Fri Jan 04, 2013 11:09 pm

tristanreid, thanks, I am using "py2exe-0.6.9.win64-py2.7.amd64.exe" from sourceforge's set of installers. I have started going through the examples at py2exe.org but am not yet done. I've been through most of the matplotlib stuff with no success, but not numpy yet. Pandas scares me because of its multiple dependencies.

ax, thanks, I will take a look at cython after I have been through all the py2exe help files.

I guess I was hoping to get lucky that someone beat me to this problem, as I cannot imagine being at the bleeding edge of anything to do with software.

EDIT: getting there now: numpy, matplotlib and pandas done after much fiddling around with things. The solution seems to be that I need to "exclude" anything that was failing to import. Eg "ImportError: No module named backend_tkagg" gets fixed by excluding "matplotlib.backends.backend_tkagg".

EDIT2: for scipy0.11.0 with py2exe0.6.9 you need to have "scipy.sparse.csgraph._validation" in your "includes" options within setup.py. There is something on stackoverflow about this.