[SOLVED] reading column data from multiple files and find average

vjramana · 11-06-2012, 10:41 PM

I have 500 files with name fort.1, fort.2 ... fort.500. Each file contains data as below:

Code:

           1  0.485     
           2  0.028     
           3  0.100     
           4  0.979     
           5  0.338     
           6  0.891     
           7  0.415     
           8  0.368     
           9  0.245     
          10  0.489     
          11  0.977     
          12  0.644     
          13  0.557     
          14  0.809     
          15  0.437     
          16  0.044     
          17  0.940     
          18  0.855     
          19  0.473     
          20  0.095     
          21  0.133     
          22  0.300     
          23  9.177E-002
          24  7.234E-002
          25  7.971E-002
          26  7.486E-002
          27  5.052E-002
          28  5.253E-002
          29  4.544E-002
          30  3.250E-002
          31  4.780E-002
          32  1.827E-002
          33  2.638E-002
          34  7.635E-003
          35  3.574E-003
          36  5.593E-003
          37 -1.564E-003
          38 -3.025E-003
          39  5.789E-003
          40  1.940E-002
          41  2.867E-002
          42  9.658E-003
          43  1.232E-002
          44  1.757E-002
          45  3.694E-003
          46  1.235E-002
          47  1.365E-002
          48  2.816E-002
          49  1.145E-002
          50  2.905E-002
          51  1.441E-002
          52  2.888E-002
          53  1.611E-002
          54  1.640E-002
          55  1.202E-002
          56  1.685E-002
          57  3.854E-002
          58  3.560E-002
          59  3.051E-002
          60  5.768E-002
          61  3.179E-002
          62  2.302E-002
          63  4.962E-002
          64  1.388E-002
          65  1.611E-002
          66  2.560E-002
          67  3.265E-002
          68  2.437E-002
          69  2.654E-002
          70  2.881E-002
          71  3.466E-002
          72  5.805E-002
          73  4.428E-002
          74  2.337E-002
          75  3.276E-002
          76  2.586E-002
          77  2.035E-002
          78  1.848E-002
          79  2.142E-002
          80  2.451E-002
          81  1.381E-002
          82  5.397E-003
          83  1.295E-002
          84  6.747E-004
          85  1.308E-002
          86 -1.099E-002
          87 -2.575E-002
          88 -2.750E-002
          89 -3.441E-002
          90 -5.103E-002
          91 -6.950E-002
          92 -6.052E-002
          93 -8.980E-002
          94 -7.342E-002
          95 -7.155E-002
          96 -7.879E-002
          97 -6.909E-002
          98 -3.717E-002
          99 -5.387E-002
         100 -5.044E-002
         101 -7.943E-002
         102 -7.830E-002
         103 -6.970E-002
         104 -6.242E-002
         105 -5.150E-002
         106 -6.974E-002
         107 -6.407E-002
         108 -6.650E-002
         109 -4.164E-002
         110 -5.494E-002
         111 -2.358E-002
         112 -4.994E-002
         113 -2.767E-002
         114 -1.217E-002
         115 -6.055E-003
         116  2.338E-002
         117  2.859E-002
         118  3.549E-002
         119  2.789E-002
         120  4.823E-002
         121  2.535E-002
         122  1.000E-002
         123  6.018E-003
         124 -5.600E-003
         125 -7.883E-003
         126 -2.459E-004
         127 -1.757E-002
         128 -3.481E-003
         129  1.608E-002
         130 -3.419E-004
         131  4.508E-003
         132 -2.569E-002
         133 -1.555E-002
         134 -1.145E-002
         135 -2.028E-002
         136 -3.549E-002
         137 -2.539E-002
         138 -2.595E-002
         139 -7.817E-003
         140  1.491E-003
         141 -1.150E-002
         142 -1.294E-002
         143 -1.821E-002
         144 -2.968E-002
         145 -4.120E-002
         146 -3.831E-002
         147 -3.555E-002
         148 -2.118E-002
         149 -3.339E-002
         150 -3.886E-002
         151 -4.846E-002
         152 -4.860E-002
         153 -4.141E-002
         154 -3.510E-002
         155 -2.524E-002
         156 -1.420E-002
         157 -2.955E-002
         158 -1.363E-002
         159 -1.171E-002
         160 -7.864E-003
         161 -1.638E-002
         162 -1.890E-002
         163 -1.804E-002
         164 -1.868E-002
         165 -1.938E-002
         166  1.961E-003
         167 -9.195E-003
         168 -1.514E-002
         169 -1.220E-002
         170 -2.618E-002
         171 -2.652E-002
         172 -4.809E-002
         173 -3.339E-002
         174 -2.520E-002
         175 -2.595E-002
         176 -4.884E-002
         177 -3.962E-002
         178 -2.304E-002
         179 -3.534E-002
         180 -2.588E-002
         181 -2.026E-002
         182 -1.639E-002
         183 -1.855E-002
         184 -3.085E-002
         185 -5.229E-002
         186 -3.041E-002
         187 -4.405E-003
         188  1.840E-002
         189  4.098E-003
         190  3.828E-003
         191  1.350E-002
         192  1.095E-002
         193  2.464E-002
         194  1.680E-002
         195  1.439E-002
         196  1.941E-002
         197  1.881E-002
         198  2.352E-003
         199  7.802E-003
         200  1.002E-002
         201  6.570E-003
         202 -3.702E-004
         203 -6.452E-003
         204 -3.553E-002
         205 -4.247E-002
         206 -3.628E-002
         207 -5.905E-002
         208 -4.433E-002
         209 -3.159E-002
         210 -3.901E-002
         211 -4.250E-002
         212 -4.635E-002
         213 -7.460E-002
         214 -4.978E-002
         215 -4.218E-002
         216 -4.466E-002
         217 -5.404E-002
         218 -3.299E-002
         219 -4.301E-002
         220 -5.980E-002
         221 -3.258E-002
         222 -5.424E-002
         223 -5.423E-002
         224 -6.446E-002
         225 -7.044E-002
         226 -6.685E-002
         227 -7.830E-002
         228 -7.556E-002
         229 -7.182E-002
         230 -6.347E-002
         231 -6.493E-002
         232 -6.835E-002
         233 -7.661E-002
         234 -5.246E-002
         235 -4.557E-002
         236 -4.386E-002
         237 -2.164E-002
         238 -1.297E-002
         239 -5.841E-003
         240  5.426E-003
         241 -9.517E-003
         242 -4.891E-003
         243 -6.319E-003
         244 -2.376E-002
         245 -2.375E-002
         246 -3.094E-002
         247 -7.458E-002
         248 -4.743E-002
         249 -5.865E-002
         250 -5.305E-002
         251 -3.925E-002
         252 -5.455E-002
         253 -5.287E-002
         254 -3.234E-002
         255 -5.884E-002
         256 -6.529E-002
         257 -8.716E-002
         258 -7.022E-002
         259 -5.437E-002
         260 -3.689E-002
         261 -5.761E-002
         262 -4.144E-002
         263 -3.219E-002
         264 -6.291E-002
         265 -6.544E-002
         266 -6.585E-002
         267 -5.391E-002
         268 -4.448E-002
         269 -4.884E-002
         270 -4.103E-002
         271 -3.919E-002
         272 -2.993E-002
         273 -3.433E-002
         274 -5.573E-002
         275 -5.293E-002
         276 -6.117E-002
         277 -5.440E-002
         278 -6.899E-002
         279 -6.212E-002
         280 -5.614E-002
         281 -5.279E-002
         282 -5.175E-002
         283 -7.694E-002
         284 -5.383E-002
         285 -5.139E-002
         286 -6.255E-002
         287 -5.752E-002
         288 -6.763E-002
         289 -5.424E-002
         290 -5.193E-002
         291 -4.667E-002
         292 -3.109E-002
         293 -3.445E-002
         294 -3.152E-002
         295 -2.688E-002
         296 -3.298E-002
         297 -1.459E-002
         298 -6.643E-003
         299 -3.268E-002
         300 -2.783E-002
         301 -5.136E-002
         302 -3.143E-002
         303 -3.541E-002
         304 -2.351E-002
         305 -4.903E-002
         306 -2.323E-002
         307 -4.682E-002
         308 -3.457E-002
         309 -4.139E-002
         310 -5.112E-002
         311 -4.009E-002
         312 -4.914E-002
         313 -5.793E-002
         314 -4.020E-002
         315 -6.546E-002
         316 -4.925E-002
         317 -4.561E-002
         318 -5.947E-002
         319 -3.709E-002
         320 -4.802E-002
         321 -2.076E-002
         322 -4.083E-002
         323 -3.372E-002
         324 -4.540E-002
         325 -5.090E-002
         326 -4.725E-002
         327 -3.132E-002
         328 -3.167E-002
         329 -4.686E-002
         330 -3.642E-002
         331 -3.420E-002
         332 -4.702E-002
         333 -5.636E-002
         334 -3.551E-002
         335 -3.117E-002
         336 -4.015E-002
         337 -3.176E-002
         338 -2.404E-002
         339 -1.786E-002
         340  1.081E-002
         341 -6.859E-003
         342  6.724E-004
         343 -1.736E-002
         344 -2.122E-002
         345 -8.305E-003
         346 -2.234E-002
         347 -1.555E-002
         348 -1.269E-002
         349 -5.237E-003
         350  2.913E-003
         351  4.062E-003
         352  1.030E-002
         353  1.694E-002
         354  2.698E-002
         355  3.004E-002
         356  3.979E-002
         357  6.822E-002
         358  8.248E-002
         359  7.821E-002
         360  9.740E-002
         361  0.302     
         362  8.590E-002
         363  7.072E-002
         364  7.107E-002
         365  7.630E-002
         366  5.826E-002
         367  3.655E-002
         368  2.728E-002
         369  2.392E-002
         370  2.150E-002
         371  2.286E-002
         372  4.101E-003
         373 -2.065E-002
         374 -1.736E-002
         375 -1.700E-002
         376 -9.210E-003
         377 -7.848E-004
         378 -6.384E-003
         379 -1.117E-003
         380 -1.229E-002
         381 -4.686E-003
         382 -2.198E-003
         383  1.325E-002
         384 -4.755E-003
         385 -3.491E-003
         386 -7.013E-004
         387 -1.366E-002
         388  8.240E-003
         389  1.118E-002
         390  3.482E-003
         391  6.902E-003
         392  1.645E-002
         393  4.505E-004
         394  1.453E-002
         395 -1.961E-002
         396 -5.619E-004
         397  6.888E-003
         398  2.243E-002
         399  1.853E-002
         400  2.840E-002
         401  3.802E-002
         402  4.437E-002
         403  4.745E-002
         404  3.337E-002
         405  4.510E-002
         406  4.990E-002
         407  5.829E-002
         408  6.655E-002
         409  8.385E-002
         410  6.333E-002
         411  7.791E-002
         412  6.778E-002
         413  4.175E-002
         414  5.007E-002
         415  5.325E-002
         416  2.038E-002
         417  4.918E-002
         418  6.371E-002
         419  7.039E-002
         420  6.680E-002
         421  5.499E-002
         422  5.926E-002
         423  4.348E-002
         424  6.663E-002
         425  5.126E-002
         426  6.945E-002
         427  8.604E-002
         428  7.108E-002
         429  8.639E-002
         430  8.253E-002
         431  7.148E-002
         432  8.261E-002
         433  7.780E-002
         434  8.312E-002
         435  6.314E-002
         436  6.973E-002
         437  8.715E-002
         438  5.625E-002
         439  5.942E-002
         440  5.494E-002
         441  4.863E-002
         442  5.967E-002
         443  2.857E-002
         444  4.531E-002
         445  3.422E-002
         446  2.668E-002
         447  2.406E-002
         448  1.481E-002
         449  1.282E-002
         450  2.635E-002
         451  3.715E-003
         452  7.148E-003
         453 -1.350E-002
         454 -4.396E-003
         455 -1.890E-005
         456 -1.973E-002
         457 -1.137E-002
         458 -4.733E-003
         459 -4.387E-003
         460  1.544E-002
         461  1.635E-002
         462  6.389E-003
         463  8.971E-003
         464 -1.599E-002
         465 -1.607E-002
         466 -2.035E-002
         467 -3.371E-002
         468 -9.178E-003
         469 -2.444E-003
         470  6.678E-003
         471 -1.864E-003
         472 -2.059E-003
         473 -2.122E-002
         474 -1.260E-002
         475 -7.000E-003
         476 -1.331E-002
         477  3.682E-003
         478 -1.522E-002
         479 -1.658E-002
         480  6.375E-003
         481 -1.384E-004
         482 -6.976E-003
         483  9.774E-003
         484  1.563E-002
         485  1.984E-002
         486  3.358E-002
         487  1.822E-002
         488  3.906E-002
         489  2.142E-002
         490  2.457E-002
         491  2.909E-002
         492  6.664E-003
         493 -1.767E-002
         494  4.956E-003
         495 -7.650E-003
         496 -7.919E-003
         497  1.852E-002
         498  1.409E-003
         499  3.928E-002
         500  3.082E-002
         501  7.884E-003
         502  5.959E-004
         503  1.146E-002
         504  1.888E-002
         505  4.558E-002
         506  4.465E-002
         507  3.957E-002
         508  4.464E-002
         509  4.251E-002
         510  3.914E-002
         511  2.827E-002
         512  2.290E-002
         513  7.710E-004
         514  1.903E-002
         515 -3.968E-003
         516 -1.428E-002
         517 -1.948E-002
         518 -3.018E-003
         519 -1.219E-002
         520 -7.318E-003
         521 -1.562E-002
         522  7.925E-004
         523 -1.458E-002
         524 -2.530E-002
         525 -1.256E-002
         526 -1.998E-002
         527 -6.243E-003
         528 -1.849E-002
         529 -6.291E-003
         530 -1.304E-002
         531 -1.490E-002
         532 -3.754E-003
         533 -8.939E-003
         534 -2.824E-002
         535 -5.431E-003
         536 -2.968E-002
         537 -6.084E-003
         538 -4.486E-003
         539 -2.917E-002
         540 -2.820E-003
         541 -1.311E-002
         542 -1.141E-002
         543 -1.551E-002
         544 -5.701E-003
         545 -9.513E-003
         546 -9.121E-003
         547 -2.539E-002
         548 -1.354E-002
         549 -3.078E-003
         550 -1.299E-002
         551 -2.689E-003
         552 -6.155E-003
         553 -1.464E-002
         554 -1.418E-002
         555 -1.441E-002
         556 -9.348E-003
         557  2.114E-002
         558  3.758E-002
         559  3.456E-002
         560  4.443E-002
         561  3.983E-002
         562  1.667E-002
         563  1.938E-002
         564 -3.070E-003
         565  2.743E-004
         566  1.806E-002
         567  8.728E-003
         568  1.852E-002
         569  1.667E-002
         570  2.315E-002
         571  3.944E-002
         572  3.243E-002
         573  3.274E-002
         574  3.111E-002
         575  3.507E-002
         576  3.126E-002
         577  2.956E-002
         578  2.312E-002
         579  4.663E-002
         580  4.892E-002
         581  5.869E-002
         582  4.133E-002
         583  5.611E-002
         584  4.012E-002
         585  5.530E-002
         586  2.384E-002
         587  1.169E-002
         588  1.164E-002
         589  4.411E-003
         590 -9.444E-003
         591 -1.900E-002
         592 -2.679E-002
         593 -2.253E-002
         594 -3.751E-002
         595 -3.415E-002
         596 -6.611E-002
         597 -5.781E-002
         598 -6.575E-002
         599 -5.224E-002
         600 -4.176E-002
         601 -4.543E-002
         602 -3.866E-002
         603 -2.046E-002
         604 -2.033E-002
         605 -1.534E-002
         606 -3.720E-002
         607 -3.893E-002
         608 -3.775E-002
         609 -3.129E-002
         610 -3.385E-002
         611 -6.400E-002
         612 -3.756E-002
         613 -5.503E-002
         614 -4.827E-002
         615 -2.286E-002
         616 -2.994E-002
         617 -1.794E-002
         618 -2.478E-002
         619 -3.824E-002
         620 -3.525E-002
         621 -3.277E-002
         622 -4.374E-002
         623 -2.732E-002
         624 -1.559E-002
         625 -3.981E-003
         626 -9.266E-004
         627 -2.803E-003
         628 -1.146E-002
         629 -5.513E-003
         630  2.502E-003
         631  6.629E-003
         632 -4.236E-003
         633  4.718E-004
         634  2.083E-003
         635  2.506E-003
         636  1.797E-004
         637  1.146E-003
         638  1.219E-003
         639 -1.045E-002
         640  5.241E-003
         641 -1.636E-002
         642  9.379E-003
         643  1.253E-002
         644  4.606E-003
         645  1.071E-004
         646  2.176E-003
         647 -8.817E-003
         648 -2.407E-003
         649 -3.217E-003
         650 -1.707E-002
         651  7.715E-003
         652 -5.286E-003
         653 -1.579E-002
         654 -1.823E-002
         655 -1.848E-002
         656 -3.980E-003
         657  4.783E-003
         658  1.769E-002
         659  7.259E-003
         660  6.037E-003
         661  8.675E-003
         662  1.575E-002
         663  5.814E-003
         664  9.309E-003
         665 -4.913E-003
         666 -7.219E-003
         667 -2.547E-002
         668 -1.611E-002
         669 -8.551E-003
         670 -3.118E-005
         671 -4.073E-003
         672 -1.862E-003
         673  1.550E-003
         674 -1.755E-002
         675 -1.389E-002
         676 -2.624E-002
         677 -1.341E-002
         678 -1.771E-002
         679 -9.014E-003
         680  6.104E-004
         681 -3.651E-003
         682 -1.676E-002
         683 -5.713E-003
         684 -1.899E-002
         685 -1.189E-003
         686  8.178E-003
         687  1.979E-002
         688  4.752E-002
         689  3.212E-002
         690  5.592E-002
         691  5.378E-002
         692  4.240E-002
         693  5.897E-002
         694  3.517E-002
         695  3.642E-002
         696  4.722E-002
         697  5.429E-002
         698  7.121E-002
         699  7.551E-002
         700  0.470     
         701  0.403     
         702  0.820     
         703  0.931     
         704  8.000E-002
         705  9.093E-002
         706  7.246E-002
         707  6.041E-002
         708  6.624E-002
         709  5.024E-002
         710  5.682E-002
         711  6.871E-002
         712  5.856E-002
         713  4.729E-002
         714  4.421E-002
         715  5.053E-002
         716  3.317E-002
         717  2.790E-002
         718  1.052E-002
         719  2.415E-002
         720  2.819E-002
         721  3.334E-002
         722  3.125E-002
         723  3.993E-002
         724  3.744E-002
         725  3.507E-002
         726  2.128E-002
         727  1.354E-002
         728  1.409E-002
         729  1.476E-002
         730  6.041E-003
         731  2.911E-003
         732 -1.783E-002
         733 -1.747E-002
         734 -2.993E-003
         735 -2.301E-002
         736 -3.786E-002
         737 -3.241E-002
         738 -3.463E-002
         739 -2.184E-002
         740 -7.210E-003
         741 -5.155E-004
         742  4.655E-003
         743  5.144E-003
         744 -1.811E-002
         745 -8.157E-003
         746 -4.225E-003
         747 -1.163E-003
         748  1.303E-002
         749  6.344E-003
         750  6.190E-004
         751  4.627E-003
         752  8.907E-003
         753  1.578E-002
         754  1.839E-002
         755  6.017E-003
         756  4.192E-003
         757 -8.540E-004
         758  1.487E-003
         759  1.037E-002
         760  4.353E-002
         761  3.034E-002
         762  3.255E-002
         763  3.868E-002
         764  2.597E-002
         765  3.123E-002
         766  3.861E-002
         767  5.497E-002
         768  5.113E-002
         769  5.199E-002
         770  4.795E-002
         771  6.065E-002
         772  7.684E-002
         773  5.833E-002
         774  5.615E-002
         775  4.555E-002
         776  3.857E-002
         777  4.766E-002
         778  2.412E-002
         779  2.335E-002
         780  1.939E-002
         781  7.654E-003
         782  2.854E-002
         783  3.870E-003
         784  1.653E-002
         785 -1.359E-002
         786  1.380E-003
         787 -1.916E-002
         788 -9.877E-003
         789 -1.022E-002
         790 -3.958E-002
         791 -3.914E-002
         792 -2.188E-002
         793 -5.981E-002
         794 -3.387E-002
         795 -4.927E-002
         796 -4.761E-002
         797 -3.484E-002
         798 -4.387E-002
         799 -4.050E-002
         800 -4.501E-002

