Good evening!
I am dealing with a LOG file of WIFI connections. Data will be analyzed by time interval every 15 minutes and users' connections will be grouped by connection location within the time interval. The location that has more connections from the same user in the same time interval will be counted.
Exemple Dataframe.
CSV download link. (https://github.com/thiagoramosws/wifi/blob/main/entrada_locais_aps.csv)
INPUT
{'User-Name': {0: 'Thiago',
1: 'Ana',
2: 'Ana',
3: 'Ana',
4: 'Jose',
5: 'Thiago',
6: 'Thiago',
7: 'Thiago',
8: 'Ana',
9: 'Thiago',
10: 'Thiago',
11: 'Thiago',
12: 'Thiago',
13: 'Ana',
14: 'Thiago',
15: 'Thiago',
16: 'Jose'},
'NAS-Identifier': {0: 'Cantina',
1: 'Cantina',
2: 'Cantina',
3: 'Dex',
4: 'Dcc',
5: 'Cantina',
6: 'Cantina',
7: 'Cantina',
8: 'Dex',
9: 'Cantina',
10: 'Dex',
11: 'Dex',
12: 'Dcc',
13: 'Dex',
14: 'Dcc',
15: 'Dcc',
16: 'Dex'},
'Event-Timestamp': {0: 'May 6 2021 08:01:00',
1: 'May 6 2021 08:01:00',
2: 'May 6 2021 08:03:00',
3: 'May 6 2021 08:04:00',
4: 'May 6 2021 08:05:00',
5: 'May 6 2021 08:06:00',
6: 'May 6 2021 08:07:00',
7: 'May 6 2021 08:08:00',
8: 'May 6 2021 08:09:00',
9: 'May 6 2021 08:10:00',
10: 'May 6 2021 08:11:00',
11: 'May 6 2021 08:12:00',
12: 'May 6 2021 08:13:00',
13: 'May 6 2021 08:14:00',
14: 'May 6 2021 08:18:00',
15: 'May 6 2021 08:23:00',
16: 'May 6 2021 08:25:00'},
'Type': {0: 'update',
1: 'update',
2: 'stop',
3: 'start',
4: ' stop',
5: 'update',
6: 'start',
7: 'start',
8: 'update',
9: 'stop',
10: 'start',
11: 'stop',
12: 'start',
13: 'update',
14: 'update',
15: 'update',
16: ' start'}}
I used the following code to test if the user is within the time range:
df_registro = pd.read_csv("entrada_locais_aps.csv")
df_registro['Event-Timestamp'] = df_registro['Event-Timestamp'].apply(lambda x: datetime.strptime(x,'%b %d %Y %H:%M:%S') #converting Event-Timestamp column to datetime type
horarios = {
'intervalo': {
1: (time(7, 0), time(7, 15)), # range 1 = 07:00h - 07:15h
2: (time(7, 15), time(7, 30)), # range 2 = 07:15h - 07:30h
3: (time(7, 30), time(7, 45)), # range 3 = 07:30h - 07:45h
4: (time(7, 45), time(8, 0)), # range 4 = 07:45h - 08:00h
5: (time(8, 0), time(8, 15)), # range 5 = 08:00h - 08:15h
6: (time(8, 15), time(8, 30)), # range 6 = 08:15h - 08:30h
7: (time(8, 30), time(8, 45)), # range 7 = 08:30h - 08:45h
8: (time(8, 45), time(9, 0)), # range 8 = 08:45h - 09:00h
}
}
encontrado = False
for nome, intervalo in horarios.items():
i=1
lista=[]
while i < 9: #I have 8 intervals of 15 in 15 minutes
y=0
inicio, fim = intervalo[i]
while y < 15: #15 is number of register dataframe
registro2 = df_registro['Event-Timestamp'].dt.time[y]
if inicio <= registro2 < fim: # checks if time is between the start and end of the range
print(df_registro['User-Name'][y], "-",df_registro['NAS-Identifier'][y])
print(f'The record is within the range {inicio:%H:%M} to {fim:%H:%M}')
print("--------------")
#Creates a list with User, Location and Time data. In this creation when changing the time range, it should change the list.
lista.append([df_registro['User-Name'][y],df_registro['NAS-Identifier'][y],df_registro['Event-Timestamp'].dt.time[y]])
encontrado = True
y += 1
i += 1
if not encontrado:
print('Register out of range')
RESULT:
Thiago - Cantina
The record is within the range 08:00 to 08:15
Ana - Cantina
The record is within the range 08:00 to 08:15
Ana - Cantina
The record is within the range 08:00 to 08:15
Ana - DEX
The record is within the range 08:00 to 08:15
Jose - DCC
The record is within the range 08:00 to 08:15
Thiago - Cantina
The record is within the range 08:00 to 08:15
Thiago - Cantina
The record is within the range 08:00 to 08:15
Thiago - Cantina
The record is within the range 08:00 to 08:15
Ana - DEX
The record is within the range 08:00 to 08:15
Thiago - Cantina
The record is within the range 08:00 to 08:15
Thiago - DEX
The record is within the range 08:00 to 08:15
Thiago - DEX
The record is within the range 08:00 to 08:15
Thiago - DCC
The record is within the range 08:00 to 08:15
Ana - DEX
The record is within the range 08:00 to 08:15
Thiago - DCC
The record is within the range 08:15 to 08:30
Now I need to count users by connection location within the time range. I used the following command:
df_registro3 = pd.DataFrame(lista,columns=['Name', 'Place', 'Hour'])
df4 = df_registro3.groupby(['Name','Place']).count(
print(df4)
Result:
Name Place
Ana Cantina 2
DEX 3
Jose DCC 1
Thiago Cantina 5
DCC 2
DEX 2
In this output is the error code. In user Thiago, the DCC location could not be considered twice, because one of the records is in another time interval. The connection was at 08:18, so it should be in the range 08:15 to 08:30. The problem is in creating the list. I tried to increment the name of the list so that each interval was a different name of the list but it didn't work.
Can anyone help?