CODEWITHSUNDEEP
pythonpandasloopsdategroup-by
XaviorL
XaviorL

Reputation: 373

Pandas: Groupby, loop and add one hour iterate with conditions within group

I have a df as below, there are some repeated date records for each person, I wish to keep the exactly same sequence/order for the rest of the data, but wish to add one hour for the duplicate date record only.

df = pd.DataFrame({
    'name': ['Jim', 'Jim', 'Jim', 'Jim', 'Mike', 'Mike', 'Mike', 'Mike',
           'Polo', 'Polo', 'Polo', 'Polo', 'Polo', 'Tom', 'Tom', 'Tom', 'Tom'],
    'Item ID': [80, 68, 751, 'Started', 32, 126, 68,  'Started', 105, 68, 251, 76, 'Started', 82, 251, 23, "Started"],
    'Date':["2018-02-15", "2018-02-14", "2015-08-17", "2015-08-17",
            "2018-09-14", "2018-06-01", "2018-06-01", "2018-05-31",
            "2017-06-01", "2017-06-01", "2014-12-01", "2014-11-23", "2013-08-11",
            "2017-07-14", "2016-02-16", "2016-02-16", "2015-06-05"],
})

    name  Item ID        Date
0    Jim       80  2018-02-15
1    Jim       68  2018-02-14
2    Jim      751  2015-08-17 # duplicate date for Jim, add one hour here
3    Jim  Started  2015-08-17
4   Mike       32  2018-09-14
5   Mike      126  2018-06-01 # duplicate date for Mike, add one hour here
6   Mike       68  2018-06-01
7   Mike  Started  2018-05-31
8   Polo      105  2017-06-01 # duplicate date for Polo, add one hour here
9   Polo       68  2017-06-01
10  Polo      251  2014-12-01
11  Polo       76  2014-11-23
12  Polo  Started  2013-08-11
13   Tom       82  2017-07-14
14   Tom      251  2016-02-16 # duplicate date for Tom, add one hour here
15   Tom       23  2016-02-16
16   Tom  Started  2015-06-05

I wrote some codes but not working well and very unproductive. If anyone had any ideas please help, a great thanks. My expected result:

    name  Item ID                 Date
0    Jim       80           2018-02-15
1    Jim       68           2018-02-14
2    Jim      751         2015-08-17 00:01:00 # added
3    Jim  Started           2015-08-17
4   Mike       32           2018-09-14
5   Mike      126         2018-06-01 00:01:00 # added
6   Mike       68           2018-06-01
7   Mike  Started           2018-05-31
8   Polo      105         2017-06-01 00:01:00 # added
9   Polo       68           2017-06-01
10  Polo      251           2014-12-01
11  Polo       76           2014-11-23
12  Polo  Started           2013-08-11
13   Tom       82           2017-07-14
14   Tom      251         2016-02-16 00:01:00 # added
15   Tom       23           2016-02-16
16   Tom  Started           2015-06-05

Upvotes: 3

Views: 91

Answers (2)

Chris
Chris

Reputation: 16147

df['Date'] = pd.to_datetime(df['Date'])
df.loc[df.duplicated(subset=['name','Date'], keep='last'), 'Date'] = df['Date'] + pd.DateOffset(hours=1)

Output

    name     Item ID                  Date
0   Jim      80        2018-02-15 00:00:00
1   Jim      68        2018-02-14 00:00:00
2   Jim      751       2015-08-17 01:00:00
3   Jim  Started       2015-08-17 00:00:00
4   Mike     32        2018-09-14 00:00:00
5   Mike    126        2018-06-01 01:00:00
6   Mike     68        2018-06-01 00:00:00
7   Mike Started       2018-05-31 00:00:00
8   Polo    105        2017-06-01 01:00:00
9   Polo     68        2017-06-01 00:00:00
10  Polo    251        2014-12-01 00:00:00
11  Polo     76        2014-11-23 00:00:00
12  Polo    Started    2013-08-11 00:00:00
13  Tom      82        2017-07-14 00:00:00
14  Tom     251        2016-02-16 01:00:00
15  Tom      23        2016-02-16 00:00:00
16  Tom Started        2015-06-05 00:00:00

Upvotes: 2

BENY
BENY

Reputation: 323306

We can do duplicated with reversed order by iloc, then add the hour to original Date

df.Date=pd.to_datetime(df.Date)+pd.to_timedelta(df.iloc[::-1].duplicated(['name','Date']).astype(int),unit='hour')
df
    name  Item ID                Date
0    Jim       80 2018-02-15 00:00:00
1    Jim       68 2018-02-14 00:00:00
2    Jim      751 2015-08-17 01:00:00
3    Jim  Started 2015-08-17 00:00:00
4   Mike       32 2018-09-14 00:00:00
5   Mike      126 2018-06-01 01:00:00
6   Mike       68 2018-06-01 00:00:00
7   Mike  Started 2018-05-31 00:00:00
8   Polo      105 2017-06-01 01:00:00
9   Polo       68 2017-06-01 00:00:00
10  Polo      251 2014-12-01 00:00:00
11  Polo       76 2014-11-23 00:00:00
12  Polo  Started 2013-08-11 00:00:00
13   Tom       82 2017-07-14 00:00:00
14   Tom      251 2016-02-16 01:00:00
15   Tom       23 2016-02-16 00:00:00
16   Tom  Started 2015-06-05 00:00:00

Upvotes: 4

Related Questions