天池大数据竞赛 Spaceack带你利用Pandas，趋势图与桑基图分析美国选民候选人喜好度

# 导入相关处理包
import pandas as pd
# 加入下面这条语句可以在 JupyterLab 上渲染画布（ JupyterLab 是天池实验室的一部分）
%matplotlib inline

# 读取候选人信息，由于原始数据没有表头，需要添加表头
candidates = pd.read_csv("weball20.txt", sep = '|',names=['CAND_ID','CAND_NAME','CAND_ICI','PTY_CD','CAND_PTY_AFFILIATION','TTL_RECEIPTS',
                                                          'TRANS_FROM_AUTH','TTL_DISB','TRANS_TO_AUTH','COH_BOP','COH_COP','CAND_CONTRIB',
                                                          'CAND_LOANS','OTHER_LOANS','CAND_LOAN_REPAY','OTHER_LOAN_REPAY','DEBTS_OWED_BY',
                                                          'TTL_INDIV_CONTRIB','CAND_OFFICE_ST','CAND_OFFICE_DISTRICT','SPEC_ELECTION','PRIM_ELECTION','RUN_ELECTION'
                                                          ,'GEN_ELECTION','GEN_ELECTION_PRECENT','OTHER_POL_CMTE_CONTRIB','POL_PTY_CONTRIB',
                                                          'CVG_END_DT','INDIV_REFUNDS','CMTE_REFUNDS'])
# 读取候选人和委员会的联系信息
ccl = pd.read_csv("ccl.txt", sep = '|',names=['CAND_ID','CAND_ELECTION_YR','FEC_ELECTION_YR','CMTE_ID','CMTE_TP','CMTE_DSGN','LINKAGE_ID'])

# 关联两个表数据
ccl = pd.merge(ccl, candidates)
# 提取出所需要的列
ccl = pd.DataFrame(ccl, columns=[ 'CMTE_ID','CAND_ID', 'CAND_NAME','CAND_PTY_AFFILIATION'])

# 读取个人捐赠数据，由于原始数据没有表头，需要添加表头
# 提示：读取本文件大概需要5-10s
itcont = pd.read_csv('itcont_2020_20200722_20200820.txt', sep='|',names=['CMTE_ID','AMNDT_IND','RPT_TP','TRANSACTION_PGI','IMAGE_NUM','TRANSACTION_TP','ENTITY_TP','NAME','CITY','STATE','ZIP_CODE','EMPLOYER','OCCUPATION','TRANSACTION_DT','TRANSACTION_AMT','OTHER_ID','TRAN_ID','FILE_NUM','MEMO_CD','MEMO_TEXT','SUB_ID'],low_memory=False)

# 将候选人与委员会关系表ccl和个人捐赠数据表itcont合并，通过 CMTE_ID
c_itcont =  pd.merge(ccl, itcont)

# 查看目前数据前6行, 若省略行数，默认显示5行
c_itcont.head(6)

# 查看目前数据后3行, 若省略行数，默认显示5行
c_itcont.head(n=3)

# 查看数据规模 多少行 多少列
c_itcont.shape

(756205, 24)

# 查看整体数据信息，包括每个字段的名称、非空数量、字段的数据类型
c_itcont.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 756205 entries, 0 to 756204
Data columns (total 24 columns):
CMTE_ID                 756205 non-null object
CAND_ID                 756205 non-null object
CAND_NAME               756205 non-null object
CAND_PTY_AFFILIATION    756205 non-null object
AMNDT_IND               756205 non-null object
RPT_TP                  756205 non-null object
TRANSACTION_PGI         756205 non-null object
IMAGE_NUM               756205 non-null int64
TRANSACTION_TP          756205 non-null object
ENTITY_TP               756205 non-null object
NAME                    756205 non-null object
CITY                    756159 non-null object
STATE                   756160 non-null object
ZIP_CODE                756092 non-null object
EMPLOYER                737413 non-null object
OCCUPATION              741294 non-null object
TRANSACTION_DT          756205 non-null int64
TRANSACTION_AMT         756205 non-null int64
OTHER_ID                647200 non-null object
TRAN_ID                 756205 non-null object
FILE_NUM                756205 non-null int64
MEMO_CD                 220 non-null object
MEMO_TEXT               655693 non-null object
SUB_ID                  756205 non-null int64
dtypes: int64(5), object(19)
memory usage: 144.2+ MB

