CODEWITHSUNDEEP
pythonpandas
Jan Sila
Jan Sila

Reputation: 1593

finding the earliest occurrence in Python

I'm running into trouble with this: I need to find the first time a user clicks on an email (variable sending) and put a one in that respective row when it occurs.

The dataset has several thousand users (hashed) who click a part of an email in a newsletter. I tried to group them by the sending, hash and then find the earliest date, but could not make it work.

So I went for a little nasty solution, which, however returns strange thing:

My dataset (relevant variables):

>>> clicks[['datetime','hash','sending']].head()

             datetime                              hash  sending
0 2016-11-01 19:13:34  0b1f4745df5925dfb1c8f53a56c43995        5
1 2016-11-01 10:47:14  0a73d5953ebf5826fbb7f3935bad026d        5
2 2016-10-31 19:09:21  605cebbabe0ba1b4248b3c54c280b477        5
3 2016-10-31 13:42:36  d26d61fb10c834292803b247a05b6cb7        5
4 2016-10-31 10:46:30  48f8ab83e8790d80af628e391f3325ad        5

There is 6 sending rounds, the datetime is datetime64[ns].

My way of doing it is as follows: 

clicks['first'] = 0

for hash in clicks['hash'].unique():
    t = clicks.ix[clicks.hash==hash, ['hash','datetime','sending']]
    part = t['sending'].unique()

    for i in part:
        temp = t.ix[t.sending == i,'datetime']
        clicks.ix[t[t.datetime == np.min(temp)].index.values,'first']=1

First of all, I dont think it is very pythonic, and is quite slow. But mostly it returns a weird type! There are 0.0 and 1.0 values, but I cannot work with them: 

    >>> type(clicks.first)
    <type 'instancemethod'>

>>> clicks.loc[clicks.first==1]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/air/anaconda/lib/python2.7/site-packages/pandas/core/indexing.py", line 1296, in __getitem__
    return self._getitem_axis(key, axis=0)
  File "/Users/air/anaconda/lib/python2.7/site-packages/pandas/core/indexing.py", line 1467, in _getitem_axis
    return self._get_label(key, axis=axis)
  File "/Users/air/anaconda/lib/python2.7/site-packages/pandas/core/indexing.py", line 93, in _get_label
    return self.obj._xs(label, axis=axis)
  File "/Users/air/anaconda/lib/python2.7/site-packages/pandas/core/generic.py", line 1749, in xs
    loc = self.index.get_loc(key)
  File "/Users/air/anaconda/lib/python2.7/site-packages/pandas/indexes/base.py", line 1947, in get_loc
    return self._engine.get_loc(self._maybe_cast_indexer(key))
  File "pandas/index.pyx", line 137, in pandas.index.IndexEngine.get_loc (pandas/index.c:4154)
  File "pandas/index.pyx", line 156, in pandas.index.IndexEngine.get_loc (pandas/index.c:3977)
  File "pandas/index.pyx", line 373, in pandas.index.Int64Engine._check_type (pandas/index.c:7634)
KeyError: False

----- UPDATE: ------

  INSTALLED VERSIONS
    ------------------
    commit: None
    python: 2.7.12.final.0
    python-bits: 64
    OS: Darwin
    OS-release: 15.6.0
    machine: x86_64
    processor: i386
    byteorder: little
    LC_ALL: None
    LANG: en_US.UTF-8

    pandas: 0.18.1

Upvotes: 3

Views: 159

Answers (2)

jezrael
jezrael

Reputation: 863146

I think you need groupby with apply where compare values with minimal and output is boolean - need cast to int 0 and 1 by astype:

