Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have the following problem. I collected data on reaction time from over 100 participants for an experiment I am running. Unfortunately, the separators between fields were not consistent, but after a lot of heartache with sed, I have managed to solve this problem.

The experiment was divided into blocks (5 for each participant) and what I need is each block to be outputted on its own line, separated by commas.

Here is a sample of my datafile:

Participant: 2456, Test: Optimism IAT. Format is stimulus ,  correct(1)/incorrect(0) ,  time(ms).  Writes 10 trials per line.
17/01/2011, 12:46:03 ,
Block 1: , Theirs   , 1        , 1921     , Myself   , 1        , 928      , Them     , 1        , 716      , Theirs   , 1        , 720      , Myself   , 1        , 533      , Me       , 1        , 596      , Themselves , 1        , 527      , Myself   , 1        , 656      , Mine     , 1        , 551      , Myself   , 1        , 624
     , Themselves , 1        , 570      , Me       , 1        , 514
     ,Block 1 Time,: 8856    ,
Block 2: , Failing  , 1        , 1835     , Happy    , 1        , 1118     , Sad      , 1        , 673      , Succeeding , 1        , 690      , Improving , 1        , 795      , Succeeding , 1        , 602      , Worse    , 1        , 586      , Succeeding , 1        , 553      , Improving , 1        , 619      , Disimproving , 1        , 659
     , Succeeding , 1        , 596      , Failing  , 1        , 539
     ,Block 2 Time,: 9265    ,
Block 3: , Succeeding , 1        , 2881     , Disimproving , 1        , 1072     , Mine     , 1        , 1120     , Me       , 1        , 627      , Happy    , 1        , 669      , Theirs   , 1        , 1539     , Worse    , 1        , 841      , Me       , 1        , 862      , Sad      , 1        , 1370     , Succeeding , 1        , 1115
    , Worse    , 1        , 855      , Theirs   , 1        , 792      , Them     , 1        , 627      , Better   , 1        , 735      , Me       , 1        , 626      , Happy    , 1        , 622      , Succeeding , 1        , 616      , Mine     , 1        , 646      , Them     , 1        , 599      , Disimproving , 1        , 607
     , Better   , 1        , 799      , Myself   , 1        , 1408     , Me       , 1        , 463      , Better   , 1        , 839      , Failing  , 1        , 602      , Mine     , 1        , 633      , Better   , 1        , 525      , Sad      , 1        , 573      , Worse    , 1        , 770      , Me       , 1        , 508
     , Theirs   , 1        , 613      , Disimproving , 1        , 649      , Improving , 1        , 701      , Theirs   , 1        , 590      , Disimproving , 1        , 716      , Better   , 1        , 714
     ,Block 3 Time,: 29924   ,
Block 4: , Them     , 1        , 1659     , Myself   , 1        , 1036     , Themselves , 1        , 595      , Me       , 1        , 509      , Myself   , 1        , 648      , Themselves , 1        , 542      , Myself   , 1        , 536      , Mine     , 1        , 537      , Theirs   , 1        , 615      , Mine     , 1        , 520
     , Me       , 1        , 596      , Mine     , 1        , 471
     ,Block 4 Time,: 8264    ,
Block 5: , Mine     , 1        , 1527     , Myself   , 1        , 1235     , Disimproving , 0        , 2001     , Theirs   , 1        , 981      , Succeeding , 1        , 1994     , Happy    , 1        , 1454     , Failing  , 1        , 1941     , Theirs   , 1        , 1151     , Failing  , 0        , 1358     , Me       , 1        , 790
     , Failing  , 1        , 717      , Mine     , 1        , 585      , Myself   , 1        , 821      , Themselves , 1        , 793      , Disimproving , 1        , 965      , Succeeding , 1        , 727      , Worse    , 1        , 961      , Theirs   , 1        , 1259     , Mine     , 1        , 578      , Better   , 1        , 1112
    , Mine     , 1        , 1207     , Happy    , 1        , 843      , Worse    , 1        , 1064     , Failing  , 1        , 699      , Happy    , 1        , 700      , Myself   , 1        , 516      , Them     , 1        , 794      , Me       , 1        , 526      , Sad      , 1        , 1118     , Improving , 1        , 826
     , Mine     , 1        , 540      , Succeeding , 1        , 952      , Myself   , 1        , 536      , Themselves , 1        , 851      , Improving , 1        , 865      , Mine     , 1        , 582
     ,Block 5 Time,: 35569  

As you can see, each of the blocks take up multiple lines. I need them to take up one line in the following format

Participant Date Time   Block  Word1 Correct1 Time1.....Word36 Correct36  Time36
2456                    1      Happy   1      1200       sad    0         1500
.
.
.
1234                     5    sad       0     1100       happy   1       900

The issue is that blocks 3 &5 have 36 stimuli while blocks 1,2 &4 have 12. I need the participant, datetime and block time in each row also.

Here is the script that got the data into the form you see here, but it doesn't give me each block on one line only which is what i need. Your help would be greatly appreciated.

BEGIN{
    FS="\\";
    RS="#";
    OFS=",";
    ORS="\n";
}{
    for(i=1;i<=NF;i++) {printf "%-10s", $i; printf ",";} 

}

I'm using gawk version 3.1.6 on Ubuntu 10.04.

share|improve this question
1  
Is there some reason you don't want to use a general programming language like Python? The solution might be longer than awk, but would probably be easier to understand and modify. –  Faheem Mitha Jun 17 '11 at 7:47
    
@Faheem Mitha - sunk costs mostly, also I'm not sure how long it would take me to learn enough python to do this task effectively can you recommend any good resources to get started? –  richiemorrisroe Jun 17 '11 at 13:00
    
I looked at your data, but am unclear what you are trying to do. (I don't see a clear matchup between your data and the results you give, for example). If you are doing fairly complicated text processing, Python is a good choice. Specific recommendations are always subjective, but people say good things about Dive Into Python. I suggest you try writing your question more clearly and try StackOverflow. If you are willing to consider a Python approach, say so. There are a ton of Python programmers there. –  Faheem Mitha Jun 18 '11 at 7:00
    
@Faheem Mitha - i actually started using python yesterday, and after reading the python.org tutorial and using the shell, i am about halfway through getting this (test) piece done. So thanks very much for the comment, which lead me to reassess my approach. –  richiemorrisroe Jun 18 '11 at 10:21
add comment

1 Answer 1

up vote 2 down vote accepted

If I understand correctly, your problem is coping with input where each record comes in multiple lines, and you don't detect the end of a record but rather the beginning of a new record: a new record begins whenever a line does not begin with a comma.

Here's some awk boilerplate you can use to pre-process the input into records.

function process (record) {
    RS = " *, *"; /*gawk allows RS to be a regexp; some implementations would require setting RS="," and manually trimming spaces*/
    $0 = record; /*automatically sets $1, $2, ..., and NF*/
    record = "";
    /*your code goes here*/
}
{ if (/^ *,/) {record = record $0} else {process(record); record=$0} }
END { if (record != "") {process(record)} }'
share|improve this answer
    
@Giles thank you, that was quite useful. However, i need to rethink my approach as my previous solution only gets me halfway there. I'll accept your answer and work away on it myself. –  richiemorrisroe Jun 17 '11 at 13:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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