# 查看数据表中数据类型的列的数据分布情况
c_itcont.describe()

#空值处理，统一填充 NOT PROVIDED
c_itcont['STATE'].fillna('NOT PROVIDED',inplace=True)
c_itcont['EMPLOYER'].fillna('NOT PROVIDED',inplace=True)
c_itcont['OCCUPATION'].fillna('NOT PROVIDED',inplace=True)
# 对日期TRANSACTION_DT列进行处理
c_itcont['TRANSACTION_DT'] = c_itcont['TRANSACTION_DT'] .astype(str)
# 将日期格式改为年月日  7242020	
c_itcont['TRANSACTION_DT'] = [i[3:7]+i[0]+i[1:3] for i in c_itcont['TRANSACTION_DT'] ]

# 计算每个总统候选人所获得的捐款总额，然后排序，取前二位
c_itcont.groupby("CAND_NAME").sum().sort_values("TRANSACTION_AMT",ascending=False).head(2)

# 提取需要的数据列
c_itcont0 = pd.DataFrame(c_itcont, columns=[ 'CAND_NAME', 'TRANSACTION_AMT', 'TRANSACTION_DT'])

# 已知所获得的捐款总额前两位的总统候选人分别是'BIDEN, JOSEPH R JR', 'TRUMP, DONALD J.'， 筛选出来
c_itcont1 = c_itcont0[c_itcont0['CAND_NAME'].isin(['BIDEN, JOSEPH R JR', 'TRUMP, DONALD J.'])]

# 因为同一天会有多笔捐款入帐，需要做分组求和
c_itcont2 = c_itcont1.groupby(['CAND_NAME', 'TRANSACTION_DT']).sum()

# 因为我们要看总额的趋势图，所以要做累加
c_itcont3 = c_itcont2.groupby(['CAND_NAME', ]).cumsum()

# 现在很快可以达到预期目标了
c_itcont3

# 整理下排列索引，这里用到了索引和列标题转换与转置操作
c_itcont4 = c_itcont3.unstack().T
c_itcont4

# grid参数 用来显示后面的辅助网格线， rot 使横坐标的日期以45度排列， 不会导致产生字符过长导致叠加的问题。（由于天池实验室环境不是最新的库，兼容问题会报警告，横坐标的日期标注无法显示。）
c_itcont4.plot(grid=True, rot=45)

!pip install pyecharts --user

Looking in indexes: https://mirrors.aliyun.com/pypi/simple
Requirement already satisfied: pyecharts in /data/nas/workspace/envs/python3.6/site-packages (1.9.0)
Requirement already satisfied: prettytable in /data/nas/workspace/envs/python3.6/site-packages (from pyecharts) (1.0.1)
Requirement already satisfied: simplejson in /data/nas/workspace/envs/python3.6/site-packages (from pyecharts) (3.17.2)
Requirement already satisfied: jinja2 in /opt/conda/lib/python3.6/site-packages (from pyecharts) (2.11.2)
Requirement already satisfied: setuptools in /opt/conda/lib/python3.6/site-packages (from prettytable->pyecharts) (49.6.0)
Requirement already satisfied: wcwidth in /opt/conda/lib/python3.6/site-packages (from prettytable->pyecharts) (0.2.5)
Requirement already satisfied: MarkupSafe>=0.23 in /opt/conda/lib/python3.6/site-packages (from jinja2->pyecharts) (1.1.1)

# 提取需要的数据列
c_itcont5 = pd.DataFrame(c_itcont, columns=[ 'STATE', 'TRANSACTION_AMT', 'CAND_PTY_AFFILIATION'])

# 提取党派集合
c_itcont_CAND_PTY_AFFILIATION = pd.DataFrame(c_itcont5,columns=['CAND_PTY_AFFILIATION'])
cgc = c_itcont_CAND_PTY_AFFILIATION.groupby('CAND_PTY_AFFILIATION').count()
node2 = list(cgc.index)
node2

