Informatics Practices: Class 12 — Chapter 3: Data Handling using Pandas - II

Pandas is an established Python library used for data manipulation, processing, and analysis. This chapter covers advanced features of DataFrame such as sorting, descriptive statistics, grouping, reshaping, handling missing values, and database integration.

The baseline dataset contains marks scored by 4 students (Raman, Zuhaire, Aashravy, Mishti) in Unit Tests 1, 2, and 3. Maximum marks are 25 per subject.

  
    >>> import pandas as pd
    >>> marksUT = {
    'Name': ['Raman','Raman','Raman','Zuhaire','Zuhaire','Zuhaire','Ashravy','Ashravy','Ashravy','Mishti','Mishti','Mishti'],
    'UT': [1,2,3,1,2,3,1,2,3,1,2,3],
    'Maths': [22,21,14,20,23,22,23,24,12,15,18,17],
    'Science': [21,20,19,17,15,18,19,22,25,22,21,18],
    'S.St': [18,17,15,22,21,19,20,24,19,25,25,20],
    'Hindi': [20,22,24,24,25,23,15,17,21,22,24,25],
    'Eng': [21,24,23,19,15,13,22,21,23,22,23,20]
    }
    >>> df = pd.DataFrame(marksUT)
  

Descriptive statistics summarize the given data to provide a basic understanding of its distribution and characteristics.

  
    >>> # Calculate maximum values (including alphabetical for Name)
    >>> print(df.max())
    Name       Zuhaire
    UT               3
    Maths           24
    Science         25
    S.St            25
    Hindi           25
    Eng             24
    dtype: object
    >>> # Restrict to numeric columns only
    >>> print(df.max(numeric_only=True))
    UT          3
    Maths      24
    Science    25
    S.St       25
    Hindi      25
    Eng        24
    dtype: int64
    >>> # Row-wise maximum calculation (axis=1)
    >>> print(df.max(axis=1).head(3))
    0    22
    1    24
    2    24
    dtype: int64
    >>> # Minimum values
    >>> print(df.min())
    Name       Ashravy
    UT               1
    Maths           12
    Science         15
    S.St            15
    Hindi           15
    Eng             13
    dtype: object
    >>> # Sum of values (concatenates strings in 'Name')
    >>> print(df['Maths'].sum())
    231
    >>> # Count of non-null values
    >>> print(df.count())
    Name       12
    UT         12
    Maths      12
    Science    12
    S.St       12
    Hindi      12
    Eng        12
    dtype: object
  

  
    >>> # Mean calculation
    >>> print(df.mean(numeric_only=True))
    UT          2.5000
    Maths      18.6000
    Science    19.8000
    S.St       20.0000
    Hindi      21.3125
    Eng        19.8000
    dtype: float64
    >>> # Median calculation
    >>> print(df.median(numeric_only=True))
    UT          2.5
    Maths      19.0
    Science    20.0
    S.St       19.5
    Hindi      21.5
    Eng        21.0
    dtype: float64
    >>> # Mode calculation for Hindi marks
    >>> print(df['Hindi'].mode())
    0    24
    dtype: int64
    >>> # Quartile calculation (25%, 50%, 75%)
    >>> print(df.quantile(q=[0.25, 0.75], numeric_only=True))
    UT  Maths  Science   S.St  Hindi    Eng
    0.25 1.0  16.50    18.00  18.75  20.75  19.75
    0.75 3.0  22.25    21.25  22.50  24.00  23.00
    >>> # Variance and Standard Deviation
    >>> print(df[['Maths', 'Science']].var())
    Maths      15.840909
    Science     7.113636
    dtype: float64
    >>> print(df[['Maths', 'Science']].std())
    Maths      3.980064
    Science    2.667140
    dtype: float64
    >>> # Comprehensive summary using describe()
    >>> print(df.describe())
  

