Import an special excel and establish relations

Question

Hello, I have an excel with the following columns:

3818	3160	1082	1123	6626	2645	8912	9428	7324
27	39	92	23	26	17	86	0	76
65	64	71	47	83	27	9	72	90
84	71	90	52	35	28	6	63	12
34	75	2	86	90	42	49	65	58
11	18	72	58	69	26	43	96	90
70	77	46	18	88	85		79	93
8		87	36	82	42		54
42		75	26	88	60		99
85		39	88	69	66		32
		79	48	39	49		96
		48	0	56	55		54
		92	74	49	77		86
		84		9			52
		0					71
							94
							77

I have created a link to share m excel file: https://docs.google.com/spreadsheets/d/1eskM3zfEBOYm5VeMlauh7-D7_XUBoY_N/edit?usp=share_link&ouid=105371682602180449764&rtpof=true&sd=true

This is an example programmed by python, the original one has hundreds of data. As you see there are several columns with different number of rows:

- The first row is the name

- The other rows are integer numbers

- The links between the columns is that one of several numbers can be repeated in several columns

My problems are:

1. I need to create automatically from the excel 9 blocks. The name of each block is the first row, and inside each block there should be the other rows of that column

2. Once the first step is created, which is already a problem, I would need to work in the relationships among the blocks. The idea is to create an edge between those blocks that are related with a label indicating the number of coincidences

Any help would be highly appreciated

Answer 1

yEd cannot import data that is organized in this way.

If you want to import your data in yEd, you need to organize it either

• in an adjacency matrix or
• in an edge list and/or a node list.

Please see Import of Excel Files for information on the required data structure.

Given the example above, I think your data is not suited for an adjacency matrix representation.

From your description I understand that each column represents one node. Thus you can convert your data into the supported node list format if you convert each column into a row.

However, even after switching from columns to rows, it is not possible to automatically infer the relationships. Instead, you need to add additional rows that represent the relationships and import that second set of rows as edge list.

Comments

Many thanks for your answer. I have read the link and your words, which I suppose that refer to the adjacency matrix. I will try to do it with the Edge List and Node List as I find it easier.
I still don't know how to properly create my node list for my case. Following your link I could create a column named Name with all the data in that column. Something like this:
3818
27
65
84
34
11
70
8
42
85
3160
39
64
71
75
18
77
1082
92
71
90
2
72
46
87
75
39
79
48
92
84
0
1123
23
47
52
86
58
18
36
26
88
48
0
74
6626
26
83
35
90
69
88
82
88
69
39
56
49
9
2645
17
27
28
42
26
85
42
60
66
49
55
77
8912
86
9
6
49
43
9428
0
72
63
65
96
79
54
99
32
96
54
86
52
71
94
77
7324
76
90
12
58
90
93

I imagine that this would create all the nodes in the graph, but not in a grouped way. Remember that my goal was to group all the original data by columns: for instance one group should be formed by these elements, with the first one as the title or the name of the group:
3818
27
65
84
34
11
70
8
42
85

Then a link should go from that box to any other box that contains repeated elements, with a label on that link indicating the number of repeated elements.

I have created the edge list, the column Count indicates the number of repeated elements between the previous two columns.
Source    Target    Count
3818    1082    1
3818    2645    4
3818    9428    1
3160    1082    3
3160    1123    1
3160    6626    1
3160    2645    1
3160    9428    2
1082    1123    2
1082    6626    2
1082    9428    4
1082    7324    2
1123    6626    3
1123    2645    1
1123    8912    1
1123    9428    3
1123    7324    1
6626    2645    2
6626    8912    2
6626    7324    2
2645    8912    1
2645    9428    1
8912    9428    1

I still need help on how to create, if possible, all the nodes and group them. A possibility would be to group them manually, but if it's possible I would need it to be automatic. Also regarding the nodes I would like the values to be shown inside the box, and when I import them like this the values are not shown in the box (they are in the properties).

Finally please check my edges configuration and tell me if that could work. Notice that in the edges only the title or the name of each box is shown, not the other numbers contained in each group.

Any suggestion would be appreciated. Thanks in advance.

---

