Getting most common value from a list of values

PowerShell Help
1

I have a list of users with phone numbers they have entered in, sometimes not using their primary phone number. I want to pull out the common most used number from the list and assign it to that user. For example:

I have a csv with:
John Smith 111-111-1111
John Smith 222-222-2222
John Smith 222-222-2222
John Smith 222-222-2222
John Smith 333-333-3333
Joe Black 444-444-4444
Joe Black 444-444-4444
Joe Black 555-555-5555
Joe Black 666-666-6666
Joe Black 777-777-7777

The result would list:
John Smith 222-222-2222
Joe Black 444-444-4444

Can someone give an example of what could use?

Thanks,
Scott

2

Take a look at Group-Object

3

Thanks, that does help.

I still think I may need some help getting me started. I guess I would have to have a Unique value for the name field and then the highest count number for the phone number field using the Group-Object cmdlet. Not really sure how to implement that though.

Thanks,
Scott

4

Assuming the CSV has 2 fields, Name and Phone.

$csv|group phone|sort count -desc|select -first 2 |select @{name='Name';expression={$_.group[0].Name}},@{name='Phone';expression={$_.group[0].Phone}}
5

@Ron - select -first 2 ??? What if he wants to work with more than just the 2 examples here?

6

I was just reproducing his output sample. I didn’t look close enough to see that there were only 2 names. Thanks for catching that, that section can be removed to process the whole list.

7

Thanks for the help!

The CSV which looks like this:

Name       Number      
----       ------      
John Smith 111-111-1111
John Smith 222-222-2222
John Smith 222-222-2222
John Smith 222-222-2222
John Smith 333-333-3333
Joe Black  444-444-4444
Joe Black  444-444-4444
Joe Black  555-555-5555
Joe Black  666-666-6666
Joe Black  777-777-7777

The script above gave me this:

Name       Phone       
----       -----       
John Smith 222-222-2222
Joe Black  444-444-4444
John Smith 111-111-1111
John Smith 333-333-3333
Joe Black  555-555-5555
Joe Black  666-666-6666
Joe Black  777-777-7777

This did only give me one of the duplicates but what I was hoping for was to only have 1 result for each Name so the desired result for above would be:

Name       Phone       
----       -----       
John Smith 222-222-2222
Joe Black  444-444-4444

If the name only has 1 number associated with it then it can just display that but for names with multiple numbers I only want to show one name with the highest count.

Thanks for helping me out!
Scott

8

And what if a name has several different numbers but none of them more than once? :wink:

9 
$csv | Group-Object -Property Name | Select-Object Name, @{n='Phone'; e = { $_.Group | Group-Object -Property Number | Sort-Object -Property Count -Descending | Select-Object -First 1 -ExpandProperty Name } }
10

This works great! Thanks so much.

Olaf Soyk,
I had not thought of that, good point. Looks like the script above will just pick the first number if there are several different numbers but none of them more than once. This is fine. This does not have to be absolutely perfect so this is fine.

Thanks for all the help!

Scott

11

for perfectness you need one more column in your csv - ‘Perfect’ :slight_smile:
or any other well distinguished method - which phone more common than others