I have a dataframe which demonstrates a hierarchy of meters. A meter has an ID, and can have any number of children, this children can also have children, which can also have children, ad infinitum.
The dataframe has a meter per row, and the level of the child is shown by column. As shown below:
The aim is to convert it to a nested dictionary in the following format:
JavaScript
x
92
92
1
{
2
"meters": [
3
{
4
"meter_id": "a",
5
"meter_children": [
6
{
7
"meter_id": "b",
8
"meter_children": []
9
},
10
{
11
"meter_id": "c",
12
"meter_children": [
13
{
14
"meter_id": "d",
15
"meter_children": []
16
}
17
]
18
},
19
{
20
"meter_id": "e",
21
"meter_children": []
22
}
23
]
24
},
25
{
26
"meter_id": "f",
27
"meter_children": []
28
},
29
{
30
"meter_id": "g",
31
"meter_children": []
32
},
33
{
34
"meter_id": "h",
35
"meter_children": []
36
},
37
{
38
"meter_id": "i",
39
"meter_children": []
40
},
41
{
42
"meter_id": "j",
43
"meter_children": []
44
},
45
{
46
"meter_id": "k",
47
"meter_children": []
48
},
49
{
50
"meter_id": "l",
51
"meter_children": [
52
{
53
"meter_id": "m",
54
"meter_children": []
55
},
56
{
57
"meter_id": "n",
58
"meter_children": []
59
},
60
{
61
"meter_id": "o",
62
"meter_children": []
63
}
64
]
65
},
66
{
67
"meter_id": "p",
68
"meter_children": []
69
},
70
{
71
"meter_id": "q",
72
"meter_children": []
73
},
74
{
75
"meter_id": "r",
76
"meter_children": []
77
},
78
{
79
"meter_id": "s",
80
"meter_children": []
81
},
82
{
83
"meter_id": "t",
84
"meter_children": []
85
},
86
{
87
"meter_id": "u",
88
"meter_children": []
89
}
90
]
91
}
92
I have managed to achieve this, using the scary code you can see below (sorry). I was wondering if there is a tool that can do this for you, or if there is a cleaner, more readable way of accomplishing this.
Note this only goes up to a nesting level of 4, but can be easily extended further.
JavaScript
1
88
88
1
results = {}
2
list_0 = []
3
4
for row in df.values:
5
6
counter = 0
7
8
for entry in row:
9
10
if entry==entry:
11
12
entry=str(entry)
13
14
if counter==0:
15
16
list_0.append({
17
"meter_id":entry,
18
"meter_children":[]
19
})
20
meter_0 = entry
21
22
list_1 = []
23
24
if counter==1:
25
26
for item in list_0:
27
28
if meter_0 in item.values():
29
30
list_1.append({
31
"meter_id":entry,
32
"meter_children":[]
33
})
34
item["meter_children"]=list_1
35
36
meter_1=entry
37
38
39
list_2=[]
40
41
if counter==2:
42
43
for item in list_0:
44
45
if meter_0 in item.values():
46
47
for item in list_1:
48
49
if meter_1 in item.values():
50
51
list_2.append({
52
"meter_id":entry,
53
"meter_children":[]
54
})
55
item["meter_children"]=list_2
56
57
meter_3=entry
58
59
list_3=[]
60
61
if counter==3:
62
63
for item in list_0:
64
65
if meter_0 in item.values():
66
67
for item in list_1:
68
69
if meter_1 in item.values():
70
71
for item in list_2:
72
73
if meter_2 in item.values():
74
75
list_3.append({
76
"meter_id":entry,
77
"meter_children":[]
78
})
79
item["meter_children"]=list_3
80
81
meter_4=entry
82
83
list_4=[]
84
85
counter+=1
86
87
results["meters"] = list_0
88
Advertisement
Answer
You can use itertools.groupby
with recursion:
JavaScript
1
11
11
1
from itertools import groupby as gb
2
d = [['a', None, None, None, None, None, None, None], [None, 'b', None, None, None, None, None, None], [None, 'c', None, None, None, None, None, None], [None, None, 'd', None, None, None, None, None], [None, 'e', None, None, None, None, None, None], ['f', None, None, None, None, None, None, None], ['g', None, None, None, None, None, None, None], ['h', None, None, None, None, None, None, None], ['i', None, None, None, None, None, None, None], ['j', None, None, None, None, None, None, None], ['k', None, None, None, None, None, None, None], ['l', None, None, None, None, None, None, None], [None, 'm', None, None, None, None, None, None], [None, 'n', None, None, None, None, None, None], [None, 'o', None, None, None, None, None, None], ['p', None, None, None, None, None, None, None], ['q', None, None, None, None, None, None, None], ['r', None, None, None, None, None, None, None], ['s', None, None, None, None, None, None, None], ['t', None, None, None, None, None, None, None], ['u', None, None, None, None, None, None, None]]
3
def get_tree(d):
4
r = []
5
for a, b in gb(d, key=lambda x:x[0] is not None):
6
if a:
7
r.extend([{"meter_id":j, "meter_children":[]} for j, *_ in b])
8
else:
9
r[-1]['meter_children'] = get_tree([j for _, *j in b])
10
return r
11
JavaScript
1
3
1
import json
2
print(json.dumps({'meters':get_tree(d)}, indent=4))
3
Output:
JavaScript
1
92
92
1
{
2
"meters": [
3
{
4
"meter_id": "a",
5
"meter_children": [
6
{
7
"meter_id": "b",
8
"meter_children": []
9
},
10
{
11
"meter_id": "c",
12
"meter_children": [
13
{
14
"meter_id": "d",
15
"meter_children": []
16
}
17
]
18
},
19
{
20
"meter_id": "e",
21
"meter_children": []
22
}
23
]
24
},
25
{
26
"meter_id": "f",
27
"meter_children": []
28
},
29
{
30
"meter_id": "g",
31
"meter_children": []
32
},
33
{
34
"meter_id": "h",
35
"meter_children": []
36
},
37
{
38
"meter_id": "i",
39
"meter_children": []
40
},
41
{
42
"meter_id": "j",
43
"meter_children": []
44
},
45
{
46
"meter_id": "k",
47
"meter_children": []
48
},
49
{
50
"meter_id": "l",
51
"meter_children": [
52
{
53
"meter_id": "m",
54
"meter_children": []
55
},
56
{
57
"meter_id": "n",
58
"meter_children": []
59
},
60
{
61
"meter_id": "o",
62
"meter_children": []
63
}
64
]
65
},
66
{
67
"meter_id": "p",
68
"meter_children": []
69
},
70
{
71
"meter_id": "q",
72
"meter_children": []
73
},
74
{
75
"meter_id": "r",
76
"meter_children": []
77
},
78
{
79
"meter_id": "s",
80
"meter_children": []
81
},
82
{
83
"meter_id": "t",
84
"meter_children": []
85
},
86
{
87
"meter_id": "u",
88
"meter_children": []
89
}
90
]
91
}
92