DS(4) :Python — Data Indexing| Time series|Joins

Related posts:

• DS(1) : Introduction to Python
• DS (2): Python -File Handling
• DS (3) : Advanced Python Libraries
• Data Visualization Testing

Subsetting Data

To subset a sequence, you can use indexing to access specific elements or ranges of elements.

DATAFRAME — A Pandas DataFrame is a 2 dimensional data structure, like a 2 dimensional array, or a table with rows and columns.

INDEXING — Indexing is a way to access individual elements of a sequence, such as a list, tuple, or string.

# Basics

import pandas as pd #import the pandas library
data = pd.read_csv('datasets/filename.csv') # read the data file
data.index # index of the dataframe;RangeIndex(start=0, stop=8523, step=1)
data.shape # # no. of rows and columns in the data; (8523, 12)
data.columns #Index(['Item_Identifier',..,'Item_Outlet_Sales'],dtype='object') # column index
data.head() # view the top rows of the data

# create random list
import random
random_list = [random.randint(1, 8523) for i in range(8523)] # create random list
random_list # view list; random numbers generated and assigned to the data rows

data.index = random_list # set the index
data.head() # view top rows data

# SET ANOTHER COLUMN AS INDEX

# 1. change the index of the dataframe
# 2. drop=True is used to drop the column that's set as index
# 3. inplace=True is used to make changes in the original dataframe
# data = pd.read_csv('datasets/Datafile.csv')

data.set_index('Outlet_Establishment_Year', drop=True, inplace=True) # example of a label based index
data.head() 3 view top rows
data.index # to view index


# reset the index
data.set_index('Item_Identifier', drop= True, inplace=True)
data.head() # view the top rows of the data
data.index # index of the data


data.loc['FDA15'] # subset the data using loc function (like filter the indexed data)
data[data['Item_Type'] == 'Dairy'] # subset data based on value of column

data.reset_index(inplace=True) # reset the index
data.head() # view the top rows of the data

Subsetting: Position Based

import pandas as pd # importing the pandas library
data = pd.read_csv('datasets/datafile.csv')

data.head(2) # view top 2 rows
data.head() # view top rows
data.tail(2) # view bottom 2 rows
data[10:15] # select the data from range 10–15

data.iloc[[1,5,2,4,6,14]] # select specific rows by index number
data.iloc[[1,4,5,2],[1,3,5]] # Select specific row(first line) and column (second line.)

Subsetting: Label Based

import pandas as pd # import the pandas library
data = pd.read_csv('datasets/datafile.csv')
data.head()
data.set_index('Item_Identifier',inplace=True, drop=True) # set the Item_Identifier as the index of the dataframe.
data.head()

data.loc['FDA15'] # select rows with index value 'FDA03'
data.loc[['FDA15', 'FDA03']] # multiple values
data.loc[['FDA15', 'FDA03'], 'Item_Fat_Content'] # Rows and Columns
data.loc[['FDA15', 'FDA03'], ['Item_Fat_Content', 'Item_Type', 'Item_MRP']] # Rows and Columns

DIFFERENCE BETWEEN LOC AND ILOC

sample_df = pd.DataFrame({ 'gender' : ['M', 'F', 'M', 'M', 'F'], 
              'grade' : ['A', 'A', 'B', 'B', 'A'],
              'marks' : [ 22, 21, 12, 14, 20], 
              'id' : ['A101', 'A102', 'A103', 'A104', 'A105']})sample_df
sample_df.loc[2:4] # will display data with index 2,3,4
sample_df.iloc[2:4] # will display data with index 2,3,

sample_df = sample_df.sort_values(by=['marks'])sample_df # sort on marks
sample_df.loc[2:4] # will give data with index 2,3,4
sample_df.iloc[2:4] # will give 2nd and 3rd row depending upon position

sample_df.set_index('id',inplace=True)sample_df # set categorical valu as index
sample_df.loc[2:4] #will give error as there are no such index
sample_df.iloc[2:4] # will give data based on position

sample_df.loc['A104':'A102']

#loc for index based label
#iloc for index position

Subsetting: Value Based

import pandas as pd # importing the pandas library
data = pd.read_csv('datasets/datafile.csv')
data.head() # view the top rows of the data

# filter rows with condition
data.loc[data.Outlet_Establishment_Year == 1987] # or
data[data.Outlet_Establishment_Year == 1987]

# filter data based on multiple conditions# and &# or |# not !
data.loc[(data.Outlet_Establishment_Year == 2009) & (data.Outlet_Size == 'Medium')]
#data[(data.Outlet_Establishment_Year != 2009)] #and, or
data[(data.Outlet_Establishment_Year == 1987) | (data.Outlet_Establishment_Year == 1988) | (data.Outlet_Establishment_Year == 1999)]

