数据清洗之重复值处理

女爷i 2023-05-21 13:52 60阅读 0赞

# 重复值处理 #

*  数据清洗一般先从重复值和缺失值开始处理
 *  重复值一般采取删除法来处理
 *  但有些重复值不能删除，例如订单明细数据或交易明细数据等

import pandas as pd
    import numpy as np
    import os

os.getcwd()

'D:\\Jupyter\\notebook\\Python数据清洗实战\\数据清洗之数据预处理'

os.chdir('D:\\Jupyter\\notebook\\Python数据清洗实战\\数据')

df = pd.read_csv('MotorcycleData.csv', encoding='gbk', na_values='Na')

df.head(5)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>Condition</th> 
   <th>Condition_Desc</th> 
   <th>Price</th> 
   <th>Location</th> 
   <th>Model_Year</th> 
   <th>Mileage</th> 
   <th>Exterior_Color</th> 
   <th>Make</th> 
   <th>Warranty</th> 
   <th>Model</th> 
   <th>...</th> 
   <th>Vehicle_Title</th> 
   <th>OBO</th> 
   <th>Feedback_Perc</th> 
   <th>Watch_Count</th> 
   <th>N_Reviews</th> 
   <th>Seller_Status</th> 
   <th>Vehicle_Tile</th> 
   <th>Auction</th> 
   <th>Buy_Now</th> 
   <th>Bid_Count</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>Used</td> 
   <td>mint!!! very low miles</td> 
   <td>$11,412</td> 
   <td>McHenry, Illinois, United States</td> 
   <td>2013.0</td> 
   <td>16,000</td> 
   <td>Black</td> 
   <td>Harley-Davidson</td> 
   <td>Unspecified</td> 
   <td>Touring</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>8.1</td> 
   <td>NaN</td> 
   <td>2427</td> 
   <td>Private Seller</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>FALSE</td> 
   <td>28.0</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>Used</td> 
   <td>Perfect condition</td> 
   <td>$17,200</td> 
   <td>Fort Recovery, Ohio, United States</td> 
   <td>2016.0</td> 
   <td>60</td> 
   <td>Black</td> 
   <td>Harley-Davidson</td> 
   <td>Vehicle has an existing warranty</td> 
   <td>Touring</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>100</td> 
   <td>17</td> 
   <td>657</td> 
   <td>Private Seller</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>TRUE</td> 
   <td>0.0</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>Used</td> 
   <td>NaN</td> 
   <td>$3,872</td> 
   <td>Chicago, Illinois, United States</td> 
   <td>1970.0</td> 
   <td>25,763</td> 
   <td>Silver/Blue</td> 
   <td>BMW</td> 
   <td>Vehicle does NOT have an existing warranty</td> 
   <td>R-Series</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>100</td> 
   <td>NaN</td> 
   <td>136</td> 
   <td>NaN</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>FALSE</td> 
   <td>26.0</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>Used</td> 
   <td>CLEAN TITLE READY TO RIDE HOME</td> 
   <td>$6,575</td> 
   <td>Green Bay, Wisconsin, United States</td> 
   <td>2009.0</td> 
   <td>33,142</td> 
   <td>Red</td> 
   <td>Harley-Davidson</td> 
   <td>NaN</td> 
   <td>Touring</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>100</td> 
   <td>NaN</td> 
   <td>2920</td> 
   <td>Dealer</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>FALSE</td> 
   <td>11.0</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>Used</td> 
   <td>NaN</td> 
   <td>$10,000</td> 
   <td>West Bend, Wisconsin, United States</td> 
   <td>2012.0</td> 
   <td>17,800</td> 
   <td>Blue</td> 
   <td>Harley-Davidson</td> 
   <td>NO WARRANTY</td> 
   <td>Touring</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>100</td> 
   <td>13</td> 
   <td>271</td> 
   <td>OWNER</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>TRUE</td> 
   <td>0.0</td> 
  </tr> 
 </tbody> 
</table>

5 rows × 22 columns

def f(x):
        if '$' in str(x):
            x = str(x).strip('$')
            x = str(x).replace(',', '')
        else:
            x = str(x).replace(',', '')
        return float(x)

df['Price'] = df['Price'].apply(f)

df['Mileage'] = df['Mileage'].apply(f)

