CODEWITHSUNDEEP
pythonmatplotlibpandastime-series
Ross Middleton
Ross Middleton

Reputation: 237

Plotting Weekly Tick Data From A Year Time Series

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.

Upvotes: 2

Views: 1997

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375805

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.

Upvotes: 1

Related Questions