LinuxQuestions.org - Transpose only few columns to rows in csv

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Transpose only few columns to rows in csv (https://www.linuxquestions.org/questions/programming-9/transpose-only-few-columns-to-rows-in-csv-4175546512/)

Transpose only few columns to rows in csv

I have a csv file in this format:

Quote:

file brands kpi country jan01 feb01
a bud aware CN 0.2 0.3

I want to tanspose it in his format I tried a lot but unable to achieve it. Is there some way i can do using awk.

Quote:

file brands kpi country monyy values
a bud aware CN jan01 0.2
a bud aware CN feb01 0.3

With this InFile ...

Code:

file brands kpi country jan01 feb01

a bud aware CN 0.2 0.3

... this code ...

Code:

 paste -sd" \n" $InFile  \

|awk '{mmmdd[1]=$5; mmmdd[2]=$6; rate[1]=$11; rate[2]=$12;

  print $1,$2,$3,$4,"monyy values";

  for (j=1;j<=2;j++) print $7,$8,$9,$10,mmmdd[j],rate[j]}'  \

|column -t >$OutFile

... produced this OutFile ...

Code:

file  brands  kpi    country  monyy  values

a    bud    aware  CN      jan01  0.2

a    bud    aware  CN      feb01  0.3

Daniel B. Martin

Thanks Martin. I have recently started learning awk.Could you please provide pointers on how to become an expert in awk :)

Quote:

Originally Posted by arjun_m (Post 5383924)

Thanks Martin. I have recently started learning awk. Could you please provide pointers on how to become an expert in awk :)

I am not an expert. All that I know about awk was self-taught, learned from reading posts in this forum. When I see an interesting post I copy-and-paste to make my own running version and then try to improve it. Improvement might be to reduce the number of lines or make it run faster or make it more readable. Go in whichever direction interests you.

Daniel B. Martin

Could you please tell how to process this script on the csv file?

Continuing on the path of incremental improvement, this is a way to eliminate the paste used in post #2.

With this InFile ...

Code:

file brands kpi country jan01 feb01

a bud aware CN 0.2 0.3

... this code ...

Code:

awk '{mmmdd[1]=$5; mmmdd[2]=$6; 

  print $1,$2,$3,$4,"monyy values";

  getline; rate[1]=$5; rate[2]=$6;

  for (j=1;j<=2;j++) print $1,$2,$3,$4,mmmdd[j],rate[j]}' $InFile \

|column -t >$OutFile

... produced this OutFile ...

Code:

file  brands  kpi    country  monyy  values

a    bud    aware  CN      jan01  0.2

a    bud    aware  CN      feb01  0.3

Daniel B. Martin

Quote:

Originally Posted by arjun_m (Post 5384079)

Could you please tell how to process this script on the csv file?

This question is difficult to answer because you didn't provide a csv file.

Daniel B. Martin

I am trying to execute the code in this fashion.

awk -f transpose.sh Book1.csv Book2.csv

Is this the correct way?
transpose.sh is the script file and Book1 is the i/p file and book2 is the o/p file

Seems i am lost and unable to execute the code

Here is the link to the csv file which I want to work on
https://drive.google.com/file/d/0BwI...ew?usp=sharing

Quote:

Originally Posted by arjun_m (Post 5384084)

I am trying to execute the code in this fashion.

awk -f transpose.sh Book1.csv Book2.csv

Is this the correct way?
transpose.sh is the script file and Book1 is the i/p file and book2 is the o/p file

1) My code is part of a bash program. I suppose it could be coded as a standalone awk program but that's unfamiliar to me.

2) csv stands for comma separated values file. Ordinarily it takes the form of a text file containing information separated by commas, hence the name. The sample input file you provided has no commas. Therefore it is not a csv file (to my understanding.)

This is my program in its entirety.

Code:

#!/bin/bash  Daniel B. Martin  Jun15

#

# To execute this program, launch a terminal session and enter:

#  bash /home/daniel/Desktop/LQfiles/dbm1430.bin



# This program inspired by ...

#  http://www.linuxquestions.org/questions/programming-9/

#    transpose-only-few-columns-to-rows-in-csv-4175546512/



# Have:

# file brands kpi country jan01 feb01

# a bud aware CN 0.2 0.3  



# Want:

# file brands kpi country monyy values

# a bud aware CN jan01 0.2

# a bud aware CN feb01 0.3 





