How to Prepare Exam for AWS Solutions Architect - Associate

On 24 January 2020, I was excited to collect the trophy I have been dreaming about: the certificate for AWS Solutions Architect - Associate. While the memory is still fresh, I would like to share my story of how to achieve this. My first-hand experience, I hope, will benefit aspiring learners to get certified.

Why I take the exam

The past couple of years have been a disguised blessing for me. I got redundant at work and had personal difficulties, which explains my discontinued activity in this space. At the end of the day, I landed a job at a great organisation where I am able to add data engineering to my career portfolio (check out why I want to do that: https://doctor-fei.blogspot.com/2020/02/why-i-learned-data-engineering-as-from.html). The organisation encourages and supports constant learning among its employees and, as an AWS partner, values AWS certifications probably the most.

In such an environment, not getting certified needs good reasons rather than getting one: all from the healthy peer pressure. Hence my journey begins.

Be clear about what the exam expects from you

AWS services at the first glance are undoubtedly daunting. By 2020 there are 212 services available. No one can learn everything. Therefore a clear goal is the solid stepping stone to success. Make sure what you are expected to learn for this exam. The exam guideline is surely your good friend.

Have a good teacher

While knowing what to learn is good, a good teacher can make your learning experience enjoyable without the pain. I’m lucky that at my work I have free access to a collection of Udemy courses, where I find the prep course by Stephane Maarek outstandingly useful. It precisely covers all you need to know and also teaches you practical knowledge as well as best practices. The course includes two practice tests which I personally find the most close ones to official tests. The importance of practice tests will be discussed shortly.

Learn efficiently

When it comes to your own due diligence, a smart learner absorbs knowledge with efficiency. You should avoid only following the episodes of the online course and hope for the best. Here are my two cents.

Understand the business problems and how cloud services solve them

Each of the services are developed to solve business problems. Pay close attention to what scenarios a service fits in. A better understanding can dramatically speed up your response to exam questions. A typical question in real exam nearly always starts with a problem, “You work for a start-up. They plan to leverage cloud resources to host their company’s website without long term commitment. What is the best EC2 type for their purpose?” Catching the business related keywords “without long term commitment” here enables you to pick up the correction option quickly.

Hands-on practice

Without hands-on practice, even the best memory can slip off your mind faster than you think. I suggest you open your AWS account and follow all the demos in the course. For one reason or another, you would encounter, at least from my own experience, unexpected errors. Don’t get frustrated when this happens. Conversely I urge you to love and embrace them. To solve the issues you have to google and figure out what it means. In doing this, you most likely read AWS documentations and Stackoverflow discussions. Therefore you learn the nitty gritty of the subject. Additionally, the benefit of this troubleshooting experience can go beyond the exam. The errors will probably appear again in your real work and you have known the solution.

Learn repeatedly

Finally, practice tests boost your performance in the exam. You should time all your practice tests. Learn the pace that is the best for you. At the time of this writing, there are 65 questions to answer within 130 minutes. After a few self-tests, I find myself spending about a minute on each question. In the real exam, I have a slow start but I am not panicking because I know statistically I use less than 130 minutes to answer all the questions and everything is OK. Practice tests can also reveal your weaknesses. Treat what you get wrong like you see an error in hands-on. Dive deeper into the AWS documentations or watch the course video again. Repeating the hands-on when there is one is the best.

Conclusion

That’s all I have got to share. My theory is the general rules here isn’t only helpful to your AWS exams but any exams. I hope you like it. Please ask any questions, leave your comments, or share your success in exams below.

Why I Learned Data Engineering as from a Data Scientist

Report from a true personal story

Disclaimer: The opinions in this article are restricted by the scope of my personal expriences. Please do NOT take it as the only advice for planning your future career.

The first job I got after leaving academia was data scientist. I loved the opportunity of crunching numbers as daily activities. But later I realised I must acquire experience in data engineering. Here’s my true story.

The first company I worked for is a small and fast growing consulting firm at that time. I was the only data scientist there. The company was doing well, landing contracts from renowned Australian brands. The projects mostly involve taking data resources form clients data warehouse or data mart (occasionally from source database, which is crazy) building customer views and setting up online, mainly email, marketing campaigns.

My role was supposed to spice the company’s products with artificial intelligence. In a couple of projects I developed customer clustering models. They group customers into natural clusters based on facts, including demographics, focusing primarily on transactional interactions with the brands. For instance purchase frequencies and volumes. The knowledge learned by the algorithm from data informs the clients about the patterns of behaviours in their customers and helps them tailor messages used in the campaign to different cohorts.

Another type of models useful in marketing campaigns I built is churn prediction. Knowing how likely to lose a customer gives the business advantage to offer promotions or discounts to retain the customers at risk.

All sounds interesting then what is the problem?

Statisticians always warn us by saying “Garbage in, garbage out”. The quality of the data asset is vital to data science project. Interestingly but probably not surprisingly, I found, the managing personnel’s attitude to the value of data science models resonates with the maturity of the data they own.

On some occasions the model was built and deployed into production quite smoothly. On many others, it was built but we never heard from the clients about deployment. It also happened that the data was too poor to dream about any valid model.

Similar things happened in my second job. Once the stakeholders were interested in having a model predicting online traffic volume. Sadly it never turns up on the company’s roadmap.

My story might be discouraging for those aspiring to be data scientist but it happens for good reasons. In my opinion, the profound reason is data science modelling as a new comer resides at the end of the data processing pipeline. Normally a pipeline starts from reading data from source systems, transforms it, and stores it in data warehouse to serve reporting views or data marts and maybe the machine learning models.

There are easy to imagine consequences out of this topology. Developing a working model draws least attention during planning meetings, despite people might talk about it a lot when brainstorming for a project. For many projects in this country, as I observe, a machine learning application is something good to have but not essential. Presumably, this is largely influenced by today’s decision-makers who received their education when informative dashboard reporting business performance was the universe. Take-off of the algorithm based models in business will need patience and time.

While I was struggling to prove my value as a data scientist, another role has been too busy to argue about their importance. They are the sometimes behind-the-scene heros who build the pipeline, backbone of any projects: the data engineers. Literally this happens in both of my jobs. My data engineer colleagues at the consulting firm get involved in all projects. How my second job ended is even more better an example to prove my point: I got redundant after an internal restructuring. After a few months even my boss didn’t survive the changes but the data engineer mate in my team kept his job safe before he quit for another job interesting him more.

Hopefully you find my story interesting and useful for your career consideration. I still strongly believe data scientist is the sexist job of the 21st century and I never stop acquiring knowledge for it. However, before this career fully pans out, getting data engineering skills and experiences helps you secure a job in this industry around data.

Please let me know if my story resonates in you or disagree with my opinions. Any criticism is welcomed. Leave your comments below. Peace.

How to Drop only Local Duplicates

This notebook is available on GitHub.

Context¶

The problem encountered was more or less a customer journey. A customer may first do A, and then B, then B again, then C three times, then do B again twice. A series like ABBCCCBB. The goal is to remove the duplicates found in the neighbouring events. If there is another element between two same elements, the two elements are not duplicates. In our example above, we want the final result as ABCB. We try to achieve this with drop_duplicates method of Pandas data frame. In this blog, I would like to share the frustration and lesson I learned from solving this problem.

In [1]:

%matplotlib inline
import pandas as pd
import numpy as np

Problem¶

Unfortunately, there isn't a built-in function in Pandas that can do that. Let's first see what we get with the built-in function.

In [2]:

dat = pd.DataFrame({'event': list('ABBCCCBB')})
dat

Out[2]:

	event
0	A
1	B
2	B
3	C
4	C
5	C
6	B
7	B

In [3]:

dat.drop_duplicates()

Out[3]:

	event
0	A
1	B
3	C

Solution in a Simple Case¶

The method clearly counts there are only three unique elements. However, it drops the trailing B's we actually want to keep. One vital observation leading to the solution is if a cell differs from the cell above, then it should be kept. So let's shift the data frame downwards so we can see a cell and the cell above in the row.

In [4]:

dat.loc[:, 'event_shifted'] = dat.event.shift()
dat.loc[:, 'is_different'] = dat.event != dat.event_shifted
dat

Out[4]:

	event	event_shifted	is_different
0	A	NaN	True
1	B	A	True
2	B	B	False
3	C	B	True
4	C	C	False
5	C	C	False
6	B	C	True
7	B	B	False

Therefore, if I keep only the rows that is_different is true, problem would be solved.

In [5]:

dat.loc[dat.is_different, ['event']]

Out[5]:

	event
0	A
1	B
3	C
6	B

Retrospectively, this totally makes sense as the range of our defination for "duplicates" is limited to the row above rather than the whole column as is assumed in drop_duplicates.

A Little More Complex Case¶

While this simple solution works magically, what about the situation of multiple columns. For example, instead of one we now have to customers. Running the same solution occasionaly will err. Like the example below.

In [6]:

dat = pd.DataFrame({'event': list('ABBCCCBBBBCCCBB'), 'customer_id': [1]*8 + [2]*7})
dat

Out[6]:

	customer_id	event
0	1	A
1	1	B
2	1	B
3	1	C
4	1	C
5	1	C
6	1	B
7	1	B
8	2	B
9	2	B
10	2	C
11	2	C
12	2	C
13	2	B
14	2	B

In [7]:

dat.loc[:, 'event_shifted'] = dat.event.shift()
dat.loc[:, 'is_different'] = dat.event != dat.event_shifted
dat.loc[dat.is_different, ['customer_id', 'event']]

Out[7]:

	customer_id	event
0	1	A
1	1	B
3	1	C
6	1	B
10	2	C
13	2	B

The first event of customer 2 was removed as is the same as the last event of customer 1. Therefore customer id should also be compared.

In [8]:

dat = pd.DataFrame({'event': list('ABBCCCBBBBCCCBB'), 'customer_id': [1]*8 + [2]*7})
shifted = dat.shift()
is_different = (dat.customer_id != shifted.customer_id) | (dat.event != shifted.event)
dat.loc[is_different]

Out[8]:

	customer_id	event
0	1	A
1	1	B
3	1	C
6	1	B
8	2	B
10	2	C
13	2	B

Now we got the correct final data set.

What Learned¶

We find a simple solution to drop duplicates only across neighbouring rows. We fully implement Pandas built-in methods or functions. No iteration through the rows, which means fast speed.

Count Number of Customers at Certain Time Points with Merge_asof

This notebook is available on GitHub.

This will probably look like a trivial example but message I would like to deliver is merge_asof is an awesome tool to merge time series datasets.

It's fairly new, recently added from probably 0.19.0.

First off, let's create some dummy data.

In [1]:

import pandas as pd

In [2]:

signup = pd.DataFrame(pd.date_range('2016-01-01', '2017-01-01', freq='1m'), columns=['signup-date'])

In [3]:

signup

Out[3]:

	signup-date
0	2016-01-31
1	2016-02-29
2	2016-03-31
3	2016-04-30
4	2016-05-31
5	2016-06-30
6	2016-07-31
7	2016-08-31
8	2016-09-30
9	2016-10-31
10	2016-11-30
11	2016-12-31

Now your task is to find how many customers you had at the time points as the following.

In [4]:

check_date = pd.DataFrame(
    [pd.datetime(2016, 4, 17), pd.datetime(2016, 5, 15), pd.datetime(2016, 6, 10)],
    columns=['check-date']
)
check_date

Out[4]:

	check-date
0	2016-04-17
1	2016-05-15
2	2016-06-10

With merge_asof, the joining keys don't have to be equal. By definition, it's a left join. With the default set-up, each row joins to a row in the right dataframe that has the biggest value for the joining key but no greater than the value in the left dataframe.

Hope you can wrap your head around what I just said. Fortunately it's easy to explain it with an example.

Let's merge check_date and signup and focus on the first row having date '2016-04-17'.

When merging happens, it looks up all the dates in signup. Eventually, it finds '2016-03-31'. This is because '2016-03-31' is the last date in signup earlier (smaller) than '2016-04-17'.

Have a look at the mergin results.

In [5]:

pd.merge_asof(check_date, signup, left_on='check-date', right_on='signup-date')

Out[5]:

	check-date	signup-date
0	2016-04-17	2016-03-31
1	2016-05-15	2016-04-30
2	2016-06-10	2016-05-31

With this in mind, we only need one more column showing the number of customers after the customer in that row signed up.

In [6]:

signup.loc[:, 'count'] = list(range(1, len(signup)+1))
signup

Out[6]:

	signup-date	count
0	2016-01-31	1
1	2016-02-29	2
2	2016-03-31	3
3	2016-04-30	4
4	2016-05-31	5
5	2016-06-30	6
6	2016-07-31	7
7	2016-08-31	8
8	2016-09-30	9
9	2016-10-31	10
10	2016-11-30	11
11	2016-12-31	12

Then merge_asof will give us the count at the date requested.

In [7]:

pd.merge_asof(check_date, signup, left_on='check-date', right_on='signup-date')

Out[7]:

	check-date	signup-date	count
0	2016-04-17	2016-03-31	3
1	2016-05-15	2016-04-30	4
2	2016-06-10	2016-05-31	5

There are still a lot more options with which you can tune the behaviour of merge_asof to fit your merging goal. For example, it is possible to join one row in the left dataframe to multiple rows in the right dataframe for time series where you can define a tolerant range, like "with 10 days".

In [8]:

pd.merge_asof(check_date, signup, left_on='check-date', right_on='signup-date', tolerance=pd.Timedelta('10days'))

Out[8]:

	check-date	signup-date	count
0	2016-04-17	NaT	NaN
1	2016-05-15	NaT	NaN
2	2016-06-10	2016-05-31	5.0

Can you see that the first two rows couldn't find any within 10 days backwards?

Now it's your time to explore the usefulness of this tool!

How to Find the First Day in a Streak with shift Method

Context¶

In certain types of businesses, customer visits can be fairly frequent, on daily or weekly basis. Keeping customers maintain their streak can generate constant revenue, which is obviously sweet to business. Marketing professinals would find out what drives customers start their streak. To this end, we may try to figure out what happened on their first day/week. This blog shows you how to locate the first day.

This notebook is available on GitHub.

Prepare Dummy Data¶

Here we create dummy data for demonstration. Assume a customer visited on the following dates saved in a Pandas series. The current streak started on Feb 1, 2000.

In [1]:

import pandas as pd
dates1 = pd.Series(pd.date_range('2000-01-20', '2000-01-30', freq='2D'))
dates2 = pd.Series(pd.date_range('2000-02-01', '2000-02-10', freq='D'))
dates = pd.concat([dates1, dates2]).reset_index(drop=True)
dates

Out[1]:

0    2000-01-20
1    2000-01-22
2    2000-01-24
3    2000-01-26
4    2000-01-28
5    2000-01-30
6    2000-02-01
7    2000-02-02
8    2000-02-03
9    2000-02-04
10   2000-02-05
11   2000-02-06
12   2000-02-07
13   2000-02-08
14   2000-02-09
15   2000-02-10
dtype: datetime64[ns]

Introduce the Tool¶

To get that date, we introduce the shift method. Have a read here if you are interested in more details. This method can move the data entity in named directions with named steps. For example, To move the series downwards a row:

In [2]:

dates.shift()

Out[2]:

0           NaT
1    2000-01-20
2    2000-01-22
3    2000-01-24
4    2000-01-26
5    2000-01-28
6    2000-01-30
7    2000-02-01
8    2000-02-02
9    2000-02-03
10   2000-02-04
11   2000-02-05
12   2000-02-06
13   2000-02-07
14   2000-02-08
15   2000-02-09
dtype: datetime64[ns]

Can you see that all the rows are shifted downwards a row? The new first row is filled with a missing value and the old last row disappeared. What if you want it move upwards? For that you need to give it a negative step:

In [3]:

dates.shift(-1)

Out[3]:

0    2000-01-22
1    2000-01-24
2    2000-01-26
3    2000-01-28
4    2000-01-30
5    2000-02-01
6    2000-02-02
7    2000-02-03
8    2000-02-04
9    2000-02-05
10   2000-02-06
11   2000-02-07
12   2000-02-08
13   2000-02-09
14   2000-02-10
15          NaT
dtype: datetime64[ns]

Operation¶

By now, you probably have already the idea in your mind of what I will do next. After shift, the dates in streak differ in one day, aka, time basis, in the same row. To make this clear, we put the serieses before and after the shifting in one single dataframe. Focus on Row 6.

In [4]:

dates_df = pd.DataFrame(
    {'before': dates, 'after_downwards': dates.shift(), 'after_upwards': dates.shift(-1)},
    columns=['before', 'after_downwards', 'after_upwards']
)
dates_df

Out[4]:

	before	after_downwards	after_upwards
0	2000-01-20	NaT	2000-01-22
1	2000-01-22	2000-01-20	2000-01-24
2	2000-01-24	2000-01-22	2000-01-26
3	2000-01-26	2000-01-24	2000-01-28
4	2000-01-28	2000-01-26	2000-01-30
5	2000-01-30	2000-01-28	2000-02-01
6	2000-02-01	2000-01-30	2000-02-02
7	2000-02-02	2000-02-01	2000-02-03
8	2000-02-03	2000-02-02	2000-02-04
9	2000-02-04	2000-02-03	2000-02-05
10	2000-02-05	2000-02-04	2000-02-06
11	2000-02-06	2000-02-05	2000-02-07
12	2000-02-07	2000-02-06	2000-02-08
13	2000-02-08	2000-02-07	2000-02-09
14	2000-02-09	2000-02-08	2000-02-10
15	2000-02-10	2000-02-09	NaT

Therefore querying the day difference can return the streak area or the non streak area.

Streak area:¶

In [5]:

dates.loc[(dates.shift(-1) - dates).dt.days == 1]

Out[5]:

6    2000-02-01
7    2000-02-02
8    2000-02-03
9    2000-02-04
10   2000-02-05
11   2000-02-06
12   2000-02-07
13   2000-02-08
14   2000-02-09
dtype: datetime64[ns]

Non-streak area:¶

In [6]:

dates.loc[(dates - dates.shift()).dt.days != 1]

Out[6]:

0   2000-01-20
1   2000-01-22
2   2000-01-24
3   2000-01-26
4   2000-01-28
5   2000-01-30
6   2000-02-01
dtype: datetime64[ns]

The intersection is of course the first day!

In [7]:

dates.loc[((dates.shift(-1) - dates).dt.days == 1) & ((dates - dates.shift()).dt.days != 1)]

Out[7]:

6   2000-02-01
dtype: datetime64[ns]

Conclusion¶

This blog shows you how to query the first day of a treak, which is useful for marketing experts to further investigate the drivers for customer's consecutive visits and therefore be able to make the success story bigger.

Note this method can be extended to other time basis like week, month, etc. This method is fast as it uses Pandas idioms instead of for loops.

There is only one streak in the example shown above, however, this method is robust to examples having mutiple streaks.

Hope this trick helps your data mining!

Select Time Series Data within a Window without Date

This notebook is available on GitHub.

Quite often we analyse time series data. Pandas ships with a plethra of out-of-box tools for that purpose, some of which is also easily customisable. Useful tutorials can be found in its documentation.

Context¶

In this blog, I will show you how to how to select data in a time window regardless the date. For instance, the data points between 0 am and 1 am everyday. Start off, we create some dummy data.

In [1]:

from datetime import datetime
import numpy as np
import pandas as pd

In [2]:

times = pd.date_range('2010-01-01', periods=48, freq='H')
dat = pd.Series(np.random.random(len(times)), index=times)
dat

Out[2]:

2010-01-01 00:00:00    0.352284
2010-01-01 01:00:00    0.241143
2010-01-01 02:00:00    0.146389
2010-01-01 03:00:00    0.545087
2010-01-01 04:00:00    0.037369
2010-01-01 05:00:00    0.961454
2010-01-01 06:00:00    0.797817
2010-01-01 07:00:00    0.250311
2010-01-01 08:00:00    0.782042
2010-01-01 09:00:00    0.063493
2010-01-01 10:00:00    0.620593
2010-01-01 11:00:00    0.411620
2010-01-01 12:00:00    0.166519
2010-01-01 13:00:00    0.990196
2010-01-01 14:00:00    0.272079
2010-01-01 15:00:00    0.600731
2010-01-01 16:00:00    0.314604
2010-01-01 17:00:00    0.317686
2010-01-01 18:00:00    0.622527
2010-01-01 19:00:00    0.466031
2010-01-01 20:00:00    0.631393
2010-01-01 21:00:00    0.987018
2010-01-01 22:00:00    0.748490
2010-01-01 23:00:00    0.285035
2010-01-02 00:00:00    0.901956
2010-01-02 01:00:00    0.502751
2010-01-02 02:00:00    0.246344
2010-01-02 03:00:00    0.932478
2010-01-02 04:00:00    0.726944
2010-01-02 05:00:00    0.171858
2010-01-02 06:00:00    0.289558
2010-01-02 07:00:00    0.938964
2010-01-02 08:00:00    0.115186
2010-01-02 09:00:00    0.260931
2010-01-02 10:00:00    0.843929
2010-01-02 11:00:00    0.938050
2010-01-02 12:00:00    0.671973
2010-01-02 13:00:00    0.898492
2010-01-02 14:00:00    0.315328
2010-01-02 15:00:00    0.339190
2010-01-02 16:00:00    0.257052
2010-01-02 17:00:00    0.916770
2010-01-02 18:00:00    0.706080
2010-01-02 19:00:00    0.014060
2010-01-02 20:00:00    0.633365
2010-01-02 21:00:00    0.010800
2010-01-02 22:00:00    0.383637
2010-01-02 23:00:00    0.850857
Freq: H, dtype: float64

Data within any time window is easy to select. Let's see I want data between 1 am and 2 am on Jan 1, 2010. Here is how you do it.

In [3]:

dat.loc['2010-01-01 01:00:00':'2010-01-01 01:59:59']

Out[3]:

2010-01-01 01:00:00    0.241143
Freq: H, dtype: float64

Attempts¶

But the problem is there isn't way to call out data between 1am and 2am everyday as straightward as the above. You can't do something like this.

In [4]:

dat.loc['01:00:00':'01:59:59']

---------------------------------------------------------------------------
OutOfBoundsDatetime                       Traceback (most recent call last)
<ipython-input-4-b3315877ca89> in <module>()
----> 1 dat.loc['01:00:00':'01:59:59']

/home/fei/miniconda3/envs/python3/lib/python3.5/site-packages/pandas/core/indexing.py in __getitem__(self, key)
   1310             return self._getitem_tuple(key)
   1311         else:
-> 1312             return self._getitem_axis(key, axis=0)
   1313 
   1314     def _getitem_axis(self, key, axis=0):

/home/fei/miniconda3/envs/python3/lib/python3.5/site-packages/pandas/core/indexing.py in _getitem_axis(self, key, axis)
   1451         if isinstance(key, slice):
   1452             self._has_valid_type(key, axis)
-> 1453             return self._get_slice_axis(key, axis=axis)
   1454         elif is_bool_indexer(key):
   1455             return self._getbool_axis(key, axis=axis)

/home/fei/miniconda3/envs/python3/lib/python3.5/site-packages/pandas/core/indexing.py in _get_slice_axis(self, slice_obj, axis)
   1332         labels = obj._get_axis(axis)
   1333         indexer = labels.slice_indexer(slice_obj.start, slice_obj.stop,
-> 1334                                        slice_obj.step, kind=self.name)
   1335 
   1336         if isinstance(indexer, slice):

/home/fei/miniconda3/envs/python3/lib/python3.5/site-packages/pandas/tseries/index.py in slice_indexer(self, start, end, step, kind)
   1496 
   1497         try:
-> 1498             return Index.slice_indexer(self, start, end, step, kind=kind)
   1499         except KeyError:
   1500             # For historical reasons DatetimeIndex by default supports

/home/fei/miniconda3/envs/python3/lib/python3.5/site-packages/pandas/indexes/base.py in slice_indexer(self, start, end, step, kind)
   2995         """
   2996         start_slice, end_slice = self.slice_locs(start, end, step=step,
-> 2997                                                  kind=kind)
   2998 
   2999         # return a slice

/home/fei/miniconda3/envs/python3/lib/python3.5/site-packages/pandas/indexes/base.py in slice_locs(self, start, end, step, kind)
   3174         start_slice = None
   3175         if start is not None:
-> 3176             start_slice = self.get_slice_bound(start, 'left', kind)
   3177         if start_slice is None:
   3178             start_slice = 0

/home/fei/miniconda3/envs/python3/lib/python3.5/site-packages/pandas/indexes/base.py in get_slice_bound(self, label, side, kind)
   3113         # For datetime indices label may be a string that has to be converted
   3114         # to datetime boundary according to its resolution.
-> 3115         label = self._maybe_cast_slice_bound(label, side, kind)
   3116 
   3117         # we need to look up the label

/home/fei/miniconda3/envs/python3/lib/python3.5/site-packages/pandas/tseries/index.py in _maybe_cast_slice_bound(self, label, side, kind)
   1450                            getattr(self, 'inferred_freq', None))
   1451             _, parsed, reso = parse_time_string(label, freq)
-> 1452             lower, upper = self._parsed_string_to_bounds(reso, parsed)
   1453             # lower, upper form the half-open interval:
   1454             #   [parsed, parsed + 1 freq)

/home/fei/miniconda3/envs/python3/lib/python3.5/site-packages/pandas/tseries/index.py in _parsed_string_to_bounds(self, reso, parsed)
   1281                           hour=parsed.hour, minute=parsed.minute,
   1282                           second=parsed.second)
-> 1283             return (Timestamp(st, tz=self.tz),
   1284                     Timestamp(Timestamp(st + offsets.Second(),
   1285                                         tz=self.tz).value - 1))

pandas/tslib.pyx in pandas.tslib.Timestamp.__new__ (pandas/tslib.c:10447)()

pandas/tslib.pyx in pandas.tslib.convert_to_tsobject (pandas/tslib.c:28700)()

pandas/tslib.pyx in pandas.tslib._check_dts_bounds (pandas/tslib.c:32835)()

OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 1-01-01 01:00:00

Or you can't do something like this:

In [5]:

dat.loc[(dat.index.time > '01:00:00') & (dat.index.time < '02:00:00')]

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-5-656590af5d6e> in <module>()
----> 1 dat.loc[(dat.index.time > '01:00:00') & (dat.index.time < '02:00:00')]

TypeError: unorderable types: datetime.time() > str()

New Light¶

It is good though, the last example shows us the possibility. The method time returns a datetime.time object.

In [6]:

type(dat.index.time[0])

Out[6]:

datetime.time

Therefore the left question is how to create a datetime.time so on two sides of the binary operator there will be objects of the same type.

The help info isn't that useful.

In [7]:

help(datetime.time)

Help on method_descriptor:

time(...)
    Return time object with same time but with tzinfo=None.

I don't know what can be its arguements. So I just try anything reasonable.

In [8]:

datetime.time('01:00:00')

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-8-a8d40eab456a> in <module>()
----> 1 datetime.time('01:00:00')

TypeError: descriptor 'time' requires a 'datetime.datetime' object but received a 'str'

Nope, it doesn't work but it provides crucial hints. We know time is a method function of datetime.datetime object. The error message reveals that time wants an same object, which is just itself. Knowing this, we can hack it. To get a time, e.g., 1 am, we can create a datetime object, without caring on which date and then we call time function!

In [9]:

one_am = datetime(2000, 1, 1, 1).time()
one_am

Out[9]:

datetime.time(1, 0)

In [10]:

two_am = datetime(2000, 1, 1, 2).time()
two_am

Out[10]:

datetime.time(2, 0)

So after all these struggling, we achieve our goal.

In [11]:

dat.loc[(dat.index.time >= one_am) & (dat.index.time < two_am)]

Out[11]:

2010-01-01 01:00:00    0.241143
2010-01-02 01:00:00    0.502751
Freq: 24H, dtype: float64

Conclusion¶

You can see we get the data in the time window on both dates available! But I don't like hacking, which is unreliable in long term. I will update this blog once I find a better way. Or you can leave a comment if you know how to do it.