使用merge时，如果没有指定 on = 哪一列，则默认以重叠列名当做链接键， 当然也可以按照多键连接，只需要’on’参数后传入多键列表即可
14.身份证发征地和当前行政区域查询
def get_location(id_number):
# print(id_number)
url = f'https://qq.ip138.com/idsearch/index.asp?userid={id_number}&action=idcard'
headers = {
'User-Agent':'Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.128 Mobile Safari/537.36'
}
res = requests.get(url,headers = headers)
html = res.text.encode("ISO-8859-1").decode("utf-8")
element = etree.HTML(html)
try:
issue_place = element.xpath("//div[@class='bd']/table/tbody/tr[5]/td[2]/p/text()")[0]
current_place = element.xpath("//div[@class='bd']/table/tbody/tr[6]/td[2]/p/text()")[0]
except Exception as e :
current_place = issue_place
return issue_place,current_place
df[['发证地区','行政区域']] = df.apply(lambda row:pd.Series(get_location(row['身份证号'])),axis=1)
15. 设置category
df["Status"] = df["Status"].astype("category")
df["Status"].cat.set_categories(["won","pending","presented","declined"],inplace=True)
16.获取除去某列的其他所有列
df.loc[:,df.columns != 'column_name' ]
17.查询只出现在left dataframe里的
(left.merge(right, on='key', how='left', indicator=True)
.query('_merge == "left_only"')
.drop('_merge', axis = 1))
18.根据第一个excel修改其他excel文件列表并 append在一起
dfs = []
for i,f in enumerate(files): # files 是excel 文件路径
df = pd.read_excel(f)
if i == 0:
col = df.columns
df.columns=col
dfs.append(df)
19. 统计每列nan总数
df.isnull().sum()