['BDY',
 'CON',
 'DEM',
 'DFL',
 'GRE',
 'IND',
 'LIB',
 'NON',
 'NPA',
 'OTH',
 'REP',
 'UNK']

# 提取州集合
c_itcont_STATE = pd.DataFrame(c_itcont5,columns=['STATE'])
sgc = c_itcont_STATE.groupby('STATE').count()
node1 = list(sgc.index)

# 为方便阅读， 引入汉化字典
scp = {'AA': '美洲军',
 'AE': '军事地区',
 'AK': '阿拉斯加州',
 'AL': '阿拉巴马州',
 'AP': '太平洋军事机构',
 'AR': '阿肯色州',
 'AS': '美属萨摩亚群岛',
 'AZ': '阿利桑那州',
 'CA': '加利福尼亚州',
 'CO': '科罗拉多州',
 'CT': '康涅狄格州',
 'DC': '华盛顿DC',
 'DE': '特拉华州',
 'FL': '佛罗里达州',
 'FM': 'FM联邦',
 'GA': '乔治亚州',
 'GU': '关岛',
 'HI': '夏威夷州',
 'IA': '爱荷华州',
 'ID': '爱达荷州',
 'IL': '伊利诺斯州',
 'IN': '印第安纳州',
 'KS': '堪萨斯州',
 'KY': 'KY',
 'LA': '路易斯安那州',
 'MA': '马萨诸塞州',
 'MD': '马里兰州',
 'ME': '缅因州',
 'MI': '密歇根州',
 'MN': '明尼苏达州',
 'MO': '密苏里州',
 'MP': 'MP',
 'MS': '密西西比州',
 'MT': '蒙大拿州',
 'NC': '北卡罗来纳州',
 'ND': '北达科他州',
 'NE': '内布拉斯加州',
 'NH': '新罕布什尔州',
 'NJ': '新泽西州',
 'NM': '新墨西哥州',
 'NOT PROVIDED': 'NOT PROVIDED',
 'NV': '内华达州',
 'NY': '纽约州',
 'OH': '俄亥俄州',
 'OK': 'OK',
 'OR': '俄勒冈州',
 'PA': '宾夕法尼亚州',
 'PR': 'PR',
 'PW': 'PW',
 'RI': '罗得岛州',
 'SC': '南卡罗来纳州',
 'SD': '南达科他州',
 'TN': '田纳西州',
 'TX': '得克萨斯州',
 'UT': '犹他州',
 'VA': '弗吉尼亚州',
 'VI': 'VI',
 'VT': '佛蒙特州',
 'WA': '华盛顿州',
 'WI': '威斯康辛州',
 'WV': '西弗吉尼亚州',
 'WY': '怀俄明州',
 'ZZ': 'ZZ'}

# 将集合加入到节点列表，nodes需要把桑基图中出现的名称全部设置进去，并且要保证links中的名称与name相同
nodes = []
for n in node1:
    nodes.append({'name': scp[n]})
for n in node2:
    nodes.append({'name': n})

# 做分组聚合
cgs = c_itcont5.groupby(['STATE','CAND_PTY_AFFILIATION']).sum()

# 将聚合结果加入到链接列表， links代表节点关系，source表示起点，target表示终点，需要将节点关系全部输入进去，value表示节点长度
links = []
for row in cgs.iterrows():
    links.append({'source': scp[row[0][0]], 'target': row[0][1], 'value': int(row[1][0])})

from pyecharts import options as opts
from pyecharts.charts import Page, Sankey

sankey = Sankey(init_opts=opts.InitOpts(width="1024", height="768"))		#可以设置大小和图标名称
sankey.add(
    '各州捐款额流向图',				#名称
    nodes,					#输入节点，如果导入json数据，nodes=json['nodes]
    links,					#输入关系,nodes=json['links']
    linestyle_opt=opts.LineStyleOpts(opacity=0.3, curve=0.5, color="source", width=10),
    label_opts=opts.LabelOpts(position="right", is_show=True, color='red'),
    node_gap=1
)
sankey.render()
# 生成的桑基图可通过下方目录查看

'/data/nas/workspace/jupyter/download/render.html'