import pandas as pd
import numpy as np
np.seterr(all = 'ignore')

{'divide': 'ignore', 'over': 'ignore', 'under': 'ignore', 'invalid': 'ignore'}

【任務一】企業收入的多樣性

【題目描述】一個企業的產業收入多樣性可以仿照資訊熵的概念來定義收入熵指標 :
I = ? ∑ i p ( x i ) l o g ( p ( x i ) ) I=-\sum_i{p(x_i)log(p(x_i))} I=?i∑?p(xi?)log(p(xi?))
其中 p ( x i ) p(x_i) p(xi?)是企業該年某產業收入額占該年所有產業總收入的比重，
在company.csv中存有需要計算的企業和年份 , 在company_data.csv中存有企業、各類收入額和收入年份的資訊，現請利用后一張表中的資料 , 在前一張表中增加一串列示該公司該年份的收入熵指標I，
【資料下載】鏈接：https://pan.baidu.com/s/1leZZctxMUSW55kZY5WwgIw 53 密碼：u6fd

My solution :

• 讀取兩表資料

df1 = pd.read_csv('company.csv')
df2 = pd.read_csv('company_data.csv')

df1.head()

df2.head()

• 經觀察兩表的證券代碼列和日期格式都不一致 , 因當首先變一致
• 將df1表中證券代碼列里的#去掉轉為int
• 將df2表日期列取前四位year轉為int

df1_ = df1.copy()
df1_['證券代碼'] = df1_['證券代碼'].str[1:].astype('int64')

df2['日期'] = df2['日期'].str[:4].astype('int64')

• 定義entropy函式計算資訊熵 , 并跳過NaN值
• 用df1表左連接df2表 , 連接列為證券代碼和日期 , 再繼續對這兩列分組 , 取出收入額列用apply呼叫資訊熵函式 , 重置索引

def entropy(x):
    if x.any():
        p = x/x.sum()
        return -(p*np.log2(p)).sum()
    return np.nan
res = df1_.merge(df2, on=['證券代碼','日期'], how='left').groupby(['證券代碼','日期'])['收入額'].apply(entropy).reset_index()
res.head()

• 將df1表新增一列收入熵指標 , 值為結果表中的收入額

df1['收入熵指標'] = res['收入額']
df1

1048 rows × 3 columns

• 將上述所有程序封裝為函式 , 并測驗性能

def information_entropy():
    df1 = pd.read_csv('company.csv')
    df2 = pd.read_csv('company_data.csv')
    df1_ = df1.copy()
    df1_['證券代碼'] = df1_['證券代碼'].str[1:].astype('int64')
    df2['日期'] = df2['日期'].str[:4].astype('int64')
    res = df1_.merge(df2, on=['證券代碼','日期'], how='left').groupby(['證券代碼','日期'])['收入額'].apply(entropy).reset_index()
    df1['收入熵指標'] = res['收入額']
    return df1

%timeit -n 5 information_entropy()

1.62 s ± 44.5 ms per loop (mean ± std. dev. of 7 runs, 5 loops each)

【任務二】組隊學習資訊表的變換

【題目描述】請把組隊學習的隊伍資訊表變換為如下形態，其中'是否隊長'一列取1表示隊長，否則為0

【資料下載】鏈接：https://pan.baidu.com/s/1ses24cTwUCbMx3rvYXaz-Q 34 密碼：iz57

My solution :

• 讀取資料

df = pd.read_excel('組隊資訊匯總表_Pandas.xlsx')

• 所在群列沒有用到 , drop掉

df.drop(columns='所在群', inplace=True)
df.head(2)

2 rows × 23 columns

• 為了使用wide_to_long將寬表轉長表 ,需要先對表columns進行重命名
• 對照結果表中的名字 , 分別將隊長和隊員用leader和member區分 , 結果表中隊長和隊員分別用1和0分類 , 不妨在重命名時就先分好類 , 在重命名的末尾追加1和0,最后直接取出字串最后一位即可

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(2)

2 rows × 23 columns

• 將重命名好的表用wide_to_long轉換為長表 , 命名對照結果表 , 省的還要再重命名
• 轉換后dropna洗掉NaN值 , 恢復索引

