1

I am trying to join two data files. The first one has 100.000 records where each line represents one person. The second file has 400.000 records where a person in the first file can appear multiple times. The first column in both files has a unique ID of each person

I want to join the files by the first column and add information from file 1 to file 2... so the new file will have 400.000 lines with added information about each person from datafile1.

I tried using a very simple:

join data2 data1 > data3

But because column 3 in data2 is either 1, 2 or 3 letters and because there is not information in all all columns the output file does not look like I want it to look like.

I want to KEEP the white spaces between all the columns and I want the columns to line up!

Data 1:

200616550010284  1655001  10072006  20012015  07102008  29012010  09012011  28122011
200616550010285  1655001  16082006  10052010  29092008  04092009                    
200616550010289  1655001  07092006  28052013  20072009  12072010  14072011  11102012
200616550010291  1655001  31102006  28032011  14012009  12012010                    
200616550010292  1655001  09112006  09092013  05012009  17012010  02022011  01032012
200616550010293  1655001  13122006  03092009  19022009  

                        

Data 2:

200616550010284 22032010  32
200616550010284 10032011 162
200616550010285 04122008  32
200616550010285 18112009  32
200616550010289 02032008  32

The data I want :

200616550010284 22032010  32  1655001  10072006  20012015  07102008  29012010  09012011  28122011
200616550010284 10032011 162  1655001  10072006  20012015  07102008  29012010  09012011  2812201
200616550010285 04122008  32  1655001  16082006  10052010  29092008  04092009 
200616550010285 18112009  32  1655001  16082006  10052010  29092008  04092009 
200616550010289 02032008  32  1655001  07092006  28052013  20072009  12072010  14072011  11102012

The Data I get when I use a simple join:

200116380810352 06042008 60 1638081 30082001 29082014 19112005 22112006 08122007 21012009
200116380810599 09052008 31 1638081 30102001 24032013 30112004 04082006 25012008 19022009
200116380810599 27102008 76 1638081 30102001 24032013 30112004 04082006 25012008 19022009
200116386910204 02072008 162 1638691 05062001 15122009 12122005 11012007 18122007 10042009
200116386910204 20052008 60 1638691 05062001 15122009 12122005 11012007 18122007 10042009
200116386910204 26042008 60 1638691 05062001 15122009 12122005 11012007 18122007 10042009
3
  • 2
    The "Data I get" does not exist in your input files and if the command you give is run on the data file samples posted then join throws errors. Please make sure you post the actual input files, the commands as typed, any errors thrown and the actual output for the most constructive help.
    – bu5hman
    May 5 at 17:27
  • does your data actually have a blank line between each record or is that just an artifact of attempting to format it for this site?
    – cas
    May 6 at 3:30
  • The data does not have empty lines between records. It had not crossed my mind that was not clear! May 7 at 13:52
0

How about

$ awk 'NR==FNR{a[$1] = substr($0,length($1)+1); next} $1 in a {print $0 a[$1]}' Data1 Data2
200616550010284 22032010  32  1655001  10072006  20012015  07102008  29012010  09012011  28122011

200616550010284 10032011 162  1655001  10072006  20012015  07102008  29012010  09012011  28122011

200616550010285 04122008  32  1655001  16082006  10052010  29092008  04092009

200616550010285 18112009  32  1655001  16082006  10052010  29092008  04092009

200616550010289 02032008  32  1655001  07092006  28052013  20072009  12072010  14072011  11102012
0

join only supports delimited columns, not fixed-size columns. supports whitespace sequences as column separators, but it does not remember how much whitespace there is after each column (and this would only work for left-aligned columns, anyway).

You can reconstruct the columns after calling join. Since columns are separated by a single space in the output, you can translate the spaces to tabs then call expand to align the columns wherever you want. However, this will result in left-aligned columns.

To cope with right-aligned columns, you can preprocess the input files to use a different character as the column separator and as the alignment character inside columns. For example, assuming that your data doesn't contain #:

join <(<data2 sed 'y/ /#/; s/\([^#]\)#/ #/g' | sort) \
     <(<data1 sed 'y/ /#/; s/\([^#]\)#/ #/g' | sort) |
  tr '#' ' '

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.