Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have a csv files with say 20 odd columns.

I need to get the 14th and 15th column values where value in 14th column is say "VALUE1" and value in 15th column is "VALUE2".

My condition gets satisfied only when 14th column has VALUE1 and 15th column has VALUE2. I need to get the aggregate count.

I think wc -l could give me the count list and cut -d "," -f14,15 gives me the 14th and 15th column values. But how to check whether 14th has VALUE1 and 15th has VALUE2?

UPD: I think the one below works

grep -r "" * | cut -d " " -f14,15 | grep "Value1" | grep "Value2"

but still I dont think this is the perfect way to do do it.

share|improve this question
    
Fyi, you use different delimiters in your cut commands. –  maxschlepzig Feb 18 '12 at 10:39
    
Your grep fails if you have "Value1Value2" in one of the 14th or 15th field. The right way to do it would be to grep on "^Value1 Value2$" –  jfgagne Feb 18 '12 at 12:35
    
@Ebbu please accept the answer which helped you to solve this issue so it will help others. Thank you! –  Rany Albeg Wein Apr 26 '13 at 15:49
add comment

3 Answers 3

awk could be more useful here.

For example:

$> echo "a b c d e" | awk '$2=="b" && $3=="c" {print}'
a b c d e

$> echo "a b c d e" | awk '$2=="a" && $3=="c" {print}'

$> echo "a b c d e" | awk '$2=="b" && $3=="d" {print}'

So answer to your question could be:

awk '$14=="VALUE1" && $15=="VALUE2" {print}'

share|improve this answer
    
I am sorry but awk is not installed. I am logged in as a remote user and installing awk is not possible. –  Ebbu Feb 18 '12 at 9:54
    
@Ebbu, then you should add to your question what system you are using. That awk is not available seems a little bit unusual. –  maxschlepzig Feb 18 '12 at 10:19
    
awk should always be available on Unix (as should be vi, contrary to perl, python and emacs). It is part of the Unix Specification since POSIX (1991). See those for more information: en.wikipedia.org/wiki/Single_UNIX_Specification std.dkuug.dk/jtc1/sc22/open/n2972.pdf –  jfgagne Feb 18 '12 at 12:37
add comment

If awk is not available you can do it with cut, grep and wc:

$ echo -e 'a,b, c,d\na,val1 ,val2,c' \
   | cut -d ',' -f2,3 | grep '^ *val1 *, *val2 *$' | wc -l

Assuming , as delimiter (and no somehow escaped , is included) in the input. For testing purposes, the columns 2 and 3 are used instead of 14 and 15.

Note that the grep pattern allows trailing whitespace after/before the values (you can remove the * sub-patterns if you don't want that). The meta-characters ^ and $ match the beginning and the end of a line.

The pipe grep "Value1" | grep "Value2" from you question does not do what you specify - it would match too much, e.g.:

    ..., Value1Value2, , ...
    ..., Value1, Value2, ...
    ..., OtherValue1, Value2, ...
    ...

If awk is available (it is pretty standard) you can do it like this:

$ echo -e 'a,b, c,d\na,val1,val2,c' \
    | awk -F, '$2 == "val1" && $3 == "val2" {++sum} END {print sum}'

awk automatically trims whitespace from the values. END is a specially pattern that matches after all lines are processed.

share|improve this answer
add comment

The following function will use only Bash to do what you want:

foo () 
{ 
    local filename="$1";
    while IFS=, read -ra arr; do
        if [[ "${arr[13]}" = "VALUE1" && "${arr[14]}" = "VALUE2" ]]; then
            printf '%s\n' "${arr[13]}" "${arr[14]}";
        fi;
    done < "$filename"
}

Usage: foo [/path/to/file.txt]

Sample output:

rany$ cat > source.txt 
a,a,a,a,a,a,a,a,a,a,a,a,a,VALUE1,VALUE2
a,a,a,a,a,a,a,a,a,a,a,a,a,NOMATCH1,NOMATCH2
a,a,a,a,a,a,a,a,a,a,a,a,a,VALUE1,VALUE2

rany$ foo source.txt
VALUE1
VALUE2
VALUE1
VALUE2
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.