重复值处理
- 数据清洗一般先从重复值和缺失值开始处理
- 重复值一般采取删除法来处理
- 但有些重复值不能删除,例如订单明细数据或交易明细数据等
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)
<div>
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<table border=”1″ class=”dataframe”>
<thead>
<tr style="text-align: right;">
<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>
<p>5 rows × 22 columns</p>
</div>
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)
<div>
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<table border=”1″ class=”dataframe”>
<thead>
<tr style="text-align: right;">
<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>
<p>5 rows × 22 columns</p>
</div>
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())
0
正文完