pandas实际应用案例-相似度计算，分组统计，写入excel

写在前面

用笛卡儿积计算成绩相似的10名学生

import pandas as pd

## 数据读取
df = pd.read_excel(r'F:\桌面\成绩表.xlsx')

df

	姓名	英语	数学	语文	体育
0	小红1	15	38	15	41
1	小红2	23	95	87	34
2	小红3	33	25	31	41
3	小红4	71	27	97	14
4	小红5	3	45	48	45
...	...	...	...	...	...
345	小红346	58	61	84	13
346	小红347	80	96	8	26
347	小红348	65	64	31	77
348	小红349	39	52	6	85
349	小红350	80	39	48	47

350 rows × 5 columns

1.学生笛卡尔积

df['col_1'] = 1

df

	姓名	英语	数学	语文	体育	col_1
0	小红1	15	38	15	41	1
1	小红2	23	95	87	34	1
2	小红3	33	25	31	41	1
3	小红4	71	27	97	14	1
4	小红5	3	45	48	45	1
...	...	...	...	...	...	...
345	小红346	58	61	84	13	1
346	小红347	80	96	8	26	1
347	小红348	65	64	31	77	1
348	小红349	39	52	6	85	1
349	小红350	80	39	48	47	1

350 rows × 6 columns

df_merge = pd.merge(left=df,right=df,left_on='col_1',right_on='col_1')

df_merge # 350*350=122500 为了计算一个学生与350个学生关联

	姓名_x	英语_x	数学_x	语文_x	体育_x	col_1	姓名_y	英语_y	数学_y	语文_y	体育_y
0	小红1	15	38	15	41	1	小红1	15	38	15	41
1	小红1	15	38	15	41	1	小红2	23	95	87	34
2	小红1	15	38	15	41	1	小红3	33	25	31	41
3	小红1	15	38	15	41	1	小红4	71	27	97	14
4	小红1	15	38	15	41	1	小红5	3	45	48	45
...	...	...	...	...	...	...	...	...	...	...	...
122495	小红350	80	39	48	47	1	小红346	58	61	84	13
122496	小红350	80	39	48	47	1	小红347	80	96	8	26
122497	小红350	80	39	48	47	1	小红348	65	64	31	77
122498	小红350	80	39	48	47	1	小红349	39	52	6	85
122499	小红350	80	39	48	47	1	小红350	80	39	48	47

122500 rows × 11 columns

2.计算相似度

col_name = list(df.columns)
col_name.remove('姓名')
col_name.remove('col_1')

col_name

['英语', '数学', '语文', '体育']

def sim_fun(row):
    sim_value = 0.0
    for col_ in col_name:
        sim_value += abs(int(row[col_+'_x'])-int(row[col_+'_y'])) # 每列相减，再算和
    return sim_value

df_merge['sim'] = df_merge.apply(sim_fun,axis=1)

df_merge

	姓名_x	英语_x	数学_x	语文_x	体育_x	col_1	姓名_y	英语_y	数学_y	语文_y	体育_y	sim
0	小红1	15	38	15	41	1	小红1	15	38	15	41	0.0
1	小红1	15	38	15	41	1	小红2	23	95	87	34	144.0
2	小红1	15	38	15	41	1	小红3	33	25	31	41	47.0
3	小红1	15	38	15	41	1	小红4	71	27	97	14	176.0
4	小红1	15	38	15	41	1	小红5	3	45	48	45	56.0
...	...	...	...	...	...	...	...	...	...	...	...	...
122495	小红350	80	39	48	47	1	小红346	58	61	84	13	114.0
122496	小红350	80	39	48	47	1	小红347	80	96	8	26	118.0
122497	小红350	80	39	48	47	1	小红348	65	64	31	77	87.0
122498	小红350	80	39	48	47	1	小红349	39	52	6	85	134.0
122499	小红350	80	39	48	47	1	小红350	80	39	48	47	0.0

122500 rows × 12 columns

## 删除小红1=小红1
df_merge = df_merge[df_merge['姓名_x'] != df_merge['姓名_y']]

df_merge

	姓名_x	英语_x	数学_x	语文_x	体育_x	col_1	姓名_y	英语_y	数学_y	语文_y	体育_y	sim
1	小红1	15	38	15	41	1	小红2	23	95	87	34	144.0
2	小红1	15	38	15	41	1	小红3	33	25	31	41	47.0
3	小红1	15	38	15	41	1	小红4	71	27	97	14	176.0
4	小红1	15	38	15	41	1	小红5	3	45	48	45	56.0
5	小红1	15	38	15	41	1	小红6	44	19	58	76	126.0
...	...	...	...	...	...	...	...	...	...	...	...	...
122494	小红350	80	39	48	47	1	小红345	12	51	38	66	109.0
122495	小红350	80	39	48	47	1	小红346	58	61	84	13	114.0
122496	小红350	80	39	48	47	1	小红347	80	96	8	26	118.0
122497	小红350	80	39	48	47	1	小红348	65	64	31	77	87.0
122498	小红350	80	39	48	47	1	小红349	39	52	6	85	134.0

122150 rows × 12 columns

3.提取每个学生相似前10的学生

def get_top_student(df_sub):
    df_sort = df_sub.sort_values(by='sim',ascending=False).head(10)
    names = ','.join(list(df_sort['姓名_y']))
    sims = ','.join([str(x) for x in list(df_sort['sim'])])
    return pd.Series({'names':names,'sims':sims})

df_result = df_merge.groupby('姓名_x').apply(get_top_student)

df_result.to_excel('F:\桌面\相似度计算.xlsx')