Pandas: Fill Rows if 2 Column Strings are the Same

In this article, we will explore how to use Python’s pandas library to fill rows in a DataFrame based on conditions applied to two column strings.

Introduction to Pandas and DataFrames

Pandas is a powerful library for data manipulation and analysis in Python. It provides data structures such as Series (1-dimensional labeled array) and DataFrame (2-dimensional labeled data structure with columns of potentially different types).

A DataFrame is similar to an Excel spreadsheet or a table in a relational database. Each column represents a variable, and each row represents a single observation.

Problem Statement

We have a sample DataFrame df that contains information about schools, students, countries, and states. The goal is to fill the missing values in the state column with existing state names from the same school and country combination.

import pandas as pd
school = ['Univ of CT','Univ of CT','Oxford','Oxford','ABC Univ']
name = ['John','Matt','John','Ashley','John']
country = ['US','US','UK','UK','']
state = ['CT','','','ENG','']
df = pd.DataFrame({'school':school,'country':country,'state':state,'name':name})

Current DataFrame

Here’s the current state of our DataFrame:

Solution Overview

To solve this problem, we can create a function called find_state that takes three arguments: the school, country, and state. This function will check if the state is missing (i.e., empty or None). If it’s not missing, it returns the state value.

If the state is missing, it looks up the existing state values in the DataFrame where the school and country match and returns the maximum state value.

Creating the find_state Function

Here’s how you can create this function:

def find_state(school, country, state):
    if len(state) > 0:
        return state
    found_state = df['state'][(df['school'] == school) & (df['country'] == country)]
    return max(found_state)

This function will be used to fill the missing values in our DataFrame.

Applying the find_state Function to the DataFrame

Now that we have our find_state function, let’s apply it to the state column of our DataFrame. We can use a list comprehension to create a new column called state_new where each value is determined by calling our find_state function:

df['state_new'] = [find_state(school, country, state) for school, country, state in 
                   df[['school','country','state']].values]
print(df)

This will give us the following output:

As you can see, our find_state function successfully filled in the missing state values based on the school and country combinations.

Using GroupBy to Find Missing Values

We also want to know how many schools and countries are represented in our DataFrame. We can use the groupby method of pandas DataFrames to do this:

df_grouped = df.groupby(['school', 'country']).count()
print(df_grouped)

This will give us a new DataFrame that contains the count of rows for each school-country combination.

We can see that our find_state function correctly filled in all the missing state values.

Conclusion

In this article, we explored how to use Python’s pandas library to fill rows in a DataFrame based on conditions applied to two column strings. We created a function called find_state that takes three arguments: the school, country, and state. This function checks if the state is missing and looks up the existing state values in the DataFrame where the school and country match.

We then applied this function to the state column of our DataFrame using a list comprehension and print the resulting DataFrame with filled-in missing state values.

Last modified on 2025-02-16