python数据清洗框架_Python 用pandas 进行数据清洗处理

Python 用pandas 进行数据清洗处理

发布时间：2018-05-23 20:14,

浏览次数：494

, 标签：

Python

pandas

1.数据读取

import pandas as pd import numpy as np import pymongo data =

pd.DataFrame(pd.read_excel('000.xlsx', index=False)) client =

pymongo.MongoClient("mongodb://XX:[email protected]:2018",connect=False) db =

client["test"] table = db["python"] df = pd.DataFrame(list(table.find()))

可以从excel，csv，mongo数据之类的读取数据

2.遍历

for i in range(data.index.max()): if any([ 'missing' in data.loc[i,:].values,

data.loc[i,'hour'] not in range(25), ]): print('已删除存在异常值 %s 行数据'%i)

data.drop([i],inplace=True) for i in range(0,len(df)): info =

df.loc[i].to_dict()

3. 去空(NA)

3.1直接去除

from numpy import nan as NA data=Series([1,NA,3.5,NA,7]) print(data.dropna())

#至少2个NA才删除 print(data.dropna(thresh=2))

3.2 用中位数或者平均数进行填充

df = df.fillna(df.median()) print(df.fillna(df.mean()))

4.对字段进行处理

def get_salary(salary): s = 0 if "-" in salary: for part in salary.split("-"):

if "万" in part: q = float(part[:-1]) * 10000 else: q = float(part[:-1]) * 1000

s += q return int(s/2.0) else: return np.nan df["salary"] =

df["salary"].apply(get_salary) df.head()

df["company"]=df["company"].apply(lambda x :x.split("/")[0].strip('"'))

5.删除重复

df["company"].drop_duplicates()

6.只留部分

df.loc[:,["address","company"]] df_c = df_c.iloc[:,[4,5]] del

data["name_grade"] del data["info_grade"]

7. 排序

df.sort_values(by='col1', ascending=False)

8. isin

mask = df['A'].isin([1]) #括号中必须为list

9. merge

df1 = pd.DataFrame({'name':['kate', 'herz', 'catherine', 'sally'], 'age':[25,

28, 39, 35]}) df2 = pd.DataFrame({'name_t':['kate', 'herz', 'catherine',

'sally'], 'score':[70, 60, 90, 100]}) print(pd.merge(df1, df2, left_on="name",

right_on="name_t").drop('name_t', axis=1))

10.保存为csv，或者到mongo

df["company"].drop_duplicates().to_csv("company.csv",encoding="utf-8")

db[MONGO_TABLE].insert(row.to_dict())

http://www.codeblogbt.com/archives/102061