# filter for a list of values
data.loc[data.Outlet_Establishment_Year.isin([1987, 1988, 1999])] # or
data[data.Outlet_Establishment_Year.isin([1987, 1988])]

# list of columns
select_columns = ['Item_Identifier', 'Item_MRP', 'Outlet_Establishment_Year', 'Outlet_Size'] # dataframe with specific columns
data[select_columns]

# list of specific columns
select_columns = ['Item_Identifier', 'Item_MRP', 'Outlet_Establishment_Year', 'Outlet_Size'] # filter the data
data[(data.Outlet_Establishment_Year == 1987) & (data.Outlet_Size == 'High')][select_columns] # or
data.loc[(data.Outlet_Establishment_Year == 1987) & (data.Outlet_Size == 'High'), select_columns]

data.dtypes # check the data types of the columns
data.select_dtypes('object') # select the columns where data type = object
data.select_dtypes('int64') # select numerical datatype

DF vs DF.loc
df[] sometimes has unwanted behavior, hence it is recommended to use df.loc[]
also df.loc[] also works with label based subsetting

Modifying Data

import pandas as pd # importing the pandas library
data = pd.read_csv('datasets/datafile.csv')
data.head()

data.isna().sum() # check for the null values
data.loc[data.Item_Weight.isna() == True] #rows with null values in Item_Weight
data.Item_Weight.mean() # find mean
# fill the null values in Item_Weight by mean
data.loc[(data.Item_Weight.isna() == True) , 'Item_Weight'] = data.Item_Weight.mean()

data.isna().sum() # check for the null values
data.dtypes # chcek datatypes
data.head()

data.Outlet_Size.mode() # follow same process for outlet size
data.Outlet_Size.value_counts() # choose which ever value is more
data.loc[(data.Outlet_Size.isna() == True) , 'Outlet_Size'] = 'Medium'

#fillna function to impute the missing values.
- fillna function is another way to impute the missing values. 
- Use the parameter inplace=True to store the results in the dataframe.
# fill the null values in Outlet Size by the most frequent value: "Medium"
data.Outlet_Size.fillna('Medium', inplace=True)
data.isna().sum() # check the null values again

# clean dat that has similar looking data with different ways to present
data.Item_Fat_Content.value_counts()
# Create a new mapping (dictionary)
mapping = { 'Low Fat' : 'LF', 'Regular' : 'R', 'LF' : 'LF',
         'reg': 'R', 'low fat' : 'LF'}
data.Item_Fat_Content.map(mapping)

# use the map function to update the values
data.Item_Fat_Content = data.Item_Fat_Content.map(mapping)
# Count of new categories in the column Item_Fat_Content
data.Item_Fat_Content.value_counts()

data.Item_MRP

data['Item_MRP_in_USD']=data.Item_MRP.apply(lambda x: x/74)
data.head() # verify
def convert(price): # create function
price = price/74
price = price + 1.28
return price
data['Item_MRP_in_USD_UPDATED'] = data.Item_MRP.apply(lambda x : convert(x))
data.head()
data[['Item_MRP','Item_MRP_in_USD']]

HOW TO CONVERT CATEGORICAL VARIABLES INTO NUMERICAL?
data.head() # currently we have 13 columns in the data
data.shape

#### `USE GET_DUMMIES`to convert categorical values to numerical - -
# convert categorical variables into numerical variables.
data = pd.get_dummies(data)
data.head() # view the data

PREPROCESSING

Sorting the Data

import pandas as pd # import the pandas library

# sample dataframe
data_frame = pd.DataFrame({ 'roll_no': [ 102, 101, 104, 103, 105], 
           'name' : ['Aravind', 'Rahul', 'Prateek', 'Piyuesh', 'Kartik'], 
           'grade': ['B', 'B', 'A', 'C', 'A'],
           'marks': [ 15, 15, 20, 4, 22],
           'city' : ['Gurugram', 'Delhi', 'Delhi', 'Gurugram', 'Hyderabad']}) 
data_frame

# sort the data
data_frame.sort_values(by=['grade'])

# sort the dataframe
data_frame.sort_values(by=['grade','marks'],ascending=[True,False])
data_frame

Now, you can observe that the sorted state of the dataframe is not saved. Use the parameter inplace = True to save the sorted state.
# save the sorted state by using inplace =True
data_frame.sort_values(by=['grade','marks'], ascending= [True, False], inplace=True)

data_frame.reset_index() # reset index
data_frame.reset_index(inplace=True, drop=True)
data_frame

Working with Multiple DataFrames