res = pd.wide_to_long(  df.reset_index(),
                        stubnames = ['昵稱','編號'],
                        i = ['index','隊伍名稱'],
                        j = '是否隊長',
                        sep = '_',
                        suffix = '.+').dropna().reset_index().drop(columns='index')
res

146 rows × 4 columns

• 到這里已經接近結果了 , 把是否隊長一列的值最后一個取出最為該列的分類
• 編號列的型別為float轉為int
• 是否隊長和隊伍名稱兩列順序倒了 , 恢復一下即可

res['是否隊長'],res['編號'] = res['是否隊長'].str[-1],res['編號'].astype('int64')

res.reindex(columns=['是否隊長','隊伍名稱','昵稱','編號'])

146 rows × 4 columns

• 將上述所有程序封裝為函式 , 并測驗性能

def transform_table():
    df = pd.read_excel('組隊資訊匯總表_Pandas.xlsx')
    df.drop(columns='所在群', inplace=True)
    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]
    res = pd.wide_to_long(  df.reset_index(),
                            stubnames = ['昵稱','編號'],
                            i = ['index','隊伍名稱'],
                            j = '是否隊長',
                            sep = '_',
                            suffix = '.+').dropna().reset_index().drop(columns='index')
    res['是否隊長'], res['編號'] = res['是否隊長'].str[-1], res['編號'].astype('int64')
    res.reindex(columns=['是否隊長','隊伍名稱','昵稱','編號'])

%timeit -n 50 transform_table()

45.7 ms ± 3 ms per loop (mean ± std. dev. of 7 runs, 50 loops each)

【任務三】美國大選投票情況

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

1. 有多少縣滿足總投票數超過縣人口數的一半
2. 把州（state）作為行索引 , 把投票候選人作為列名 , 列名的順序按照候選人在全美的總票數由高到低排序 , 行列對應的元素為該候選人在該州獲得的總票數

此處是一個樣例，實際的州或人名用原表的英語代替
		                拜登   川普
			  威斯康星州   2      1
			  德克薩斯州   3      4

3. 每一個州下設若干縣 , 定義拜登在該縣的得票率減去川普在該縣的得票率為該縣的BT指標 , 若某個州所有縣BT指標的中位數大于0 , 則稱該州為Biden State , 請找出所有的Biden State

【資料下載】鏈接：https://pan.baidu.com/s/182rr3CpstVux2CFdFd_Pcg 32 提取碼：q674

My solution :

• 讀取兩表資料

df1 = pd.read_csv('president_county_candidate.csv')
df2 = pd.read_csv('county_population.csv')

df1.head()

df2.head()

• 為了后續分組或合并操作 , 先統一state和county列名和值
• 將df2中US County按,拆分 , 注意逗號后還有個空格 , 否則拆分后值并不相同

df2[['county','state']] = pd.DataFrame([*df2['US County'].str.split(', ')])
df2.county = df2.county.str[1:]
df2.drop(columns='US County', inplace=True)
df2.head()

1. 有多少縣滿足總投票數超過縣人口數的一半 ?

• 對df1按state和county分組 , 求和計算每個county總票數
• 再與df2按state和county兩列merge , 將Population轉移過來

df_merge = df1.groupby(['state','county'])['total_votes'].sum().reset_index().merge(df2, on=['state','county'], how='left')
df_merge.head()

• 對上述結果取出total_votes與Population作比較篩選出即可

df_merge[df_merge['total_votes'] > 0.5*df_merge['Population']]

1434 rows × 4 columns

2. 把州（state）作為行索引 , 把投票候選人作為列名 , 列名的順序按照候選人在全美的總票數由高到低排序 , 行列對應的元素為該候選人在該州獲得的總票數

• 依題意可以用pivot_table透視 , 填入行和列 , 對同一位置用sum聚合 , 打開margins匯總 , 對最后一行All降序排列
• 可以看到第一列是每行的匯總 , 也就是每個state的匯總 , 第二列是Biden最高票 , Trump緊隨其后

df1.pivot_table(values = ['total_votes'],
                index = ['state'],
                columns = 'candidate',
                aggfunc = 'sum',
                margins = True).sort_values('All', 1, ascending=False).head()

5 rows × 39 columns