# File identification

    Path=${0%%.*}

  InFile=$Path"inp.txt"

 OutFile=$Path"out.txt"

  Wanted=$Path"wanted.txt"



echo

echo; echo "Method #1 of LQ Member danielbmartin."

 paste -sd" \n" $InFile  \

|awk '{mmmdd[1]=$5; mmmdd[2]=$6; rate[1]=$11; rate[2]=$12;

  print $1,$2,$3,$4,"monyy values";

  for (j=1;j<=2;j++) print $7,$8,$9,$10,mmmdd[j],rate[j]}'  \

|column -t >$OutFile

echo; echo "OutFile ..."; cat $OutFile; echo "End Of File"

#echo; echo "Wanted ...";  cat $Wanted;  echo "End Of File"



echo

echo; echo "Method #2 of LQ Member danielbmartin."

awk '{mmmdd[1]=$5; mmmdd[2]=$6; 

  print $1,$2,$3,$4,"monyy values";

  getline; rate[1]=$5; rate[2]=$6;

  for (j=1;j<=2;j++) print $1,$2,$3,$4,mmmdd[j],rate[j]}' $InFile \

|column -t >$OutFile

echo; echo "OutFile ..."; cat $OutFile; echo "End Of File"

echo; echo "Wanted ...";  cat $Wanted;  echo "End Of File"



echo; echo "Normal end of job.";echo; exit

Daniel B. Martin

Hi martin,

I am unable to achieve the desired result.

Quote:

OutFile ...
BRANDS KPI COUNTRY Sample monyy values
BUDWEISER (ANY) FB CANADA JAN15 Total
Sample (ANY) FB CANADA FEB15
BUDWEISER (ANY) 3FB CANADA monyy values
BUDWEISER (ANY) ECONSID CANADA Total Total
BUDWEISER (ANY) ECONSID CANADA Sample Sample
BUDWEISER (ANY) BRANDAW CANADA monyy values
BUDWEISER (ANY) P7D CANADA Total Total
BUDWEISER (ANY) P7D CANADA Sample Sample
BUDWEISER (ANY) P4W CANADA monyy values
BUDWEISER (ANY) P3M CANADA Total Total
BUDWEISER (ANY) P3M CANADA Sample Sample
BUDWEISER (ANY) EVER CANADA monyy values
BUD LIGHT (ANY) FB Total CANADA
BUD LIGHT (ANY) FB Sample Total
BUD LIGHT (ANY) 3FB monyy values
BUD LIGHT (ANY) ECONSID CANADA CANADA
BUD LIGHT (ANY) ECONSID Total Total
BUD LIGHT (ANY) BRANDAW monyy values
BUD LIGHT (ANY) P7D CANADA CANADA
BUD LIGHT (ANY) P7D Total Total
BUD LIGHT (ANY) P4W monyy values
BUD LIGHT (ANY) P3M CANADA CANADA
BUD LIGHT (ANY) P3M Total Total
BUD LIGHT (ANY) EVER monyy values
BUD LIGHT (ANY) EVER CANADA CANADA
BUD LIGHT (ANY) EVER Total Total
End Of File

Wanted ...
cat: dbm1730wanted.txt: No such file or directory
End Of File

Normal end of job.

What should be the content of dbm1730wanted.txt ?

Quote:

Originally Posted by arjun_m (Post 5384104)

What should be the content of dbm1730wanted.txt ?

That file is the desired result as shown in your original post.

The Wanted file contains ...

Code:

file brands kpi country monyy values

a bud aware CN jan01 0.2

a bud aware CN feb01 0.3

The awk produces this. The column is used for cosmetic purposes, to make an attractive table.

Daniel B. Martin

Quote:

Originally Posted by arjun_m (Post 5384104)

I am unable to achieve the desired result.

Thank you for providing the unwanted OutFile. It would be even more helpful for you to provide the real-world InFile. I wrote code to fit your original small sample. Perhaps that code wasn't what you really needed.

Daniel B. Martin

The code is producing an outfile.txt but not the wanted.txt file

Quote:

Originally Posted by arjun_m (Post 5384112)

The code is producing an outfile.txt but not the wanted.txt file

The "wanted" file was copy-and-pasted from your original post.. I found it useful to do this for debugging. It enabled an easy "eyeball test" to verify that the code was working.

As this forum exchange develops I get the feeling that we are not communicating well. This may be my fault for not understanding your problem. If I have led you down a wrong path, I apologize.

Daniel B. Martin