import pandas as pd # importing the pandas library
# read the datasets
outlet_size_small = pd.read_csv('Concatenation data/datafile1.csv')
outlet_size_medium = pd.read_csv('Concatenation data/datafile2.csv')
outlet_size_large = pd.read_csv('Concatenation data/datafile3.csv')
outlet_size_small.shape, outlet_size_medium.shape, outlet_size_large.shape

outlet_size_small.head()
outlet_size_medium.head()
outlet_size_large.head()

# CONCATENATE ALL THE DATAFRAMES; ROW-WISE - AXIS=0 and COLUMN-WISE - AXIS=1
# dataframes list
all_dataframes = [outlet_size_small, outlet_size_medium, outlet_size_large]
data = pd.concat(all_dataframes, axis=0) # concatenate all the dataframes rowwise
data.shape # shape of the data

# for columnwise concat no of rows should be same and index should be sorted
sample_dataframe = pd.DataFrame({ 'roll_no': [ 102, 101, 104, 103, 105], 
                  'name' : ['Aravind', 'Rahul', 'Prateek', 'Piyuesh', 'Kartik'], 
                  'grade': ['B', 'B', 'A', 'C', 'A'], 
                  'marks': [ 15, 15, 20, 4, 22], 
                   'city' : ['Gurugram', 'Delhi', 'Delhi', 'Gurugram', 'Hyderabad']})
sample_dataframe
# another sample dataframe
phone_no = pd.DataFrame({ 'phone_no' : [212202, 202021, 212334, 213431, 211721]})
phone_no
combined = pd.concat([sample_dataframe,phone_no], axis=1) # it will assign data is sequence
combined

SQL Joins using Python

student_df = pd.DataFrame({ 'roll_no': [ 102, 101, 104, 103, 105], 
            'name' : ['Aravind', 'Rahul', 'Prateek', 'Piyuesh', 'Kartik'],
            'grade': ['B', 'B', 'A', 'C', 'A'], 
            'marks': [ 15, 15, 20, 4, 22], 
            'city' : ['Gurugram', 'Delhi', 'Delhi', 'Gurugram', 'Hyderabad']})
student_df

city_state_mapping = pd.DataFrame({ 'city' : ['Gurugram', 'Delhi', 'Hyderabad', 'Faridabad'],
             'state' : ['Haryana', 'Delhi', 'Telangana', 'Haryana']})
city_state_mapping

# add another column state to the student_df using the city_state_mapping. 
# left join-  use the merge function and set parameters how='left' and on='city.
student_df.merge(city_state_mapping, how='left', on='city')
# Right join
roll_no = pd.DataFrame({ 'roll_no' : [ 102, 103]})
roll_no
student_df
student_df.merge(roll_no, how='right', on='roll_no')

student_selection = pd.DataFrame({ 'roll_no' : [102, 105, 101], 
      'company' : ['ABC', 'XYZ', 'ABC'], 
      'package (lpa)' : [ 8, 14.5, 11 ]})
student_selection
student_df
student_df.merge(student_selection, how='outer')

student_df = pd.DataFrame({ 'college': ['ZU UNIVERSITY', 'ZU UNIVERSITY', 'ZU UNIVERSITY', 'ZU UNIVERSITY', 'ZU UNIVERSITY'], 
        'roll_no': [ 102, 101, 104, 103, 105], 
        'name' : ['Aravind', 'Rahul', 'Prateek', 'Piyuesh', 'Kartik'], 
        'grade': ['B', 'B', 'A', 'C', 'A'], 
        'marks': [ 15, 15, 20, 4, 22], 
        'city' : ['Gurugram', 'Delhi', 'Delhi', 'Gurugram', 'Hyderabad']})
 student_df
pool = pd.DataFrame({ 'college': ['ZU UNIVERSITY', 'ZU UNIVERSITY', 'AB UNIVERSITY', 'ZU UNIVERSITY','AB UNIVERSITY'], 
      'name' : ['Aravind', 'Rahul', 'Rahul', 'Prateek', 'Harsh'],
      'company' : ['ABC', 'XYZ', 'ABC', 'AEP', 'ABC'], 
      'package (lpa)' : [ 8, 14.5, 11, 6, 6 ]})
pool
# join on 2 columns
student_df.merge(pool, how='inner', on=['college', 'name'])

Aggregating and Summarizing DataFrames

import pandas as pd # import the pandas library
data = pd.read_csv('Aggregation in Python/datafile.csv')
data.head()

data.Item_MRP.sum() # calculate the sum of all MRP
data.Item_MRP.mean() # calculate the average Item_MRP
data.Item_MRP.median() # calculate the median of Item_MRP

data.Outlet_Type.mode()
data.Outlet_Type.value_counts()

data.describe() # get the summary
data.isna().sum() #How to get number of missing values in each columns?

