Creating a DataFrame from a Column of Comma-Separated Values

When working with data in Python, it’s not uncommon to encounter columns that contain comma-separated values (CSVs). In this blog post, we’ll explore how to create a DataFrame from such a column using the popular Pandas library.

Introduction

The question at hand involves a DataFrame df with columns “nome”, “tipo”, and “resumo”. The “resumo” column contains a list of crimes investigated for prosecution in court proceedings, separated by commas. We want to create a new DataFrame that shows the count of each crime type for each name, divided by INQ and AP processes.

Understanding the Problem

To tackle this problem, we need to understand how Pandas handles comma-separated values (CSVs). When working with CSVs in Pandas, we can use the str.split() function to split the string into individual elements. In this case, we want to split the “resumo” column into individual crimes.

Solution 1: Using Split and Join

One approach is to use the split() function to split the “resumo” column into individual crimes, and then join them back together with the rest of the DataFrame using the join() method.

s = (df.pop('resumo').str.split(',', expand=True)
     .stack()
     .reset_index(level=1, drop=True)
     .rename('resumo'))

df = df.join(s).groupby(['nome','tipo','resumo']).size().reset_index(name='count')
print(df)

In this code snippet:

• We use str.split() to split the “resumo” column into individual crimes. The expand=True parameter ensures that each element becomes a separate Series.
• We then use stack() to transform the resulting Series into a new DataFrame with a single column “resumo”.
• We reset the index of the resulting DataFrame using reset_index(level=1, drop=True) to remove the original index.
• Finally, we rename the column “resumo” back to its original name and join it with the rest of the DataFrame using join().
• We group the resulting DataFrame by “nome”, “tipo”, and “resumo”, and then use size() to count the occurrences of each crime type.

Solution 2: Using Counter

Another approach is to use the Counter class from the collections module to split the “resumo” column into individual crimes. This approach can be useful when you want to create a dictionary-like object that maps keys to values.

s = df.dropna().groupby(['nome', 'tipo']).resumo.agg(', '.join).str.split(', ').agg(Counter)
print(s)

In this code snippet:

• We use dropna() to remove rows with missing values in the “resumo” column.
• We then group the resulting DataFrame by “nome” and “tipo”, and aggregate the “resumo” column using agg(', '.join).
• This produces a Series where each element is a string containing all crimes separated by commas.
• We then use str.split() to split each string into individual crimes. The resulting Series is another Series with comma-separated values as keys.
• Finally, we use agg(Counter) to convert the resulting Series into a dictionary-like object that maps keys (crimes) to values (counts).

Combining Solutions

We can combine both solutions by creating a new DataFrame from the first solution and then adding additional columns using the second solution.

df1 = (df.pop('resumo').str.split(',', expand=True)
       .stack()
       .reset_index(level=1, drop=True)
       .rename('resumo'))

s = df.dropna().groupby(['nome', 'tipo']).resumo.agg(', '.join).str.split(', ').agg(Counter)

df2 = pd.DataFrame(s.values.tolist(), index=s.index).stack().astype(int).reset_index(name='count').rename(columns={'level_2':'resumo'})

print(df1.join(df2))

In this code snippet:

• We create a new DataFrame df1 using the first solution.
• We then use the second solution to create another Series s.
• We convert the Series s into a DataFrame df2 and stack it with the original index.
• Finally, we join the two DataFrames together.

Conclusion

In this blog post, we explored how to create a DataFrame from a column of comma-separated values using Pandas. We presented two approaches: one that uses splitting and joining, and another that uses the Counter class. By combining these solutions, we can create a powerful tool for working with comma-separated data in Python.

Last modified on 2023-11-15