0

I would like to read a file.csv file and keep the rows (i.e., all the columns) of the file if and only if the sixth column of that file matches values in user_ids_to_keep.csv.

The file.csv file I would like to read is as following:

Year,WeekOfYear,DaysInWeek,USAIsBankHoliday,holidays,**user_id**       
2013,1,5,2,1,1       
2013,1,5,2,1,2       
2013,1,5,2,1,3       
, etc.

The user_ids_to_keep.csv from which I am getting the matching ids list the ids as following:

1    
3    
5    
, etc.    

The output.csv file I would like to create should look like the following:

Year,WeekOfYear,DaysInWeek,USAIsBankHoliday,holidays,**user_id**       
2013,1,5,2,1,1       
2013,1,5,2,1,3       
,etc. 

If the column user_id was the first of the file.csv file I believe I could have used the command

grep -Fwf

but now I am not sure how to get around this.

  • how you want to match the sixth column with user_ids_to_keep.csv elements ? I mean to say 1st row of sixth column would always match with 1st row of user_ids_to_keep.csv or what ? – Rahul Apr 13 '16 at 9:01
0

Show files:

head *

...which outputs:

==> file.csv <==
Year,WeekOfYear,DaysInWeek,USAIsBankHoliday,holidays,**user_id**       
2013,1,5,2,1,1       
2013,1,5,2,1,2       
2013,1,5,2,1,3  

==> user_ids_to_keep.csv <==
1    
3    
5  

Ugly POSIX code, (can be copied as one-liner):

{ read x ; echo $x ; \
  while read x ; \
  do for f in `cat user_ids_to_keep.csv` ; \
     do n=${x#${x%,*},} ; \
         if [ $n = $f ] ; \
         then echo $x ; \
              continue ; \
         fi ; \
     done ; \
  done ; } < file.csv | tee output.csv

Displays:

Year,WeekOfYear,DaysInWeek,USAIsBankHoliday,holidays,**user_id**
2013,1,5,2,1,1
2013,1,5,2,1,3
0

Can be written in one line:

paste -sd '' user_ids_to_keep.csv | sed 's/[ \t]*//' | xargs -i@ sed -n '/,[@][ \t]*$/p' file.csv

Description:

paste -sd '' user_ids_to_keep.csv

will output the file data in one line without newlines

sed 's/[ \t]*//'

will delete the all spaces from previous output

xargs -i@ sed -n '/,[@][ \t]*$/p' file.csv

will use the previous output in variable '@' as a pattern to compare the end of every line from file.csv and print it if pattern matches

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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