0

Need a small help. Working on the following. Separating rows.

enter image description here

Input: 

Name,  Channel,  Duration, Start_Time   
John, A, 2, 15:55:00    
John, A,    3, 15:57:00 
John,  A,  5, 16:00:00  
Joseph, B, 10, 15:25:00

Output

Name, Channel,  TB, Count, Duration
John, A, 15:30:00-16:00:00,1,5
John,  A,  16:00:00-16:30:00,  1, 5
Joseph, B, 15:00:00-15:30:00, 1,    5
Joseph, B, 15:30:00-16:00:00, 1,    5

Thank you in advance

Taazar
  • 1,545
  • 18
  • 27
Srikanth Ayithy
  • 99
  • 9
  • 3
    Can you please explain the logic? Also please don't add pictures of data. Give a [reproducible example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples/38466059) – Sotos Nov 19 '18 at 10:39

1 Answers1

0

Use - 

df['TB'] = pd.cut(df['Start_time'], bins=pd.date_range(start='15:00:00', end='16:30:00', freq='30min'))

Output

    Name    Channel Duration    Start_Time  Start_time  TB
0   John    A   2   15:55:00    2018-11-19 15:55:00 (2018-11-19 15:30:00, 2018-11-19 16:00:00]
1   John    A   3   15:57:00    2018-11-19 15:57:00 (2018-11-19 15:30:00, 2018-11-19 16:00:00]
2   John    A   5   16:00:00    2018-11-19 16:00:00 (2018-11-19 15:30:00, 2018-11-19 16:00:00]
3   Joseph  B   10  15:25:00    2018-11-19 15:25:00 (2018-11-19 15:00:00, 2018-11-19 15:30:00]

If you want the exact format, do - 

df['TB'] = pd.cut(df['Start_time'], bins=pd.date_range(start='15:00:00', end='16:30:00', freq='30min')).apply(lambda x: ' - '.join(str(x).replace('(','').replace(']','').split(',')))

This will yield - 

    Name    Channel Duration    Start_Time  TB
0   John    A   2   15:55:00    2018-11-19 15:30:00 - 2018-11-19 16:00:00
1   John    A   3   15:57:00    2018-11-19 15:30:00 - 2018-11-19 16:00:00
2   John    A   5   16:00:00    2018-11-19 15:30:00 - 2018-11-19 16:00:00
3   Joseph  B   10  15:25:00    2018-11-19 15:00:00 - 2018-11-19 15:30:00
Vivek Kalyanarangan
  • 8,951
  • 1
  • 23
  • 42
  • df=df['Start_time'].astype('datetime64[D]').dtype df['TB'] = pd.cut(df['Start_time'], bins=pd.date_range(start='20:30:00', end='21:00:00', freq='30min')) Getting an Error - 'There are no fields in dtype datetime64[ns].' Issue is with the data types it appears to be. I tried converting into datetime formats. Still it is showing different datatype error everytime. – Srikanth Ayithy Nov 19 '18 at 15:24