transpose data in a file

jone kim · 05-02-2014, 02:41 AM

I've a set of information in a file which is in vertical format. I need to transpose the information in horizontal format.

//// information in the file
bankId: 1VVV2564
custCnt: 4598
totalAmt: $42,256,896

bankId: 1WTY0264
custCnt: 1256
totalAmt: $56,896
/// transposing the information

bankId custCnt totalAmt
1VVV2564 4598 $42,256,896
1WTY0264 1256 $56,896
///////////////////////////////////////////////////
How can I do this kind of data transformation using shell script?

MensaWater · 05-02-2014, 10:02 AM

Below uses a file named "testfile" - you'd substitute the real name of your file for that.

Code:

echo "bankid custCnt totalAmt"
egrep "bankId:|custCnt:|totalAmt:" testfile |awk '/bankId:/ {printf $2" "}
/custCnt:/ {printf $2" "}
/totalAmt:/ {print $2}'

Above is a good example of getting multiple lines from a file with awk
It first locates the patterns then printf outputs from the first pattern line then the next pattern line etc... Note that final is
a "print" rather than a "printf". You could use a printf on final line but would have to then use $2"\n" to include a line feed.

jone kim · 05-03-2014, 09:49 AM

Thanks MensaWater.

this is very useful.

Can I ask you another question, which is being quite doubt in my mind.

What if the first column is a variable, I mean the first column values changes with the type of information in the file.
How can we transpose the data in such cases?

Thank You!!!

pan64 · 05-03-2014, 11:12 AM

Quote:

Originally Posted by MensaWater

Below uses a file named "testfile" - you'd substitute the real name of your file for that.

Code:

echo "bankid custCnt totalAmt"
egrep "bankId:|custCnt:|totalAmt:" testfile |awk '/bankId:/ {printf $2" "}
/custCnt:/ {printf $2" "}
/totalAmt:/ {print $2}'

why do you need egrep at all? awk '...' testfile should be ok. Also there is a problem with this code, it will print the values in the order of occurrence, will not reorder them and will do interesting things if something were missing, mixed, duped (corrupted) in the source file.

MensaWater · 05-05-2014, 09:12 AM

What I wrote does was the OP requested. Yes doing a "cat testfile | awk" or doing the awk " " testfile work as well.

It was a quick and dirty to do what the OP asked. If you have better suggestions how to handle all forms of data why not post them rather than punching holes in what I wrote? Nothing in my post said it handle all error checking for all forms of data - it does however handle the data he provided.

pan64 · 05-06-2014, 12:32 AM

are you kidding? in my practice not only the solution is important but the usability too: how it will work with other data, how it will handle corrupt input - what about error handling .... This is not punching holes but knowing the limits. You gave us a working solution, I tried to improve it a bit and also found a possible problem. We have not seen all the input lines (so yes, it will surely handle the data provided, but what about the other lines?)

Yes, I have another idea, that will be the answer to #3, it is a more general solution. Unfortunately I have had not enough time to present it, so this is now only a simple pseudo-code, not tested at all:
awk ' { a[$1] = $2 } # will store all the var=value pairs in an array
{ some condition if the data is collected, can be printed:
for x in a print a[x] # so print the elements of that array
clear the array and start over }
'

jone kim · 05-06-2014, 06:27 PM

To pan64:

Thanks for the suggestion.

Please can you give us the implemented code as directed by your suggestions. That will be more beneficial to us.

Thanks!

jone kim · 05-06-2014, 06:33 PM

@ MensaWater:

You wrote that:

Quote:

It was a quick and dirty to do what the OP asked.

can we optimize your solution?

and what about my another concern;

Quote:

What if the first column is a variable, I mean the first column values changes with the type of information in the file.
How can we transpose the data in such cases?

Thank You!!!

pan64 · 05-07-2014, 07:56 AM

Code:

awk ' length { a[$1] = $2; next } 
      { for ( t in a ) printf t "\t"
        printf "\n"
        for ( t in a ) printf a[t] "\t"
        delete a
        printf "\n"
      }' inputfile

this can also be improved

MensaWater · 05-07-2014, 08:13 AM

Quote:

Originally Posted by pan64

Code:

awk ' length { a[$1] = $2; next } 
      { for ( t in a ) printf t "\t"
        printf "\n"
        for ( t in a ) printf a[t] "\t"
        delete a
        printf "\n"
      }' inputfile

this can also be improved

Running that only provides the header and the first record:
custCnt: bankId: totalAmt:
4598 1VVV2564 $42,256,896

MensaWater · 05-07-2014, 08:16 AM

Quote:

Originally Posted by jone kim

can we optimize your solution?
Thank You!!!

The idea in this forum is to help others learn not to write your code for you. You are certainly welcome to optimize but really ought to be researching further yourself. My post was to show you how to accomplish your basic task and explain what I did in doing so. You might wish to check out "awk tutorial" in a web search. Once you come up with ideas based on that and try them then you should ask questions at point where you get stuck.

pan64 · 05-07-2014, 08:36 AM

Quote:

Originally Posted by MensaWater

Running that only provides the header and the first record:
custCnt: bankId: totalAmt:
4598 1VVV2564 $42,256,896

it requires an empty line as delimiter between records. You need to insert an additional line at the end of the input file.
I agree with you (#11), therefore it is only an idea not a complete solution.

jone kim · 05-11-2014, 04:26 AM

Thank you for the replies guys.

I did not have clear idea to solve the problem. I did not know where to start with, so I needed help with you guys who have prior knowledge.
Now I can research on this and may be if I come up with new ideas to solve this I'll definitely post it.

Thanks guys for the comments.

Thank You very much!!!