程式設計實踐（Pandas）綜合練習1

心得：花了一天時間做出來了三道題，但是辦法實在太笨了，後面希望可以看一下優秀筆記的寫法，多學習多練習。時間已經過去了半個多月，從不會寫程式碼到現在已經可以獨立做出綜合練習（雖然辦法很笨，感覺有很多地方想複雜了），自己感覺還是很開心的，繼續努力！

1. 企業收入的多樣性

一個企業的產業收入多樣性可以仿照資訊熵的概念來定義收入熵指標：$\mathrm{I}=-{\sum }_{\mathrm{i}}\mathrm{p}({\mathrm{x}}_{\mathrm{i}})\log (\mathrm{p}({\mathrm{x}}_{\mathrm{i}}))$，其中$\mathrm{p}({\mathrm{x}}_{\mathrm{i}})$是企業該年某產業收入額佔該年所有產業總收入的比重。在company.csv中存有需要計算的企業和年份，在company_data.csv中存有企業、各類收入額和收入年份的資訊。現請利用後一張表中的資料，在前一張表中增加一列表示該公司該年份的收入熵指標$\mathrm{I}$。

# df1_2新增一列年份
df1_2['年份'] = pd.to_datetime(df1_2['日期']).dt.year
# 算出每個企業每個產業每一年的收入額
s1 = df1_2.groupby(['證券程式碼','收入型別','年份'])['收入額'].sum()
len(s1)
>>> 964022

經過計算發現s2即收入額，資料為1年1條資料，可以直接算p(xi)

# 直接算p(xi)
s2 =  df1_2.groupby(['證券程式碼','年份'])['收入額'].transform(lambda x: x/x.sum())
# 將佔比加入到df1_2中
df1_2.insert(df1_2.shape[1], '佔比', s2)
# 算出每個證券程式碼的收入熵指標 I
df1_2['對數佔比'] = df1_2['佔比'].apply(np.log)
df1_2['p(xi)*log(p(xi))'] = df1_2.apply(lambda x: x['佔比'] * x['對數佔比'], axis=1)
s3 = -df1_2.groupby(['證券程式碼','年份'])['p(xi)*log(p(xi))'].sum()
df1_3 = s3.reset_index()
df1_3.columns = ['證券程式碼', '年份', '資訊熵']
df1_3.head()

	證券程式碼	年份	資訊熵
0	1	2008	2.085159
1	1	2009	1.671752
2	1	2010	2.108355
3	1	2011	3.150479
4	1	2012	2.718759

# 更改df1_1的證券程式碼
df1_4 = pd.concat([df1_1['證券程式碼'], df1_1['日期'], df1_1['證券程式碼'].str.split('#',expand = True)], axis=1)
s4 = pd.to_numeric(df1_4.iloc[:,-1])
df1_1.insert(df1_1.shape[1], '證券程式碼1', s4)
df1_1 = df1_1.merge(df1_3, left_on=['證券程式碼1','日期'],right_on=['證券程式碼','年份'],how='left')
df1_final = pd.DataFrame(df1_1, columns = ['證券程式碼_x','日期','資訊熵'])
df1_final.columns = ['證券程式碼','日期','資訊熵']
df1_final.head()

	證券程式碼	日期	資訊熵
0	#000007	2014	3.070462
1	#000403	2015	2.790585
2	#000408	2016	2.818541
3	#000408	2017	NaN
4	#000426	2015	3.084266

2. 組隊學習資訊表的變換

請把組隊學習的隊伍資訊表變換為如下形態，其中“是否隊長”一列取1表示隊長，否則為0

自己的想法：
Step1：把編號和暱稱合併為1列
Step2：使用melt把寬表變長表
Step3：拆分
Step4：新增【是否隊長】1列
Step5：排序

df2_1 = pd.read_excel('data/final/組隊資訊彙總表（Pandas）.xlsx')
# Step1：把編號和暱稱合併（方法太笨，後面嘗試優化）
df2_1 = df2_1.astype(str)
df2_1['隊長'] = df2_1['隊長_群暱稱'] + '|' +  df2_1['隊長編號']
df2_1['隊員1'] = df2_1['隊員_群暱稱'] + '|' + df2_1['隊員1 編號']
df2_1['隊員2'] = df2_1['隊員_群暱稱.1'] + '|' + df2_1['隊員2 編號'] 
df2_1['隊員3'] = df2_1['隊員_群暱稱.2'] + '|' + df2_1['隊員3 編號']
df2_1['隊員4'] = df2_1['隊員_群暱稱.3'] + '|' + df2_1['隊員4 編號']
df2_1['隊員5'] = df2_1['隊員_群暱稱.4'] + '|' + df2_1['隊員5 編號']
df2_1['隊員6'] = df2_1['隊員_群暱稱.5'] + '|' + df2_1['隊員6 編號']
df2_1['隊員7'] = df2_1['隊員_群暱稱.6'] + '|' + df2_1['隊員7 編號']
df2_1['隊員8'] = df2_1['隊員_群暱稱.7'] + '|' + df2_1['隊員8 編號']
df2_1['隊員9'] = df2_1['隊員_群暱稱.8'] + '|' + df2_1['隊員9 編號']
df2_1['隊員10'] = df2_1['隊員_群暱稱.9'] + '|' + df2_1['隊員10編號']
# 建立1張新表
df2_2 = pd.DataFrame(df2_1, columns = ['隊伍名稱', '隊長', '隊員1', '隊員2', '隊員3', '隊員4', '隊員5', '隊員6', '隊員7', '隊員8', '隊員9', '隊員10'])
df2_2 = df2_2.reset_index()
# Step2：使用melt——寬表變長表
df_melted = df2_2.melt(id_vars = ['index','隊伍名稱'],
                       value_vars  = ['隊長', '隊員1', '隊員2', '隊員3', '隊員4','隊員5', '隊員6','隊員7', '隊員8','隊員9', '隊員10'],
                       var_name = 'title',
                       value_name = '暱稱|編號')
