python数据清洗常用举例

分手后的思念是犯贱 2022-09-07 14:47 239阅读 0赞

## python数据清洗常用举例 ##

### 数据展示 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDA1NTY2NA_size_16_color_FFFFFF_t_70]

### 练习代码 ###

import pandas as pd
    import numpy as np
    import os
    from datetime import datetime
    # review_date 转为时间戳形式
    
    
    
    # import matplotlib.pyplot as plt
    data=pd.read_excel(r"C:\Users\10746\Desktop\aa\111.xlsx",engine='openpyxl')
    pd.set_option('display.max_columns',None)
    pd.set_option('display.max_rows',None)
    print(data.head())
    
    #删除重复值
    data.drop_duplicates(['班级','姓名'],inplace=True)
    
    #查看异常值
    print(data.describe())
    # data.iloc[:,2:5].plot.box()
    # pd.set_option('display.max_columns',None)
    # pd.set_option('display.max_rows',None)
    data[(data[['语文','数学','英语']]>100) | (data[['语文','数学','英语']]<0)]=np.nan
    # datetime.date(2021,12,1)
    #空值处理
    #查看空置
    print(data.isnull().sum())
    #空值赋值
    da=data.groupby(['班级','年级'])['语文','数学','英语'].mean()
    
    data.set_index(['班级','年级'],inplace=True,drop=False)
    
    
    
    data['语文'].fillna(da['语文'],inplace=True)
    data['数学'].fillna(da['数学'],inplace=True)
    data['英语'].fillna(da['英语'],inplace=True)
    
    #分组排序
    data.reset_index(drop=True,inplace=True)
    data.sort_values(by=['年级','班级'],inplace=True,ascending=[True,False])
    # data['rank'] = data.groupby(['年级','班级'])['语文'].rank(method='min',ascending=False)
    data['rank'] = [i for i in range(data.iloc[:,0].count())]
    
    #格式修改
    data['语文']=data['语文'].astype(int)
    
    #日期修改
    data['rq'] = data['日期'].apply(lambda x : x.strftime('%Y/%m/%d')) #将日期变为/连接的，格式变为obj
    data['rq']=data['rq'].apply(pd.to_datetime,format='%Y-%m-%d')#将rq还原为日期
    # data['rq']=data['rq'].apply(lambda x:datetime.strftime(x,"%Y'年'"))#将rq还原为日期 由日期格式转化为字符串格式的函数为: datetime.datetime.strftime() 由字符串格式转化为日期格式的函数为: datetime.datetime.strptime()
    
    #转为时间戳
    data['rq']=data['rq'].apply(lambda x:int(datetime.timestamp(x)))#转为时间戳
    data['rq']=pd.to_datetime(data['rq'], unit='s')#时间戳转时间
    
    data['rq']=data['rq'] + pd.Timedelta(days=1)#加一天
    
    data['y'] = data['日期'].dt.year #提取年 month day hor minute
    data['rrq']=f'{
          data["rq"].dt.year}年{
          data["rq"].dt.month}月{
          data["rq"].dt.day}日'
    data['rrq']=list(map(lambda x:f'{
          x:%Y}年',data['rq']))
    if os.path.exists(r'C:\Users\10746\Desktop\aa\1.xlsx'):
        os.remove(r'C:\Users\10746\Desktop\aa\1.xlsx')
    data.to_excel(r'C:\Users\10746\Desktop\aa\1.xlsx')

### 结果展示 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDA1NTY2NA_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDA1NTY2NA_size_16_color_FFFFFF_t_70]: /images/20220829/b5f4c6e2206348489bf0d25e05706456.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDA1NTY2NA_size_16_color_FFFFFF_t_70 1]: /images/20220829/377cd1696e034925bffea186579ade53.png