Chapter 29 Data Manipulation Practice

29.1 Data Filtering and Selection

sample data

data = {
    'department': ['Sales', 'Sales', 'HR', 'HR', 'IT', 'IT', 'Finance', 'Finance'],
    'employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace', 'Hannah'],
    'salary': [70000, 80000, 50000, 60000, 90000, 85000, 95000, 65000],
}
df = pd.DataFrame(data)
df

##   department employee  salary
## 0      Sales    Alice   70000
## 1      Sales      Bob   80000
## 2         HR  Charlie   50000
## 3         HR    David   60000
## 4         IT      Eve   90000
## 5         IT    Frank   85000
## 6    Finance    Grace   95000
## 7    Finance   Hannah   65000

29.1.0.1 filtering rows

df[df['employee'] == 'Eve']

##   department employee  salary
## 4         IT      Eve   90000

df[df['department'].isin(['HR', 'IT'])]

##   department employee  salary
## 2         HR  Charlie   50000
## 3         HR    David   60000
## 4         IT      Eve   90000
## 5         IT    Frank   85000

df[df['salary'] > 80000]

##   department employee  salary
## 4         IT      Eve   90000
## 5         IT    Frank   85000
## 6    Finance    Grace   95000

df[(df['salary'] > 70000) & (df['salary'] < 90000)]

##   department employee  salary
## 1      Sales      Bob   80000
## 5         IT    Frank   85000

df.groupby('department').filter(lambda x: x['salary'].sum() > 150000)

##   department employee  salary
## 4         IT      Eve   90000
## 5         IT    Frank   85000
## 6    Finance    Grace   95000
## 7    Finance   Hannah   65000

df.loc[0:3, :]

##   department employee  salary
## 0      Sales    Alice   70000
## 1      Sales      Bob   80000
## 2         HR  Charlie   50000
## 3         HR    David   60000

df.iloc[0:3, :]

##   department employee  salary
## 0      Sales    Alice   70000
## 1      Sales      Bob   80000
## 2         HR  Charlie   50000

29.1.0.2 filtering columns

cols = ['employee', 'salary']

df[cols]

##   employee  salary
## 0    Alice   70000
## 1      Bob   80000
## 2  Charlie   50000
## 3    David   60000
## 4      Eve   90000
## 5    Frank   85000
## 6    Grace   95000
## 7   Hannah   65000

df.loc[0:3, ['employee', 'salary']]

##   employee  salary
## 0    Alice   70000
## 1      Bob   80000
## 2  Charlie   50000
## 3    David   60000

df.iloc[0:3, :]

##   department employee  salary
## 0      Sales    Alice   70000
## 1      Sales      Bob   80000
## 2         HR  Charlie   50000

29.1.1 Aggregation and Grouping:

df.groupby('department')['salary'].mean()

## department
## Finance    80000.0
## HR         55000.0
## IT         87500.0
## Sales      75000.0
## Name: salary, dtype: float64

29.1.2 Joining and Merging Data:

merged_df = pd.merge(df1, df2, on='key')

29.1.3 recoding

Sample Data

data = {
    'employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace', 'Hannah'],
    'salary': [70000, 80000, 50000, 60000, 90000, 85000, 75000, 65000]
}

df = pd.DataFrame(data)

df.head()

##   employee  salary
## 0    Alice   70000
## 1      Bob   80000
## 2  Charlie   50000
## 3    David   60000
## 4      Eve   90000

29.1.3.1 np.where() method

# this condition creates a series of boolean
condition = df['salary'] > 85000

df['label'] = np.where(condition, 'high', 'low')

df.head()

##   employee  salary label
## 0    Alice   70000   low
## 1      Bob   80000   low
## 2  Charlie   50000   low
## 3    David   60000   low
## 4      Eve   90000  high

condition = df['employee'] == 'David'

df['employee'] = np.where(condition, 'Davut', df['employee'])

df.head()

##   employee  salary label
## 0    Alice   70000   low
## 1      Bob   80000   low
## 2  Charlie   50000   low
## 3    Davut   60000   low
## 4      Eve   90000  high

29.1.3.2 multiple np.where()