# Step3：拆分
df2_3 = pd.concat([df_melted['index'],df_melted['隊伍名稱'],df_melted['title'], df_melted['暱稱|編號'].str.split('|', expand=True)], axis=1)
df2_3.columns = ['index','隊伍名稱','title','暱稱','編號']
df2_4 = df2_3[~df2_3['編號'].isin(['nan'])]
# Step4：新增【是否隊長】1列
s1 = df2_4.title.apply(lambda x: 1 if '隊長' in x else 0)
df2_4.insert(1,'是否隊長',s1)
# Step5：排序
df2_4 = df2_4.sort_values(by=['index','是否隊長'], ascending=[True, False])
df2_4 = df2_4.reset_index(drop=True)
df2_final = pd.DataFrame(df2_4, columns = ['是否隊長','隊伍名稱','暱稱','編號'])

3. 美國大選投票情況

兩張資料表中分別給出了美國各縣（county）的人口數以及大選的投票情況，請解決以下問題：

• 有多少縣滿足總投票數超過縣人口數的一半
• 把州（state）作為行索引，把投票候選人作為列名，列名的順序按照候選人在全美的總票數由高到低排序，行列對應的元素為該候選人在該州獲得的總票數
• 每一個州下設若干縣，定義拜登在該縣的得票率減去川普在該縣的得票率為該縣的BT指標，若某個州所有縣BT指標的中位數大於0，則稱該州為Biden State，請找出所有的Biden State

3.1自己的想法：
Step1：計算出每個縣的總投票數，建立新表df3_3
Step2：合併df3_3的列【.country,state】與df3_1匹配表
Step3：計數：總投票數超過縣人口數的一半

df3_1 = pd.read_csv('data/final/county_population.csv')
df3_2 = pd.read_csv('data/final/president_county_candidate.csv')
# Step1：計算出每個縣的總投票數，建立新表df3_3
df3_3 = df3_2.groupby(['state','county'])['total_votes']
df3_3 = pd.DataFrame(df3_3.sum()).reset_index()
# Step2：合併df3_3的列【.country,state】並新建1列
df3_3['US County'] = '.' + df3_3['county'] + ', ' + df3_3['state']
# 新建列與df3_1匹配
df3_4 = df3_3.merge(df3_1, on='US County', how='left')
# Step3：篩選出符合條件【總投票數超過縣人口數的一半】的行
df3_5 = df3_4[(df3_4['total_votes']/df3_4['Population']>0.5)]
len(df3_5)
>>> 1434

3.2自己的想法：
Step1：候選人總票數求和排序
Step2：取出指定順序的列名
Step3：長表變寬表——pivot_table
Step4：調整列順序

# Step1：候選人總票數求和排序
df3_6 = df3_2.groupby(['candidate'])['total_votes']
# Step2：取出指定順序的列名
cols = pd.DataFrame(df3_6.sum().sort_values(ascending = False)).index
# Step3：長表變寬表——pivot_table
df3_7 = df3_2.pivot_table(index = 'state',
                  columns = 'candidate',
                  values = 'total_votes')
# Step4：調整列順序
df3_7 = df3_7[cols]
df3_7.head()

candidate	Joe Biden	Donald Trump	Jo Jorgensen	Howie Hawkins	Write-ins	Rocky De La Fuente	Gloria La Riva	Kanye West	Don Blankenship	Brock Pierce	...	Tom Hoefling	Ricki Sue King	Princess Jacob-Fambro	Blake Huber	Richard Duncan	Joseph Kishore	Jordan Scott	Gary Swing	Keith McCormic	Zachary Scalf
state
Alabama	12681.313433	21509.970149	375.761194	NaN	109.134328	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
Alaska	3835.125000	4747.300000	222.400000	NaN	877.179487	7.950000	NaN	NaN	28.175000	20.625000	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
Arizona	111476.200000	110779.066667	3431.000000	NaN	135.466667	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
Arkansas	5652.426667	10141.960000	175.106667	39.733333	NaN	17.613333	17.813333	54.653333	28.106667	28.546667	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
California	191547.655172	103551.051724	3239.396552	1396.982759	5.333333	1037.155172	879.931034	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

5 rows × 38 columns

3.2自己的想法：
Step1：算出所有縣的BT指標（Joe Biden_votes - Donald Trump_votes）
Step2：找出Biden State（median(BT)>0）

# 新建一張表
df3_8 = df3_2.pivot_table(index = ['state', 'county'],
                          columns = 'candidate',
                          values = 'total_votes')
col2 = ['Joe Biden', 'Donald Trump']
df3_8 = df3_8[col2].reset_index()
# Step1：算出所有縣的BT指標（Joe Biden_votes - Donald Trump_votes）
df3_8['BT指標'] = df3_8.apply(lambda x: x['Joe Biden'] - x['Donald Trump'], axis=1)
# Step2：找出Biden State（median(BT)>0）
gb = df3_8.groupby(['state'])['BT指標']
df3_9 = pd.DataFrame(gb.median()>0).reset_index()
Biden_State = df3_9[df3_9['BT指標'] == True]
len(Biden_State)
>>> 9