[data mining] use Excel to mine the equity relationship and count the distribution and trend of different types of kinship in listed companies according to the year [visual presentation]

Personal profile ：

brief introduction ： Junior is reading , Share some learning notes and experience
Areas of interest ：Python, Artificial intelligence , Algorithms and data structures
Maxim ： stream , move forward , or you 'll fall behind ！
Purpose ： Progress together ！
hello ！ Hello, everyone. I'm 【 Nanpengyou 】, You can also call me Xiaoyou , Little friend , Xiaoyou, wait
If there's something wrong , Welcome to correct. In addition, if you think it's good, please support the blogger in the third company

Welcome to the big guys

A few days ago, my friend asked me to help him mine the data , Please treat me to delicious food when it's ready 🤩🤩 Although not, you can't lose face ！（ Not for delicious food ） Just try to use Python Did a little .

Probably require ：

1、 database , Is a given set of raw data ;
2、 Mining databases “ Relationship details ” Medium “ spouse / spouse 、 Brothers and sisters 、 Father and son 、 Father and daughter 、 Mother and son 、 mother and daughter ” Specific information ;
3、 The excavated information is sorted into 【 Example 】 In the format ;

Be careful ：
1） A securities code corresponds to multiple years ;
2） The company under the securities code has some kind of relationship marked as 1, There is no correspondence, marked as 0.
3） The distribution and trend of different types of kinship in listed companies are counted according to the year 【 Visual presentation - Free choice 】.

data ：

I looked at about 100000 ！
Originally wanted to simply use Python Run the , Look at the effect , But I gave up without a result in a few minutes ！（ The reason is that I don't have good skills ）

So I use Excel Table data mining .

Method ：

First step ： Year of extraction .

Create a new year column , Click the space under the year , Input function

=YEAR(B2)
Extract the year in the statistics deadline

Just double click the cross below , The year column is completed ！

The second step , Judge the relationship between shareholders

Enter the function... In the space under the closed series

=IF(OR(ISNUMBER(FIND({“ Brother ”,“ brother ”,“ Brother ”,“ The elder sister ”,“ Sister ”},J2))),1,0)
Determine whether the relationship details contain characters such as elder brother or the first or elder sister or younger sister , If included, it is 1, Not included is 0

Other similar input corresponding functions

=IF(OR(ISNUMBER(FIND({“ spouse ”,“ Her husband, ”,“ Wife ”},J2))),1,0)
spouse
=IF(AND(ISNUMBER(FIND({“ Father ”,“ Son ”},J2))),1,0)
Father and son
=IF(AND(ISNUMBER(FIND({“ Father ”,“ Woman ”},J2))),1,0)
Father and daughter
=IF(AND(ISNUMBER(FIND({“ mother ”,“ Son ”},J2))),1,0)
Mother and son
=IF(AND(ISNUMBER(FIND({“ mother ”,“ Woman ”},J2))),1,0)
mother and daughter
Because of the amount of data , I didn't look at them one by one , There may be something similar to who is whose eldest son , Who is whose son-in-law has not been extracted

In this way, the relationship between shareholders is extracted .

The third step , Make statistics

Similarly, we also use functions ！
Count the total number of different relationships of shareholders in each year according to the year
The first blank under each column relationship , Enter the following functions respectively

=SUMIF(C2:C199673,T2,L2:L199673)
=SUMIF(C2:C199673,T2,K2:K199673)
=SUMIF(C2:C199673,T2,M2:M199673)
=SUMIF(C2:C199673,T2,N2:N199673)
=SUMIF(C2:C199673,T2,O2:O199673)
=SUMIF(C2:C199673,T2,P2:P199673)
The functionality , Judge whether the year in the year column is equal to the corresponding year , If equal, the relationships in the corresponding rows are added and summed

result ：

Step four , Make a chart

It was done here , But I think it will be more interesting if it is made dynamic ！
But if you type in code , Some trouble , And need some programming foundation .

Recommend an online dynamic map making website

Flourish
You can make beautiful dynamic charts without programming foundation

First prepare a Excel surface , Input statistics

Go to the website

Dynamic chart of equity relationship

Just upload the data ！
You can also adjust the color according to your preferences , Size, etc
Is it convenient , My friend thinks it's cool ！ Very satisfied ！
And it can release ！ You can let others download and access ！

Edit a HTML file

<!DOCTYPE html><html lang="en"><head>    <meta charset="UTF-8">    <meta http-equiv="X-UA-Compatible" content="IE=edge">    <meta name="viewport" content="width=device-width, initial-scale=1.0">    <title>Document</title></head><body>    <div class="flourish-embed flourish-bar-chart-race" data-src="visualisation/9458106"><script src="https://public.flourish.studio/resources/embed.js"></script></div></body></html>

Send it to a friend , You can access it remotely ！

summary ：
It doesn't work Python Make it out , But also mastered Excel Methods ！