3. 每一個州下設若干縣 , 定義拜登在該縣的得票率減去川普在該縣的得票率為該縣的BT指標 , 若某個州所有縣BT指標的中位數大于0 , 則稱該州為Biden State , 請找出所有的Biden State
方法一 :

• 定義一個計算BT指標的函式 , 分別取出Biden的票數 , Trump的票數 , 計算每個county總票數 , 做差相除得到BT
• 對state和county分組 , 取出candidate和total_votes兩列呼叫apply計算BT

def BT(x):
    biden = x[x['candidate']=='Joe Biden']['total_votes'].values
    trump = x[x['candidate']=='Donald Trump']['total_votes'].values
    return pd.Series((biden-trump)/x['total_votes'].sum(), index=['BT'])   
bt = df1.groupby(['state','county'])[['candidate','total_votes']].apply(BT)
bt.head()

• 將bt結果恢復索引重新對state分組 , 用filter過濾每個state下county的BT指標中位數是否大于0
• 對state去重后即滿足條件的所有state , 只有9個

bt.reset_index().groupby('state').filter(lambda x:x.BT.median()>0)[['state']].drop_duplicates()

方法二 :

• 分別用bool條件取出biden和trump的所有行 , 再對state和county分組求出每個county的總票數
• 這三個df巧了都是一樣的大小 , 說明每個county都有biden和trump的票

biden_df = df1[df1['candidate']=='Joe Biden'][['state','county','total_votes']]
trump_df = df1[df1['candidate']=='Donald Trump'][['state','county','total_votes']]
sum_df = df1.groupby(['state','county'])[['total_votes']].sum().reset_index()

• 將上述三個一樣大的df合并

res = biden_df.merge(trump_df, on=['state','county'] ,suffixes=('_biden','_trump')).merge(sum_df, on=['state','county'])
res.head()

• 分別取出biden列和trump列做差后除以sum列得出BT指標

res['BT'] = (res['total_votes_biden']-res['total_votes_trump'])/res['total_votes']
res.head()

• 同樣的 , 按要求過濾后取出所有滿足條件的state , 也是9個

res[['state','BT']].groupby('state').filter(lambda x:x.median()>0)[['state']].drop_duplicates()

• 下面將上述兩種方法分別封裝成方法 , 并測驗性能

def method1():
    bt = df1.groupby(['state','county'])[['candidate','total_votes']].apply(BT)
    bt.reset_index().groupby('state').filter(lambda x:x.BT.median()>0)[['state']].drop_duplicates()

def method2():
    biden_df = df1[df1['candidate']=='Joe Biden'][['state','county','total_votes']]
    trump_df = df1[df1['candidate']=='Donald Trump'][['state','county','total_votes']]
    sum_df = df1.groupby(['state','county'])[['total_votes']].sum().reset_index()
    res = biden_df.merge(trump_df, on=['state','county'] ,suffixes=('_biden','_trump')).merge(sum_df, on=['state','county'])
    res['BT'] = (res['total_votes_biden']-res['total_votes_trump'])/res['total_votes']
    res[['state','BT']].groupby('state').filter(lambda x:x.median()>0)[['state']].drop_duplicates()

%timeit method1()

6.56 s ± 210 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit method2()

90.9 ms ± 4.83 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

• 可以看到方法二雖然拆分好多步驟 , 但是沒有用apply呼叫自定義函式 , 性能強到飛起

【任務四】計算城市間的距離矩陣

【題目描述】資料中給出了若干城市的經緯度，請構造關于城市的距離DataFrame ，其橫縱索引為城市名稱 ， 值為矩陣 M M M , M i j M_ij Mi?j表示城市i與城市j間的球面距離（可以利用geopy包中distance模塊的geodesic函式），并規定與自身的距離為0，

My solution :

• 讀取表資料 , 以,分割 , 并重命名列名

df = pd.read_table('map.txt', sep=',', names=['city1','longitude','latitude'], skiprows=1)
df.head()

• 將緯度和經度打包成元組供后面計算距離用
• 洗掉原來的兩列經緯度

df['coord1'] = pd.Series([*zip(df.latitude, df.longitude)])

df.drop(columns=['longitude','latitude'], inplace=True)
df.head(3)

• 復制一份df表 , 并重命名列名加以區分 , 并設定city2為index , 為后續透視表做準備

