0

I have multiple tab delimited data files that are separated by month in the format jan06.txt, feb06.txt, ..., dec07.txt.

Within each file, it looks something like:

Header1 Header2 Header3 ...
Data1   Data2   Data3   ...
Data4   Data5   Data6   ...
...     ...     ...

What I want to do is to combine all all the data files into a single data file, with just a single header at the top, but also include a new data column that contains the month and year so I don't lose that information from the file name. So my new, single data file would contain:

Date   Header1 Header2 Header3 ...
200601 Data1   Data2   Data3   ...
200602 Data4   Data5   Data6   ...
...    ...     ...     ...

Where 200601 would refer to Jan 06, 200602 refer to Feb 06, etc.

I know if I do something like cat *.txt > data.txt, I could combine all my files. However, the two issues remain:

  1. I have a header in every file that would get concatenated, which I don't want.
  2. I would lose the month information that is stored in the file name.

I think I can do this with some combination of cat and sed, but I am not sure how to start.

3
  • (I'm not any good with script, I just wondered...) how do you want the data compressed (for lack of a better term) into a single data file? It appears to me that 200601,Header1 would have lots of data entries the old way and only 1 the new way. Feb 1, 2013 at 23:16
  • @MarcusChan I added more to my question description above so hopefully that helps. But to explain more, for jan06.txt I would want 200601 appended to a new data column on the left side for every row entry for jan01.txt. For feb06.txt, I would want 200602 appended to every row. I would then want all of those rows together in one data file with just a single header at the top. Feb 1, 2013 at 23:46
  • also asked on stackoverflow Feb 2, 2013 at 3:05

1 Answer 1

0

Here's an awk program that should do what you need:

awk '
    BEGIN {
        # create an array so we can map the month number to month name
        split("jan feb mar apr may jun jul aug sep oct nov dec", months)
    }
    function filename2date(filename,         month, year, i) {
        month = substr(filename, 1, 3)
        year = substr(filename, 4, 2)
        for (i=1; i<=12; i++) 
            if (months[i] == month) 
                return sprintf("20%s%02d", year, i)
        return filename
    }
    NR == 1  { 
        # this is the first line of the first file
        print "Date", $0 
    }
    FNR == 1 { 
        # this is the first line of each file
        date = filename2date(FILENAME)
        next
    }
    { print date, $0 }
' ???[0-9][0-9].txt > data.txt

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .