題目內容http://datawhale.club/t/topic/579/4

Task1企業收入的多樣性

程式碼

// An highlighted block
import pandas as pd
import numpy  as np
df1 = pd.read_csv('company.csv')
df2 = pd.read_csv('company_data.csv')
df1.head(5)
df2.head(5)
df11 = df1.copy()
df11['證券程式碼'] = df11['證券程式碼'].str[1:].astype('int64')#使兩張表的證券程式碼格式一致
df2['日期'] = df2['日期'].str[:4].astype('int64')#使兩張表的日期格式一致
def entropy(x):
    if x.any():
        p = x/x.sum()
        return -(p*np.log2(p)).sum()
    return np.nan
res = df11.merge(df2, on=['證券程式碼','日期'], how='left').groupby(['證券程式碼','日期'])['收入額'].apply(entropy).reset_index()
res.head(5)
df1['收入熵指標'] = res['收入額']
df1

結果展示


![

Task2組隊學習資訊表的變換

// An highlighted block
df = pd.read_excel('team_data.xlsx') #把表格名稱改成了team_data,所以是讀team_data
df.drop(columns='所在群', inplace=True) #所在群的資訊沒用到，刪去
df.head(5)
col_1 = np.array(['隊伍名稱','編號_leader01','暱稱_leader01'])
col_2 = np.array([[f'編號_member{i}0', f'暱稱_member{i}0']for i in range(1,11)]).flatten()
df.columns = np.r_[col_1,col_2]
df.head(5)
res = pd.wide_to_long(  df.reset_index(),
                        stubnames = ['暱稱','編號'],
                        i = ['index','隊伍名稱'],
                        j = '是否隊長',
                        sep = '_',
                        suffix = '.+').dropna().reset_index().drop(columns='index')
res
res['是否隊長'],res['編號'] = res['是否隊長'].str[-1],res['編號'].astype('int64')
res.reindex(columns=['是否隊長','隊伍名稱','暱稱','編號']

結果展示

Task3美國大選投票情況

程式碼

// An highlighted block
df1=pd.read_csv('president_county_candidate.csv')
df2=pd.read_csv('county_population.csv')
df1.head(5)
df2.head(5)
sum_vote=df1.groupby(['county','state'])['total_votes'].sum()
sum_vote=sum_vote.to_frame().reset_index()
US_county='.'+sum_vote['county']+', '+sum_vote['state']
df3=sum_vote.copy()
df3.head(5)
df4=df3.drop(['county','state'],axis=1).copy()
df4['US County']=US_county
df_12=df2.merge(df4,on='US County',how='left')
df_12[df_12['total_votes']/df_12['Population']>0.5].count(0)
columns=df1.groupby('candidate')['total_votes'].sum().sort_values(ascending = False).index
result=df1.pivot_table(index='state',columns='candidate',values='total_votes')
result.reindex(columns=columns)
df1.groupby(['state','county'])['total_votes'].transform('sum')
df1['縣總票數']=df1.groupby(['state','county'])['total_votes'].transform('sum')
df1['縣得票率']=df1['total_votes']/df1['縣總票數']
df_bt=df1.pivot(index=['state','county'],columns='candidate',values='縣得票率')
s_bt=df_bt['Joe Biden']-df_bt['Donald Trump']
df3=s_bt.to_frame()
result3=df3.rename(columns={0:'BT指標'}).reset_index()
def function(x):
    if x.median()>0:
        return 'Biden State'
    else:
        return 'Not Biden State'
result=result3.groupby('state')['BT指標'].transform(function)
result3[result=='Biden State']['state'].drop_duplicates().reset_index(drop=True)