Menu
How do I convert column B into the transition matrix in python?
Size of the matrix is 19 which is unique values in column B. There are a total of 432 rows in the dataset.
time A B
2017-10-26 09:00:00 36 816
2017-10-26 10:45:00 43 816
2017-10-26 12:30:00 50 998
2017-10-26 12:45:00 51 750
2017-10-26 13:00:00 52 998
2017-10-26 13:15:00 53 998
2017-10-26 13:30:00 54 998
2017-10-26 14:00:00 56 998
2017-10-26 14:15:00 57 834
2017-10-26 14:30:00 58 1285
2017-10-26 14:45:00 59 1288
2017-10-26 23:45:00 95 1285
2017-10-27 03:00:00 12 1285
2017-10-27 03:30:00 14 1285
...
2017-11-02 14:00:00 56 998
2017-11-02 14:15:00 57 998
2017-11-02 14:30:00 58 998
2017-11-02 14:45:00 59 998
2017-11-02 15:00:00 60 816
2017-11-02 15:15:00 61 275
2017-11-02 15:30:00 62 225
2017-11-02 15:45:00 63 1288
2017-11-02 16:00:00 64 1088
2017-11-02 18:15:00 73 1285
2017-11-02 20:30:00 82 1285
2017-11-02 21:00:00 84 1088
2017-11-02 21:15:00 85 1088
2017-11-02 21:30:00 86 1088
2017-11-02 22:00:00 88 1088
2017-11-02 22:30:00 90 1088
2017-11-02 23:00:00 92 1088
2017-11-02 23:30:00 94 1088
2017-11-02 23:45:00 95 1088
The matrix should contain the number of transition between them.
B -----------------1088------1288----------------------------
B
.
.
1088 8 2
.
.
.
.
. Number of transitions between them.
..
.
.
652
I use your data to create DataFrame only with column B but it should work also with all columns.
text = '''time A B
2017-10-26 09:00:00 36 816
2017-10-26 10:45:00 43 816
2017-10-26 12:30:00 50 998
2017-10-26 12:45:00 51 750
2017-10-26 13:00:00 52 998
2017-10-26 13:15:00 53 998
2017-10-26 13:30:00 54 998
2017-10-26 14:00:00 56 998
2017-10-26 14:15:00 57 834
2017-10-26 14:30:00 58 1285
2017-10-26 14:45:00 59 1288
2017-10-26 23:45:00 95 1285
2017-10-27 03:00:00 12 1285
2017-10-27 03:30:00 14 1285
2017-11-02 14:00:00 56 998
2017-11-02 14:15:00 57 998
2017-11-02 14:30:00 58 998
2017-11-02 14:45:00 59 998
2017-11-02 15:00:00 60 816
2017-11-02 15:15:00 61 275
2017-11-02 15:30:00 62 225
2017-11-02 15:45:00 63 1288
2017-11-02 16:00:00 64 1088
2017-11-02 18:15:00 73 1285
2017-11-02 20:30:00 82 1285
2017-11-02 21:00:00 84 1088
2017-11-02 21:15:00 85 1088
2017-11-02 21:30:00 86 1088
2017-11-02 22:00:00 88 1088
2017-11-02 22:30:00 90 1088
2017-11-02 23:00:00 92 1088
2017-11-02 23:30:00 94 1088
2017-11-02 23:45:00 95 1088'''
import pandas as pd
B = [int(row[29:].strip()) for row in text.split('\n') if 'B' not in row]
df = pd.DataFrame({'B': B})
I get unique values in colum to use it later to create matrix
numbers = sorted(df['B'].unique())
print(numbers)
[225, 275, 750, 816, 834, 998, 1088, 1285, 1288]
I create shifted column C so I have both values in every row
df['C'] = df.shift(-1)
print(df)
B C
0 816 816.0
1 816 998.0
2 998 750.0
3 750 998.0
I group by ['B', 'C'] so I can count pairs
groups = df.groupby(['B', 'C'])
counts = {i[0]:(len(i[1]) if i[0][0] != i[0][1] else 0) for i in groups} # don't count (816,816)
# counts = {i[0]:len(i[1]) for i in groups} # count even (816,816)
print(counts)
{(225, 1288.0): 2, (275, 225.0): 2, (750, 998.0): 2, (816, 275.0): 2, (816, 816.0): 2, (816, 998.0): 2, (834, 1285.0): 2, (998, 750.0): 2, (998, 816.0): 2, (998, 834.0): 2, (998, 998.0): 12, (1088, 1088.0): 14, (1088, 1285.0): 2, (1285, 998.0): 2, (1285, 1088.0): 2, (1285, 1285.0): 6, (1285, 1288.0): 2, (1288, 1088.0): 2, (1288, 1285.0): 2}
Now I can create matrix. Using numbers and counts I create column/Series (with correct index) and I add it to matrix.
matrix = pd.DataFrame()
for x in numbers:
matrix[x] = pd.Series([counts.get((x,y), 0) for y in numbers], index=numbers)
print(matrix)
Result
225 275 750 816 834 998 1088 1285 1288
225 0 2 0 0 0 0 0 0 0
275 0 0 0 2 0 0 0 0 0
750 0 0 0 0 0 2 0 0 0
816 0 0 0 2 0 2 0 0 0
834 0 0 0 0 0 2 0 0 0
998 0 0 2 2 0 12 0 2 0
1088 0 0 0 0 0 0 14 2 2
1285 0 0 0 0 2 0 2 6 2
1288 2 0 0 0 0 0 0 2 0
Full example
text = '''time A B
2017-10-26 09:00:00 36 816
2017-10-26 10:45:00 43 816
2017-10-26 12:30:00 50 998
2017-10-26 12:45:00 51 750
2017-10-26 13:00:00 52 998
2017-10-26 13:15:00 53 998
2017-10-26 13:30:00 54 998
2017-10-26 14:00:00 56 998
2017-10-26 14:15:00 57 834
2017-10-26 14:30:00 58 1285
2017-10-26 14:45:00 59 1288
2017-10-26 23:45:00 95 1285
2017-10-27 03:00:00 12 1285
2017-10-27 03:30:00 14 1285
2017-11-02 14:00:00 56 998
2017-11-02 14:15:00 57 998
2017-11-02 14:30:00 58 998
2017-11-02 14:45:00 59 998
2017-11-02 15:00:00 60 816
2017-11-02 15:15:00 61 275
2017-11-02 15:30:00 62 225
2017-11-02 15:45:00 63 1288
2017-11-02 16:00:00 64 1088
2017-11-02 18:15:00 73 1285
2017-11-02 20:30:00 82 1285
2017-11-02 21:00:00 84 1088
2017-11-02 21:15:00 85 1088
2017-11-02 21:30:00 86 1088
2017-11-02 22:00:00 88 1088
2017-11-02 22:30:00 90 1088
2017-11-02 23:00:00 92 1088
2017-11-02 23:30:00 94 1088
2017-11-02 23:45:00 95 1088'''
import pandas as pd
B = [int(row[29:].strip()) for row in text.split('\n') if 'B' not in row]
df = pd.DataFrame({'B': B})
numbers = sorted(df['B'].unique())
print(numbers)
df['C'] = df.shift(-1)
print(df)
groups = df.groupby(['B', 'C'])
counts = {i[0]:(len(i[1]) if i[0][0] != i[0][1] else 0) for i in groups} # don't count (816,816)
# counts = {i[0]:len(i[1]) for i in groups} # count even (816,816)
print(counts)
matrix = pd.DataFrame()
for x in numbers:
matrix[str(x)] = pd.Series([counts.get((x,y), 0) for y in numbers], index=numbers)
print(matrix)
EDIT:
counts = {i[0]:(len(i[1]) if i[0][0] != i[0][1] else 0) for i in groups} # don't count (816,816)
as normal for loop
counts = {}
for pair, group in groups:
if pair[0] != pair[1]: # don't count (816,816)
counts[pair] = len(group)
else:
counts[pair] = 0
Invert value when it is bigger thant 10
counts = {}
for pair, group in groups:
if pair[0] != pair[1]: # don't count (816,816)
count = len(group)
if count > 10 :
counts[pair] = -count
else
counts[pair] = count
else:
counts[pair] = 0
EDIT:
counts = {}
for pair, group in groups:
if pair[0] != pair[1]: # don't count (816,816)
#counts[(A,B)] = len((A,B)) + len((B,A))
if pair not in counts:
counts[pair] = len(group) # put first value
else:
counts[pair] += len(group) # add second value
#counts[(B,A)] = len((A,B)) + len((B,A))
if (pair[1],pair[0]) not in counts:
counts[(pair[1],pair[0])] = len(group) # put first value
else:
counts[(pair[1],pair[0])] += len(group) # add second value
else:
counts[pair] = 0 # (816,816) gives 0
#counts[(A,B)] == counts[(B,A)]
counts_2 = {}
for pair, count in counts.items():
if count > 10 :
counts_2[pair] = -count
else:
counts_2[pair] = count
matrix = pd.DataFrame()
for x in numbers:
matrix[str(x)] = pd.Series([counts_2.get((x,y), 0) for y in numbers], index=numbers)
print(matrix)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