condition1 = df['salary'] <= 60000 
condition2 = (df['salary'] > 60000) & (df['salary'] <= 80000)


df['label'] = np.where(condition1, 'Low', 'High')
df['label'] = np.where(condition2, 'Medium', df['label'])


df.head()

##   employee  salary   label
## 0    Alice   70000  Medium
## 1      Bob   80000  Medium
## 2  Charlie   50000     Low
## 3    Davut   60000     Low
## 4      Eve   90000    High

# Use np.where for multiple conditions

condition_1 = df['salary'] <= 60000
condition_2 = df['salary'] <= 80000

df['label2'] = np.where(condition_1, 'Low', 
                                    np.where(condition_2, 'Medium', 'High') )

print(df)

##   employee  salary   label  label2
## 0    Alice   70000  Medium  Medium
## 1      Bob   80000  Medium  Medium
## 2  Charlie   50000     Low     Low
## 3    Davut   60000     Low     Low
## 4      Eve   90000    High    High
## 5    Frank   85000    High    High
## 6    Grace   75000  Medium  Medium
## 7   Hannah   65000  Medium  Medium

29.1.3.3 pd.cut() method

# Define thresholds for 'low', 'medium', and 'high' earners

bins = [0, 60000, 80000, float('inf')]
labels = ['Low', 'Medium', 'High']

# Use pd.cut to create a new column 'category'
df['category'] = pd.cut(df['salary'], bins=bins, labels=labels)

df.head()

##   employee  salary   label  label2 category
## 0    Alice   70000  Medium  Medium   Medium
## 1      Bob   80000  Medium  Medium   Medium
## 2  Charlie   50000     Low     Low      Low
## 3    Davut   60000     Low     Low      Low
## 4      Eve   90000    High    High     High

29.1.3.4 Alternative to np.where()

29.1.3.4.1 Using pd.Series.apply with a Lambda Function:

if else condition

df['high_low'] = df['salary'].apply(lambda x: 'high' if x > 85000 else 'low')

29.1.3.4.2 Using DataFrame.assign with np.where:

df = df.assign(high_low2 = np.where(df['salary'] > 85000, 'high', 'low'))
df.head()

##   employee  salary   label  label2 category high_low high_low2
## 0    Alice   70000  Medium  Medium   Medium      low       low
## 1      Bob   80000  Medium  Medium   Medium      low       low
## 2  Charlie   50000     Low     Low      Low      low       low
## 3    Davut   60000     Low     Low      Low      low       low
## 4      Eve   90000    High    High     High     high      high

29.1.3.4.3 Using DataFrame.loc:

df['new_label'] = 'low'

df.loc[df['salary'] > 85000, 'new_label'] = 'high'

29.1.3.5 np.select() method

data = {
    'employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace', 'Hannah'],
    'salary': [70000, 80000, 50000, 60000, 90000, 85000, 75000, 65000]
}

df = pd.DataFrame(data)

# Define conditions for categorizing salaries
conditions = [
    (df['salary'] <= 60000),
    (df['salary'] > 60000) & (df['salary'] <= 80000),
    (df['salary'] > 80000)
]

# Corresponding choices for each condition
choices = ['Low', 'Medium', 'High']

# Use np.select to create a new column 'earnings_category'
df['category2'] = np.select(conditions, choices)

df.head()

##   employee  salary category2
## 0    Alice   70000    Medium
## 1      Bob   80000    Medium
## 2  Charlie   50000       Low
## 3    David   60000       Low
## 4      Eve   90000      High

29.1.3.6 apply() method

# Custom function to categorize salary
def categorize_salary(salary):
    if salary <= 60000:
        return 'Low'
    elif 60000 < salary <= 80000:
        return 'Medium'
    else:
        return 'High'

# Apply the custom function to create a new column 'earnings_category'
df['category3'] = df['salary'].apply(categorize_salary)

df.head()

##   employee  salary category2 category3
## 0    Alice   70000    Medium    Medium
## 1      Bob   80000    Medium    Medium
## 2  Charlie   50000       Low       Low
## 3    David   60000       Low       Low
## 4      Eve   90000      High      High