It seems to me that I misunderstood how you want to represent your data.

In your original post, there is the column

8912

86

9

6

49

43

(I have chosen this column for the sole reason that it is the shortest one). I had thought the data in this column should result in one node named "8912" and several custom properties for the remaining values:

Your comment suggests that my understanding is not correct.

Thus I would like to ask you to (manually) create a sample diagram that shows how the data in one column should be represented in the diagram.

---

Many many thanks again for your help.

To show you how I would like this to be implemented I have created manually a graph with two groups, 8192 as you suggested and another one (1123) which has one element repeated. As you see they are linked by an Edge with the number 1 above (if there were 3 elements repeated, which is not the case, the number 3 should go there).

I don't know how to share a graphml with you using this forum as I can not register, that's why I'm sharing with you the file through a google drive link: https://drive.google.com/file/d/1HO487blbE-ljDlHpq2-tpxsZCfpNvNNc/view?usp=share_link

I have made a screenshot of the result as well and upload it here just if you prefer to open it instead of the graph: https://i.postimg.cc/kMFf1CGG/Screenshot-2023-02-24-105817.png

That is my idea, which can be improved and I'm open to any suggestions. As you see I need the values to be shown inside the box together with the Title/name of the column.

Thanks again

---

Thank you very much for the additional information.

So, you want one node per column and the value of each row added to the text of the node's second label.
Unfortunately, yEd is not able to accumulate multiple data values into one label.
If you change your data representation from columns to rows (as I advised in my original answer), yEd will be able to create this kind of diagram:

With the additional data imported as custom properties:

Corresponding GraphML.

To achieve this, you would need to configure Excel Import like this:

As an alternative, you could create one node for each value and assign the nodes corresponding to your values as child nodes to group nodes representing your names:

Corresponding GraphML.

This can be achieved with the following import settings:

Note the separate ID column, This is necessary, because there can be duplicate values belonging to different names.

And finally, because you asked: How to upload files to yEd Q&A? explains how to share files in this forum.

---

Hello again, it's almost perfect, I believe that with your last advise all is at 99%...
Here I have the Excel: https://docs.google.com/spreadsheets/d/1FvYKWPtzCDQM0Ck7lENIi0W0e7afNjO3/edit?usp=share_link&ouid=105371682602180449764&rtpof=true&sd=true

Here the graghml: https://drive.google.com/file/d/1NAIA-wdUZxs3DOLu4xI6Uf6R0ffXG8DA/view?usp=share_link

I believe that most of it is perfect. Problems are:
- I don't understand why there is a box in the aupper right corner out of all the groups
- I woulkd like to know if there's an easy way to order the graph so that all the relations can be seen. Manually I have done it, more or less, but maybe you have a suggestion about it.

Thanks again for all your help.

---

Your Google documents are access restricted.

---

Sorry, now the documents are properly shared to anyone.

Another question by the way. When importing the excel numbers, they convert them into the scientific way. For instance, a phone like 688455125 would be imported as 6.88455125E8. That's not good for me as phones are better understood in the normal way, noy in the scientific one. Is there any possibility to import them without converting them scientifically?

Thanks again for your help

---

Re.

 I don't understand why there is a box in the upper right corner out of all the groups

That is because the value in cell I5 is "1082" instead of the ID of node "1082". Since there is no row in the node list for ID "1082", the Excel import creates a default node for the edge corresponding to H5-J5.

Re.

 I would like to know if there's an easy way to order the graph so that all the relations can be seen. Manually I have done it, more or less, but maybe you have a suggestion about it.

On the "Presentation" tab of the Excel Import settings, there is a field "Layout". Make sure it is set to "Hierarchical". Other than that, you can always run one of yEd's layout algorithms after the import.

Re.

Another question by the way. When importing the excel numbers, they convert them into the scientific way. For instance, a phone like 688455125 would be imported as 6.88455125E8. That's not good for me as phones are better understood in the normal way, not in the scientific one. Is there any possibility to import them without converting them scientifically?

Numbers are always converted to decimals. If you do not want that to happen, you need to format the content of these cell as text.

---

All the answers perfect. Thank you very much and congratulations, you have a great program!!!