clicks = pd.DataFrame({'hash': {0: '0b1f4745df5925dfb1c8f53a56c43995', 1: '0a73d5953ebf5826fbb7f3935bad026d', 2: '605cebbabe0ba1b4248b3c54c280b477', 3: '0b1f4745df5925dfb1c8f53a56c43995', 4: '0a73d5953ebf5826fbb7f3935bad026d', 5: '605cebbabe0ba1b4248b3c54c280b477', 6: 'd26d61fb10c834292803b247a05b6cb7', 7: '48f8ab83e8790d80af628e391f3325ad'}, 'sending': {0: 5, 1: 5, 2: 5, 3: 5, 4: 5, 5: 5, 6: 5, 7: 5}, 'datetime': {0: pd.Timestamp('2016-11-01 19:13:34'), 1: pd.Timestamp('2016-11-01 10:47:14'), 2: pd.Timestamp('2016-10-31 19:09:21'), 3: pd.Timestamp('2016-11-01 19:13:34'), 4: pd.Timestamp('2016-11-01 11:47:14'), 5: pd.Timestamp('2016-10-31 19:09:20'), 6: pd.Timestamp('2016-10-31 13:42:36'), 7: pd.Timestamp('2016-10-31 10:46:30')}})
print (clicks)
             datetime                              hash  sending
0 2016-11-01 19:13:34  0b1f4745df5925dfb1c8f53a56c43995        5
1 2016-11-01 10:47:14  0a73d5953ebf5826fbb7f3935bad026d        5
2 2016-10-31 19:09:21  605cebbabe0ba1b4248b3c54c280b477        5
3 2016-11-01 19:13:34  0b1f4745df5925dfb1c8f53a56c43995        5
4 2016-11-01 11:47:14  0a73d5953ebf5826fbb7f3935bad026d        5
5 2016-10-31 19:09:20  605cebbabe0ba1b4248b3c54c280b477        5
6 2016-10-31 13:42:36  d26d61fb10c834292803b247a05b6cb7        5
7 2016-10-31 10:46:30  48f8ab83e8790d80af628e391f3325ad        5

#if column dtype of column datetime is not datetime (with this sample not necessary)
clicks.datetime = pd.to_datetime(clicks.datetime)
clicks['first'] = clicks.groupby(['hash','sending'])['datetime'] \
                        .apply(lambda x: x == x.min()) \
                        .astype(int)
print (clicks)
             datetime                              hash  sending  first
0 2016-11-01 19:13:34  0b1f4745df5925dfb1c8f53a56c43995        5      1
1 2016-11-01 10:47:14  0a73d5953ebf5826fbb7f3935bad026d        5      1
2 2016-10-31 19:09:21  605cebbabe0ba1b4248b3c54c280b477        5      0
3 2016-11-01 19:13:34  0b1f4745df5925dfb1c8f53a56c43995        5      1
4 2016-11-01 11:47:14  0a73d5953ebf5826fbb7f3935bad026d        5      0
5 2016-10-31 19:09:20  605cebbabe0ba1b4248b3c54c280b477        5      1
6 2016-10-31 13:42:36  d26d61fb10c834292803b247a05b6cb7        5      1
7 2016-10-31 10:46:30  48f8ab83e8790d80af628e391f3325ad        5      1

----- UPDATE: ------

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.12.final.0
python-bits: 64
OS: Darwin
OS-release: 15.6.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.18.1

Upvotes: 4

Charles
Charles

Reputation: 4352

Note: I'm not familiar with the pandas module, but I do work with python often (it systems engineering)

Why don't you just use the datetime module? You easily sort them based on the timestamp. For example:

Python 2.7.12 (default, Oct 26 2016, 11:37:25)
[GCC 4.2.1 Compatible Apple LLVM 8.0.0 (clang-800.0.38)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import datetime
>>> fmt = '%Y-%m-%d %H:%S:%M'
>>> timestamps = ['2016-11-01 19:13:34', '2016-11-01 10:47:14',
...               '2016-10-31 19:09:21', '2016-10-31 13:42:36',
...               '2016-10-31 10:46:30']
>>> def compare_dates(d1, d2):
...     d1_dt = datetime.datetime.strptime(d1, fmt)
...     d2_dt = datetime.datetime.strptime(d2, fmt)
...     if d1 > d2:
...         return 1
...     elif d1 == d2:
...         return 0
...     else:
...         return -1
...
>>> timestamps.sort(cmp=compare_dates)
>>> timestamps
['2016-10-31 10:46:30', '2016-10-31 13:42:36', '2016-10-31 19:09:21', '2016-11-01 10:47:14', '2016-11-01 19:13:34']
>>>

As you can see, it's easy to sort dates with the datetime module. Seems trivial to write a comparison function and sort them based on the date to find the earliest occurrence.

Upvotes: 1

Related Questions