Looping Through Columns in a Pandas DataFrame for Unique Values

When working with large datasets, it’s often necessary to process each column individually. In this case, we’re dealing with a pandas DataFrame containing approximately 52 columns. Our goal is to extract the unique values from each column without manually writing out code for every single one.

Understanding Pandas DataFrames and Series

Before diving into looping through columns, let’s take a brief look at what pandas DataFrames and Series are. A pandas DataFrame is a two-dimensional data structure consisting of rows and columns, similar to an Excel spreadsheet or a table in a relational database. Each column represents a variable, while each row corresponds to a single observation.

On the other hand, a pandas Series is a one-dimensional labeled array, equivalent to a column in our DataFrame. Series are useful for handling time series data or variables with missing values.

Looping Through Columns

Now that we’ve covered the basics of DataFrames and Series, let’s get back to looping through columns. We’ll explore three different approaches: using print, leveraging the unique function, applying a lambda function with apply, and utilizing the drop_duplicates method.

Approach 1: Using print

The simplest way to print unique values from each column is to use the following code snippet:

for col in df.columns:
    print(col, df[col].unique())

This code iterates through each column (represented by the df.columns attribute), printing the column name followed by its unique values. Note that this approach can be cumbersome for large DataFrames.

Approach 2: Using unique with apply

Another way to achieve the same result is to use the unique function in combination with the apply method:

df1 = df.apply(lambda x: pd.Series(x.unique()))
print(df1)

In this code, we apply a lambda function to each column. The lambda function takes a Series as input and returns its unique values as a new Series.

Here’s an example of how this approach produces the output:

df = pd.DataFrame({'A':[1,2,3],
                   'B':[4,4,6],
                   'C':[9,9,9],
                   'D':[1,1,5],
                   'E':[5,3,3],
                   'F':[7,4,7]})

print(df)
   A  B  C  D  E  F
0  1  4  9  1  5  7
1  2  4  9  1  3  4
2  3  6  9  5  3  7

df1 = df.apply(lambda x: pd.Series(x.unique()))
print(df1)
   A    B    C    D    E    F
0  1  4.0  9.0  1.0  5.0  7.0
1  2  6.0  NaN  5.0  3.0  4.0
2  3  NaN  NaN  NaN  NaN  NaN

As you can see, the output is a new DataFrame with unique values from each original column.

Approach 3: Using drop_duplicates

A third approach to extracting unique values involves using the drop_duplicates method:

df1 = df.apply(lambda x: x.drop_duplicates())
print(df1)

In this code, we apply a lambda function to each column. The lambda function takes a Series as input and returns its duplicates removed.

Here’s an example of how this approach produces the output:

df = pd.DataFrame({'A':[1,2,3],
                   'B':[4,4,6],
                   'C':[9,9,9],
                   'D':[1,1,5],
                   'E':[5,3,3],
                   'F':[7,4,7]})

print(df)
   A  B  C  D  E  F
0  1  4  9  1  5  7
1  2  4  9  1  3  4
2  3  6  9  5  3  7

df1 = df.apply(lambda x: x.drop_duplicates())
print(df1)
   A    B    C    D    E    F
0  1  4.0  9.0  1.0  5.0  7.0
1  2  NaN  NaN  NaN  3.0  4.0
2  3  6.0  NaN  5.0  NaN  NaN

As you can see, the output is a new DataFrame with duplicates removed from each original column.

Choosing the Right Approach

Each approach has its own trade-offs and use cases. Here’s a brief summary:

• Using print is simple but can be cumbersome for large DataFrames.
• Applying unique with apply produces a new DataFrame with unique values, but may require additional processing if you need to handle missing values or outliers.
• Utilizing drop_duplicates removes duplicates from each column, but may not preserve the original order of elements.

Ultimately, the choice of approach depends on your specific requirements and the characteristics of your data.

Last modified on 2023-05-10