-2

I'm very new to python. Would like to seek your advise.

I have this simple dataframe list of items workers request for.

I tried this to get unique values:

import numpy as np
import pandas as pd
df = pd.read_csv('item.csv')

column_values=df[['Name', 'Item']].values

unique_values = np.unique(column_values)

print(unique_values)
print('Total no. of items: ')
print('No. of hand sanitizer: ')
print('No. of mask: ')
print('No. of wet tissue: ')
print('\n')

I'm not sure how to code the counting and which type of loop to use for the name.

I tried a few coding to get it list the name of the requestor, total no. of items requested, total of each item but cannot get the output as below by using loop:

My desired output would be like this:

Eric

Total no. of items: 11

No. of hand sanitizer: 5

No. of mask: 3

No. of wet tissue: 3

Farhana

Total no. of items: 9

No. of hand sanitizer: 5

No. of mask: 2

No. of wet tissue: 2

.... and so on

Appreciate your advise and input.

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
Diyah
  • 1
  • you can check [`groupby`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html) – Tugay May 22 '21 at 13:32
  • 1
    I’d agree groupby is the way to go – Daniel Butler May 22 '21 at 13:37
  • 1
    Welcome to Stack Overflow! Please include a _small_ subset of your data as a __copyable__ piece of code that can be used for testing as well as your expected output for the __provided__ data. See [MRE - Minimal, Reproducible, Example](https://stackoverflow.com/help/minimal-reproducible-example), and [How to make good reproducible pandas examples](https://stackoverflow.com/q/20109391/15497888). – Henry Ecker May 22 '21 at 13:49

2 Answers2

1

An example of your desired result would be great (e.g. from input to output).

I think this is what you need:

df = df.groupby('Name')['Items'].count()

which gives the number of items for each name.

And with the following code you get the counting for each item.

df = df.groupby(['Name', 'Item'])['Item'].count()
Peter Julian
  • 126
  • 9
0

You'd want to use pandas .groupby() method and Counter() function from the collections module in Python's standard library.

import pandas as pd
from collections import Counter

# Create pandas dataframe
df = pd.DataFrame({
    'names' : ['Eric', 'Eric', 'Eric', 'Eric', 'Farhana', 'Farhana', 'Farhana'],
    'items' : ['a', 'b', 'c', 'a', 'a', 'b', 'a'] 
})

# Assign unique `items` to items variable
items = df['items'].unique()

# Use groupby and counter
gb = df.groupby('names')['items'].agg(['count', Counter])

# Print results
for name in gb.index:
    print(name)
    print(f"Total no. of items: {gb.loc[name, 'count']}")
    for item in items:
        print(f"No of {item}: {gb.loc[name, 'Counter'].get(item, 0)}")

Output:

Eric
Total no. of items: 4
No of a: 2
No of b: 1
No of c: 1
Farhana
Total no. of items: 3
No of a: 2
No of b: 1
No of c: 0

If you don't want to print zeros, just loop over the Counter's keys.

for name in gb.index:
    print(name)
    print(f"Total no. of items: {gb.loc[name, 'count']}")
    for item in gb.loc[name, 'Counter'].keys():
        print(f"No of {item}: {gb.loc[name, 'Counter'][item]}")

Output:

Eric
Total no. of items: 4
No of a: 2
No of b: 1
No of c: 1
Farhana
Total no. of items: 3
No of a: 2
No of b: 1
Gusti Adli
  • 1,225
  • 4
  • 13
  • Hi Gusti Thank you for your inputs. If I need it based on columns with header eg: 'Name' and 'Item' is it possible? – Diyah May 22 '21 at 15:45
  • possible? yes. how? you're better of printing `df`, `items`, and `gb` from my answer and see what they do. Read the documentation of each method/function linked in the answer if you're confused. – Gusti Adli May 22 '21 at 15:59