Getting most common value from a list of values

This topic contains 10 replies, has 5 voices, and was last updated by Profile photo of Max Kozlov Max Kozlov 3 days, 2 hours ago.

  • Author
    Posts
  • #58954
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    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

  • #58956
    Profile photo of Alex Aymonier
    Alex Aymonier
    Participant

    Take a look at Group-Object

  • #58959
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    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

  • #58989
    Profile photo of Ron
    Ron
    Participant

    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}}
    • #58995
      Profile photo of Olaf Soyk
      Olaf Soyk
      Participant

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

    • #59002
      Profile photo of Ron
      Ron
      Participant

      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.

  • #59097
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    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

  • #59098
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

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

  • #59124
    Profile photo of Max Kozlov
    Max Kozlov
    Participant
    $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 } }
    
    • #59125
      Profile photo of Scott Windmiller
      Scott Windmiller
      Participant

      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

  • #59217
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    for perfectness you need one more column in your csv – 'Perfect' 🙂
    or any other well distinguished method – which phone more common than others

You must be logged in to reply to this topic.