GROUP BY
data.head()
d = data.groupby(['Item_Type'])
d
d.first()
pd.pivot_table(data, index= ['Outlet_Size', 'Item_Type'], values= 'Item_MRP', aggfunc= 'mean')

d.mean(numeric_only=True)
d.mean(numeric_only=True)['Item_MRP']
d.max(numeric_only=True)

# step wise groupby

d = data.groupby(['Outlet_Size', 'Item_Type']).mean(numeric_only=True)
d

d['Item_MRP']

Pivot Table

pd.pivot_table(data, index='Item_Type', values="Item_MRP", aggfunc= 'mean')
pd.pivot_table(data, index= ['Outlet_Size', 'Item_Type'], values= 'Item_MRP', aggfunc= 'mean')

Cross Table

• The crosstab() function is used to compute a frequency table of two or more factors. By default, it computes a frequency table of the factors unless an array of values or an aggregation function which is passed.

pd.crosstab(data['Outlet_Size'], data['Item_Type'])

average_item_visibility = data.groupby(['Item_Identifier'])['Item_Visibility'].mean().reset_index()
average_item_visibility

def get_item_visibility(x) : 
return average_item_visibility.loc[(average_item_visibility.Item_Identifier == x), 'Item_Visibility'].values[0]
# let's test it on the sample Item_Identifier
get_item_visibility('DRA24')

data['average_item_visibility'] = data.Item_Identifier.apply(get_item_visibility)
data.head()

#or

data['average_item_visibility_2'] = data.groupby(['Item_Identifier'])['Item_Visibility'].transform('mean')
data[['average_item_visibility', 'average_item_visibility_2']]

Preprocessing Timeseries Data

import pandas as pd
import datetime 
data = pd.read_csv('Time series Data/time_series.csv') # read the data set
data.head(5) # view the top rows

data.dtypes

CHANGE THE DATA TYPE TO DATETIME

data.Datetime = pd.to_datetime(data.Datetime) # change type to datetime
data.dtypes # check the data types again

data
data.Datetime.apply(lambda x: x.day_name())
data.Datetime.apply(lambda x: x.month_name())
data_2 = pd.read_csv('Time series Data/time_series_2.csv')

data_2 # view the top rows of the data

We can read the date time by specifying the format.

Here are some of the common used directives.
%a    Weekday as locale's abbreviated name.    
%A    Weekday as locale's full name.
%d    Day of the month as a zero-padded decimal number.
%b    Month as locale's abbreviated name.
%B    Month as locale's full name. 
%m    Month as a zero-padded decimal number. 
%y    Year without century as a zero-padded decimal number. 
%Y    Year with century as a decimal number. 
%H    Hour (24-hour clock) as a zero-padded decimal number.

# convert to datetime by specifying the data time format
data_2.Datetime = pd.to_datetime(data_2.Datetime, format="%d %b %Y ")
data_2.head()

TIME BASED FEATURES

Get Manpreet Buttar’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Create features like month and month_name from the data.

data
# create month and month_name 
data['month'] = data.Datetime.dt.monthdata['month_name'] = data.Datetime.dt.month_name()

# view the data
data
Create day_name, day_of_week, & day_of_year
# create features
data['day_name'] = data.Datetime.dt.day_name()data['day_of_week'] = data.Datetime.dt.dayofweekdata['day_of_year'] = data.Datetime.dt.dayofyear
# view the data
data.head()

DIFFERENCE BETWEEN 2 DATES

data.head()
data['today'] = pd.to_datetime(datetime.date.today())
data.head()
difference_of_dates = data['today'] - data['Datetime']
difference_of_dates
difference_of_dates.apply(lambda x: x.days)
data['day_difference'] = difference_of_dates.apply(lambda x: x.days)
data.head()

CHALLENGES WITH TIME DATA

data['asia_timezone'] = data.Datetime.dt.tz_localize('Asia/Calcutta')
data.head()

# change the asia time zone to UTC
data['utc_timezone'] = data.asia_timezone.dt.tz_convert('UTC')
data.head()
data[['asia_timezone', 'utc_timezone']]
asia_timezone
utc_timezone

Select a random date

data['asia_timezone'][18287]
Timestamp('2014–09–25 23:00:00+0530', tz='Asia/Calcutta')
data['utc_timezone'][18287]
Timestamp('2014–09–25 17:30:00+0000', tz='UTC')

READING DATA WITH UNIX TIMESTAMP.

data_with_unix_ts = pd.read_csv('Time series Data/data_with_timestamp.csv')
data_with_unix_ts.head()
# convert the unix timestamp to datetime.
data_with_unix_ts.timestamp = pd.to_datetime(data_with_unix_ts.timestamp, unit='s')
data_with_unix_ts.head() # view the top rows