Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-pro· GoogleHugging Face LMArena Human Preference 140K

give me cheatsheet for dataframe df. so that i can remember how to manipulate for better…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Geminigemini-2.5-flash-preview-04-17’s answer.
  1. User

    give me cheatsheet for dataframe df. so that i can remember how to manipulate for better analysis

  2. Gemini Assistant

    Of course! Here is a comprehensive Pandas DataFrame cheat sheet focused on data manipulation for analysis.

    I've structured it to follow a typical data analysis workflow.

    Pandas DataFrame Cheat Sheet (df)

    First, let's assume you have a DataFrame named df.

    import pandas as pd
    import numpy as np
    
    # Sample DataFrame for examples
    data = {
        'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eva', 'Frank'],
        'age': [24, 27, 22, 32, 24, 45],
        'city': ['New York', 'Los Angeles', 'Chicago', 'New York', 'Chicago', 'Los Angeles'],
        'score': [88.5, 91.2, np.nan, 78.0, 91.2, 84.3],
        'join_date': pd.to_datetime(['2021-10-01', '2022-01-15', '2021-11-20', '2023-05-10', '2022-01-15', '2020-03-07'])
    }
    df = pd.DataFrame(data)
    

    1. Inspecting Your Data (Getting a First Look)

    Get a quick understanding of your DataFrame's structure and content.

    # Display the first 5 rows
    df.head()
    
    # Display the last 5 rows
    df.tail()
    
    # Get the dimensions (rows, columns)
    df.shape
    
    # Get a concise summary of the DataFrame
    df.info() # Very useful for checking data types and non-null counts
    
    # Get descriptive statistics for numerical columns
    df.describe()
    
    # List all column names
    df.columns
    
    # Get the data types of each column
    df.dtypes
    
    # Count unique values in a column
    df['city'].value_counts()
    

    2. Selection & Slicing (Subsetting Data)

    Accessing specific parts of your data.

    GoalSyntaxExample
    Select a single columndf['col_name'] or df.col_namedf['age']
    Select multiple columnsdf[['col1', 'col2']]df[['name', 'score']]
    Select rows by label/indexdf.loc[label]df.loc[0]
    Select rows by integer positiondf.iloc[position]df.iloc[0]
    Slice rows by integer positiondf.iloc[start:end]df.iloc[1:4]
    Select specific rows & columns by labeldf.loc[rows, cols]df.loc[0:2, ['name', 'city']]
    Select specific rows & columns by positiondf.iloc[rows, cols]df.iloc[0:3, 0:2]
    Select a single value by labeldf.at[row_label, col_label]df.at[0, 'name']
    Select a single value by positiondf.iat[row_pos, col_pos]df.iat[0, 0]

    3. Filtering (Conditional Selection)

    Selecting rows based on conditions.

    # Single condition
    df[df['age'] > 30]
    
    # Multiple conditions (AND) - Use &
    df[(df['age'] > 25) & (df['city'] == 'New York')]
    
    # Multiple conditions (OR) - Use |
    df[(df['age'] < 25) | (df['city'] == 'Los Angeles')]
    
    # Filtering using .isin() for multiple possible values
    df[df['city'].isin(['Chicago', 'New York'])]
    
    # Filtering to find non-null values
    df[df['score'].notna()] # or df[df['score'].notnull()]
    
    # Filtering to find null values
    df[df['score'].isna()] # or df[df['score'].isnull()]
    
    # Using string methods in filters
    df[df['name'].str.startswith('A')]
    

    Important: Always wrap your conditions in parentheses () when combining them with & or |.


    4. Data Cleaning & Manipulation

    Modifying the DataFrame.

    # --- Handling Missing Values ---
    # Drop rows with any missing values
    df.dropna()
    
    # Drop columns with any missing values
    df.dropna(axis=1)
    
    # Fill missing values with a specific value (e.g., 0)
    df['score'].fillna(0)
    
    # Fill missing values with the mean of the column
    df['score'].fillna(df['score'].mean())
    
    
    # --- Adding / Modifying Columns ---
    # Add a new column with a default value
    df['status'] = 'Active'
    
    # Add a new column based on existing columns
    df['age_in_5_years'] = df['age'] + 5
    
    # Apply a function to a column
    df['name_lower'] = df['name'].apply(str.lower)
    
    # Apply a lambda function for more complex logic
    df['is_senior'] = df['age'].apply(lambda x: 'Yes' if x > 40 else 'No')
    
    
    # --- Renaming & Dropping ---
    # Rename specific columns
    df.rename(columns={'name': 'full_name', 'score': 'final_score'})
    
    # Drop columns
    df.drop(columns=['join_date', 'status'])
    
    # Drop rows by index label
    df.drop(index=[0, 2])
    
    
    # --- Changing Data Types ---
    # Change a column's data type
    df['age'] = df['age'].astype(float)
    
    
    # --- Removing Duplicates ---
    # Find duplicate rows
    df.duplicated()
    
    # Drop duplicate rows (keeps the first occurrence)
    df.drop_duplicates()
    
    # Drop duplicates based on a specific column
    df.drop_duplicates(subset=['city'])
    

    5. Grouping & Aggregating

    Summarizing data is key for analysis. The groupby is your best friend.

    # Group by a single column and get the mean of other columns
    df.groupby('city').mean()
    
    # Group by a column and get the sum
    df.groupby('city')['score'].sum()
    
    # Group by multiple columns
    df.groupby(['city', 'age']).size() # or .count()
    
    # Use .agg() for multiple aggregations at once
    df.groupby('city').agg(
        avg_score=('score', 'mean'),
        max_age=('age', 'max'),
        count=('name', 'count')
    )
    

    Common aggregation functions: mean(), sum(), min(), max(), count(), size(), std(), var().


    6. Sorting

    Ordering your data to find top/bottom values.

    # Sort by a single column (ascending by default)
    df.sort_values(by='age')
    
    # Sort by a single column (descending)
    df.sort_values(by='score', ascending=False)
    
    # Sort by multiple columns
    df.sort_values(by=['city', 'age'], ascending=[True, False])
    

    7. Combining DataFrames

    Merging and concatenating data from different sources.

    # Assume df1 and df2 are two separate DataFrames
    
    # --- Concatenating (Stacking) ---
    # Stack rows on top of each other (must have same columns)
    pd.concat([df1, df2])
    
    # Stack columns side-by-side
    pd.concat([df1, df2], axis=1)
    
    
    # --- Merging (SQL-style Joins) ---
    # Inner join on a common key
    pd.merge(df1, df2, on='key_column', how='inner')
    
    # Left join
    pd.merge(df1, df2, on='key_column', how='left')
    
    # Right join
    pd.merge(df1, df2, on='key_column', how='right')
    
    # Outer join
    pd.merge(df1, df2, on='key_column', how='outer')
    

    8. Working with Special Data Types

    Text Data (.str accessor)

    df['name'].str.lower()       # Convert to lowercase
    df['name'].str.upper()       # Convert to uppercase
    df['city'].str.contains('New') # Returns boolean Series
    df['city'].str.replace(' ', '_') # Replace characters
    

    Datetime Data (.dt accessor)

    Assumes 'join_date' column is datetime type (pd.to_datetime())

    df['join_date'].dt.year      # Extract year
    df['join_date'].dt.month     # Extract month
    df['join_date'].dt.day       # Extract day
    df['join_date'].dt.dayofweek # Extract day of the week (Mon=0, Sun=6)
    df['join_date'].dt.strftime('%Y-%m-%d') # Format date to string
    

    9. Saving Your Results

    Exporting your manipulated DataFrame.

    # Save to a CSV file (without the DataFrame index)
    df.to_csv('output_data.csv', index=False)
    
    # Save to an Excel file
    df.to_excel('output_data.xlsx', index=False)
    

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.