df2 = df.rename(columns={'city1':'city2','coord1':'coord2'}).set_index('city2')
df2.head(3)

• 將df和df2擴展 , 先借用groupby對df兩列分組 , 看似分了個寂寞 , 實則用apply將df2一個一個拼上去了 , 將原表在索引里的坐標coord1恢復到資料列 , 用stack把列移下來做一個reshape , 再重置索引 , 將空列名起個名字coords , 這一列都是坐標了 , 為后續透視表做完了準備

df_expand = df.groupby(['city1','coord1']).apply(lambda x:df2).reset_index(1).stack().reset_index().rename(columns={0:'coords'})
df_expand.head(3)

• 匯入計算距離的函式geodesic
• 將上述準備好的表進行透視 , 并對透視結果坐標列用geodesic計算距離 , 用km做單位 , 再保留兩位小數

from geopy.distance import geodesic
df_expand.pivot_table(values = 'coords',
                      index = 'city1',
                      columns = 'city2',
                      aggfunc = lambda x : geodesic(*x).km
                     ).round(2).head(3)

3 rows × 34 columns

• 將上述所有程序封裝為函式 , 并測驗性能

def calculate_M():
    df = pd.read_table('map.txt', sep=',', names=['city1','longitude','latitude'], skiprows=1)
    df['coord1'] = pd.Series([*zip(df.latitude, df.longitude)])
    df.drop(columns=['longitude','latitude'], inplace=True)
    df2 = df.rename(columns={'city1':'city2', 'coord1':'coord2'}).set_index('city2')
    df_expand = df.groupby(['city1','coord1']).apply(lambda x:df2).reset_index(1).stack().reset_index().rename(columns={0:'coords'})
    return df_expand.pivot_table(values = 'coords',
                                  index = 'city1',
                                  columns = 'city2',
                                  aggfunc = lambda x : geodesic(*x).km
                                 ).round(2)

%timeit -n 10 calculate_M()

395 ms ± 23.5 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

附錄 :

geopy包

1. 根據城市名查城市位置

• 創建定位器 :

from geopy.geocoders import Nominatim
geolocator = Nominatim(user_agent="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.121 Safari/537.36")

• 根據城市名稱查詢位置 :

location = geolocator.geocode("南京市雨花臺區")
location.address

'雨花臺區, 南京市, 江蘇省, 中國'

• 經度 :

location.longitude

118.7724224

• 緯度 :

location.latitude

31.9932018

2. 根據經緯度查詢位置 :

location = geolocator.reverse("31.997858805465647, 118.78544536405718")

location.address

'雨花東路, 雨花臺區, 建鄴區, 南京市, 江蘇省, 21006, 中國'

location.raw

{'place_id': 134810031,
 'licence': 'Data ? OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright',
 'osm_type': 'way',
 'osm_id': 189414212,
 'lat': '31.99705152324867',
 'lon': '118.78513775762214',
 'display_name': '雨花東路, 雨花臺區, 建鄴區, 南京市, 江蘇省, 21006, 中國',
 'address': {'road': '雨花東路',
  'suburb': '雨花臺區',
  'city': '建鄴區',
  'state': '江蘇省',
  'postcode': '21006',
  'country': '中國',
  'country_code': 'cn'},
 'boundingbox': ['31.9964788', '31.9989487', '118.7819222', '118.7866616']}

3. 計算距離 :

from geopy.distance import distance, geodesic

wellington, salamanca = (-41.32, 174.81), (40.96, -5.50)
distance(wellington, salamanca, ellipsoid='GRS-80').miles

12402.369702934551

shanghai, beijing = (31.235929042252014,121.48053886017651), (39.910924547299565,116.4133836971231)
distance(shanghai, beijing).km

1065.985103985533

geodesic(shanghai, beijing).km

1065.985103985533

因此 , 任務四的資料集就可以自己造了 :

cities = df['city1']
cities.head()

0     沈陽市
1     長春市
2    哈爾濱市
3     北京市
4     天津市
Name: city1, dtype: object

def get_lon_lat(city):
    location = geolocator.geocode(city)
    return location.longitude,location.latitude
longitude, latitude = [*zip(*[get_lon_lat(city) for city in cities])]
data = pd.DataFrame({'city':cities, 'longitude':longitude, 'latitude':latitude})
data.head()