Class 3 Pandas

In [ ]:

# importing the libraries
import numpy as np
import pandas as pd

# importing the libraries import numpy as np import pandas as pd

In [ ]:

# In Colab, files are saved under /content and are deleted when the session ends.
# To keep them in your Google Drive instead, uncomment these lines:
# from google.colab import drive
# drive.mount('/content/drive')

# In Colab, files are saved under /content and are deleted when the session ends. # To keep them in your Google Drive instead, uncomment these lines: # from google.colab import drive # drive.mount('/content/drive')

Mounted at /content/drive

3.1 Pandas - Series and DataFrames¶

Pandas Series

• Pandas Series is a one-dimensional labeled array/list capable of holding data of any type (integer, string, float, python objects, etc.).
• The labels are collectively called index.
• Pandas Series can be thought as a single column of an excel spreadsheet and each entry in a series corresponds to an individual row in the spreadsheet.

In [ ]:

# creating a list of price of different medicines
med_price_list = [55,25,75,40,90]

# converting the med_price_list to an array
med_price_arr = np.array(med_price_list)

# converting the list and array into a Pandas Series object
series_list = pd.Series(med_price_list)
series_arr = pd.Series(med_price_arr)

# printing the converted series object
print(series_list)
print(series_arr)

# creating a list of price of different medicines med_price_list = [55,25,75,40,90] # converting the med_price_list to an array med_price_arr = np.array(med_price_list) # converting the list and array into a Pandas Series object series_list = pd.Series(med_price_list) series_arr = pd.Series(med_price_arr) # printing the converted series object print(series_list) print(series_arr)

0    55
1    25
2    75
3    40
4    90
dtype: int64
0    55
1    25
2    75
3    40
4    90
dtype: int64

• We can see that the list and array have been converted to a Pandas Series object.
• We also see that the series has automatically got index labels. Let's see how these can be modified.

In [ ]:

# changing the index of a series
med_price_list_labeled = pd.Series(med_price_list,
                                   index = ['Omeprazole','Azithromycin','Metformin','Ibuprofen','Cetirizine'])
print(med_price_list_labeled)

# changing the index of a series med_price_list_labeled = pd.Series(med_price_list, index = ['Omeprazole','Azithromycin','Metformin','Ibuprofen','Cetirizine']) print(med_price_list_labeled)

Omeprazole      55
Azithromycin    25
Metformin       75
Ibuprofen       40
Cetirizine      90
dtype: int64

Performing mathematical operations on Pandas Series

• The price of each medicine was increased by $2.5. Let's add this to the existing price.

In [ ]:

# adding 2.5 to existing prices
med_price_list_labeled_updated = med_price_list_labeled + 2.5
med_price_list_labeled_updated

# adding 2.5 to existing prices med_price_list_labeled_updated = med_price_list_labeled + 2.5 med_price_list_labeled_updated

Out[ ]:

Omeprazole      57.5
Azithromycin    27.5
Metformin       77.5
Ibuprofen       42.5
Cetirizine      92.5
dtype: float64

• A new price list was released by vendors for each medicine. Let's find the difference between new price and the old price

In [ ]:

new_price_list = [77, 45.5, 100, 50, 80]
new_price_list_labeled = pd.Series(new_price_list, index = ['Omeprazole','Azithromycin','Metformin','Ibuprofen','Cetirizine'])
print(new_price_list_labeled)

new_price_list = [77, 45.5, 100, 50, 80] new_price_list_labeled = pd.Series(new_price_list, index = ['Omeprazole','Azithromycin','Metformin','Ibuprofen','Cetirizine']) print(new_price_list_labeled)

Omeprazole       77.0
Azithromycin     45.5
Metformin       100.0
Ibuprofen        50.0
Cetirizine       80.0
dtype: float64

In [ ]:

print('Difference between new price and old price - ')
print(new_price_list_labeled - med_price_list_labeled_updated)

print('Difference between new price and old price - ') print(new_price_list_labeled - med_price_list_labeled_updated)

Difference between new price and old price - 
Omeprazole      19.5
Azithromycin    18.0
Metformin       22.5
Ibuprofen        7.5
Cetirizine     -12.5
dtype: float64

Mathematical operations in Pandas are performer "labelled wise"---Big adventage!

In [ ]:

new_price_list = [100, 50, 80, 77, 45.5]
new_price_list_labeled = pd.Series(new_price_list, index = ['Metformin','Ibuprofen','Cetirizine', 'Omeprazole','Azithromycin',])
print(new_price_list_labeled)

new_price_list = [100, 50, 80, 77, 45.5] new_price_list_labeled = pd.Series(new_price_list, index = ['Metformin','Ibuprofen','Cetirizine', 'Omeprazole','Azithromycin',]) print(new_price_list_labeled)

Metformin       100.0
Ibuprofen        50.0
Cetirizine       80.0
Omeprazole       77.0
Azithromycin     45.5
dtype: float64

In [ ]:

med_price_list_labeled_updated

med_price_list_labeled_updated

Out[ ]:

Omeprazole      57.5
Azithromycin    27.5
Metformin       77.5
Ibuprofen       42.5
Cetirizine      92.5
dtype: float64

In [ ]:

print('Difference between new price and old price - ')
print(new_price_list_labeled - med_price_list_labeled_updated)

print('Difference between new price and old price - ') print(new_price_list_labeled - med_price_list_labeled_updated)

Difference between new price and old price - 
Azithromycin    18.0
Cetirizine     -12.5
Ibuprofen        7.5
Metformin       22.5
Omeprazole      19.5
dtype: float64

Pandas DataFrame

Pandas DataFrame is a two-dimensional tabular data structure with labeled axes (rows and columns).

Creating a Pandas DataFrame using a list

In [ ]:

student = ['Mary', 'Peter', 'Susan', 'Toby', 'Vishal']
df1 = pd.DataFrame(student,columns=['Student'])
df1

student = ['Mary', 'Peter', 'Susan', 'Toby', 'Vishal'] df1 = pd.DataFrame(student,columns=['Student']) df1

Out[ ]:

	Student
0	Mary
1	Peter
2	Susan
3	Toby
4	Vishal

Creating a Pandas DataFrame using a dictionary

In [ ]:

# defining another list
grades = ['B-','A+','A-', 'B+', 'C']

# creating the dataframe using a dictionary
df2 = pd.DataFrame({'Student':student,'Grade':grades})
df2

# defining another list grades = ['B-','A+','A-', 'B+', 'C'] # creating the dataframe using a dictionary df2 = pd.DataFrame({'Student':student,'Grade':grades}) df2

Out[ ]:

	Student	Grade
0	Mary	B-
1	Peter	A+
2	Susan	A-
3	Toby	B+
4	Vishal	C

In [ ]:

type(df2)