df.head(5)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>Condition</th> 
   <th>Condition_Desc</th> 
   <th>Price</th> 
   <th>Location</th> 
   <th>Model_Year</th> 
   <th>Mileage</th> 
   <th>Exterior_Color</th> 
   <th>Make</th> 
   <th>Warranty</th> 
   <th>Model</th> 
   <th>...</th> 
   <th>Vehicle_Title</th> 
   <th>OBO</th> 
   <th>Feedback_Perc</th> 
   <th>Watch_Count</th> 
   <th>N_Reviews</th> 
   <th>Seller_Status</th> 
   <th>Vehicle_Tile</th> 
   <th>Auction</th> 
   <th>Buy_Now</th> 
   <th>Bid_Count</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>Used</td> 
   <td>mint!!! very low miles</td> 
   <td>11412.0</td> 
   <td>McHenry, Illinois, United States</td> 
   <td>2013.0</td> 
   <td>16000.0</td> 
   <td>Black</td> 
   <td>Harley-Davidson</td> 
   <td>Unspecified</td> 
   <td>Touring</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>8.1</td> 
   <td>NaN</td> 
   <td>2427</td> 
   <td>Private Seller</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>FALSE</td> 
   <td>28.0</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>Used</td> 
   <td>Perfect condition</td> 
   <td>17200.0</td> 
   <td>Fort Recovery, Ohio, United States</td> 
   <td>2016.0</td> 
   <td>60.0</td> 
   <td>Black</td> 
   <td>Harley-Davidson</td> 
   <td>Vehicle has an existing warranty</td> 
   <td>Touring</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>100</td> 
   <td>17</td> 
   <td>657</td> 
   <td>Private Seller</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>TRUE</td> 
   <td>0.0</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>Used</td> 
   <td>NaN</td> 
   <td>3872.0</td> 
   <td>Chicago, Illinois, United States</td> 
   <td>1970.0</td> 
   <td>25763.0</td> 
   <td>Silver/Blue</td> 
   <td>BMW</td> 
   <td>Vehicle does NOT have an existing warranty</td> 
   <td>R-Series</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>100</td> 
   <td>NaN</td> 
   <td>136</td> 
   <td>NaN</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>FALSE</td> 
   <td>26.0</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>Used</td> 
   <td>CLEAN TITLE READY TO RIDE HOME</td> 
   <td>6575.0</td> 
   <td>Green Bay, Wisconsin, United States</td> 
   <td>2009.0</td> 
   <td>33142.0</td> 
   <td>Red</td> 
   <td>Harley-Davidson</td> 
   <td>NaN</td> 
   <td>Touring</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>100</td> 
   <td>NaN</td> 
   <td>2920</td> 
   <td>Dealer</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>FALSE</td> 
   <td>11.0</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>Used</td> 
   <td>NaN</td> 
   <td>10000.0</td> 
   <td>West Bend, Wisconsin, United States</td> 
   <td>2012.0</td> 
   <td>17800.0</td> 
   <td>Blue</td> 
   <td>Harley-Davidson</td> 
   <td>NO WARRANTY</td> 
   <td>Touring</td> 
   <td>...</td> 
   <td>NaN</td> 
   <td>FALSE</td> 
   <td>100</td> 
   <td>13</td> 
   <td>271</td> 
   <td>OWNER</td> 
   <td>Clear</td> 
   <td>True</td> 
   <td>TRUE</td> 
   <td>0.0</td> 
  </tr> 
 </tbody> 
</table>

5 rows × 22 columns

df.info()

<class 'pandas.core.frame.DataFrame'>
    RangeIndex: 7493 entries, 0 to 7492
    Data columns (total 22 columns):
    Condition         7493 non-null object
    Condition_Desc    1656 non-null object
    Price             7493 non-null float64
    Location          7491 non-null object
    Model_Year        7489 non-null float64
    Mileage           7467 non-null float64
    Exterior_Color    6778 non-null object
    Make              7489 non-null object
    Warranty          5108 non-null object
    Model             7370 non-null object
    Sub_Model         2426 non-null object
    Type              6011 non-null object
    Vehicle_Title     268 non-null object
    OBO               7427 non-null object
    Feedback_Perc     6611 non-null object
    Watch_Count       3517 non-null object
    N_Reviews         7487 non-null object
    Seller_Status     6868 non-null object
    Vehicle_Tile      7439 non-null object
    Auction           7476 non-null object
    Buy_Now           7256 non-null object
    Bid_Count         2190 non-null float64
    dtypes: float64(4), object(18)
    memory usage: 1.3+ MB

any(df.duplicated())

True

# 显示重复数据
    # df[df.duplicated()]

# 统计重复数据
    np.sum(df.duplicated())

1221

# 删除重复值
    df.drop_duplicates(inplace=True)

df.columns

Index(['Condition', 'Condition_Desc', 'Price', 'Location', 'Model_Year',
           'Mileage', 'Exterior_Color', 'Make', 'Warranty', 'Model', 'Sub_Model',
           'Type', 'Vehicle_Title', 'OBO', 'Feedback_Perc', 'Watch_Count',
           'N_Reviews', 'Seller_Status', 'Vehicle_Tile', 'Auction', 'Buy_Now',
           'Bid_Count'],
          dtype='object')

# 根据指定变量判断重复值
    df.drop_duplicates(subset=['Condition', 'Condition_Desc', 'Price', 'Location'], inplace=True)

# 重复已经被删除
    np.sum(df.duplicated())