Aggregation transforms a dataset by applying mathematical operations to produce single or multiple summarized numeric values from arrays of data.

  
    >>> # Apply a single aggregation function to the entire DataFrame
    >>> print(df.aggregate('max'))
    Name       Zuhaire
    UT               3
    Maths           24
    dtype: object
    >>> # Apply multiple aggregation functions simultaneously
    >>> print(df.aggregate(['max', 'count']))
    Name  UT  Maths  Science  S.St  Hindi  Eng
    max   Zuhaire   3     24       25    25     25   24
    count      12  12     12       12    12     12   12
    >>> # Apply specific aggregations to specific columns with row-wise summation
    >>> print(df[['Maths', 'Science']].aggregate('sum', axis=1).head(3))
    0    43
    1    41
    2    33
    dtype: int64
  

Sorting arranges data elements in a specified order (ascending or descending) based on one or more columns.

  
    >>> # Sort by student Name in ascending order
    >>> print(df.sort_values(by=['Name']).head(4))
    Name  UT  Maths  Science  S.St  Hindi  Eng
    6   Ashravy   1     23       19    20     15   22
    7   Ashravy   2     24       22    24     17   21
    8   Ashravy   3     12       25    19     21   23
    9    Mishti   1     15       22    25     22   22
    >>> # Sort by English marks in descending order for Unit Test 3
    >>> dfUT3 = df[df.UT == 3]
    >>> print(dfUT3.sort_values(by=['Eng'], ascending=False))
    Name  UT  Maths  Science  S.St  Hindi  Eng
    2      Raman   3     14       19    15     24   23
    8    Ashravy   3     12       25    19     21   23
    11    Mishti   3     17       18    20     25   20
    5    Zuhaire   3     22       18    19     23   13
    >>> # Sorting by multiple columns (Science ascending, then Hindi ascending)
    >>> print(dfUT3.sort_values(by=['Science', 'Hindi']))
    Name  UT  Maths  Science  S.St  Hindi  Eng
    5    Zuhaire   3     22       18    19     23   13
    11    Mishti   3     17       18    20     25   20
    2      Raman   3     14       19    15     24   23
    8    Ashravy   3     12       25    19     21   23
  

The groupby() function splits the dataset into groups based on specified criteria, applies a function, and combines the results (Split-Apply-Combine strategy).

"The GROUP BY function works based on a split-apply-combine strategy: Step 1: Split the data into groups by creating a GROUP BY object. Step 2: Apply the required function. Step 3: Combine the results to form a new DataFrame."

---

Socio-Technical Analysis & Core Systemic Impact: Grouping allows educators and analysts to aggregate individual performance metrics (like unit test scores) into institutional insights (like student-wise averages or test-wise trends), shifting focus from raw transactional data to systemic performance evaluation.

  
    >>> # Group by Name
    >>> g1 = df.groupby('Name')
    >>> # Display first entry of each group
    >>> print(g1.first())
    UT  Maths  Science  S.St  Hindi  Eng
    Name
    Ashravy   1     23       19    20     15   22
    Mishti    1     15       22    25     22   22
    Raman     1     22       21    18     20   21
    Zuhaire   1     20       17    22     24   19
    >>> # Display size of each group
    >>> print(g1.size())
    Name
    Ashravy    3
    Mishti     3
    Raman      3
    Zuhaire    3
    dtype: int64
    >>> # Retrieve a single group
    >>> print(g1.get_group('Raman'))
    Name  UT  Maths  Science  S.St  Hindi  Eng
    0  Raman   1     22       21    18     20   21
    1  Raman   2     21       20    17     22   24
    2  Raman   3     14       19    15     24   23
    >>> # Grouping by multiple attributes (Name and UT)
    >>> g2 = df.groupby(['Name', 'UT'])
    >>> print(g2.first().head(3))
    >>> # Aggregation on groups: average marks per UT
    >>> print(df.groupby(['UT']).aggregate('mean'))
    Maths  Science   S.St  Hindi    Eng
    UT
    1   20.00    19.75  21.25  20.25  21.00
    2   21.50    19.50  21.75  22.00  20.75
    3   16.25    20.00  18.25  23.25  19.75
  