type(df2)

Out[ ]:

pandas.core.frame.DataFrame

Creating a Pandas DataFrame using Series

The data for total energy consumption for the U.S. was collected from 2012 - 2018. Let's see how this data can be presented in form of data frame.

In [ ]:

year = pd.Series([2012,2013,2014,2015,2016,2017,2018])
energy_consumption = pd.Series([2152,2196,2217,2194,2172,2180,2258])

df3 = pd.DataFrame({'Year':year,'Energy_Consumption(Mtoe)':energy_consumption})
df3

year = pd.Series([2012,2013,2014,2015,2016,2017,2018]) energy_consumption = pd.Series([2152,2196,2217,2194,2172,2180,2258]) df3 = pd.DataFrame({'Year':year,'Energy_Consumption(Mtoe)':energy_consumption}) df3

Out[ ]:

	Year	Energy_Consumption(Mtoe)
0	2012	2152
1	2013	2196
2	2014	2217
3	2015	2194
4	2016	2172
5	2017	2180
6	2018	2258

Note that the lists do not need to be Pandas series:

In [ ]:

year = [2012,2013,2014,2015,2016,2017,2018]
energy_consumption = pd.Series([2152,2196,2217,2194,2172,2180,2258])

df3 = pd.DataFrame({'Year':year,'Energy_Consumption(Mtoe)':energy_consumption})
df3
type(df3)

year = [2012,2013,2014,2015,2016,2017,2018] energy_consumption = pd.Series([2152,2196,2217,2194,2172,2180,2258]) df3 = pd.DataFrame({'Year':year,'Energy_Consumption(Mtoe)':energy_consumption}) df3 type(df3)

Out[ ]:

pandas.core.frame.DataFrame

Creating a Pandas DataFrame using random values

For encryption purposes a web browser company wants to generate random values which have mean equal to 0 and variance equal to 1. They want 5 randomly generated numbers in 2 different trials.

In [ ]:

# we can create a new dataframe using random values
df4 = pd.DataFrame(np.random.randn(5,2),columns = ['Trial 1', 'Trial 2'])
df4

# we can create a new dataframe using random values df4 = pd.DataFrame(np.random.randn(5,2),columns = ['Trial 1', 'Trial 2']) df4

Out[ ]:

	Trial 1	Trial 2
0	0.149649	-1.688353
1	-0.895165	-1.162521
2	0.163333	-1.653325
3	-1.083162	-0.425456
4	-0.441486	-2.629662

3.2 Pandas - Accessing and Modifying¶

Accessing Series

The revenue (in billion dollars) of different telecommunication operators in U.S. was collected for the year of 2020. The following lists consist of the names of the telecommunication operators and their respective revenue (in billion dollars).

In [ ]:

operators = ['AT&T', 'Verizon', 'T-Mobile US', 'US Cellular']
revenue = [171.76, 128.29, 68.4, 4.04]

#creating a Series from lists
telecom = pd.Series(revenue, index=operators)
telecom

operators = ['AT&T', 'Verizon', 'T-Mobile US', 'US Cellular'] revenue = [171.76, 128.29, 68.4, 4.04] #creating a Series from lists telecom = pd.Series(revenue, index=operators) telecom

Out[ ]:

AT&T           171.76
Verizon        128.29
T-Mobile US     68.40
US Cellular      4.04
dtype: float64

Accessing Pandas Series using its index

In [ ]:

# accessing the first element of series
telecom[0]

# accessing the first element of series telecom[0]

Out[ ]:

171.76

In [ ]:

#  accessing firt 3 elements of a series
telecom[:3]

# accessing firt 3 elements of a series telecom[:3]

Out[ ]:

AT&T           171.76
Verizon        128.29
T-Mobile US     68.40
dtype: float64

In [ ]:

# accessing the last two elements of a series
telecom[-2:]

# accessing the last two elements of a series telecom[-2:]

Out[ ]:

T-Mobile US    68.40
US Cellular     4.04
dtype: float64

In [ ]:

telecom[-3:-1]

telecom[-3:-1]

Out[ ]:

Verizon        128.29
T-Mobile US     68.40
dtype: float64

In [ ]:

# accessing multiple elements of a series
telecom[[0,2,3]]

# accessing multiple elements of a series telecom[[0,2,3]]

Out[ ]:

AT&T           171.76
T-Mobile US     68.40
US Cellular      4.04
dtype: float64

Accessing Pandas Series using its labeled index

In [ ]:

# accessing the revenue of AT&T
telecom['AT&T']

# accessing the revenue of AT&T telecom['AT&T']

Out[ ]:

171.76

In [ ]:

#  accessing firt 3 revenues of operators in the series
telecom[:'T-Mobile US']

# accessing firt 3 revenues of operators in the series telecom[:'T-Mobile US']

Out[ ]:

AT&T           171.76
Verizon        128.29
T-Mobile US     68.40
dtype: float64

In [ ]:

# accessing multiple values
telecom[['AT&T','US Cellular','Verizon']]

# accessing multiple values telecom[['AT&T','US Cellular','Verizon']]

Out[ ]:

AT&T           171.76
US Cellular      4.04
Verizon        128.29
dtype: float64

Accessing DataFrames

The data of the customers visiting 24/7 Stores from different locations was collected. The data includes Customer ID, location of store, gender of the customer, type of product purchased, quantity of products purchased, total bill amount. Let's create the dataset and see how to access different entries of it.

In [ ]:

# creating the dataframe using dictionary
store_data = pd.DataFrame({'CustomerID': ['CustID00','CustID01','CustID02','CustID03','CustID04']
                           ,'location': ['Chicago', 'Boston', 'Seattle', 'San Francisco', 'Austin']
                           ,'gender': ['M','M','F','M','F']
                           ,'type': ['Electronics','Food&Beverages','Food&Beverages','Medicine','Beauty']
                           ,'quantity':[1,3,4,2,1],'total_bill':[100,75,125,50,80]})
store_data

# creating the dataframe using dictionary store_data = pd.DataFrame({'CustomerID': ['CustID00','CustID01','CustID02','CustID03','CustID04'] ,'location': ['Chicago', 'Boston', 'Seattle', 'San Francisco', 'Austin'] ,'gender': ['M','M','F','M','F'] ,'type': ['Electronics','Food&Beverages','Food&Beverages','Medicine','Beauty'] ,'quantity':[1,3,4,2,1],'total_bill':[100,75,125,50,80]}) store_data

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill
0	CustID00	Chicago	M	Electronics	1	100
1	CustID01	Boston	M	Food&Beverages	3	75
2	CustID02	Seattle	F	Food&Beverages	4	125
3	CustID03	San Francisco	M	Medicine	2	50
4	CustID04	Austin	F	Beauty	1	80

In [ ]:

# accessing first row of the dataframe
store_data[:1]

# accessing first row of the dataframe store_data[:1]

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill
0	CustID00	Chicago	M	Electronics	1	100

In [ ]:

# accessing first column of the dataframe
store_data['location']

# accessing first column of the dataframe store_data['location']

Out[ ]:

0          Chicago
1           Boston
2          Seattle
3    San Francisco
4           Austin
Name: location, dtype: object

In [ ]:

store_data.location

store_data.location

Out[ ]:

0          Chicago
1           Boston
2          Seattle
3    San Francisco
4           Austin
Name: location, dtype: object

In [ ]:

# accessing rows with the step size of 2
store_data[::2]

# accessing rows with the step size of 2 store_data[::2]

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill
0	CustID00	Chicago	M	Electronics	1	100
2	CustID02	Seattle	F	Food&Beverages	4	125
4	CustID04	Austin	F	Beauty	1	80

In [ ]:

# accessing the rows in reverse
store_data[::-2]

# accessing the rows in reverse store_data[::-2]

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill
4	CustID04	Austin	F	Beauty	1	80
2	CustID02	Seattle	F	Food&Beverages	4	125
0	CustID00	Chicago	M	Electronics	1	100

In [ ]:

days = pd.Series([420, 380, 390], index=['day1', 'day2', 'day3'])
days[:'day3']

days = pd.Series([420, 380, 390], index=['day1', 'day2', 'day3']) days[:'day3']

Out[ ]:

day1    420
day2    380
day3    390
dtype: int64

Using loc and iloc method

loc method

• loc is a method to access rows and columns on pandas objects. When using the loc method on a dataframe, we specify which rows and which columns we want by using the following format:

  • dataframe.loc[row selection, column selection]

• DataFrame.loc[] method is a method that takes only index labels and returns row or dataframe if the index label exists in the data frame.

In [ ]:

print(store_data)

print(store_data)

  CustomerID       location gender            type  quantity  total_bill
0   CustID00        Chicago      M     Electronics         1         100
1   CustID01         Boston      M  Food&Beverages         3          75
2   CustID02        Seattle      F  Food&Beverages         4         125
3   CustID03  San Francisco      M        Medicine         2          50
4   CustID04         Austin      F          Beauty         1          80

In [ ]:

# accessing first index value using loc method (indexing starts from 0 in python)
store_data.loc[1]

# accessing first index value using loc method (indexing starts from 0 in python) store_data.loc[1]

Out[ ]:

CustomerID          CustID01
location              Boston
gender                     M
type          Food&Beverages
quantity                   3
total_bill                75
Name: 1, dtype: object

Accessing selected rows and columns using loc method

In [ ]:

# accessing 1st and 4th index values along with location and type columns
store_data.loc[[1,4],['location','type']]

# accessing 1st and 4th index values along with location and type columns store_data.loc[[1,4],['location','type']]

Out[ ]:

	location	type
1	Boston	Food&Beverages
4	Austin	Beauty

iloc method

• The iloc indexer for Pandas Dataframe is used for integer location-based indexing/selection by position. When using the loc method on a dataframe, we specify which rows and which columns we want by using the following format:

  • dataframe.iloc[row selection, column selection]

In [ ]:

# accessing selected rows and columns using iloc method
store_data.iloc[[1,4],[0,2]]

# accessing selected rows and columns using iloc method store_data.iloc[[1,4],[0,2]]

Out[ ]:

	CustomerID	gender
1	CustID01	M
4	CustID04	F

Difference between loc and iloc indexing methods

• loc is label-based, which means that you have to specify rows and columns based on their row and column labels.
• iloc is integer position-based, so you have to specify rows and columns by their integer position values (0-based integer position).

If we use labels instead of index values in .iloc it will throw an error.

In [ ]:

# accessing selected rows and columns using iloc method
store_data.iloc[[1,4],['location','type']]

# accessing selected rows and columns using iloc method store_data.iloc[[1,4],['location','type']]

---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
<ipython-input-35-a6ec8a89badc> in <cell line: 2>()
      1 # accessing selected rows and columns using iloc method
----> 2 store_data.iloc[[1,4],['location','type']]

/usr/local/lib/python3.10/dist-packages/pandas/core/indexing.py in __getitem__(self, key)
   1065             if self._is_scalar_access(key):
   1066                 return self.obj._get_value(*key, takeable=self._takeable)
-> 1067             return self._getitem_tuple(key)
   1068         else:
   1069             # we by definition only have the 0th axis

/usr/local/lib/python3.10/dist-packages/pandas/core/indexing.py in _getitem_tuple(self, tup)
   1561     def _getitem_tuple(self, tup: tuple):
   1562 
-> 1563         tup = self._validate_tuple_indexer(tup)
   1564         with suppress(IndexingError):
   1565             return self._getitem_lowerdim(tup)

/usr/local/lib/python3.10/dist-packages/pandas/core/indexing.py in _validate_tuple_indexer(self, key)
    871         for i, k in enumerate(key):
    872             try:
--> 873                 self._validate_key(k, i)
    874             except ValueError as err:
    875                 raise ValueError(

/usr/local/lib/python3.10/dist-packages/pandas/core/indexing.py in _validate_key(self, key, axis)
   1475             # check that the key has a numeric dtype
   1476             if not is_numeric_dtype(arr.dtype):
-> 1477                 raise IndexError(f".iloc requires numeric indexers, got {arr}")
   1478 
   1479             # check that the key does not exceed the maximum size of the index

IndexError: .iloc requires numeric indexers, got ['location' 'type']

• As expected, .iloc has given error on using 'labels'.

We can modify entries of a dataframe using loc or iloc too

In [ ]:

print(store_data.loc[4,'type'])
store_data.loc[4,'type'] = 'Electronics'

print(store_data.loc[4,'type']) store_data.loc[4,'type'] = 'Electronics'

Beauty

In [ ]:

store_data

store_data

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill
0	CustID00	Chicago	M	Electronics	1	100
1	CustID01	Boston	M	Food&Beverages	3	75
2	CustID02	Seattle	F	Food&Beverages	4	125
3	CustID03	San Francisco	M	Medicine	2	50
4	CustID04	Austin	F	Electronics	1	80

In [ ]:

store_data.iloc[4,3] = 'Beauty'
store_data

store_data.iloc[4,3] = 'Beauty' store_data

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill
0	CustID00	Chicago	M	Electronics	1	100
1	CustID01	Boston	M	Food&Beverages	3	75
2	CustID02	Seattle	F	Food&Beverages	4	125
3	CustID03	San Francisco	M	Medicine	2	50
4	CustID04	Austin	F	Beauty	1	80

Condition based indexing

In [ ]:

store_data['quantity']>1

store_data['quantity']>1

Out[ ]:

0    False
1     True
2     True
3     True
4    False
Name: quantity, dtype: bool

In [ ]:

#####HELP

#####HELP

In [ ]:

store_data.iloc[[1,2],[1,2,3,4]]

store_data.iloc[[1,2],[1,2,3,4]]

Out[ ]:

	location	gender	type	quantity
1	Boston	M	Food&Beverages	3
2	Seattle	F	Food&Beverages	4

• Wherever the condition of greater than 1 is satisfied in quantity column, 'True' is returned. Let's retrieve the original values wherever the condition is satisfied.

This should also work:

In [ ]:

store_data.iloc[[1:3,1:5]

store_data.iloc[[1:3,1:5]

In [ ]:

store_data.loc[store_data['quantity']>1]

store_data.loc[store_data['quantity']>1]

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill
1	CustID01	Boston	M	Food&Beverages	3	75
2	CustID02	Seattle	F	Food&Beverages	4	125
3	CustID03	San Francisco	M	Medicine	2	50

• Wherever the condition is satisfied we get the original values, and wherever the condition is not satisfied we do not get those records in the output.

Column addition and removal from a Pandas DataFrame

Adding a new column in a DataFrame

In [ ]:

store_data

store_data

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill
0	CustID00	Chicago	M	Electronics	1	100
1	CustID01	Boston	M	Food&Beverages	3	75
2	CustID02	Seattle	F	Food&Beverages	4	125
3	CustID03	San Francisco	M	Medicine	2	50
4	CustID04	Austin	F	Beauty	1	80

In [ ]:

# adding a new column in data frame store_data which is a rating (out of 5) given by customer based on their shopping experience
store_data['rating'] = [2,5,3,4,4]
store_data

# adding a new column in data frame store_data which is a rating (out of 5) given by customer based on their shopping experience store_data['rating'] = [2,5,3,4,4] store_data

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill	rating
0	CustID00	Chicago	M	Electronics	1	100	2
1	CustID01	Boston	M	Food&Beverages	3	75	5
2	CustID02	Seattle	F	Food&Beverages	4	125	3
3	CustID03	San Francisco	M	Medicine	2	50	4
4	CustID04	Austin	F	Beauty	1	80	4

Removing a column from a DataFrame

• The CustomerID column is a unique identifier of each customer. This unique identifier will not help 24/7 Stores in getting useful insights about their customers. So, they have decided to remove this column from the data frame.

In [ ]:

store_data.drop('CustomerID',axis=1)

store_data.drop('CustomerID',axis=1)

Out[ ]:

	location	gender	type	quantity	total_bill	rating
0	Chicago	M	Electronics	1	100	2
1	Boston	M	Food&Beverages	3	75	5
2	Seattle	F	Food&Beverages	4	125	3
3	San Francisco	M	Medicine	2	50	4
4	Austin	F	Beauty	1	80	4

• We sucessfully removed the 'CustomerID' from dataframe. But this change is not permanent in the dataframe, let's have a look at the store_data again.

In [ ]:

store_data

store_data

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill	rating
0	CustID00	Chicago	M	Electronics	1	100	2
1	CustID01	Boston	M	Food&Beverages	3	75	5
2	CustID02	Seattle	F	Food&Beverages	4	125	3
3	CustID03	San Francisco	M	Medicine	2	50	4
4	CustID04	Austin	F	Beauty	1	80	4

• We see that store_data still has column 'CustomerID' in it.
• To make permanent changes to a dataframe there are two methods will have to use a parameter inplace and set its value to True.

In [ ]:

store_data.drop('CustomerID',axis=1,inplace=True)
store_data

store_data.drop('CustomerID',axis=1,inplace=True) store_data

Out[ ]:

	location	gender	type	quantity	total_bill	rating
0	Chicago	M	Electronics	1	100	2
1	Boston	M	Food&Beverages	3	75	5
2	Seattle	F	Food&Beverages	4	125	3
3	San Francisco	M	Medicine	2	50	4
4	Austin	F	Beauty	1	80	4

• Now the column has been permanently removed from the dataframe.

Or, we can redefine the name of the object:

In [ ]:

store_data = pd.DataFrame({'CustomerID': ['CustID00','CustID01','CustID02','CustID03','CustID04']
                           ,'location': ['Chicago', 'Boston', 'Seattle', 'San Francisco', 'Austin']
                           ,'gender': ['M','M','F','M','F']
                           ,'type': ['Electronics','Food&Beverages','Food&Beverages','Medicine','Beauty']
                           ,'quantity':[1,3,4,2,1],'total_bill':[100,75,125,50,80]})
store_data

store_data = pd.DataFrame({'CustomerID': ['CustID00','CustID01','CustID02','CustID03','CustID04'] ,'location': ['Chicago', 'Boston', 'Seattle', 'San Francisco', 'Austin'] ,'gender': ['M','M','F','M','F'] ,'type': ['Electronics','Food&Beverages','Food&Beverages','Medicine','Beauty'] ,'quantity':[1,3,4,2,1],'total_bill':[100,75,125,50,80]}) store_data

Out[ ]:

	CustomerID	location	gender	type	quantity	total_bill
0	CustID00	Chicago	M	Electronics	1	100
1	CustID01	Boston	M	Food&Beverages	3	75
2	CustID02	Seattle	F	Food&Beverages	4	125
3	CustID03	San Francisco	M	Medicine	2	50
4	CustID04	Austin	F	Beauty	1	80

In [ ]:

store_data = store_data.drop('CustomerID',axis=1)
store_data

store_data = store_data.drop('CustomerID',axis=1) store_data

Out[ ]:

	location	gender	type	quantity	total_bill
0	Chicago	M	Electronics	1	100
1	Boston	M	Food&Beverages	3	75
2	Seattle	F	Food&Beverages	4	125
3	San Francisco	M	Medicine	2	50
4	Austin	F	Beauty	1	80

In [ ]:

# we can also remove multiple columns simultaneously
# it is always a good idea to store the new/updated data frames in new variables to avoid changes to the existing data frame

# creating a copy of the existing data frame
new_store_data = store_data.copy
store_data

# we can also remove multiple columns simultaneously # it is always a good idea to store the new/updated data frames in new variables to avoid changes to the existing data frame # creating a copy of the existing data frame new_store_data = store_data.copy store_data

Out[ ]:

	location	gender	type	quantity	total_bill
0	Chicago	M	Electronics	1	100
1	Boston	M	Food&Beverages	3	75
2	Seattle	F	Food&Beverages	4	125
3	San Francisco	M	Medicine	2	50
4	Austin	F	Beauty	1	80

In [ ]:

print(new_store_data)

print(new_store_data)

<bound method NDFrame.copy of         location gender            type  quantity  total_bill
0        Chicago      M     Electronics         1         100
1         Boston      M  Food&Beverages         3          75
2        Seattle      F  Food&Beverages         4         125
3  San Francisco      M        Medicine         2          50
4         Austin      F          Beauty         1          80>

In [ ]:

# dropping location and rating columns simultaneously
new_store_data.drop(["location","gender"],axis=1,inplace=True)
new_store_data

# dropping location and rating columns simultaneously new_store_data.drop(["location","gender"],axis=1,inplace=True) new_store_data

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-50-228522ca4bb9> in <cell line: 2>()
      1 # dropping location and rating columns simultaneously
----> 2 new_store_data.drop(["location","gender"],axis=1,inplace=True)
      3 new_store_data

AttributeError: 'function' object has no attribute 'drop'

In [ ]:

# lets check if store_data was impacted
store_data

# lets check if store_data was impacted store_data

Out[ ]:

	location	gender	type	quantity	total_bill
0	Chicago	M	Electronics	1	100
1	Boston	M	Food&Beverages	3	75
2	Seattle	F	Food&Beverages	4	125
3	San Francisco	M	Medicine	2	50
4	Austin	F	Beauty	1	80

• There were no changes to data frame store_data.

• Deep copy stores copies of the object’s value.
• Shallow Copy stores the references of objects to the original memory address.

Removing rows from a dataframe

In [ ]:

store_data.drop(1,axis=0)

store_data.drop(1,axis=0)

Out[ ]:

	location	gender	type	quantity	total_bill
0	Chicago	M	Electronics	1	100
2	Seattle	F	Food&Beverages	4	125
3	San Francisco	M	Medicine	2	50
4	Austin	F	Beauty	1	80

In [ ]:

store_data

store_data

Out[ ]:

	location	gender	type	quantity	total_bill
0	Chicago	M	Electronics	1	100
1	Boston	M	Food&Beverages	3	75
2	Seattle	F	Food&Beverages	4	125
3	San Francisco	M	Medicine	2	50
4	Austin	F	Beauty	1	80

• Notice that we used axis=0 to drop a row from a data frame, while we were using axis=1 for dropping a column from the data frame.
• Also, to make permanent changes to the data frame we will have to use inplace=True parameter.
• We also see that the index are not correct now as first row has been removed. So, we will have to reset the index of the data frame. Let's see how this can be done.

In [ ]:

# creating a new dataframe
store_data_new  = store_data.drop(1,axis=0)
store_data_new

# creating a new dataframe store_data_new = store_data.drop(1,axis=0) store_data_new

Out[ ]:

	location	gender	type	quantity	total_bill
0	Chicago	M	Electronics	1	100
2	Seattle	F	Food&Beverages	4	125
3	San Francisco	M	Medicine	2	50
4	Austin	F	Beauty	1	80

In [ ]:

# resetting the index of data frame
store_data_new.reset_index()

# resetting the index of data frame store_data_new.reset_index()

Out[ ]:

	index	location	gender	type	quantity	total_bill
0	0	Chicago	M	Electronics	1	100
1	2	Seattle	F	Food&Beverages	4	125
2	3	San Francisco	M	Medicine	2	50
3	4	Austin	F	Beauty	1	80

• We see that the index of the data frame is now resetted but the index has become a column in the data frame. We do not need the index to become a column so we can simply set the parameter drop=True in reset_index() function.

In [ ]:

# setting inplace = True to make the changes permanent
store_data_new.reset_index(drop=True,inplace=True)
store_data_new

# setting inplace = True to make the changes permanent store_data_new.reset_index(drop=True,inplace=True) store_data_new

Out[ ]:

	location	gender	type	quantity	total_bill
0	Chicago	M	Electronics	1	100
1	Seattle	F	Food&Beverages	4	125
2	San Francisco	M	Medicine	2	50
3	Austin	F	Beauty	1	80

3.3 Pandas - Combining DataFrames¶

We will examine 3 methods for combining dataframes

1. concat
2. join
3. merge

In [ ]:

data_cust = pd.DataFrame({"customerID":['101','102','103','104'],
                        'category': ['Medium','Medium','High','Low'],
                        'first_visit': ['yes','no','yes','yes'],
                        'sales': [123,52,214,663]},index=[0,1,2,3])

data_cust_new = pd.DataFrame({"customerID":['101','103','104','105'],
                    'distance': [12,9,44,21],
                    'sales': [123,214,663,331]},index=[4,5,6,7])

data_cust = pd.DataFrame({"customerID":['101','102','103','104'], 'category': ['Medium','Medium','High','Low'], 'first_visit': ['yes','no','yes','yes'], 'sales': [123,52,214,663]},index=[0,1,2,3]) data_cust_new = pd.DataFrame({"customerID":['101','103','104','105'], 'distance': [12,9,44,21], 'sales': [123,214,663,331]},index=[4,5,6,7])

In [ ]:

data_cust

data_cust

Out[ ]:

	customerID	category	first_visit	sales
0	101	Medium	yes	123
1	102	Medium	no	52
2	103	High	yes	214
3	104	Low	yes	663

In [ ]:

data_cust_new

data_cust_new

Out[ ]:

	customerID	distance	sales
4	101	12	123
5	103	9	214
6	104	44	663
7	105	21	331

CONCATANTION

In [ ]:

pd.concat([data_cust,data_cust_new],axis=0)

pd.concat([data_cust,data_cust_new],axis=0)

Out[ ]:

	customerID	category	first_visit	sales	distance
0	101	Medium	yes	123	NaN
1	102	Medium	no	52	NaN
2	103	High	yes	214	NaN
3	104	Low	yes	663	NaN
4	101	NaN	NaN	123	12.0
5	103	NaN	NaN	214	9.0
6	104	NaN	NaN	663	44.0
7	105	NaN	NaN	331	21.0

In [ ]:

pd.concat([data_cust,data_cust_new],axis=1)

pd.concat([data_cust,data_cust_new],axis=1)

Out[ ]:

	customerID	category	first_visit	sales	customerID	distance	sales
0	101	Medium	yes	123.0	NaN	NaN	NaN
1	102	Medium	no	52.0	NaN	NaN	NaN
2	103	High	yes	214.0	NaN	NaN	NaN
3	104	Low	yes	663.0	NaN	NaN	NaN
4	NaN	NaN	NaN	NaN	101	12.0	123.0
5	NaN	NaN	NaN	NaN	103	9.0	214.0
6	NaN	NaN	NaN	NaN	104	44.0	663.0
7	NaN	NaN	NaN	NaN	105	21.0	331.0

Merge and Join

• Merge combines dataframes using a column's values to identify common entries

• Join combines dataframes using the index to identify common entries

Merge

In [ ]:

pd.merge(data_cust,data_cust_new,how='outer',on='customerID') # outer merge is union of on

pd.merge(data_cust,data_cust_new,how='outer',on='customerID') # outer merge is union of on

Out[ ]:

	customerID	category	first_visit	sales_x	distance	sales_y
0	101	Medium	yes	123.0	12.0	123.0
1	102	Medium	no	52.0	NaN	NaN
2	103	High	yes	214.0	9.0	214.0
3	104	Low	yes	663.0	44.0	663.0
4	105	NaN	NaN	NaN	21.0	331.0

In [ ]:

pd.merge(data_cust,data_cust_new,how='inner',on='customerID') # inner merge is intersection of on

pd.merge(data_cust,data_cust_new,how='inner',on='customerID') # inner merge is intersection of on

Out[ ]:

	customerID	category	first_visit	sales_x	distance	sales_y
0	101	Medium	yes	123	12	123
1	103	High	yes	214	9	214
2	104	Low	yes	663	44	663

In [ ]:

pd.merge(data_cust,data_cust_new,how='right',on='customerID')

pd.merge(data_cust,data_cust_new,how='right',on='customerID')

Out[ ]:

	customerID	category	first_visit	sales_x	distance	sales_y
0	101	Medium	yes	123.0	12	123
1	103	High	yes	214.0	9	214
2	104	Low	yes	663.0	44	663
3	105	NaN	NaN	NaN	21	331

Join

In [ ]:

data_quarters = pd.DataFrame({'Q1': [101,102,103],
                              'Q2': [201,202,203]},
                               index=['I0','I1','I2'])

data_quarters_new = pd.DataFrame({'Q3': [301,302,303],
                                  'Q4': [401,402,403]},
                               index=['I0','I2','I3'])

data_quarters = pd.DataFrame({'Q1': [101,102,103], 'Q2': [201,202,203]}, index=['I0','I1','I2']) data_quarters_new = pd.DataFrame({'Q3': [301,302,303], 'Q4': [401,402,403]}, index=['I0','I2','I3'])

In [ ]:

data_quarters

data_quarters

Out[ ]:

	Q1	Q2
I0	101	201
I1	102	202
I2	103	203

In [ ]:

data_quarters_new

data_quarters_new

Out[ ]:

	Q3	Q4
I0	301	401
I2	302	402
I3	303	403

• join behaves just like merge, except instead of using the values of one of the columns to combine data frames, it uses the index labels

In [ ]:

data_quarters.join(data_quarters_new,how='right') # outer, inner, left, and right work the same as merge

data_quarters.join(data_quarters_new,how='right') # outer, inner, left, and right work the same as merge

Out[ ]:

	Q1	Q2	Q3	Q4
I0	101.0	201.0	301	401
I2	103.0	203.0	302	402
I3	NaN	NaN	303	403

3.4 Pandas - Saving and Loading DataFrames¶

Note

In real-life scenario, we deal with much larger datasets that have thousands of rows and multiple columns. It will not be feasible for us to create datasets using multiple lists, especially if the number of columns and rows increases.

So, it is clear we need a more efficient way of handling the data simultaneously at the columns and row levels. In Python, we can import dataset from our local system, from links, or from databases and work on them directly instead of creating our own dataset.

Loading a CSV file in Python

For Jupyter Notebook

• When the data file and jupyter notebook are in the same folder.

In [ ]:

# Using pd.read_csv() function will work without any path if the notebook and dataset are in the folder

# data = pd.read_csv('StockData.csv')

# Using pd.read_csv() function will work without any path if the notebook and dataset are in the folder # data = pd.read_csv('StockData.csv')

For Google Colab: read the file from a URL

The simplest way in Colab is to read the file straight from the internet with its URL. You don't need to upload anything or connect your Drive.

In [ ]:

# In Colab, files are saved under /content and are deleted when the session ends.
# To keep them in your Google Drive instead, uncomment these lines:
# from google.colab import drive
# drive.mount('/content/drive')

# In Colab, files are saved under /content and are deleted when the session ends. # To keep them in your Google Drive instead, uncomment these lines: # from google.colab import drive # drive.mount('/content/drive')

Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount("/content/drive", force_remount=True).

read_csv() works the same with a URL as with a local file:

In [ ]:

path = "https://raw.githubusercontent.com/hizocar/usm-course-intensivo/main/docs/datasets/StockData.csv"
data = pd.read_csv(path)

path = "https://raw.githubusercontent.com/hizocar/usm-course-intensivo/main/docs/datasets/StockData.csv" data = pd.read_csv(path)

In [ ]:

# head() function helps us to see the first 5 rows of the data
data.head()

# head() function helps us to see the first 5 rows of the data data.head()

Out[ ]:

	stock	date	price
0	AAPL	08-02-2013	67.8542
1	AAPL	11-02-2013	68.5614
2	AAPL	12-02-2013	66.8428
3	AAPL	13-02-2013	66.7156
4	AAPL	14-02-2013	66.6556

Loading an excel file in Python

In [ ]:

path_excel = "https://raw.githubusercontent.com/hizocar/usm-course-intensivo/main/docs/datasets/StockData.xlsx"
data_excel = pd.read_excel(path_excel)

path_excel = "https://raw.githubusercontent.com/hizocar/usm-course-intensivo/main/docs/datasets/StockData.xlsx" data_excel = pd.read_excel(path_excel)

In [ ]:

data_excel.head()

data_excel.head()

Out[ ]:

	stock	date	price
0	AAPL	2013-02-08	67.8542
1	AAPL	2013-02-11	68.5614
2	AAPL	2013-02-12	66.8428
3	AAPL	2013-02-13	66.7156
4	AAPL	2013-02-14	66.6556

Saving a dataset in Python

Saving the dataset as a csv file

To save a dataset as .csv file the syntax used is -

data.to_csv('name of the file.csv', index=False)

In [ ]:

data.to_csv('Saved_StockData.csv', index=False)

data.to_csv('Saved_StockData.csv', index=False)

• In jupyter notebook, the dataset will be saved in the folder where the jupyter notebook is located.
• We can also save the dataset to a desired folder by providing the path/location of the folder.

Saving the dataset as an excel spreadsheet

To save a dataset as .xlsx file the syntax used is -

data.to_excel('name of the file.xlsx',index=False)

In [ ]:

data.to_excel('Saved_StockData.xlsx', index=False)

data.to_excel('Saved_StockData.xlsx', index=False)

3.5 Pandas - Functions¶

head() - to check the first 5 rows of the dataset

In [ ]:

data.head()

data.head()

Out[ ]:

	stock	date	price
0	AAPL	08-02-2013	67.8542
1	AAPL	11-02-2013	68.5614
2	AAPL	12-02-2013	66.8428
3	AAPL	13-02-2013	66.7156
4	AAPL	14-02-2013	66.6556

In [ ]:

data.head(10)

data.head(10)

Out[ ]:

	stock	date	price
0	AAPL	08-02-2013	67.8542
1	AAPL	11-02-2013	68.5614
2	AAPL	12-02-2013	66.8428
3	AAPL	13-02-2013	66.7156
4	AAPL	14-02-2013	66.6556
5	AAPL	15-02-2013	65.7371
6	AAPL	19-02-2013	65.7128
7	AAPL	20-02-2013	64.1214
8	AAPL	21-02-2013	63.7228
9	AAPL	22-02-2013	64.4014

tail() - to check the last 5 rows of the dataset

In [ ]:

data.tail()

data.tail()

Out[ ]:

	stock	date	price
5031	ZTS	01-02-2018	77.82
5032	ZTS	02-02-2018	76.78
5033	ZTS	05-02-2018	73.83
5034	ZTS	06-02-2018	73.27
5035	ZTS	07-02-2018	73.86

shape - to check the number of rows and columns in the dataset

In [ ]:

data.shape   # This is not a function but a method. It does not have () after. It is an attribute to the data frame

data.shape # This is not a function but a method. It does not have () after. It is an attribute to the data frame

Out[ ]:

(5036, 3)

• The dataset has 5036 rows and 3 columns.

info() - to check the data type of the columns

In [ ]:

data.info()

data.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5036 entries, 0 to 5035
Data columns (total 3 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   stock   5036 non-null   object 
 1   date    5036 non-null   object 
 2   price   5036 non-null   float64
dtypes: float64(1), object(2)
memory usage: 118.2+ KB

• The price column is numeric in nature while the stock and date columns are of object types.

Or, we can use the following:

In [ ]:

data.dtypes

data.dtypes

Out[ ]:

stock     object
date      object
price    float64
dtype: object

min() - to check the minimum value of a numeric column

In [ ]:

data['price'].min()

data['price'].min()

Out[ ]:

28.4

max() - to check the maximum value of a numeric column

In [ ]:

data['price'].max()

data['price'].max()

Out[ ]:

179.26

unique() - to check the number of unique values that are present in a column

In [ ]:

data['stock'].unique()

data['stock'].unique()

Out[ ]:

array(['AAPL', 'SNI', 'TJX', 'ZTS'], dtype=object)

value_counts() - to check the number of values that each unique quantity has in a column

In [ ]:

data['stock'].value_counts()

data['stock'].value_counts()

Out[ ]:

AAPL    1259
SNI     1259
TJX     1259
ZTS     1259
Name: stock, dtype: int64

value_counts(normalize=True) - using the normalize parameter and initializing it to True will return the relative frequencies of the unique values.

In [ ]:

data['stock'].value_counts(normalize=True)

data['stock'].value_counts(normalize=True)

Out[ ]:

AAPL    0.25
SNI     0.25
TJX     0.25
ZTS     0.25
Name: stock, dtype: float64

Statistical Functions

In [ ]:

data.head()

data.head()

Out[ ]:

	stock	date	price
0	AAPL	08-02-2013	67.8542
1	AAPL	11-02-2013	68.5614
2	AAPL	12-02-2013	66.8428
3	AAPL	13-02-2013	66.7156
4	AAPL	14-02-2013	66.6556

In [ ]:

data["price"].head()

data["price"].head()

Out[ ]:

0    67.8542
1    68.5614
2    66.8428
3    66.7156
4    66.6556
Name: price, dtype: float64

mean() - to check the mean (average) value of the column

In [ ]:

data['price'].mean()

data['price'].mean()

Out[ ]:

73.05702966640192

median() - to check the median value of the column

In [ ]:

data['price'].median()

data['price'].median()

Out[ ]:

69.08500000000001

mode() - to check the mode value of the column

In [ ]:

data['stock'].mode()

data['stock'].mode()

Out[ ]:

0    AAPL
1     SNI
2     TJX
3     ZTS
Name: stock, dtype: object

In [ ]:

data['price'].mode()

data['price'].mode()

Out[ ]:

0    74.59
Name: price, dtype: float64

To access a particular mode when the dataset has more than 1 mode

In [ ]:

#to access the first mode
data['price'].mode()[0]

#to access the first mode data['price'].mode()[0]

Out[ ]:

74.59

In [ ]:

data.describe()

data.describe()

Out[ ]:

	price
count	5036.000000
mean	73.057030
std	29.011126
min	28.400000
25%	54.067500
50%	69.085000
75%	79.250000
max	179.260000

Group By function

• Pandas dataframe.groupby() function is used to split the data into groups based on some criteria.

In [ ]:

data.groupby(['stock'])['price'].mean()

data.groupby(['stock'])['price'].mean()

Out[ ]:

stock
AAPL    109.066698
SNI      71.319206
TJX      66.743566
ZTS      45.098648
Name: price, dtype: float64

• Here the groupby function is used to split the data into the 4 stocks that are present in the dataset and then the mean price of each of the 4 stock is calculated.

In [ ]:

# similarly we can get the median price of each stock
data.groupby(['stock'])['price'].median()

# similarly we can get the median price of each stock data.groupby(['stock'])['price'].median()

Out[ ]:

stock
AAPL    109.01
SNI      72.31
TJX      68.85
ZTS      45.62
Name: price, dtype: float64

• Here the groupby function is used to split the data into the 4 stocks that are present in the dataset and then the median price of each of the 4 stock is calculated.

Let's create a function to increase the price of the stock by 10%

In [ ]:

def profit(s):
    return s + s*0.10 # increase of 10%

def profit(s): return s + s*0.10 # increase of 10%

The Pandas apply() function lets you to manipulate columns and rows in a DataFrame.

In [ ]:

data['price'].apply(profit)

data['price'].apply(profit)

Out[ ]:

0       74.63962
1       75.41754
2       73.52708
3       73.38716
4       73.32116
          ...   
5031    85.60200
5032    84.45800
5033    81.21300
5034    80.59700
5035    81.24600
Name: price, Length: 5036, dtype: float64

• We can now add this updated values in the dataset.

In [ ]:

data['new_price'] =data['price'].apply(profit)
data.head()

data['new_price'] =data['price'].apply(profit) data.head()

Out[ ]:

	stock	date	price	new_price
0	AAPL	08-02-2013	67.8542	74.63962
1	AAPL	11-02-2013	68.5614	75.41754
2	AAPL	12-02-2013	66.8428	73.52708
3	AAPL	13-02-2013	66.7156	73.38716
4	AAPL	14-02-2013	66.6556	73.32116

Pandas sort_values() function sorts a data frame in ascending or descending order of passed column.

In [ ]:

data.sort_values(by='new_price',ascending=False) # by default ascending is set to True

data.sort_values(by='new_price',ascending=False) # by default ascending is set to True

Out[ ]:

	stock	date	price	new_price
1244	AAPL	18-01-2018	179.26	197.186
1243	AAPL	17-01-2018	179.10	197.010
1245	AAPL	19-01-2018	178.46	196.306
1241	AAPL	12-01-2018	177.09	194.799
1247	AAPL	23-01-2018	177.04	194.744
...	...	...	...	...
4076	ZTS	17-04-2014	28.60	31.460
4074	ZTS	15-04-2014	28.55	31.405
4075	ZTS	16-04-2014	28.53	31.383
4073	ZTS	14-04-2014	28.48	31.328
4072	ZTS	11-04-2014	28.40	31.240

5036 rows × 4 columns

3.6 Pandas - Date-time Functions¶

In [ ]:

# reading the StockData
path = "https://raw.githubusercontent.com/hizocar/usm-course-intensivo/main/docs/datasets/StockData.csv"
data = pd.read_csv(path)

# reading the StockData path = "https://raw.githubusercontent.com/hizocar/usm-course-intensivo/main/docs/datasets/StockData.csv" data = pd.read_csv(path)

In [ ]:

# checking the first 5 rows of the dataset
data.head()

# checking the first 5 rows of the dataset data.head()

Out[ ]:

	stock	date	price
0	AAPL	08-02-2013	67.8542
1	AAPL	11-02-2013	68.5614
2	AAPL	12-02-2013	66.8428
3	AAPL	13-02-2013	66.7156
4	AAPL	14-02-2013	66.6556

In [ ]:

# checking the data type of columns in the dataset
data.info()

# checking the data type of columns in the dataset data.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5036 entries, 0 to 5035
Data columns (total 3 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   stock   5036 non-null   object 
 1   date    5036 non-null   object 
 2   price   5036 non-null   float64
dtypes: float64(1), object(2)
memory usage: 118.2+ KB

• We observe that the date column is of object type whereas it should be of date time data type.

In [ ]:

# converting the date column to datetime format
data['date']  = pd.to_datetime(data['date'],dayfirst=True)

# converting the date column to datetime format data['date'] = pd.to_datetime(data['date'],dayfirst=True)

In [ ]:

data.head()

data.head()

Out[ ]:

	stock	date	price
0	AAPL	2013-02-08	67.8542
1	AAPL	2013-02-11	68.5614
2	AAPL	2013-02-12	66.8428
3	AAPL	2013-02-13	66.7156
4	AAPL	2013-02-14	66.6556

In [ ]:

data.info()

data.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5036 entries, 0 to 5035
Data columns (total 3 columns):
 #   Column  Non-Null Count  Dtype         
---  ------  --------------  -----         
 0   stock   5036 non-null   object        
 1   date    5036 non-null   datetime64[ns]
 2   price   5036 non-null   float64       
dtypes: datetime64[ns](1), float64(1), object(1)
memory usage: 118.2+ KB

• We observe that the date column has been converted to datetime format

In [ ]:

data.head()

data.head()

Out[ ]:

	stock	date	price
0	AAPL	2013-02-08	67.8542
1	AAPL	2013-02-11	68.5614
2	AAPL	2013-02-12	66.8428
3	AAPL	2013-02-13	66.7156
4	AAPL	2013-02-14	66.6556

The column 'date' is now in datetime format. Now we can change the format of the date to any other format

In [ ]:

data['date'].dt.strftime('%m/%d/%Y')

data['date'].dt.strftime('%m/%d/%Y')

Out[ ]:

0       02/08/2013
1       02/11/2013
2       02/12/2013
3       02/13/2013
4       02/14/2013
           ...    
5031    02/01/2018
5032    02/02/2018
5033    02/05/2018
5034    02/06/2018
5035    02/07/2018
Name: date, Length: 5036, dtype: object

In [ ]:

data['date'].dt.strftime('%m-%d-%y')

data['date'].dt.strftime('%m-%d-%y')

Out[ ]:

0       02-08-13
1       02-11-13
2       02-12-13
3       02-13-13
4       02-14-13
          ...   
5031    02-01-18
5032    02-02-18
5033    02-05-18
5034    02-06-18
5035    02-07-18
Name: date, Length: 5036, dtype: object

Extracting year from the date column

In [ ]:

data['date'].dt.year

data['date'].dt.year

Out[ ]:

0       2013
1       2013
2       2013
3       2013
4       2013
        ... 
5031    2018
5032    2018
5033    2018
5034    2018
5035    2018
Name: date, Length: 5036, dtype: int64

Creating a new column and adding the extracted year values into the dataframe.

In [ ]:

data['year'] = data['date'].dt.year

data['year'] = data['date'].dt.year

Extracting month from the date column

In [ ]:

data['date'].dt.month

data['date'].dt.month

Out[ ]:

0       2
1       2
2       2
3       2
4       2
       ..
5031    2
5032    2
5033    2
5034    2
5035    2
Name: date, Length: 5036, dtype: int64

Creating a new column and adding the extracted month values into the dataframe.

In [ ]:

data['month'] = data['date'].dt.month

data['month'] = data['date'].dt.month

In [ ]:

data.head()

data.head()

Out[ ]:

	stock	date	price	year	month
0	AAPL	2013-02-08	67.8542	2013	2
1	AAPL	2013-02-11	68.5614	2013	2
2	AAPL	2013-02-12	66.8428	2013	2
3	AAPL	2013-02-13	66.7156	2013	2
4	AAPL	2013-02-14	66.6556	2013	2

Extracting day from the date column

In [ ]:

data['date'].dt.day

data['date'].dt.day

Out[ ]:

0        8
1       11
2       12
3       13
4       14
        ..
5031     1
5032     2
5033     5
5034     6
5035     7
Name: date, Length: 5036, dtype: int64

Creating a new column and adding the extracted day values into the dataframe.

In [ ]:

data['day'] = data['date'].dt.day

data['day'] = data['date'].dt.day

In [ ]:

data.head()

data.head()

Out[ ]:

	stock	date	price	year	month	day
0	AAPL	2013-02-08	67.8542	2013	2	8
1	AAPL	2013-02-11	68.5614	2013	2	11
2	AAPL	2013-02-12	66.8428	2013	2	12
3	AAPL	2013-02-13	66.7156	2013	2	13
4	AAPL	2013-02-14	66.6556	2013	2	14

• We can see that year, month, and day columns have been added in the dataset.

In [ ]:

# The datetime format is convenient for many tasks!
data['date'][1]-data['date'][0]

# The datetime format is convenient for many tasks! data['date'][1]-data['date'][0]

Out[ ]:

Timedelta('3 days 00:00:00')

In [ ]:

import datetime

import datetime

In [ ]:

date = datetime.date(2022, 1, 29)
print(date)

date = datetime.date(2022, 1, 29) print(date)

2022-01-29