0

Hello I have a similar problem to the one in this post

But since I am new to Bash and my file is a bit different I could not modify and apply the answers to my code.

I have a csv file with multiple columns (all columns are comma separated), the column I want to split looks like this: (input file)

post_id
86680728811_272953252761568
 86680728811_273859942672742
86680728811_281125741936891
86680728811_10150500662053812
_86680728811_10150500969563812
86680728811_10150501303143812
86680728811_305275689511038
_86680728811_10150501624593812
 86680728811_10150501873973812
86680728811_145945585518261

I would like to extract the second id number (the one that comes after the underscore). Keeping in mind that some columns start with a number, some with space, and some with '_'.

The output I would like is to add two new columns each contain the IDs separated by '_'. Example of the first line:

page ID             post ID 
86680728811         272953252761568

I tried to use a regex to read the number:

awk -F',' '{print $2} /(?<=_)[0-9]+/' FB_Dataset.csv

But nothing I have tried has worked so far. Any suggestion would help. Thanks

12
  • Is that one column of a comma separated file - or all columns of an underscore-separated file? – steeldriver Sep 20 '19 at 1:31
  • all columns are comma separated. – leena Sep 20 '19 at 1:32
  • Can you please edit your question to give a short testable sample of your input file and desired output? – steeldriver Sep 20 '19 at 1:35
  • Done. thank you . – leena Sep 20 '19 at 1:38
  • 1
    So... where are the commas? – steeldriver Sep 20 '19 at 1:41
0

Does this work for you? I will assume this format:

A B C post_id
a,b,c,86680728811_272953252761568
a,b,c, 86680728811_273859942672742
a,b,c,86680728811_281125741936891
a,b,c,86680728811_10150500662053812
a,b,c,_86680728811_10150500969563812
a,b,c,86680728811_10150501303143812
a,b,c,86680728811_305275689511038
a,b,c,_86680728811_10150501624593812
a,b,c, 86680728811_10150501873973812
a,b,c,86680728811_145945585518261

Then the commands

cat file | sed -Ee 's/(.*)post_id/\1page ID post I/' -e 's/,[_ ]/,/' -e 's/_/,/'

Output:

A B C page ID post ID
a,b,c,86680728811,272953252761568
a,b,c,86680728811,273859942672742
a,b,c,86680728811,281125741936891
a,b,c,86680728811,10150500662053812
a,b,c,86680728811,10150500969563812
a,b,c,86680728811,10150501303143812
a,b,c,86680728811,305275689511038
a,b,c,86680728811,10150501624593812
a,b,c,86680728811,10150501873973812
a,b,c,86680728811,145945585518261

-E use extended regular expressions (GNU), to be possible to capture groups.


Change the header to add page ID post ID capturing the first group (.*) until post_id, and replacing it with the captured group \1and page ID post ID

's/(.*)post_id/\1page ID post ID/'

Remove the leading spaces and the underscore _ from the lines after a comma, and replace it with a single comma.

sed 's/,[_ ]/,/'

Finally, replace the underscores _ with a comma.

sed 's/_/,/'

Beware that I deleted the cut command that didn't have to be there (it was reaminder of other commands I tried)

2
  • it works. Thank you Guillermo. Any references for this code to help me understand it? – leena Sep 20 '19 at 2:52
  • Thank you for the clarification. Should I use '-e' or 'sed'? I noticed something strange with the code. It splits the previous column header name. Is it possible to write a code to extract only the post_id and store it in a new column? (the second part after the score) – leena Sep 20 '19 at 23:18
0
awk -F', *_?' -v OFS=, '
  NR==1 {
    for (i=1;i<=NF;i++) {
      if ($i == "post_id") {
        $i = "page ID" OFS "post ID";
        col=$1;
      };
    };
    print;
    next
  };

  {
    split($col,a,/_/);
    $col=a[1] OFS a[2];
    print;
  };
  ' FB_Dataset.csv

Because the field data you've shown has an inconsistent format (some with leading spaces, some starting with an underscore, perhaps some with both), this awk script uses the regular expression , *_? ("a comma followed by zero-or-more spaces and optionally followed by an underscore") as the field separator (FS).

It also sets the Output Field Separator (OFS) to a comma.

While reading the input, it handles the first line (the CSV headers) and all remaining lines differently:

For the first line (NR==1), it examines the values of each field, looking for the string "post_id". If it finds that string, it changes that field's value so that it has the two new field names (page ID and post ID) separated by the OFS. It also stores the index number of that field in variable col for later use. Finally, it prints the modified line.

This assumes that the field names are unique, as they should be for valid CSV files. It will not work correctly if more than one field has the name post_id.

For the remaining lines, it splits field $col into array a using underscore (_) characters as the separator. It then replaces $col with the first two elements of that array separated by the OFS. Then it prints the modified line.

Sample Input:

A,B,C,post_id,D,E,F
a,b,c,86680728811_272953252761568,d,e,f
a,b,c, 86680728811_273859942672742,d,e,f
a,b,c,86680728811_281125741936891,d,e,f

Sample Output:

A,B,C,page ID,post ID,D,E,F
a,b,c,86680728811,272953252761568,d,e,f
a,b,c,86680728811,273859942672742,d,e,f
a,b,c,86680728811,281125741936891,d,e,f

In the header line, the post_id field has been converted to two fields (page ID and post ID), and in the CSV data, the corresponding field has been split into two fields.

BTW, because the script searches for the matching field name (post_id) in the header line, it will work with any number of fields before and/or after the field we want to split. With this sample data, it found that the fourth field contained the name we want, so col=4


Note that $i and $col do not mean the same thing in awk as they do in shell.

  • In shell, they would mean the variables whose names are i and col.
  • In awk, they mean "the value of the field whose index number is equal to the value of variable i (or variable col)". i.e. it's access to a field via indirection.

    e.g. if i=1 then $i means "the value in field 1", which is the same as $1.

    This is useful if, e.g., you need to perform arithmetic on a field number. In awk, NF is an automatically-created variable containing the index number of the last field of the current input line. so $NF means "the value in the last field", and $(NF-1) means "the value in the second-last field", and so on.

3
  • Thank you for the code and the explanation cas! It is too advanced for a beginner like me. – leena Sep 20 '19 at 23:21
  • i'll add some example output (using input based on the input in Guillermo's answer). – cas Sep 21 '19 at 0:55
  • Thank you cas . – leena Sep 21 '19 at 9:53
0

I would use:

awk -F' *_?' '{ print $(NF-1), $NF }' infile
2
  • this returns the last two columns with other random cells. Thank for trying – leena Sep 21 '19 at 9:05
  • @leena please edit your question with a sample of your actual input data; the answer is based on your given data in your question, if it's different from actual format, so you need update that in your question – αғsнιη Sep 21 '19 at 9:34

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.