Statement of Completion#ad509b0d
Data Cleaning with Pandas
medium
From Raw to Refined: Hands-On Mobile Phone Data Cleaning
Resolution
Activities
In [1]:
import numpy as np
import pandas as pd
import warnings
In [2]:
warnings.filterwarnings('ignore')
In [193]:
df = pd.read_csv('mobile_phones_data.xls')
In [4]:
df.head()
Out[4]:
Unnamed: 0 | Brand me | Ratings | RAM | ROM | Mobile_Size | Primary_Cam | Selfi_Cam | Battery_Power | Price | |
---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | LG V30+ (Black, 128 ) | 4.3 | 4.0 GB | 128.0 GB | 6.0 inches | 48 MP | 13.0 MP | 4000 mAh | Rs. 24_999 |
1 | 1 | I Kall K11 | 3.4 | 6.0 GB | 64.0 GB | 4.5 inches | 48 MP | 12.0 MP | 4000 mAh | Rs. 15999 |
2 | 2 | Nokia 105 ss | 4.3 | 4.0 GB | 4.0 GB | 4.5 inches | 64 MP | 16.0 MP | 4000 mAh | Rs. 15000 |
3 | 3 | Samsung Galaxy A50 (White, 64 ) | 4.4 | 6.0 GB | 64.0 GB | 6.4 inches | 48 MP | 15.0 MP | 3800 mAh | Rs. 18999 |
4 | 4 | POCO F1 (Steel Blue, 128 ) | 4.5 | 6.0 GB | 128.0 GB | 6.18 inches | 35 MP | 15.0 MP | 3800 mAh | Rs. 18999 |
In [5]:
df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 836 entries, 0 to 835 Data columns (total 10 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Unnamed: 0 836 non-null int64 1 Brand me 836 non-null object 2 Ratings 804 non-null object 3 RAM 829 non-null object 4 ROM 832 non-null object 5 Mobile_Size 834 non-null object 6 Primary_Cam 836 non-null object 7 Selfi_Cam 567 non-null object 8 Battery_Power 836 non-null object 9 Price 834 non-null object dtypes: int64(1), object(9) memory usage: 65.4+ KB
Activities¶
1) Remove Irrelevant Column¶
In [194]:
# Enter your code here
df.drop(columns = 'Unnamed: 0', inplace = True)
2) Rename Column Name: Brand me
to Model_Name
¶
In [195]:
# Enter your code here
df.rename(columns = {'Brand me': 'Model_Name'}, inplace = True)
In [16]:
df.head()
Out[16]:
Model_Name | Ratings | RAM | ROM | Mobile_Size | Primary_Cam | Selfi_Cam | Battery_Power | Price | |
---|---|---|---|---|---|---|---|---|---|
0 | LG V30+ (Black, 128 ) | 4.3 | 4.0 GB | 128.0 GB | 6.0 inches | 48 MP | 13.0 MP | 4000 mAh | Rs. 24_999 |
1 | I Kall K11 | 3.4 | 6.0 GB | 64.0 GB | 4.5 inches | 48 MP | 12.0 MP | 4000 mAh | Rs. 15999 |
2 | Nokia 105 ss | 4.3 | 4.0 GB | 4.0 GB | 4.5 inches | 64 MP | 16.0 MP | 4000 mAh | Rs. 15000 |
3 | Samsung Galaxy A50 (White, 64 ) | 4.4 | 6.0 GB | 64.0 GB | 6.4 inches | 48 MP | 15.0 MP | 3800 mAh | Rs. 18999 |
4 | POCO F1 (Steel Blue, 128 ) | 4.5 | 6.0 GB | 128.0 GB | 6.18 inches | 35 MP | 15.0 MP | 3800 mAh | Rs. 18999 |
3) Remove Duplicate Entries¶
In [196]:
# Enter your code here
df.drop_duplicates(inplace = True)
4) Remove Null Values¶
In [22]:
# Enter your code here
df.isnull().sum()
Out[22]:
Model_Name 0 Ratings 0 RAM 0 ROM 0 Mobile_Size 0 Primary_Cam 0 Selfi_Cam 0 Battery_Power 0 Price 0 dtype: int64
In [197]:
df.dropna(inplace = True)
Let's examine the Ratings column. By executing the following cell, you'll discover various anomalies: values below 1
, negative numbers, and even a string entry like #FIELD!
. Given that this dataset originates from an ecommerce platform, it's implausible to encounter negative values, values less than 1, or string entries in the Ratings column.
In the image below, you can see that users can only give a rating of one or higher, and they can't provide ratings using words.
In [24]:
df['Ratings'].unique()
Out[24]:
array(['4.3', '3.4', '4.4', '4.5', '4.7', '3.9', '3.8', '4.6', '3.7', '4', '4.2', '3.6', '#FIELD!', '4.1', '3.5', '3.2', '0', '3', '-4.4', '-0.8', '0.1'], dtype=object)
In [164]:
import numpy as np
5) Remove Invalid Ratings¶
In [198]:
# Enter your code here
df.loc[df['Ratings'] == '#FIELD!', 'Ratings'] = np.nan
df['Ratings'] = df['Ratings'].astype(float)
df.loc[df['Ratings'] < 1, 'Ratings'] = np.nan
In [199]:
df.dropna(inplace = True)
In [ ]:
6) Strip GB
Unit from RAM
and ROM
Columns¶
In [46]:
# Enter your code here
df['RAM'].unique()
Out[46]:
array(['4.0 GB', '6.0 GB', '8.0 GB', '2.0 GB', '12.0 GB', '3.0 GB', '1.0 GB', '5.0 GB', '34.0 GB', '10.0 GB'], dtype=object)
In [48]:
df['RAM']
Out[48]:
0 4.0 GB 1 6.0 GB 2 4.0 GB 3 6.0 GB 4 6.0 GB ... 824 4.0 GB 825 4.0 GB 831 6.0 GB 833 3.0 GB 835 6.0 GB Name: RAM, Length: 426, dtype: object
In [200]:
df['RAM'] = df['RAM'].str.replace(' GB', '')
df['ROM'] = df['ROM'].str.replace(' GB', '')
Previously, after removing the GB
, the RAM
and ROM
columns contained float values but were still treated as object data types. Therefore, in the next activity, we will convert the object type to float data type for these columns.
7) Convert RAM
and ROM
Columns to Float Data Type¶
In [201]:
# Enter your code here
df['RAM'] = df['RAM'].astype(float)
df['ROM'] = df['ROM'].astype(float)
In the previous activity, we have converted the RAM
and ROM
columns from object
to float
. In the next activity, we will convert both columns from float
to integer
. You might have two questions running in your mind. One is why we are converting float
to int
now? Let me answer it: we convert from float
to integer
to ensure that all values are represented as whole numbers, which is essential for data consistency and compatibility, particularly for RAM
and ROM
specifications of mobile phones. After understanding the reason for the first one, your second question might be: can't we directly convert float values represented as objects into integers? The answer is that direct conversion from float values represented as objects to integers is not possible; we first need to convert them to float to handle potential decimal points or non-integer values before converting them to integers.
8) Convert RAM
and ROM
Columns to Integer Data Type¶
In [202]:
# Enter your code here
df['RAM'] = df['RAM'].astype(int)
df['ROM'] = df['ROM'].astype(int)
Upon close observation of the dataset, you'll notice that nearly all mobile phones were launched before October 23, 2020
. This assertion is based on the fact that only iPhone models from the 11 series are present in the dataset, suggesting that it predates the release of the Apple iPhone 12 series, which occurred on October 23, 2020
. Additionally, considering that the maximum RAM capacity available in mobile phones at that time was 16GB
, it's improbable to find any mobile device with more than 16GB
of RAM in our dataset.
9) Exclude the mobiles which have RAM
greater than 16GB
¶
In [203]:
# Enter your code here
df = df[df['RAM'] <= 16]
10) Remove the inches
in the Mobile_Size
column and Convert it into appropriate data type¶
In [204]:
# Enter your code here
df['Mobile_Size'] = df['Mobile_Size'].str.rstrip(' inches').astype(float)
11) Primary Camera Resolution Refinement: Strip MP
and Convert to Integer¶
In [205]:
# Enter your code here
df['Primary_Cam'] = df['Primary_Cam'].str.rstrip(' MP').astype(float).astype(int)
12) Selfie Camera Cleaning: Strip MP
and Convert to Integer¶
In [206]:
# Enter your code here
df['Selfi_Cam'] = df['Selfi_Cam'].str.rstrip(' MP').astype(float).astype(int)
13) Extract Battery Power Values from String Format¶
In [89]:
df['Battery_Power']
Out[89]:
0 4000 mAh 1 4000 mAh 2 4000 mAh 3 3800 mAh 4 3800 mAh ... 824 1200 mAh 825 3000 mAh 831 2800 mAh 833 3_800 mAh 835 1050 mAh Name: Battery_Power, Length: 425, dtype: object
In [207]:
# Enter your code here
df['Battery_Power'] = df['Battery_Power'].str.rstrip(' mAh')
Have you noticed any formatting issues in the Battery_Power column, particularly regarding the presence of underscores in some values? Despite removing mAh
, the formatting remains inconsistent. For instance, some values contain underscores. In data contexts, such as programming, underscores often serve as visual separators to enhance readability, as seen in languages like Python where large numbers are written with underscores (e.g., 14_000 is interpreted as 14,000). Let's address this inconsistency next.
14) Battery Power: Remove Underscores and Convert to Integer¶
In [208]:
# Enter your code here
df['Battery_Power'] = df['Battery_Power'].str.rstrip('_').astype(float).astype(int)
The Battery_Power
column contains invalid values, as you'll see by running the cell below. Notably, it includes 0
which is not possible for any mobile phone battery, as well as capacities exceeding 6000 mAh
before October 23, 2020
. The maximum capacity recorded for mobile phone batteries up to that date was 6000 mAh
. Let's address these discrepancies in the next activity.
In [176]:
df['Battery_Power'].unique()
Out[176]:
array([ 4000, 3800, 5000, 4700, 4020, 4100, 1050, 3000, 4230, 1550, 4010, 3500, 1750, 1200, 2500, 4200, 3600, 3030, 0, 2800, 3180, 3200, 4030, 1020, 1100, 3260, 3050, 3750, 3010, 3100, 3225, 3700, 6000, 4440, 4025, 4065, 4085, 1500, 2000, 50000, 2900, 4900, 4045, 60000, 4550, 4780, 3350, 1900])
15) Exclude Invalid Battery Power Values¶
In [99]:
df
Out[99]:
Brand me | Ratings | RAM | ROM | Mobile_Size | Primary_Cam | Selfi_Cam | Battery_Power | Price | |
---|---|---|---|---|---|---|---|---|---|
0 | LG V30+ (Black, 128 ) | 4.3 | 4 | 128 | 6.00 | 48 | 13 | 4000 | Rs. 24_999 |
1 | I Kall K11 | 3.4 | 6 | 64 | 4.50 | 48 | 12 | 4000 | Rs. 15999 |
2 | Nokia 105 ss | 4.3 | 4 | 4 | 4.50 | 64 | 16 | 4000 | Rs. 15000 |
3 | Samsung Galaxy A50 (White, 64 ) | 4.4 | 6 | 64 | 6.40 | 48 | 15 | 3800 | Rs. 18999 |
4 | POCO F1 (Steel Blue, 128 ) | 4.5 | 6 | 128 | 6.18 | 35 | 15 | 3800 | Rs. 18999 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
824 | Nokia 3310 DS | 3.8 | 4 | 64 | 4.54 | 48 | 15 | 1200 | Rs. 3580 |
825 | Micax X1i-Pop | 4.1 | 4 | 40 | 4.77 | 48 | 15 | 3000 | Rs. 1080 |
831 | Karbonn K24 Plus Pro | 3.8 | 6 | 32 | 4.54 | 48 | 12 | 2800 | Rs. 1299 |
833 | Alcatel 5V (Spectrum Blue, 32 ) | 4.4 | 3 | 32 | 6.20 | 48 | 1 | 3800 | Rs. 9790 |
835 | Blacear B5 Grip | 3.5 | 6 | 32 | 4.50 | 64 | 15 | 1050 | Rs. 0 |
422 rows × 9 columns
In [209]:
# Enter your code here
df = df[(df['Battery_Power'] > 0) & (df['Battery_Power'] <= 6000)]
In [178]:
df
Out[178]:
Model_Name | Ratings | RAM | ROM | Mobile_Size | Primary_Cam | Selfi_Cam | Battery_Power | Price | |
---|---|---|---|---|---|---|---|---|---|
0 | LG V30+ (Black, 128 ) | 4.3 | 4 | 128 | 6.00 | 48 | 13 | 4000 | Rs. 24_999 |
1 | I Kall K11 | 3.4 | 6 | 64 | 4.50 | 48 | 12 | 4000 | Rs. 15999 |
2 | Nokia 105 ss | 4.3 | 4 | 4 | 4.50 | 64 | 16 | 4000 | Rs. 15000 |
3 | Samsung Galaxy A50 (White, 64 ) | 4.4 | 6 | 64 | 6.40 | 48 | 15 | 3800 | Rs. 18999 |
4 | POCO F1 (Steel Blue, 128 ) | 4.5 | 6 | 128 | 6.18 | 35 | 15 | 3800 | Rs. 18999 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
824 | Nokia 3310 DS | 3.8 | 4 | 64 | 4.54 | 48 | 15 | 1200 | Rs. 3580 |
825 | Micax X1i-Pop | 4.1 | 4 | 40 | 4.77 | 48 | 15 | 3000 | Rs. 1080 |
831 | Karbonn K24 Plus Pro | 3.8 | 6 | 32 | 4.54 | 48 | 12 | 2800 | Rs. 1299 |
833 | Alcatel 5V (Spectrum Blue, 32 ) | 4.4 | 3 | 32 | 6.20 | 48 | 1 | 3800 | Rs. 9790 |
835 | Blacear B5 Grip | 3.5 | 6 | 32 | 4.50 | 64 | 15 | 1050 | Rs. 0 |
422 rows × 9 columns
16) Format Price Data: Removing Currency Symbol and Underscores¶
In [210]:
df['Price'].str.lstrip('Rs. ')
Out[210]:
0 24_999 1 15999 2 15000 3 18999 4 18999 ... 824 3580 825 1080 831 1299 833 9790 835 0 Name: Price, Length: 422, dtype: object
In [118]:
df['Price'][0]
Out[118]:
'Rs. 24_999'
In [211]:
# Enter your code here
df['Price'] = df['Price'].str.lstrip('Rs. ')
df['Price'] = df['Price'].str.replace('_', '').astype(float).astype(int)
df['Price']
Out[211]:
0 24999 1 15999 2 15000 3 18999 4 18999 ... 824 3580 825 1080 831 1299 833 9790 835 0 Name: Price, Length: 422, dtype: int64
Which mobile phone typically commands the highest price? It's usually Apple. In most cases, it's improbable for any other phone to surpass Apple's prices. The highest price recorded for an Apple phone in our dataset is 140,300 (INR)
. Hence, we can consider any value above that as invalid.
17) Filter Invalid Price Values¶
In [181]:
# Enter your code here
df2['Price'].describe()
Out[181]:
count 413.000000 mean 18224.077482 std 25350.899384 min 479.000000 25% 1199.000000 50% 9999.000000 75% 24990.000000 max 140300.000000 Name: Price, dtype: float64
In [212]:
df = df[(df['Price'] > 0) & (df['Price'] <= 140300)]
Have you seen how messy the model names look? They contain extra details like color and RAM
specifications in parentheses, which don't belong in the Model_Name
column. To make the data clearer, we have to get rid of both the parentheses and their contents.
18) Enhance Model Names: Removing Parentheses and Their Contents¶
In [188]:
df['Model_Name']
Out[188]:
0 LG V30+ (Black, 128 ) 1 I Kall K11 2 Nokia 105 ss 3 Samsung Galaxy A50 (White, 64 ) 4 POCO F1 (Steel Blue, 128 ) ... 805 Lava 34 824 Nokia 3310 DS 825 Micax X1i-Pop 831 Karbonn K24 Plus Pro 833 Alcatel 5V (Spectrum Blue, 32 ) Name: Model_Name, Length: 413, dtype: object
In [213]:
# Enter your code here
df['Model_Name'] = df['Model_Name'].str.replace(r"\(.*\)", "", regex=True)
In [ ]: