Plotting Weekly Tick Data From A Year Time Series

Question

I have a time series that lists tick price data for a futures contract for a few months worth of trading history. I would like to have one plot (line chart) that shows the trading history of the tick data for each week for the most recent 4 weeks in the time series (the series is continually being updated)

The X axis will show the days Monday to Friday and there will be 4 separate lines on the chart at any one time detailing the tick data. I have managed to do this using some code that plots the last trade on each day but I need the tick data plotting instead of just one data point per day for each line.

Here is an Excel Chart (!) of what I am trying to represent with tick data, only the lines will be a lot more volatile as there will be more data points.
Essentially, 4 lines on one chart showing the past four weeks of trading tick data:

weekly plot

I don't have the code that I tried with me (its at work) but can upload tomorrow.

Do you have an example of what you're looking for, such as a link or an image that you can put up? Also, please post what you've already tried. — Phillip Cloud, Aug 14 '13 at 21:38

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

To give you some fake data:

In [11]: rng = pd.date_range('2013', freq='H', periods=1000)

In [12]: df = pd.DataFrame(np.random.randn(len(rng)), index=rng, columns=['data'])

First, populate the week number (as a column):

In [13]: df['week'] = df.index.week

Next, calculate the time since the start of the week (there may be a more elegant method):

In [14]: df['week_beginning'] = df.index.to_period('W').to_timestamp()

In [15]: df['week_time'] = df.index.to_series() - df['week_beginning']

Now you can use a pivot_table:

In [16]: df.pivot_table(values='data', rows='week_time', cols='week')
Out[16]: 
<class 'pandas.core.frame.DataFrame'>
Int64Index: 168 entries, 0 to 601200000000000
Data columns (total 7 columns):
33    120  non-null values
34    168  non-null values
35    168  non-null values
36    168  non-null values
37    168  non-null values
38    168  non-null values
39    40  non-null values
dtypes: float64(7)

It sounds like this is the thing you want to plot (using the technique found in your previous question):

In [17]: df.pivot_table('data', 'week_time', 'week').rename(columns=lambda x: 'Week ' + str(x)).plot()

Note: this particular plot is rather messy, since there are a lot of datapoints, it may make sense to aggregate some of this data beforehand.

edited May 23 '17 at 11:51

Community

1
1

answered Aug 14 '13 at 22:45

Andy Hayden

359,921
101
625
535

Perfect! Just tried out the code and it is exactly what I need. – Ross Middleton Aug 14 '13 at 22:59
One quick thing, is there any way to just plot the most recent four weeks of data? – Ross Middleton Aug 14 '13 at 22:59
if it's in the (pivoted) format above then use `pv[pv.columns[-4:]]` :) – Andy Hayden Aug 14 '13 at 23:16
Is there any way to change the X axis labels (['week_time']) to Mon,Tue,Wed,Thurs,Fri? – Ross Middleton Aug 15 '13 at 09:20
good question, I think so (should be able to change the labels with matplotlib options, not 100% on how to do it though) see http://stackoverflow.com/questions/11244514/matplotlib-modify-tick-label-text – Andy Hayden Aug 15 '13 at 10:32
Yeah I cant figure it out either :-( – Ross Middleton Aug 16 '13 at 09:02
I think it could be worth asking a specific question about how to save a figure created from a `df.plot()`... also seems like it could be a good enhancement to give a savefig argument to plot. – Andy Hayden Aug 16 '13 at 10:58

Plotting Weekly Tick Data From A Year Time Series

1 Answers1