teaching/PandasLab03.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({\n",
    "    'student_id': ['A101', 'A102', 'A103', 'A104', 'A105'],\n",
    "    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],\n",
    "    'gender': ['female', 'male', 'male', 'male', 'female'],\n",
    "    'age': [20, 21, 19, 20, 22],\n",
    "    'score_math': [85, 90, 70, 75, 80],\n",
    "    'score_english': [80, 85, 75, 70, 90]\n",
    "})\n",
    "\n",
    "df2 = pd.DataFrame({\n",
    "    'student_id': ['A101', 'A102', 'A103', 'A104', 'A106'],\n",
    "    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Frank'],\n",
    "    'gender': ['female', 'male', 'male', 'male', 'male'],\n",
    "    'age': [20, 21, 19, 20, 23],\n",
    "    'score_math': [85, 90, 70, 75, 95],\n",
    "    'score_english': [80, 85, 75, 70, 80]\n",
    "})\n",
    "\n",
    "df3 = pd.DataFrame({\n",
    "    'student_id': ['A107', 'A108', 'A109', 'A110', 'A111'],\n",
    "    'name': ['Grace', 'Helen', 'Iris', 'John', 'Kevin'],\n",
    "    'gender': ['female', 'female', 'female', 'male', 'male'],\n",
    "    'age': [21, 20, 19, 22, 20],\n",
    "    'score_math': [90, 85, 75, 80, 95],\n",
    "    'score_history': [85, 80, 70, 75, 90]\n",
    "})"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "  student_id     name  gender  age  score_math  score_english\n0       A101    Alice  female   20          85             80\n1       A102      Bob    male   21          90             85\n2       A103  Charlie    male   19          70             75\n3       A104    David    male   20          75             70\n4       A105      Eve  female   22          80             90",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A105</td>\n      <td>Eve</td>\n      <td>female</td>\n      <td>22</td>\n      <td>80</td>\n      <td>90</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "  student_id     name  gender  age  score_math  score_english\n0       A101    Alice  female   20          85             80\n1       A102      Bob    male   21          90             85\n2       A103  Charlie    male   19          70             75\n3       A104    David    male   20          75             70\n4       A106    Frank    male   23          95             80",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A106</td>\n      <td>Frank</td>\n      <td>male</td>\n      <td>23</td>\n      <td>95</td>\n      <td>80</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "  student_id   name  gender  age  score_math  score_history\n0       A107  Grace  female   21          90             85\n1       A108  Helen  female   20          85             80\n2       A109   Iris  female   19          75             70\n3       A110   John    male   22          80             75\n4       A111  Kevin    male   20          95             90",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_history</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A107</td>\n      <td>Grace</td>\n      <td>female</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A108</td>\n      <td>Helen</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A109</td>\n      <td>Iris</td>\n      <td>female</td>\n      <td>19</td>\n      <td>75</td>\n      <td>70</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A110</td>\n      <td>John</td>\n      <td>male</td>\n      <td>22</td>\n      <td>80</td>\n      <td>75</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A111</td>\n      <td>Kevin</td>\n      <td>male</td>\n      <td>20</td>\n      <td>95</td>\n      <td>90</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Problems:\n",
    "[] Merge df1 and df2 on the student_id column, and save the result to a new DataFrame called merged_df. How many rows and columns does merged_df have?\n",
    "[] Merge df1 and df3 on the student_id column, and save the result to a new DataFrame called merged_df2. How many rows and columns does merged_df2 have?\n",
    "[] Concatenate df1, df2, and df3 vertically, and save the result to a new DataFrame called concatenated_df. How many rows and columns does concatenated_df have?\n",
    "[] Use the isnull() method to find the null values in df1.\n",
    "[] Use the fillna() method to fill the null values in df1 with the average value of each column.\n",
    "[] Use the groupby() method to group df1 by the gender column, and calculate the average math score and average English score for each gender.\n",
    "[] Use the pivot_table() method to create a pivot table from df1, where the rows are the gender column, the columns are the age column (grouped into bins of 2 years), and the values are the average math scores.\n",
    "[] Use the sort_values() method to sort df1 by the score_math column in descending order.\n",
    "[] Use the drop_duplicates() method to remove duplicate rows from df1 based on the name column.\n",
    "[] Use the loc() method to select the rows in df1 where the math score is greater than or equal to 80, and the English score is greater than or equal to 85.\n",
    "[] Use the apply() method to create a new column in df1 called total_score, which is the sum of the score_math and score_english columns.\n",
    "[] Use the merge() method to merge df1 and df2 on the student_id column, and specify the how parameter to perform a left join. Save the result to a new DataFrame called merged_df_left. How many rows and columns does merged_df_left have?\n",
    "[] Use the merge() method to merge df1 and df2 on the student_id column, and specify the how parameter to perform an inner join. Save the result to a new DataFrame called merged_df_inner. How many rows and columns does merged_df_inner have?\n",
    "[] Use the merge() method to merge df1 and df2 on the student_id column, and specify the how parameter to perform a right join. Save the result to a new DataFrame called merged_df_right. How many rows and columns does merged_df_right have?\n",
    "[] Use the merge() method to merge df1 and df2 on the student_id column, and specify the how parameter to perform an outer join. Save the result to a new DataFrame called merged_df_outer. How many rows and columns does merged_df_outer have?"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows: 4\n",
      "Number of columns: 11\n"
     ]
    },
    {
     "data": {
      "text/plain": "  student_id   name_x gender_x  age_x  score_math_x  score_english_x   name_y  \\\n0       A101    Alice   female     20            85               80    Alice   \n1       A102      Bob     male     21            90               85      Bob   \n2       A103  Charlie     male     19            70               75  Charlie   \n3       A104    David     male     20            75               70    David   \n\n  gender_y  age_y  score_math_y  score_english_y  \n0   female     20            85               80  \n1     male     21            90               85  \n2     male     19            70               75  \n3     male     20            75               70  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name_x</th>\n      <th>gender_x</th>\n      <th>age_x</th>\n      <th>score_math_x</th>\n      <th>score_english_x</th>\n      <th>name_y</th>\n      <th>gender_y</th>\n      <th>age_y</th>\n      <th>score_math_y</th>\n      <th>score_english_y</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge df1 and df2 on the student_id column, and save the result to a new DataFrame called merged_df. How many rows and columns does merged_df have? (on = 'student_id', 'name')\n",
    "merged_df = pd.merge(df1, df2, on='student_id')\n",
    "print(f'Number of rows: {merged_df.shape[0]}')\n",
    "print(f'Number of columns: {merged_df.shape[1]}')\n",
    "merged_df"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows: 0\n",
      "Number of columns: 11\n"
     ]
    },
    {
     "data": {
      "text/plain": "Empty DataFrame\nColumns: [student_id, name_x, gender_x, age_x, score_math_x, score_english, name_y, gender_y, age_y, score_math_y, score_history]\nIndex: []",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name_x</th>\n      <th>gender_x</th>\n      <th>age_x</th>\n      <th>score_math_x</th>\n      <th>score_english</th>\n      <th>name_y</th>\n      <th>gender_y</th>\n      <th>age_y</th>\n      <th>score_math_y</th>\n      <th>score_history</th>\n    </tr>\n  </thead>\n  <tbody>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge df1 and df3 on the student_id column, and save the result to a new DataFrame called merged_df2. How many rows and columns does merged_df2 have?\n",
    "merged_df2 = pd.merge(df1, df3, on='student_id')\n",
    "print(f'Number of rows: {merged_df2.shape[0]}')\n",
    "print(f'Number of columns: {merged_df2.shape[1]}')\n",
    "merged_df2"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows: 15\n",
      "Number of columns: 7\n",
      "   student_id     name  gender  age  score_math  score_english  score_history\n",
      "0        A101    Alice  female   20          85           80.0            NaN\n",
      "1        A102      Bob    male   21          90           85.0            NaN\n",
      "2        A103  Charlie    male   19          70           75.0            NaN\n",
      "3        A104    David    male   20          75           70.0            NaN\n",
      "4        A105      Eve  female   22          80           90.0            NaN\n",
      "5        A101    Alice  female   20          85           80.0            NaN\n",
      "6        A102      Bob    male   21          90           85.0            NaN\n",
      "7        A103  Charlie    male   19          70           75.0            NaN\n",
      "8        A104    David    male   20          75           70.0            NaN\n",
      "9        A106    Frank    male   23          95           80.0            NaN\n",
      "10       A107    Grace  female   21          90            NaN           85.0\n",
      "11       A108    Helen  female   20          85            NaN           80.0\n",
      "12       A109     Iris  female   19          75            NaN           70.0\n",
      "13       A110     John    male   22          80            NaN           75.0\n",
      "14       A111    Kevin    male   20          95            NaN           90.0\n"
     ]
    }
   ],
   "source": [
    "# Concatenate df1, df2, and df3 vertically, and save the result to a new DataFrame called concatenated_df. How many rows and columns does concatenated_df have?\n",
    "\n",
    "concatenated_df = pd.concat([df1, df2, df3])\n",
    "concatenated_df.reset_index(drop=True, inplace=True)\n",
    "print(f'Number of rows: {concatenated_df.shape[0]}')\n",
    "print(f'Number of columns: {concatenated_df.shape[1]}')\n",
    "print(concatenated_df)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "outputs": [
    {
     "data": {
      "text/plain": "   student_id     name  gender  age  score_math  score_english  score_history\n0        A101    Alice  female   20          85           80.0            NaN\n1        A102      Bob    male   21          90           85.0            NaN\n2        A103  Charlie    male   19          70           75.0            NaN\n3        A104    David    male   20          75           70.0            NaN\n4        A105      Eve  female   22          80           90.0            NaN\n5        A106    Frank    male   23          95           80.0            NaN\n6        A107    Grace  female   21          90            NaN           85.0\n7        A108    Helen  female   20          85            NaN           80.0\n8        A109     Iris  female   19          75            NaN           70.0\n9        A110     John    male   22          80            NaN           75.0\n10       A111    Kevin    male   20          95            NaN           90.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n      <th>score_history</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A105</td>\n      <td>Eve</td>\n      <td>female</td>\n      <td>22</td>\n      <td>80</td>\n      <td>90.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>A106</td>\n      <td>Frank</td>\n      <td>male</td>\n      <td>23</td>\n      <td>95</td>\n      <td>80.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>A107</td>\n      <td>Grace</td>\n      <td>female</td>\n      <td>21</td>\n      <td>90</td>\n      <td>NaN</td>\n      <td>85.0</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>A108</td>\n      <td>Helen</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>NaN</td>\n      <td>80.0</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>A109</td>\n      <td>Iris</td>\n      <td>female</td>\n      <td>19</td>\n      <td>75</td>\n      <td>NaN</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>A110</td>\n      <td>John</td>\n      <td>male</td>\n      <td>22</td>\n      <td>80</td>\n      <td>NaN</td>\n      <td>75.0</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>A111</td>\n      <td>Kevin</td>\n      <td>male</td>\n      <td>20</td>\n      <td>95</td>\n      <td>NaN</td>\n      <td>90.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop duplicate\n",
    "concatenated_df.drop_duplicates(subset='student_id', inplace=True, keep='first', ignore_index=True)\n",
    "concatenated_df"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [
    {
     "data": {
      "text/plain": "    student_id   name  gender    age  score_math  score_english  score_history\n0        False  False   False  False       False          False           True\n1        False  False   False  False       False          False           True\n2        False  False   False  False       False          False           True\n3        False  False   False  False       False          False           True\n4        False  False   False  False       False          False           True\n5        False  False   False  False       False          False           True\n6        False  False   False  False       False           True          False\n7        False  False   False  False       False           True          False\n8        False  False   False  False       False           True          False\n9        False  False   False  False       False           True          False\n10       False  False   False  False       False           True          False",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n      <th>score_history</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n      <td>False</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the isnull() method to find the null values in df1.\n",
    "concatenated_df.isnull()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "outputs": [
    {
     "data": {
      "text/plain": "   student_id     name  gender  age  score_math score_english score_history\n0        A101    Alice  female   20          85          80.0            ☺️\n1        A102      Bob    male   21          90          85.0            ☺️\n2        A103  Charlie    male   19          70          75.0            ☺️\n3        A104    David    male   20          75          70.0            ☺️\n4        A105      Eve  female   22          80          90.0            ☺️\n5        A106    Frank    male   23          95          80.0            ☺️\n6        A107    Grace  female   21          90            ☺️          85.0\n7        A108    Helen  female   20          85            ☺️          80.0\n8        A109     Iris  female   19          75            ☺️          70.0\n9        A110     John    male   22          80            ☺️          75.0\n10       A111    Kevin    male   20          95            ☺️          90.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n      <th>score_history</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80.0</td>\n      <td>☺️</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85.0</td>\n      <td>☺️</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75.0</td>\n      <td>☺️</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70.0</td>\n      <td>☺️</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A105</td>\n      <td>Eve</td>\n      <td>female</td>\n      <td>22</td>\n      <td>80</td>\n      <td>90.0</td>\n      <td>☺️</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>A106</td>\n      <td>Frank</td>\n      <td>male</td>\n      <td>23</td>\n      <td>95</td>\n      <td>80.0</td>\n      <td>☺️</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>A107</td>\n      <td>Grace</td>\n      <td>female</td>\n      <td>21</td>\n      <td>90</td>\n      <td>☺️</td>\n      <td>85.0</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>A108</td>\n      <td>Helen</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>☺️</td>\n      <td>80.0</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>A109</td>\n      <td>Iris</td>\n      <td>female</td>\n      <td>19</td>\n      <td>75</td>\n      <td>☺️</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>A110</td>\n      <td>John</td>\n      <td>male</td>\n      <td>22</td>\n      <td>80</td>\n      <td>☺️</td>\n      <td>75.0</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>A111</td>\n      <td>Kevin</td>\n      <td>male</td>\n      <td>20</td>\n      <td>95</td>\n      <td>☺️</td>\n      <td>90.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the fillna() method to fill the null values in df1 with the average value of each column.\n",
    "concatenated_df.fillna(\"☺️\")"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "outputs": [
    {
     "data": {
      "text/plain": "age       19    20    21    22\ngender                        \nfemale   NaN  85.0   NaN  80.0\nmale    70.0  75.0  90.0   NaN",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>age</th>\n      <th>19</th>\n      <th>20</th>\n      <th>21</th>\n      <th>22</th>\n    </tr>\n    <tr>\n      <th>gender</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>female</th>\n      <td>NaN</td>\n      <td>85.0</td>\n      <td>NaN</td>\n      <td>80.0</td>\n    </tr>\n    <tr>\n      <th>male</th>\n      <td>70.0</td>\n      <td>75.0</td>\n      <td>90.0</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# [] Use the pivot_table() method to create a pivot table from df1, where the rows are the gender column, the columns are the age column (grouped into bins of 2 years), and the values are the average math scores.\n",
    "pivot_table = pd.pivot_table(df1, columns='age', aggfunc='mean', values='score_math', index='gender')\n",
    "pivot_table"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "outputs": [
    {
     "data": {
      "text/plain": "  student_id     name  gender  age  score_math  score_english\n2       A103  Charlie    male   19          70             75\n3       A104    David    male   20          75             70\n4       A105      Eve  female   22          80             90\n0       A101    Alice  female   20          85             80\n1       A102      Bob    male   21          90             85",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A105</td>\n      <td>Eve</td>\n      <td>female</td>\n      <td>22</td>\n      <td>80</td>\n      <td>90</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the sort_values() method to sort df1 by the score_math column in descending order.\n",
    "df1.sort_values(by='score_math', ascending=True)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "outputs": [
    {
     "data": {
      "text/plain": "  student_id     name  gender  age  score_math  score_english  score_history\n0       A101    Alice  female   20          85           80.0            NaN\n1       A102      Bob    male   21          90           85.0            NaN\n2       A103  Charlie    male   19          70           75.0            NaN\n3       A104    David    male   20          75           70.0            NaN\n4       A105      Eve  female   22          80           90.0            NaN\n0       A101    Alice  female   20          85            NaN            NaN\n1       A102      Bob    male   21          90            NaN            NaN\n2       A103  Charlie    male   19          70            NaN            NaN\n3       A104    David    male   20          75            NaN            NaN\n4       A106    Frank    male   23          95            NaN            NaN\n0       A107    Grace  female   21          90            NaN           85.0\n1       A108    Helen  female   20          85            NaN           80.0\n2       A109     Iris  female   19          75            NaN           70.0\n3       A110     John    male   22          80            NaN           75.0\n4       A111    Kevin    male   20          95            NaN           90.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n      <th>score_history</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A105</td>\n      <td>Eve</td>\n      <td>female</td>\n      <td>22</td>\n      <td>80</td>\n      <td>90.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A106</td>\n      <td>Frank</td>\n      <td>male</td>\n      <td>23</td>\n      <td>95</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>A107</td>\n      <td>Grace</td>\n      <td>female</td>\n      <td>21</td>\n      <td>90</td>\n      <td>NaN</td>\n      <td>85.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A108</td>\n      <td>Helen</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>NaN</td>\n      <td>80.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A109</td>\n      <td>Iris</td>\n      <td>female</td>\n      <td>19</td>\n      <td>75</td>\n      <td>NaN</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A110</td>\n      <td>John</td>\n      <td>male</td>\n      <td>22</td>\n      <td>80</td>\n      <td>NaN</td>\n      <td>75.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A111</td>\n      <td>Kevin</td>\n      <td>male</td>\n      <td>20</td>\n      <td>95</td>\n      <td>NaN</td>\n      <td>90.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concatenated_df"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "outputs": [
    {
     "data": {
      "text/plain": "   student_id     name  gender  age  score_math  score_english  score_history\n0        A101    Alice  female   20          85           80.0            NaN\n1        A102      Bob    male   21          90           85.0            NaN\n2        A103  Charlie    male   19          70           75.0            NaN\n3        A104    David    male   20          75           70.0            NaN\n4        A105      Eve  female   22          80           90.0            NaN\n5        A106    Frank    male   23          95            NaN            NaN\n6        A107    Grace  female   21          90            NaN           85.0\n7        A108    Helen  female   20          85            NaN           80.0\n8        A109     Iris  female   19          75            NaN           70.0\n9        A110     John    male   22          80            NaN           75.0\n10       A111    Kevin    male   20          95            NaN           90.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n      <th>score_history</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A105</td>\n      <td>Eve</td>\n      <td>female</td>\n      <td>22</td>\n      <td>80</td>\n      <td>90.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>A106</td>\n      <td>Frank</td>\n      <td>male</td>\n      <td>23</td>\n      <td>95</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>A107</td>\n      <td>Grace</td>\n      <td>female</td>\n      <td>21</td>\n      <td>90</td>\n      <td>NaN</td>\n      <td>85.0</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>A108</td>\n      <td>Helen</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>NaN</td>\n      <td>80.0</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>A109</td>\n      <td>Iris</td>\n      <td>female</td>\n      <td>19</td>\n      <td>75</td>\n      <td>NaN</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>A110</td>\n      <td>John</td>\n      <td>male</td>\n      <td>22</td>\n      <td>80</td>\n      <td>NaN</td>\n      <td>75.0</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>A111</td>\n      <td>Kevin</td>\n      <td>male</td>\n      <td>20</td>\n      <td>95</td>\n      <td>NaN</td>\n      <td>90.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the drop_duplicates() method to remove duplicate rows from concatenated_df based on the name column.\n",
    "concatenated_df.drop_duplicates(subset='student_id', keep='first', ignore_index=True)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "outputs": [
    {
     "data": {
      "text/plain": "  student_id name  gender  age  score_math  score_english\n1       A102  Bob    male   21          90             85\n4       A105  Eve  female   22          80             90",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A105</td>\n      <td>Eve</td>\n      <td>female</td>\n      <td>22</td>\n      <td>80</td>\n      <td>90</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the loc() method to select the rows in df1 where the math score is greater than or equal to 80, and the English score is greater than or equal to 85.\n",
    "df1.loc[(df1['score_math'] >= 80) & (df1['score_english'] >= 85)]"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "outputs": [
    {
     "data": {
      "text/plain": "  student_id     name  gender  age  score_math  score_english  total_score\n0       A101    Alice  female   20          85             80          165\n1       A102      Bob    male   21          90             85          175\n2       A103  Charlie    male   19          70             75          145\n3       A104    David    male   20          75             70          145\n4       A105      Eve  female   22          80             90          170",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name</th>\n      <th>gender</th>\n      <th>age</th>\n      <th>score_math</th>\n      <th>score_english</th>\n      <th>total_score</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80</td>\n      <td>165</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85</td>\n      <td>175</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75</td>\n      <td>145</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70</td>\n      <td>145</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A105</td>\n      <td>Eve</td>\n      <td>female</td>\n      <td>22</td>\n      <td>80</td>\n      <td>90</td>\n      <td>170</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the apply() method to create a new column in df1 called total_score, which is the sum of the score_math and score_english columns.\n",
    "df1['total_score'] = df1.apply(lambda x: x['score_math'] + x['score_english'], axis=1)\n",
    "df1"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows: 5\n",
      "Number of columns: 11\n"
     ]
    },
    {
     "data": {
      "text/plain": "  student_id   name_x gender_x  age_x  score_math_x  score_english  \\\n0       A101    Alice   female     20            85             80   \n1       A102      Bob     male     21            90             85   \n2       A103  Charlie     male     19            70             75   \n3       A104    David     male     20            75             70   \n4       A105      Eve   female     22            80             90   \n\n   total_score   name_y gender_y  age_y  score_math_y  \n0          165    Alice   female   20.0          85.0  \n1          175      Bob     male   21.0          90.0  \n2          145  Charlie     male   19.0          70.0  \n3          145    David     male   20.0          75.0  \n4          170      NaN      NaN    NaN           NaN  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>student_id</th>\n      <th>name_x</th>\n      <th>gender_x</th>\n      <th>age_x</th>\n      <th>score_math_x</th>\n      <th>score_english</th>\n      <th>total_score</th>\n      <th>name_y</th>\n      <th>gender_y</th>\n      <th>age_y</th>\n      <th>score_math_y</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A101</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20</td>\n      <td>85</td>\n      <td>80</td>\n      <td>165</td>\n      <td>Alice</td>\n      <td>female</td>\n      <td>20.0</td>\n      <td>85.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A102</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21</td>\n      <td>90</td>\n      <td>85</td>\n      <td>175</td>\n      <td>Bob</td>\n      <td>male</td>\n      <td>21.0</td>\n      <td>90.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A103</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19</td>\n      <td>70</td>\n      <td>75</td>\n      <td>145</td>\n      <td>Charlie</td>\n      <td>male</td>\n      <td>19.0</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A104</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20</td>\n      <td>75</td>\n      <td>70</td>\n      <td>145</td>\n      <td>David</td>\n      <td>male</td>\n      <td>20.0</td>\n      <td>75.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A105</td>\n      <td>Eve</td>\n      <td>female</td>\n      <td>22</td>\n      <td>80</td>\n      <td>90</td>\n      <td>170</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the merge() method to merge df1 and df2 on the student_id column, and specify the how parameter to perform a left join. Save the result to a new DataFrame called merged_df_left. How many rows and columns does merged_df_left have?\n",
    "merged_df_left = pd.merge(df1, df2, on='student_id', how='left')\n",
    "print(f'Number of rows: {merged_df_left.shape[0]}')\n",
    "print(f'Number of columns: {merged_df_left.shape[1]}')\n",
    "merged_df_left"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}