Transpose only few columns to rows in csv

arjun_m · 06-26-2015, 11:51 AM

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

danielbmartin · 06-26-2015, 02:50 PM

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

arjun_m · 06-28-2015, 01:35 AM

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

danielbmartin · 06-28-2015, 11:48 AM

Quote:

Originally Posted by arjun_m

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

arjun_m · 06-28-2015, 12:14 PM

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

danielbmartin · 06-28-2015, 12:15 PM

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

danielbmartin · 06-28-2015, 12:18 PM

Quote:

Originally Posted by arjun_m

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

arjun_m · 06-28-2015, 12:21 PM

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

arjun_m · 06-28-2015, 12:33 PM

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

danielbmartin · 06-28-2015, 12:36 PM

Quote:

Originally Posted by arjun_m

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

arjun_m · 06-28-2015, 01:10 PM

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 ?

danielbmartin · 06-28-2015, 01:18 PM

Quote:

Originally Posted by arjun_m

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

danielbmartin · 06-28-2015, 01:22 PM

Quote:

Originally Posted by arjun_m

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

arjun_m · 06-28-2015, 01:26 PM

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

danielbmartin · 06-28-2015, 01:35 PM

Quote:

Originally Posted by arjun_m

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