CODEWITHSUNDEEP
pandas
qwerty
qwerty

Reputation: 887

Creating index by ID and Time - Pandas

I've got data frame with ID's and time (date and time). I want to index this data by ID (sorted) and time (sorted), where the date and time are separated. Also, to create variable "Weekend" that get a 1 at weekend (Saturday or Sunday), and a 0 otherwise.

Data frame demonstration:

ID  Date_n_time             X
1   08/27/2019 08:40:04     2
3   07/27/2019 08:40:04     1
1   08/27/2019 09:40:04     7
2   07/27/2019 08:50:00     3
3   07/29/2019 08:40:04     4
1   08/28/2019 07:40:03     5
3   07/29/2019 08:41:05     6

Expected result:

ID  Date        Time        X   Weekend
1   08/27/2019  08:40:04    2   0
                09:40:04    7   0
    08/28/2019  07:40:03    5   0
2   07/27/2019  08:50:00    3   1
3   07/27/2019  08:40:04    1   1
    07/29/2019  08:40:04    4   0
                08:41:05    6   0

Upvotes: 1

Views: 391

Answers (2)

user3483203
user3483203

Reputation: 51175

This is just a couple dt lookups and a sort. To find the weekend, just use .dt.weekday, and some math to make sure it's either Saturday or Sunday.

s = df['Date_n_time'].dt

d = dict(
    Date=s.date,
    Time=s.time,
    Weekend=(s.weekday // 5),
)

df.drop('Date_n_time', 1).assign(**d).set_index(['ID', 'Date', 'Time']).sort_index()


                        X  Weekend
ID Date       Time
1  2019-08-27 08:40:04  2        0
              09:40:04  7        0
   2019-08-28 07:40:03  5        0
2  2019-07-27 08:50:00  3        1
3  2019-07-27 08:40:04  1        1
   2019-07-29 08:40:04  4        0
              08:41:05  6        0

Upvotes: 1

maow
maow

Reputation: 2887

If it is not already convert the column date the datetime datatype with

df['Date_n_time'] = pd.to_datetime(df['Date_n_time'])

Then you can use the member functions of the Timestamp class documented here: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Timestamp.html

You probably need 

df['Date'] = df['Date_n_time'].dt.date()
df['X'] = df['Date_n_time'].dt.weekday

and the other two you need to lookup yourself ;) If there isi no function available you can always specify your own format with strftime

Upvotes: 0

Related Questions