-2

I have a CSV file with some data in several columns. Actual data begins at row 11 and all the columns are filled with data in row 11. In the next 4 rows, the first four columns (ABCD) are empty and I need the data in row 11 ABCD columns copied over to rows 12, 13, 14 and 15. (rows 11-15)

It needs to repeat for every 5 rows i.e., 16-20, 21-25 so on until 3706-3710. (abcd columns data of row 16 are copied to 17-20, etc.)

The last one is at row 3706 - 3710.

In this sample file below:

hello hello hello hello
hello hello hello hello
hello hello hello hello
hello hello hello hello
hello hello hello hello
hello hello hello hello
hello hello hello hello
hello hello hello hello
hello hello hello hello
hello hello hello hello
colA,colB,colC,colD,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
colA,colB,colC,colD,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
colA,colB,colC,colD,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
colA,colB,colC,colD,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
colA,colB,colC,colD,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
colA,colB,colC,colD,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF
,,,,colE,colF

I need the values in ABCD columns copied to the next four rows until the end of the file.

closed as unclear what you're asking by Rui F Ribeiro, Romeo Ninov, Christopher, steve, Anthony Geoghegan Apr 19 '18 at 13:27

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    And what is your question? Be more specific about what you're having trouble with... Describe what you tried to do and what failed or behaved unexpectedly... – filbranden Apr 18 '18 at 15:48
  • I need to update this csv file. All the columns in rows 11, 16, 21....3706 has data. Need to copy the data in ABCD columns of row 11 to next 4 rows and similary 16 data in next 4 rows ...until 3706 row – nsrini Apr 18 '18 at 16:02
1

Given this input file:

junk
junk
junk
junk
junk
junk
junk
junk
junk
junk
dataA,dataB,dataC,dataD,dataE,dataF
,,,,dataX,dataY
,,,,dataX,dataY
,,,,dataX,dataY
,,,,dataX,dataY
dataG,dataH,dataI,dataJ,dataK,dataL
,,,,dataX,dataY
,,,,dataX,dataY
,,,,dataX,dataY
,,,,dataX,dataY
dataM,dataN,dataO,dataP,dataQ,dataR
,,,,dataX,dataY
,,,,dataX,dataY
,,,,dataX,dataY
,,,,dataX,dataY

This seems to do the trick:

$ awk 'BEGIN {FS=","; OFS=","} NR<11 {next} (NR-11)%5==0 { d1=$1;d2=$2;d3=$3;d4=$4 } (NR-11)%5 { print d1,d2,d3,d4,$5,$6}' input2
dataA,dataB,dataC,dataD,dataX,dataY
dataA,dataB,dataC,dataD,dataX,dataY
dataA,dataB,dataC,dataD,dataX,dataY
dataA,dataB,dataC,dataD,dataX,dataY
dataG,dataH,dataI,dataJ,dataX,dataY
dataG,dataH,dataI,dataJ,dataX,dataY
dataG,dataH,dataI,dataJ,dataX,dataY
dataG,dataH,dataI,dataJ,dataX,dataY
dataM,dataN,dataO,dataP,dataX,dataY
dataM,dataN,dataO,dataP,dataX,dataY
dataM,dataN,dataO,dataP,dataX,dataY
dataM,dataN,dataO,dataP,dataX,dataY

To break out the awk script:

BEGIN {
   FS=","     # Set the field separators for
   OFS=","    # input and output for CSV data
} 

NR<11 {next}  # Skip the first ten lines of the file

(NR-11)%5==0 { # On every fifth line after the tenth,
    d1=$1      # Use variables to hold the data values
    d2=$2      # which are to be copied to the next
    d3=$3      # few rows of output
    d4=$4
}

(NR-11)%5 {    # On all non such lines after the tenth,
   # Print the data, substituting the data held from the marker lines
   print d1,d2,d3,d4,$5,$6
}
0

DopeGhoti’s answer is very good, but

  • It doesn’t print input lines 11, 16, 21, …  While the question doesn’t say, my interpretation is that these lines should be printed.  Especially because of the reference to “rows 11-15” at the end of the first paragraph.
  • It doesn’t handle input fields beyond #6.  While the question shows an example input file in which the lines have only six fields, it never states this constraint (saying only “data in several columns”), and I believe that we shouldn’t always assume that the real data are exactly like the example.

So here’s my modification:

awk 'BEGIN {FS=","; OFS=","} NR<11 {next} (NR-11)%5==0 { d1=$1;d2=$2;d3=$3;d4=$4 }
                                          (NR-11)%5    { $1=d1;$2=d2;$3=d3;$4=d4 } {print}'

This

  • moves the print statement out to a separate action block, so lines 11, 16, 21, …, will be printed.
  • overwrites $1, $2, $3 and $4 in lines 12, 13, 14, 15, 17, 18, 19, 20, etc. (fields that are expected to be blank) with the saved values (d1, d2, d3 and d4) from lines 11, 16, 21, …, and then prints the entire (modified) lines, including an arbitrary number of fields.

For example, for this more interesting example file:

The
quick
brown
fox
jumps
high,over
the
very,very lazy
dog
catcher.
1,Hydrogen,1.00794(7),2.2,First Ionization Energy (eV),13.59844
,,,,Atomic Radius,25,apple
,,,,Van der Waals Radius,120
,,,,Covalent Radius,38
,,,,Valence electrons,1
2,Helium,4.002602(2),no data,First Ionization Energy (eV),24.58741
,,,,Atomic Radius,31,cherry
,,,,Van der Waals Radius,140
,,,,Covalent Radius,32
,,,,Valence electrons,2
3,Lithium,6.941(2),0.98,First Ionization Energy (eV),5.39172
,,,,Atomic Radius,145,banana cream
,,,,Van der Waals Radius,182
,,,,Covalent Radius,134
,,,,Valence electrons,1

(data taken from Wikipedia, with the addition of pie flavors on lines 12, 17, and 22), my command produces

1,Hydrogen,1.00794(7),2.2,First Ionization Energy (eV),13.59844
1,Hydrogen,1.00794(7),2.2,Atomic Radius,25,apple
1,Hydrogen,1.00794(7),2.2,Van der Waals Radius,120
1,Hydrogen,1.00794(7),2.2,Covalent Radius,38
1,Hydrogen,1.00794(7),2.2,Valence electrons,1
2,Helium,4.002602(2),no data,First Ionization Energy (eV),24.58741
2,Helium,4.002602(2),no data,Atomic Radius,31,cherry
2,Helium,4.002602(2),no data,Van der Waals Radius,140
2,Helium,4.002602(2),no data,Covalent Radius,32
2,Helium,4.002602(2),no data,Valence electrons,2
3,Lithium,6.941(2),0.98,First Ionization Energy (eV),5.39172
3,Lithium,6.941(2),0.98,Atomic Radius,145,banana cream
3,Lithium,6.941(2),0.98,Van der Waals Radius,182
3,Lithium,6.941(2),0.98,Covalent Radius,134
3,Lithium,6.941(2),0.98,Valence electrons,1

The question is also unclear about whether lines 1-10 should be printed.  If they should, then change NR<11 {next} to NR<11 {print; next} (in either my command or DopeGhoti’s).

  • you are right G-Man, I need the first 10 lines also need to be printed along with the modification suggested by you. – nsrini Apr 19 '18 at 13:13

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