I want to get the average of each line of second column from every file. In other words, get average of second column first line from all files and store in "output.file". Then get average of second column of second line and store in the same "output.file".
I tried with paste command but fail to get what I want.
IS there any way to do in AWK?

Appreciate any help.
Thanks

H_TeXMeX_H · 11-07-2012, 02:07 AM

I don't know what output.file is supposed to look like but something like:

Code:

ls -1 | sort -V | while read line; do awk '{sum+=$2}END{print sum/NR}' "$line" >> output.file; done

millgates · 11-07-2012, 02:43 AM

How about something like this?

Code:

awk 'NF==2{s[$1]=$2;n[$1]++}END{for(i in s){printf "%i\t%f\n",i,s[i]/n[i]}}' fort.*|sort -nk1 > output.file

This sums lines based on the number in the first column. It should work even if some of the files don't contain all the lines and are of different length. If you know that all the files have the same number of lines, sorted by their index, it can be simplified to something like

Code:

awk '{s[FNR]+=$2}END{for(i=1;i<=FNR;i++){print i, s[i]/(ARGC-1)}}' fort.* > output.file

grail · 11-07-2012, 09:48 AM

@millgates - I think your second answer could be on to something, however, if the last file read, fort.500, has let us say 10 lines in it, then FNR will equal only 10 but there may be 100's
of other lines, as per the data example, which would need to be averaged.

@OP - my question back to you would be, is the average always of all files which means if a file has less numbers than another it will assume zero for other entries or should the
average only be calculated based on the number of files that have the same corresponding line numbers?

Example:

Code:

$ cat fort.1
1
2
3
4
5
6
7
8
9
$ cat fort.2
10
11
12
13
14
15

So in this example, if we look at the second part of my question, you would only average the first 6 lines by 2 as line wise they both have this many, whereas the last
3 lines in file 1 would be divided by 1 as they only appear in 1 file?

millgates · 11-07-2012, 10:53 AM

Quote:

Originally Posted by grail

@millgates - I think your second answer could be on to something, however, if the last file read, fort.500, has let us say 10 lines in it, then FNR will equal only 10 but there may be 100's
of other lines, as per the data example, which would need to be averaged.

Yes, as I mentioned in my previous post, that solution assumes all files have the same number of lines. If not, you would have to either iterate over the array and then sort the output as I did in the first example, or remember the max FNR. It all depends what the OP wants, as you mentioned. Is it the number of line that matters or the number in the first column? What to do when some entries are missing in some of the files? ...