Adding (mathematically) columns of a CSV based on information in another column with PowerShell

Question

I was having a really hard time describing what I need in the Title, so I apologize ahead of time if that makes absolutely no sense.

If I have a CSV that has 2 columns, one with a persons name and a second column with a numeric value I need to find the duplicates in the names column then add the numeric values for that person together to get a total number in a new CSV.

This is a very simplified version of the real CSV

Name,Number
Dog,1
Cat,2
Fish,1
Dog,3
Dog,2
Cat,2
Fish,1

Given the information above, what I would like to be able to produce is this:

Name,Number
Dog,6
Cat,4
Fish,2

I really don't have any idea how to get there or if it's possible with PowerShell. I can only get as far as using group-object to group by name, but I have no clue how to add the columns after that.

The biggest problem I'm coming across with my research on this is that most if not all the results I get when googling involve adding new columns to a csv and not performing the mathematical calculation.

I would call what you are describing as 'aggregating'. You can see a bash/awk answer at stackoverflow.com/questions/3934423/… — Daniel
– Daniel, Commented Jul 20, 2012 at 20:47

user1417978 · Accepted Answer · 2012-07-20 21:36:47Z

5

I finally got it

$csvfile = import-csv c:\csvfile.csv

$csvfile | group name | select name,@{Name="Totals";Expression={($_.group | Measure-Object -sum number).sum}}

Credit goes to:

http://www.hanselman.com/blog/ParsingCSVsAndPoorMansWebLogAnalysisWithPowerShell.aspx

answered Jul 20, 2012 at 21:36

user1417978

1331 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding (mathematically) columns of a CSV based on information in another column with PowerShell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related