CODEWITHSUNDEEP
pythonpandaspandas-groupby
Seb
Seb

Reputation: 37

PYTHON - PANDAS - Groupby update row value

I have a pandas df which looks like that (I duplicated each row) :

          START                END               
0 2018-03-02 23:56:02  2018-03-03 01:25:50 
1 2018-03-03 23:44:10  2018-03-04 03:03:05 
2 2018-02-05 21:57:06  2018-02-06 08:25:19
3 2018-02-06 19:30:00  2018-02-07 09:04:13
4 2018-02-07 21:51:07  2018-02-08 08:13:34
0 2018-03-02 23:56:02  2018-03-03 01:25:50 
1 2018-03-03 23:44:10  2018-03-04 03:03:05
2 2018-02-05 21:57:06  2018-02-06 08:25:19
3 2018-02-06 19:30:00  2018-02-07 09:04:13
4 2018-02-07 21:51:07  2018-02-08 08:13:34

I'd like tu update rows to look like that :

          START                END               
0 2018-03-02 23:56:02  **2018-03-02 23:59:59** 
1 2018-03-03 23:44:10  **2018-03-03 23:59:59** 
2 2018-02-05 21:57:06  **2018-02-05 23:59:59**
3 2018-02-06 19:30:00  **2018-02-06 23:59:59**
4 2018-02-07 21:51:07  **2018-02-07 23:59:59**
0 **2018-03-03 00:00:00**  2018-03-03 01:25:50 
1 **2018-03-04 00:00:00**  2018-03-04 03:03:05
2 **2018-02-06 00:00:00**  2018-02-06 08:25:19
3 **2018-02-07 00:00:00**  2018-02-07 09:04:13
4 **2018-02-08 00:00:00**  2018-02-08 08:13:34

I tried to use groupby with head or tail but it doesn't work :

df.loc[df.groupby(df.index).head(1).index, 'END'] = df.START.replace(hour=23, minute=59, second=59)
df.loc[df.groupby(df.index).tail(1).index, 'START'] = df.END.replace(hour=0, minute=0, second=0)

I think I'm missing something.Thanks for you help.

Upvotes: 1

Views: 73

Answers (2)

jezrael
jezrael

Reputation: 862431

print (df)
                START                 END
0 2018-03-02 23:56:02 2018-03-03 01:25:50
1 2018-03-03 23:44:10 2018-03-04 03:03:05
2 2018-02-05 21:57:06 2018-02-06 08:25:19
3 2018-02-06 19:30:00 2018-02-07 09:04:13
4 2018-02-07 21:51:07 2018-02-08 08:13:34

First use dt.floor for set start and end dates:

df1, df2 = df.copy(), df.copy()
df1['END'] = df1.START.dt.floor('d') + pd.Timedelta(1, unit='d') - pd.Timedelta(1, unit='s')
df2['START'] = df2.END.dt.floor('d')

And last concat:

df = pd.concat([df1,df2], ignore_index=True)
print (df)
                START                 END
0 2018-03-02 23:56:02 2018-03-02 23:59:59
1 2018-03-03 23:44:10 2018-03-03 23:59:59
2 2018-02-05 21:57:06 2018-02-05 23:59:59
3 2018-02-06 19:30:00 2018-02-06 23:59:59
4 2018-02-07 21:51:07 2018-02-07 23:59:59
5 2018-03-03 00:00:00 2018-03-03 01:25:50
6 2018-03-04 00:00:00 2018-03-04 03:03:05
7 2018-02-06 00:00:00 2018-02-06 08:25:19
8 2018-02-07 00:00:00 2018-02-07 09:04:13
9 2018-02-08 00:00:00 2018-02-08 08:13:34

Instead floor is possible use slowier apply + replace:

df1['END'] = df1.START.apply(lambda x: x.replace(hour=23, minute=59, second=59))
df2['START'] = df2.END.apply(lambda x: x.replace(hour=0, minute=0, second=0))

Timings:

df = pd.concat([df] * 10000, ignore_index=True)


In [242]: %%timeit
     ...: df1, df2 = df.copy(), df.copy()
     ...: df1['END'] = df1.START.dt.floor('d') + pd.Timedelta(1, unit='d') - pd.Timedelta(1, unit='s')
     ...: df2['START'] = df2.END.dt.floor('d')
     ...: 
100 loops, best of 3: 19.1 ms per loop

In [243]: %%timeit 
     ...: df1, df2 = df.copy(), df.copy()
     ...: df1['END'] = df1.START.apply(lambda x: x.replace(hour=23, minute=59, second=59))
     ...: df2['START'] = df2.END.apply(lambda x: x.replace(hour=0, minute=0, second=0))
     ...: 
1 loop, best of 3: 534 ms per loop

Upvotes: 3

Anne
Anne

Reputation: 593

Trying to formulate what you want to do:

For each row that is duplicate,
* create 1 row with the begin time (and replace end time)
* create 1 row with the end time (and replace start time)

Maybe it helps to use the duplicated function?

df[df.duplicated(keep='first')]

should return the first half where you can then replace endtime, likewise you use

df[df.duplicated(keep='last')]

for the other half.

You can read more about the function here: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.duplicated.html

Upvotes: 0

Related Questions