Indexes are used for fast retrieval of data. Pandas allows resetting, setting, or dropping indexes to align with analytical requirements.

  
    >>> # Slicing data creates non-continuous index
    >>> dfUT1 = df[df.UT == 1]
    >>> print(dfUT1)
    Name  UT  Maths  Science  S.St  Hindi  Eng
    0     Raman   1     22       21    18     20   21
    3   Zuhaire   1     20       17    22     24   19
    6   Ashravy   1     23       19    20     15   22
    9    Mishti   1     15       22    25     22   22
    >>> # Reset index to create a continuous sequence (keeps old index as a column)
    >>> dfUT1.reset_index(inplace=True)
    >>> print(dfUT1)
    index     Name  UT  Maths  Science  S.St  Hindi  Eng
    0      0    Raman   1     22       21    18     20   21
    1      3  Zuhaire   1     20       17    22     24   19
    >>> # Drop the old index column
    >>> dfUT1.drop(columns=['index'], inplace=True)
    >>> # Set a specific column as the index
    >>> dfUT1.set_index('Name', inplace=True)
    >>> print(dfUT1)
    UT  Maths  Science  S.St  Hindi  Eng
    Name
    Raman     1     22       21    18     20   21
    Zuhaire   1     20       17    22     24   19
    >>> # Revert back to default integer index
    >>> dfUT1.reset_index(inplace=True)
  

Reshaping changes the layout of a dataset to make it more readable and suitable for analysis. Pandas provides pivot() and pivot_table() for this purpose.

  
    >>> # Reshaping store sales data
    >>> pivot1 = df.pivot(index='Store', columns='Year', values='Total_sales(Rs)')
    >>> print(pivot1)
    Year      2016      2017     2018
    Store
    S1     12000.0   20000.0  30000.0
    S2         NaN   10000.0  11000.0
    S3    420000.0  450000.0  89000.0
    S4    330000.0       NaN      NaN
  

  
    >>> # Pivot table with duplicate entries, aggregating by mean
    >>> df1 = df.pivot_table(index=['Item', 'Color'])
    >>> print(df1)
    Price(Rs)  Units_in_stock
    Item   Color
    Pen    Blue            35.0            34.5
    Red             17.5            30.0
    Pencil Black            6.0            40.5
    >>> # Using multiple aggregate functions (sum, max, mean)
    >>> pivot_table1 = df.pivot_table(index='Item', columns='Color', values='Units_in_stock', aggfunc=[sum, max, 'mean'])
  

Real-world datasets often contain missing values, represented as NaN (Not a Number) in Pandas. These must be identified and resolved to prevent analytical errors.

"Missing values can be handled by either dropping the entire row having missing value or replacing it with an appropriate value."

---

Socio-Technical Analysis & Core Systemic Impact: Dropping rows with missing values reduces dataset size and can introduce selection bias if the missingness is non-random. Conversely, estimating/imputing values (e.g., using zero, mean, or forward-fill) preserves sample size but introduces artificial variance, potentially skewing downstream statistical models.

  
    >>> # Check for nulls in the entire DataFrame
    >>> print(df.isnull().any())
    Name       False
    UT         False
    Maths       True
    Science     True
    S.St       False
    Hindi      False
    Eng         True
    dtype: bool
    >>> # Count missing values per column
    >>> print(df.isnull().sum())
    Maths      1
    Science    1
    Eng        1
    dtype: int64
  

  
    >>> # Drop any row containing at least one NaN value
    >>> df_clean = df.dropna()
    >>> # Drop in-place for a specific subset
    >>> dfRaman.dropna(inplace=True, how='any')
  

  
    >>> # Replace NaN with 0 in Science marks
    >>> df['Science'].fillna(0, inplace=True)
    >>> # Forward fill (pad) English marks
    >>> df['Eng'].fillna(method='pad', inplace=True)
  

Integrating Pandas with relational databases like MySQL allows seamless data pipelines for loading raw transactional records and saving processed analytical results.

  
    >>> import pandas as pd
    >>> import sqlalchemy
    >>> # Create database engine connection
    >>> engine = sqlalchemy.create_engine('mysql+pymysql://root:smsmb@localhost:3306/CARSHOWROOM')
    >>> # Import table data using SQL query
    >>> df_inventory = pd.read_sql_query('SELECT * FROM INVENTORY', engine)
    >>> print(df_inventory.head(2))
  

  
    >>> # Export DataFrame to MySQL table
    >>> df.to_sql('showroom_info', engine, if_exists='replace', index=False)