Group name into same group

Asked

Viewed 70 times

1

I have a sales chart with sales names, products and other information imported from a spreadsheet xlsx.

But it turns out that the names of the sellers appear repeated according to sales, for example:

João  - Venda de produto A 
Marco - Venda de produto B
João - Venda de produto B
João - Venda de produto A

In the example above, I wanted them to add up all the sales of "João" to compare with those of "Marco" for example, and not that the chart repeated his name ad infinitum.

python

  • How do you have the data originally? In what format? List, csv, as you put it (string)?

  • The file is an excel table in xlsx - ai has the column "sellers" which as said to each sale repeats the name of the guy. Here comes other information like values, etc etc

  • Here, follow a screen print link

  • You see, "Andre" comes up three times. The "Paul" 2...was to appear the total of each together and not separate to make the sales comparison.

2 answers

2

Assuming you have a list of tuples of the type:

[ ('JOAO', 10.50), ( 'JOAO', 33.10 ), ('MARCO', 4.00 ), ('JOAO', 15.99), ('MARCO', 21.54) ]

How about:

vendas = [ ('JOAO', 10.50), ( 'JOAO', 33.10 ), ('MARCO', 4.00 ), ('JOAO', 15.99), ('MARCO', 21.54) ]

totais = {}

for vendedor, venda in vendas:
    if vendedor in totais:
        totais[vendedor] += venda
    else:
        totais[vendedor] = venda

print(totais)

Exit:

{'MARCO': 25.54, 'JOAO': 59.59}

You can also use a defaultdict to simplify the solution:

from collections import defaultdict

vendas = [ ('JOAO', 10.50), ( 'JOAO', 33.10 ), ('MARCO', 4.00 ), ('JOAO', 15.99), ('MARCO', 21.54) ];

totais = defaultdict(float)

for vendedor, venda in vendas:
    totais[vendedor] += venda

print(dict(totais))

Exit:

{'MARCO': 25.54, 'JOAO': 59.59}

  • And how would I do that with an imported file. xlsx ?

  • I am pressing enter unintentionally...well, there is a column where only the names are found, and for example until the line 55 only has "Paulo", then "Andre" and so on with other sellers.

  • Without a sample of the input file and a good explanation of what you mean by "imported" it is difficult to elaborate a more appropriate response.

  • Follow print of input file link

  • 1

    You don’t even have to care defaultdict in this case: https://answall.com/questions/271026/como-creat-um-dicionario-e-auto-incrementar-values/271029#271029

1

To read the xlsx file you can use the openpyxl library. It can be installed using Pip:

pip install openpyxl

Based on Lacobus' response, the script would look like this:

from openpyxl import load_workbook
wb = load_workbook(filename='nome_do_arquivo.xlsx')

totais = {}

for row in wb['nome_da_planilha']:
    vendedor = row[0].value
    if vendedor in totais:
        totais[vendedor] += 1
    else:
        totais[vendedor] = 1

print(totais)

Browser other questions tagged python

You are not signed in. Login or sign up in order to post.