In pandas, how to count items between commas, dividing between column types?

Question

In python3 and pandas I have this dataframe:

df_selecao_atual.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 340 entries, 2 to 678
Data columns (total 26 columns):
cpf                                339 non-null object
nome                               340 non-null object
nome_completo                      340 non-null object
partido                            339 non-null object
uf                                 339 non-null object
cargo_parlamentar                  340 non-null object
tipo                               340 non-null object
classe                             340 non-null object
numero                             340 non-null object
único                              340 non-null object
assunto                            340 non-null object
data_inicial                       340 non-null object
data_final                         340 non-null object
andamento                          340 non-null object
link                               340 non-null object
transparencia                      339 non-null object
conferencia                        339 non-null object
data_conferencia                   339 non-null object
resumo                             332 non-null object
observacao                         311 non-null object
link_noticia_tribunal_confiavel    104 non-null object
interessa                          337 non-null object
ministro_relator                   339 non-null object
processo_conectado                 44 non-null object
situacao                           337 non-null object
cadastro_push                      337 non-null object
dtypes: object(26)
memory usage: 71.7+ KB

Each line of this dataframe has information about legal proceedings, one court case per line

The column "nome" has names of people, such as:

FULANO DE TAL
BELTRANO DA SILVA
SICRANO APARECIDO
NINGUEM AUGUSTO

The "tipo" column has the types of lawsuits, only two types:

INQ
AP

And column "resumo" has crimes investigated for prosecution in court proceedings. But each legal process may one or more crimes. And the crimes are separated by ",":

Peculato, Lavagem de Dinheiro
Corrupção passiva, Ocultação de bens, Lavagem de dinheiro
Corrupção passiva, Lavagem de dinheiro, Crimes Eleitorais
Crimes Eleitorais, Lavagem de dinheiro
Peculato
Quadrilha ou Bando, Crimes da Lei de licitações, Peculato

I need to count:

In each name
Divided by INQ and AP processes
The appearance of each individual crime between ","

Taking the example above the "resumo" column, something like this:

Peculato, Lavagem de dinheiro
Corrupção passiva, Ocultação de bens, Lavagem de dinheiro
Corrupção passiva, Lavagem de dinheiro, Crimes Eleitorais
Crimes Eleitorais, Lavagem de dinheiro
Peculato
Quadrilha ou Bando, Crimes da Lei de licitações, Peculato

All are lawsuits against the person "John Doe". The first two lines are of type "AP" and the remaining "INQ"

Then John Doe has:

1 AP for Peculato
2 AP for Lavagem de dinheiro
1 AP for Corrupção passiva
1 AP for Ocultação de bens

1 INQ for Corrupção passiva
2 INQ for Lavagem de dinheiro
2 INQ for Crimes Eleitorais
2 INQ for Peculato
1 INQ for Quadrilha ou Bando
1 INQ for Crimes da Lei de licitações

I thought about using PivotTable, but I have the problem of having to count type of crime only once, in each type of court case. Did not work

df_selecao_atual.pivot_table(index=['nome', 'tipo', 'resumo'],aggfunc='size').reset_index()

Please, does anyone know how I could do it?

-/-

A sample of the rows look like

df_selecao_atual[['tipo', 'resumo', 'nome']].head(5).to_dict()
{'tipo': {2: 'INQ', 3: 'AP', 4: 'INQ', 5: 'INQ', 6: 'AP'},
 'resumo': {2: 'Desvio de verbas públicas',
  3: 'Desvio de verbas públicas',
  4: nan,
  5: 'Prestação de contas rejeitada',
  6: 'Peculato, Gestão fraudulenta'},
 'nome': {2: 'CÉSAR MESSIAS',
  3: 'CÉSAR MESSIAS',
  4: 'FLAVIANO MELO',
  5: 'FLAVIANO MELO',
  6: 'FLAVIANO MELO'}}

Hi reinaldo, can you posts a sample of `df[['tipo', 'resumo', 'nome']].head(5).to_dict()` ? (with the data masked so not to disclose the real information)? Just wanted to know how the rows look like — rafaelc, Aug 11 '18 at 10:57

score 2 · Accepted Answer · answered Aug 11 '18 at 11:22

Using your sample data, you can aggregate by ', ' and then use Counter

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


nome           tipo
CÉSAR MESSIAS  AP                       {'Desvio de verbas públicas': 1}
               INQ                      {'Desvio de verbas públicas': 1}
FLAVIANO MELO  AP      {'Peculato': 1, 'Gestão fraudulenta': 1, 'Desv...
               INQ                  {'Prestação de contas rejeitada': 1}
Name: resumo, dtype: object

Now it gets easier to format this information the way you want. For example,

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

for _, g in df2.groupby('nome'):
    x = f'{_} has: '
    a = [f'{v} {t} for {k}' for d,t in zip(g.resumo, g.tipo) for (k, v) in d.items()]
    lines = x + '\n'.join(a)
    print(lines)

which yields

CÉSAR MESSIAS has: 
1 AP for Desvio de verbas públicas
1 INQ for Desvio de verbas públicas
FLAVIANO MELO has: 
1 AP for Peculato
1 AP for Gestão fraudulenta
1 AP for Desvio de Verbas públicas
1 AP for Prestação de contas rejeitada
1 INQ for Prestação de contas rejeitada

Thank you very much. I tried the first command: df2 = df_selecao_atual.dropna().groupby(['nome', 'tipo']).resumo.agg(', '.join).str.split(', ').agg(Counter).reset_index() — Reinaldo Chaves, Aug 11 '18 at 12:37
But I had this error message: NameError: name 'Counter' is not defined — Reinaldo Chaves, Aug 11 '18 at 12:37

score 1 · Answer 2 · answered Aug 11 '18 at 11:20

1

Before being able to use pivot, group by or aggregate functions to get your summary statistics, be sure that your dataframe is in a tidy format:
basically 1 observation/row, 1 variable/column, 1 value/cell.
In your case, you have list elements (several values) in your "resumo" column.
You need to change that.
I would use the same solution as given by this post to "distribute" those list elements over rows:

df_serie = df_selecao_atual.apply(lambda x: pd.Series(x['resumo']),axis=1).stack().reset_index(level=1, drop=True)  
df_serie.name = 'resumo'
df_selecao_atual.drop('resumo', axis=1).join(df_serie)

answered Aug 11 '18 at 11:20

3pwd

119
2
5

Thank you. On each line do I need to create a new column for each item in the summary column? – Reinaldo Chaves Aug 11 '18 at 12:43
I tried: df_serie = df_selecao_atual.apply(lambda x: pd.Series(x['resumo']),axis=1).stack().reset_index(level=1, drop=True) – Reinaldo Chaves Aug 11 '18 at 12:44
And: df_serie.name = 'resumo' – Reinaldo Chaves Aug 11 '18 at 12:44
And: new = df_selecao_atual.drop('resumo', axis=1).join(df_serie) – Reinaldo Chaves Aug 11 '18 at 12:45
But the generated dataframe has a "resumo" column with the same content – Reinaldo Chaves Aug 11 '18 at 12:45

In pandas, how to count items between commas, dividing between column types?

2 Answers2

Linked