数据清洗之 字符串数据处理

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')

df.head(5)

.dataframe tbody tr th:only-of-type {

    vertical-align: middle;

}

.dataframe tbody tr th {

    vertical-align: top;

}

.dataframe thead th {

    text-align: right;

}

<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>

<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>

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 object

Location          7491 non-null object

Model_Year        7489 non-null float64

Mileage           7468 non-null object

Exterior_Color    6778 non-null object

Make              7489 non-null object

Warranty          5109 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(2), object(20)

memory usage: 1.3+ MB

# 里面有字符串，不能进行转换
# df['Price'].astype(float)

# .str 方法可用于提取字符
df['Price'].str[1:3].head(5)

0    11

1    17

2    3,

3    6,

4    10

Name: Price, dtype: object

# 首先要对字符串进行相关处理
df['价格'] = df['Price'].str.strip('$')

df['价格'].head(5)

0    11,412 

1    17,200 

2     3,872 

3     6,575 

4    10,000 

Name: 价格, dtype: object

df['价格'] = df['价格'].str.replace(',', '')

df['价格'].head(5)

0    11412 

1    17200 

2     3872 

3     6575 

4    10000 

Name: 价格, dtype: object

df['价格'] = df['价格'].astype(float)

df['价格'].head(5)

0    11412.0

1    17200.0

2     3872.0

3     6575.0

4    10000.0

Name: 价格, dtype: float64

df.dtypes

Condition          object

Condition_Desc     object

Price              object

Location           object

Model_Year        float64

Mileage            object

Exterior_Color     object

Make               object

Warranty           object

Model              object

Sub_Model          object

Type               object

Vehicle_Title      object

OBO                object

Feedback_Perc      object

Watch_Count        object

N_Reviews          object

Seller_Status      object

Vehicle_Tile       object

Auction            object

Buy_Now            object

Bid_Count         float64

价格                float64

dtype: object

# 字符串分割
df['Location'].str.split(',').str[0].head(5)

0          McHenry

1    Fort Recovery

2          Chicago

3        Green Bay

4        West Bend

Name: Location, dtype: object

# 计算字符串的长度
df['Location'].str.len().head(5)

0    32.0

1    34.0

2    32.0

3    35.0

4    35.0

Name: Location, dtype: float64