I have two files, in which one has data on each person's start date, increment, promotion and other details, and the other file has their research output over the years. Can a script be written in such a way that it takes each person's starting year and subtract it from the current year and give their research progress as a 6-year trend? Both files are in CSV/XLSX format. The problem is since each person joins in a different year I am not able to write a script which accounts for it. I am giving below a sample of both the files.


File - 1

Authors/Years   1988    1989    1990    1991    1992    1993    1994 .... 2017
Mr. John Smith    0       0      10      12      32      23      12  ....  3
Mr. John Doera    1       2      30      1       32       3       2  ....  3
Mr. Avani thea    0       0      10      72      12       2      12  ....  3


File 2:-

Name             Designation    Dept    DOJ         LastPrDate  NextPrDate  
Mr. Avani thea    Assoc.Prof    AE     31/10/2006   03/29/2014  03/29/2020
Mr. John Doera    Assis.Prof    CE     11/09/1996   03/02/2004  03/9/2010
John Smith        Professor     ME     1/11/2004    01/11/2000  03/9/2001

Expected out come. File 2:-

Name             Designation    DOJ         1st-six year   2nd-six year 3rd-six year  
Mr. Avani thea    Assoc.Prof    31/10/2006      10            20                 3
Mr. John Doera    Assis.Prof    11/09/1996      8             9                 3   
John Smith        Professor     1/11/2004        0            12                 13

Both the file contain the names which are not common so problem is excluding them from each other, also performance should be counted after their joining date i.e., excluding any research before joining. The data has many more rows.


Take a look at the paste command used in combination with awk.

If the data order is the same in the two files you can merge them into one.

You should be able next to grab the information you want looking fields with awk and even calculate or place if conditions.

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.