pandas - Python - add a timestamp column if meet condition in csv file

Question

I have a csv file that look like this:

       MAC
bc:95:50:0a:82:80
bc:95:50:0a:82:80
bc:95:50:0a:82:80
bc:95:50:0a:82:80

bc:95:50:0a:85:60
bc:95:50:0a:85:60
bc:95:50:0a:85:60
bc:95:50:0a:85:60
bc:95:50:0a:85:60

bc:95:50:9e:58:40
bc:95:50:9e:58:40
bc:95:50:9e:58:40
bc:95:50:9e:58:40
bc:95:50:9e:58:40

There are 3 MAC addresses in my csv file and I want to add a 5 mins timestamp for each MAC address like this:

        MAC             Time
bc:95:50:0a:82:80   2020-11-30 7:05
bc:95:50:0a:82:80   2020-11-30 7:10
bc:95:50:0a:82:80   2020-11-30 7:15
bc:95:50:0a:82:80   2020-11-30 7:20

bc:95:50:0a:85:60   2020-11-30 7:05
bc:95:50:0a:85:60   2020-11-30 7:10
bc:95:50:0a:85:60   2020-11-30 7:15
bc:95:50:0a:85:60   2020-11-30 7:20
bc:95:50:0a:85:60   2020-11-30 7:25

bc:95:50:9e:58:40   2020-11-30 7:05
bc:95:50:9e:58:40   2020-11-30 7:10
bc:95:50:9e:58:40   2020-11-30 7:15
bc:95:50:9e:58:40   2020-11-30 7:20

Right now I can only generate the timestamp:

dt = datetime.datetime(2020, 11, 30, 7, 5, 0)
end = datetime.datetime(2020, 11, 30, 23, 59, 59)
step = datetime.timedelta(minutes=5)

result = []
while dt < end:
    result.append(dt.strftime('%Y-%m-%d %H:%M:%S'))
    dt += step
wtr = csv.writer(open ('out.csv', 'w'), delimiter=',', lineterminator='
')
for x in result:
    wtr.writerow ([x])

But I don't know how to add these timestamps to the MAC column.

question from:https://stackoverflow.com/questions/66056440/python-add-a-timestamp-column-if-meet-condition-in-csv-file

Answer 1

@Henry, I have created the data frame below, but you can read from CSV

df = pd.DataFrame({'MAC': {0: 'bc:95:50:0a:82:80',
  1: 'bc:95:50:0a:82:80',
  2: 'bc:95:50:0a:82:80',
  3: 'bc:95:50:0a:82:80',
  4: 'bc:95:50:0a:85:60',
  5: 'bc:95:50:0a:85:60',
  6: 'bc:95:50:0a:85:60',
  7: 'bc:95:50:0a:85:60',
  8: 'bc:95:50:0a:85:60',
  9: 'bc:95:50:9e:58:40',
  10: 'bc:95:50:9e:58:40',
  11: 'bc:95:50:9e:58:40',
  12: 'bc:95:50:9e:58:40',
  13: 'bc:95:50:9e:58:40'}})

import datetime
import csv
import numpy as np

dt = datetime.datetime(2020, 11, 30, 7, 5, 0)
step = datetime.timedelta(minutes=5)
  
df['cumct'] = df.groupby('MAC').cumcount()+1
df['date'] = df['cumct'] * step + dt
df = df.drop(columns='cumct')
df.to_csv('out.csv', index=False)

The out.csv looks like this -

MAC,date
bc:95:50:0a:82:80,2020-11-30 07:10:00
bc:95:50:0a:82:80,2020-11-30 07:15:00
bc:95:50:0a:82:80,2020-11-30 07:20:00
bc:95:50:0a:82:80,2020-11-30 07:25:00
bc:95:50:0a:85:60,2020-11-30 07:10:00
bc:95:50:0a:85:60,2020-11-30 07:15:00
bc:95:50:0a:85:60,2020-11-30 07:20:00
bc:95:50:0a:85:60,2020-11-30 07:25:00
bc:95:50:0a:85:60,2020-11-30 07:30:00
bc:95:50:9e:58:40,2020-11-30 07:10:00
bc:95:50:9e:58:40,2020-11-30 07:15:00
bc:95:50:9e:58:40,2020-11-30 07:20:00
bc:95:50:9e:58:40,2020-11-30 07:25:00
bc:95:50:9e:58:40,2020-11-30 07:30:00