<jupyter_start><jupyter_text>1. Checking Null Values<jupyter_code>df.isnull().sum()<jupyter_output><empty_output><jupyter_text>2. Adding Total Billing Amount Column 2.1) Removing Duplicate Rows with Uncertain values<jupyter_code>data = []

for i in df.values:
    if (i[2] != 'Quantity Ordered'):          # Taking only valid Data
        data.append(i)
        
data = pd.DataFrame(data, columns = ['order_id','product','quantity','price','date','address','month'])

data.head()<jupyter_output><empty_output><jupyter_text>2.2) Creating Total Billing amount Column<jupyter_code>total = []

for i in data.values:
    
    qn = i[2]
    pr = i[3]
    
    total.append(round(int(qn)*float(pr), 2))
    
data['total'] = total

data.head()<jupyter_output><empty_output><jupyter_text>3. Preprocessing Date 3.1) Adding Date and Time Column<jupyter_code>date = []
time = []

for i in data['date']:             # Month/Date/Year Hour:Minutes
    
    date.append(i.split(' ')[0])
    time.append(i.split(' ')[1])
    

data['date'] = date
data['time'] = time

data.head()<jupyter_output><empty_output><jupyter_text>3.2) Adding Hour Column<jupyter_code>hour = []

for i in data['time']:
    hour.append(int(i.split(':')[0]))       
    
data['hour'] = hour
data.head()<jupyter_output><empty_output><jupyter_text>3.3) Adding Date, Month and Year<jupyter_code>date  = []
month = []
year  = []

for i in data['date']:
    month.append(i.split('/')[0])
    date.append(i.split('/')[1])
    year.append(i.split('/')[2])
    
data['date_']  = date
data['month_'] = month
data['year_']  = year

data.head()<jupyter_output><empty_output><jupyter_text>3.4 ) Adding Day Column (Ex. Mon, Tue, Sat & Sun)<jupyter_code>day = []

for i in data['date']:
    
    date  = int(i.split('/')[1])
    month = int(i.split('/')[0])
    year  = int(i.split('/')[2])

    day.append(datetime.date(year,month,date).strftime('%a'))
    
data['day'] = day

data.head()<jupyter_output><empty_output><jupyter_text>4. Preprocessing Address 4.1) Address Walkthrough<jupyter_code>data['address']<jupyter_output><empty_output><jupyter_text>4.2) Adding PinCode Column<jupyter_code>pincode = []

for i in data['address']:
    pincode.append(i.split(' ')[-1])
    
data['pincode'] = pincode

data.head()<jupyter_output><empty_output><jupyter_text>4.3) Validate Pincode<jupyter_code>pincode = []

for i in data['pincode']:  
    
    if (len(i) == 5):
        pincode.append(int(i))
    
data['pincode'] = pincode

data.head()<jupyter_output><empty_output><jupyter_text>4.4) Adding States Column<jupyter_code>state = []

for i in data['address']:
    
    if len(i.split(' ')[-2]) == 2:
        state.append(i.split(' ')[-2])

data['state'] = state

data.head()<jupyter_output><empty_output><jupyter_text>4.5) Adding City Column<jupyter_code>city = []

for i in data['address']:
    
    city.append(i.split(',')[-2].strip())
    
data['city'] = city
    
data.head()<jupyter_output><empty_output><jupyter_text>4.6) Adding Updated Address<jupyter_code>address = []

for i in data['address']:
    
    address.append(i.split(',')[0].strip())
    
data['address'] = address

data.head()<jupyter_output><empty_output><jupyter_text>5. Final Preprocessed Dataset 5.1) Rearrange Columns in terms of Order<jupyter_code># data['order_id']
# data['product']
# data['quantity']
# data['price']
# data['total']
# data['date']     full_date
# data['hour']
# data['date_']    date, Typecast to Int
# data['month_']   Typecast to Int
# data['year_']    Add 20 in begining, Typecast to Int
# data['day']
# data['address']
# data['city']
# data['state']
# data['pincode']<jupyter_output><empty_output><jupyter_text>5.2) Changing date Column to full_date<jupyter_code>data['full_date'] = data['date']

del data['date']

data.head()<jupyter_output><empty_output><jupyter_text>5.3) Final Preprocessing Date Column<jupyter_code>date = []

for i in data['date_']:
    date.append(int(i))
    
data['date'] = date

data.head()<jupyter_output><empty_output><jupyter_text>5.4) Typecasting Month Column<jupyter_code>month = []

for i in data['month_']:
    
    month.append(int(i))
    
data['month'] = month

del data['month_']

data.head()<jupyter_output><empty_output><jupyter_text>5.5) Final Preprocessing of Year Column<jupyter_code>year = []

for i in data['year_']:
    
    year.append(int('20'+i))
    
data['year'] = year

del data['year_']

data.head()<jupyter_output><empty_output><jupyter_text>6. Saving Final Data<jupyter_code>df = pd.DataFrame()
df['order_id']  = data['order_id']
df['product']   = data['product']
df['quantity']  = data['quantity']
df['price']     = data['price']
df['total']     = data['total']
df['full_date'] = data['full_date']
df['hour']      = data['hour']
df['date']      = data['date']
df['month']     = data['month']
df['year']      = data['year']
df['day']       = data['day']
df['address']   = data['address']
df['city']      = data['city']
df['state']     = data['state']
df['pincode']   = data['pincode']


df.head()
df.isnull().sum()
df.to_csv('data.csv', index = False)<jupyter_output><empty_output><jupyter_text>Bonus | Adding Timezone<jupyter_code>timezone = []

for i in df['hour']:
    if (i <= 12):
        timezone.append('AM')
        
    else:
        timezone.append('PM')
        
df['timezone'] = timezone

df.head()
df.to_csv('data.csv', index = False)
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.19029017857142858, 'fraction_numerical': 0.012276785714285714, 'mean_word_length': 4.58939708939709, 'pattern_counts': {'":': 0, '<': 82, '<?xml version=': 0, '>': 81, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '575397', 'license': 'Apache-2.0', 'n_tokens_mistral': 2105, 'n_tokens_neox': 2079, 'n_words': 345, 'path': 'Week - 11/Data Preprocessing - Date/Sales Analysis - Dataset Preprocessing.ipynb', 'repo_name': 'AshishJangra27/Data-Science-Specialization', 'size': '5376'}