[SOLVED] [bash] group duplicates and singles

hashbang#! · 04-04-2014, 07:21 AM

I have a CSV file containing people with addresses.

From this file I need to create 2 output files:
1) people who live in a single person household (one person per address)
2) people who live with other people at the same address

(I do not want to filter out duplicates, just split people into 2 different output files.)

The address is identified by columns 11-16.

What's the best way forward?

grail · 04-04-2014, 08:19 AM

What language and what have you tried and where are you stuck?

hashbang#! · 04-04-2014, 08:31 AM

bash: grep, sort, awk ...

I suspect it's a case for awk but I am not particularly good at awk scripts.

schneidz · 04-04-2014, 08:52 AM

maybe you can use cut to get columns 11-16.
then in a loop grep -c the address.
if it is equal to 1 then single else multi-dweller.

hashbang#! · 04-04-2014, 09:15 AM

I have already isolated the addresses with cut.

It's a shame that grep -c doesn't work in conjunction with -f rturning a count for each line in the filter file!

danielbmartin · 04-04-2014, 10:11 AM

Quote:

Originally Posted by hashbang#!

I have a CSV file containing people with addresses.

From this file I need to create 2 output files:
1) people who live in a single person household (one person per address)
2) people who live with other people at the same address

(I do not want to filter out duplicates, just split people into 2 different output files.)

The address is identified by columns 11-16.

What's the best way forward?

Help us to help you. Provide a sample input file (10-15 lines will do). Construct a sample output file which corresponds to your sample input and post both samples here. With "InFile" and "OutFile" examples we can better understand your needs and also judge if our proposed solution fills those needs.

Your description contains ambiguities, at least to my eye. Reading it literally, both output files contain only personal names, and no addresses. Is that what you really want?

Daniel B. Martin

grail · 04-04-2014, 10:28 AM

So you mention:

Quote:

bash: grep, sort, awk ...

I suspect it's a case for awk but I am not particularly good at awk scripts.

And then say:

Quote:

I have already isolated the addresses with cut.

It's a shame that grep -c doesn't work in conjunction with -f rturning a count for each line in the filter file!

Reading these it is hard to workout if you have made an attempt or not or are stuck but don't want to show where?

Again, provide examples, as suggested by Daniel, but then also show what code you have already done and where you are stuck?

This is not your first post, so one would think you know how to ask questions and what the usual response is from the people on the forums when you don't
seem to show any attempt.

danielbmartin · 04-04-2014, 05:14 PM

With this CSV InFile ...

Code:

Jackson Browne,13 Park Ave
Hank Williams,713 Violet Ave
Lou Reed,5 Lynn Rd
Gene Vincent,5 Lynn Rd
Ray Charles,13 Park Ave
Tom Petty,3333 Morningside Dr
Carly Rae Jepsen,9 Chestnut Ln
Willie Nelson,713 Violet Ave
Bob Dylan,713 Violet Ave

... this code ...

Code:

awk -F, -v W1=$Singles -v W2=$Cohabs  \
    '{a[$2]=a[$2] ", "$1; c[$2]++}
  END{for (j in a) {OutRec=j" => "substr(a[j],3);
     if (c[j]==1) print OutRec >W1
             else print OutRec >W2}}' $InFile 
echo "Singles ..."; cat  $Singles; echo "End Of File"
echo "Cohabs ...";  cat  $Cohabs;  echo "End Of File"

... produced this result ...

Code:

Singles ...
9 Chestnut Ln => Carly Rae Jepsen
3333 Morningside Dr => Tom Petty
End Of File
Cohabs ...
5 Lynn Rd => Lou Reed, Gene Vincent
713 Violet Ave => Hank Williams, Willie Nelson, Bob Dylan
13 Park Ave => Jackson Browne, Ray Charles
End Of File

Daniel B. Martin

hashbang#! · 04-21-2014, 10:34 AM

Thank you Daniel. That was what I'd been looking for.