新希望金科风险策略岗笔试题（sql/python）

请将表1变成表2形式。

表1：

表2：

python回答1：

import pandas as pd
import numpy as np

# 这个函数是用来添加新的列的
def labelname(label):
    ls=[]
    for i in range(ser_max[label]):
        ls.append(label+str(i+1))
    return ls

# 这个函数是用来获得某个分类的
def to_frame(label):
    df = ser[label].apply(lambda x :','.join(x)).to_frame()
    return df

if __name__=='__main__':
	
	#读取数据，基本工作准备
    df1=pd.read_excel(r'C:/Users/***/Desktop/table.xlsx')
    ser = df1.groupby(['person','city','age'])
    ser_max = ser.count().max()
    label_lst = df1.columns[3:]

	#将groupby后的组内数据展示出来
    df=pd.DataFrame()
    for i in label_lst:
        df_t=to_frame(i)
        df=pd.concat([df,df_t],axis=1)
    df.reset_index(inplace=True)
	
	#将组内数据再次分开
    for i in label_lst:
        df_n=df[i].str.split(r',',expand=True)
        df=pd.concat([df,df_n],axis=1)
    df.drop(label_lst,axis=1,inplace=True)

	#df的重命名	
    col_name=[]
    for i in label_lst:
        col_name=col_name+labelname(i)
    col_name = list(df1.columns[0:3])+col_name
    df.columns=col_name

    df.to_excel('C:/Users/***/Desktop/result.xlsx')

python回答2：

import pandas as pd
def func(df):
    res=[]
    res_cols=[]
    for person,person_data in df.groupby(['person','city','age']):
        res_one=list(person)
        res_one_cols=['person','city','age']
        num=1
        for product,product_data in person_data.groupby(['product','brand']):
            res_one.extend(list(product))
            res_one_cols.extend(['product_{}'.format(str(num)),'brand_{}'.format(str(num))])
            num+=1
        res.append(res_one)
        res_cols.append(res_one_cols)
    res_df=pd.DataFrame(res)
    len_res=res_df.shape[1]
    for col in res_cols:
        if len(col)==len_res:
            columns_res=col
            res_df.columns=columns_res
    return res_df

sql回答：

SELECT
	vv.person,
	vv.city,
	vv.age,
	substring_index( substring_index( brand, ',', 1 ), ',', - 1 ) AS brand1,
	substring_index( substring_index( brand, ',', 2 ), ',', - 1 ) AS brand2,
	substring_index( substring_index( brand, ',', 3 ), ',', - 1 ) AS brand3,
	substring_index( substring_index( product, ',', 1 ), ',', - 1 ) AS product1,
	substring_index( substring_index( product, ',', 2 ), ',', - 1 ) AS product2,
	substring_index( substring_index( product, ',', 3 ), ',', - 1 ) AS product3 
FROM
	(SELECT v.person, v.city, 	v.age,
	group_concat( v.product ) AS product,
	group_concat( v.brand ) AS brand 
FROM TABLE1 v 
GROUP BY v.person, v.city, v.age) vv