Average column values in gnuplot

Question

My data looks as follows

0.5,187713,4.96,0.724973,0.01481065,72.84,15.53
0.6,167717,4.43,0.733582,0.01796329,75.73,24.52
0.7,152797,4.03,0.756354,0.02086922,77.89,31.24
0.8,136517,3.60,0.799969,0.02446527,80.25,38.56
0.9,123985,3.27,0.899915,0.02825937,82.06,44.20
1.0,115901,3.06,0.999732,0.03093004,83.23,47.84
1.1,108449,2.86,1.099942,0.03345339,84.31,51.20
1.2,101089,2.67,1.199958,0.03645456,85.37,54.51
1.3,95801,2.53,1.299897,0.03908459,86.14,56.89
1.4,90617,2.39,1.399975,0.04149375,86.89,59.22
1.5,86261,2.28,1.499799,0.04465310,87.52,61.18
1.6,81665,2.16,1.599730,0.04746546,88.18,63.25
1.7,75541,1.99,1.699975,0.05180608,89.07,66.00
1.8,71753,1.89,1.799943,0.05508243,89.62,67.71
1.9,68641,1.81,1.899839,0.05791313,90.07,69.11
2.0,65809,1.74,1.999994,0.06138851,90.48,70.38
2.1,62457,1.65,2.099834,0.06460564,90.96,71.89
2.2,59921,1.58,2.199455,0.06766316,91.33,73.03
2.3,57153,1.51,2.299838,0.07162909,91.73,74.28
2.4,54133,1.43,2.399758,0.07701663,92.17,75.64
2.5,51997,1.37,2.499986,0.08052349,92.48,76.60
2.6,49929,1.32,2.599958,0.08450297,92.78,77.53
2.7,47749,1.26,2.699994,0.08875672,93.09,78.51
2.8,46117,1.22,2.799693,0.09129460,93.33,79.25
2.9,44541,1.18,2.899123,0.09506496,93.56,79.96
3.0,42449,1.12,2.999615,0.10022962,93.86,80.90
0.5,363958,4.99,0.893074,0.00910343,68.40,-7.52
0.6,327814,4.49,0.885607,0.01095668,71.54,3.16
0.7,293146,4.02,0.854532,0.01319982,74.55,13.40
0.8,262754,3.60,0.825171,0.01558911,77.19,22.38
0.9,228338,3.13,0.899962,0.01902715,80.18,32.54
1.0,209250,2.87,0.999932,0.02151380,81.83,38.18
1.1,191994,2.63,1.099992,0.02434067,83.33,43.28
1.2,176146,2.42,1.199958,0.02678609,84.71,47.96
1.3,163674,2.24,1.299986,0.02972868,85.79,51.65
1.4,150718,2.07,1.399852,0.03245382,86.92,55.47
1.5,140770,1.93,1.499975,0.03466649,87.78,58.41
1.6,131222,1.80,1.599923,0.03772851,88.61,61.23
1.7,123078,1.69,1.699973,0.04065058,89.31,63.64
1.8,115958,1.59,1.799857,0.04318717,89.93,65.74
1.9,108574,1.49,1.899959,0.04667913,90.57,67.92
2.0,102922,1.41,1.999690,0.04887618,91.06,69.59
2.1,97694,1.34,2.099395,0.05191641,91.52,71.14
2.2,92662,1.27,2.199871,0.05443381,91.96,72.63
2.3,88414,1.21,2.299978,0.05684952,92.32,73.88
2.4,84422,1.16,2.399668,0.05921618,92.67,75.06
2.5,80010,1.10,2.499896,0.06153083,93.05,76.36
2.6,76902,1.05,2.599916,0.06406729,93.32,77.28
2.7,74738,1.02,2.699690,0.06577387,93.51,77.92
2.8,71402,0.98,2.799920,0.06812163,93.80,78.91
2.9,69494,0.95,2.899854,0.07040185,93.97,79.47
3.0,67802,0.93,2.999359,0.07110504,94.11,79.97
0.5,534550,4.14,0.734336,0.00835252,76.01,0.14
0.6,476090,3.69,0.720035,0.00992851,78.63,11.06
0.7,428958,3.33,0.725414,0.01170297,80.75,19.86
0.8,380154,2.95,0.799996,0.01400433,82.94,28.98
0.9,346950,2.69,0.899954,0.01620170,84.43,35.18
1.0,322358,2.50,0.999959,0.01831071,85.53,39.78
1.1,298446,2.31,1.099999,0.02039410,86.60,44.25
1.2,277966,2.15,1.199981,0.02250517,87.52,48.07
1.3,260462,2.02,1.299975,0.02448279,88.31,51.34
1.4,243746,1.89,1.399996,0.02647294,89.06,54.46
1.5,229794,1.78,1.499931,0.02863376,89.69,57.07
1.6,216470,1.68,1.599929,0.03059017,90.28,59.56
1.7,201230,1.56,1.699958,0.03353148,90.97,62.41
1.8,196614,1.52,1.799805,0.03449286,91.17,63.27
1.9,184150,1.43,1.899865,0.03673122,91.73,65.60
2.0,174902,1.36,1.999945,0.03896826,92.15,67.33
2.1,165938,1.29,2.100006,0.04116238,92.55,69.00
2.2,152798,1.18,2.199811,0.04479913,93.14,71.45
2.3,144942,1.12,2.299993,0.04762763,93.49,72.92
2.4,138230,1.07,2.399992,0.04986823,93.80,74.18
2.5,132038,1.02,2.499928,0.05213663,94.07,75.33
2.6,127214,0.99,2.599795,0.05376293,94.29,76.23
2.7,120298,0.93,2.699970,0.05624358,94.60,77.53
2.8,114262,0.89,2.799894,0.05943829,94.87,78.65
2.9,109318,0.85,2.899960,0.06168545,95.09,79.58
3.0,104310,0.81,2.999739,0.06380893,95.32,80.51
0.5,89890,1.69,0.755600,0.00422898,80.95,18.51
0.6,85038,1.60,0.748339,0.00456993,81.98,22.91
0.7,72838,1.37,0.759211,0.00544418,84.56,33.97
0.8,58858,1.11,0.800069,0.00675947,87.53,46.64
0.9,51682,0.97,0.899958,0.00792572,89.05,53.15
1.0,45974,0.87,0.999927,0.00903898,90.26,58.32
1.1,40886,0.77,1.099930,0.01020669,91.34,62.94
1.2,37006,0.70,1.199869,0.01139348,92.16,66.45
1.3,34054,0.64,1.299873,0.01247451,92.78,69.13
1.4,31186,0.59,1.399789,0.01361101,93.39,71.73
1.5,29202,0.55,1.499947,0.01491799,93.81,73.53
1.6,27118,0.51,1.599840,0.01589767,94.25,75.42
1.7,25534,0.48,1.699513,0.01710643,94.59,76.85
1.8,24302,0.46,1.799406,0.01799776,94.85,77.97
1.9,22534,0.42,1.899377,0.01915440,95.22,79.57
2.0,21586,0.41,1.999911,0.02004283,95.43,80.43
2.1,20946,0.39,2.099712,0.02191064,95.56,81.01
2.2,20170,0.38,2.199660,0.02283212,95.73,81.72
2.3,19134,0.36,2.299889,0.02365377,95.95,82.65
2.4,18362,0.35,2.399900,0.02465888,96.11,83.35
2.5,17638,0.33,2.500004,0.02573230,96.26,84.01
2.6,17282,0.33,2.599989,0.02678808,96.34,84.33
2.7,16334,0.31,2.698711,0.02664875,96.54,85.19
2.8,15910,0.30,2.799017,0.02837672,96.63,85.58
2.9,15514,0.29,2.899958,0.02954554,96.71,85.94
3.0,15234,0.29,2.998002,0.03020222,96.77,86.19
0.5,287898,5.11,0.726749,0.01140151,68.65,3.52
0.6,251562,4.47,0.740393,0.01399680,72.61,15.70
0.7,224914,3.99,0.771689,0.01705783,75.51,24.63
0.8,197218,3.50,0.799995,0.02070237,78.53,33.91
0.9,178274,3.16,0.899829,0.02424657,80.59,40.26
1.0,163942,2.91,0.999974,0.02702463,82.15,45.06
1.1,151798,2.69,1.099961,0.02969011,83.47,49.13
1.2,140698,2.50,1.199965,0.03246072,84.68,52.85
1.3,132298,2.35,1.299974,0.03494901,85.59,55.67
1.4,124078,2.20,1.400006,0.03771346,86.49,58.42
1.5,117798,2.09,1.499890,0.04049115,87.17,60.52
1.6,111338,1.98,1.599910,0.04290295,87.88,62.69
1.7,104162,1.85,1.699918,0.04631012,88.66,65.09
1.8,102090,1.81,1.799761,0.04713748,88.88,65.79
1.9,93682,1.66,1.899958,0.05178072,89.80,68.61
2.0,89590,1.59,1.999850,0.05435155,90.24,69.98
2.1,85438,1.52,2.099739,0.05697525,90.70,71.37
2.2,81782,1.45,2.199392,0.05973938,91.10,72.59
2.3,78194,1.39,2.299670,0.06251692,91.49,73.80
2.4,81362,1.44,2.399894,0.06098039,91.14,72.73
2.5,72966,1.30,2.499983,0.06687551,92.06,75.55
2.6,71042,1.26,2.599894,0.06841763,92.26,76.19
2.7,66342,1.18,2.699802,0.07292893,92.78,77.77
2.8,65978,1.17,2.799538,0.07353166,92.82,77.89
2.9,63942,1.14,2.899680,0.07552722,93.04,78.57
3.0,59486,1.06,2.999513,0.08029330,93.52,80.07

I want to do an average for the data(based on first column) and then plot the 3rd and 4th column of the average.

Note: Only the 1st , 3rd and 4th column are important here

For example for 0.5:

0.5,187713,4.96,0.724973,0.01481065,72.84,15.53
0.5,363958,4.99,0.893074,0.00910343,68.40,-7.52
0.5,534550,4.14,0.734336,0.00835252,76.01,0.14
0.5,89890,1.69,0.755600,0.00422898,80.95,18.51
0.5,287898,5.11,0.726749,0.01140151,68.65,3.52

Outlier: 0.5,89890,1.69,0.755600,0.00422898,80.95,18.51

Since outliers may be present , i think the median would be better than the mean(Feel free to suggest improvements) to ignore the outliers.

As a result for 0.5 we would get a single row as follows : 0.5,4.96,0.734336

I want to repeat for every point in the data and plot the 3rd and 4th column.It will be (4.96,0.734336) for 0.5

How can automate it with gnuplot?

Edit: After thinking about it , mean would be better since the data is for images and for a large corpus , the outliers would likely disappear

Regards

For the mean value, use smooth unique, stackoverflow.com/a/36497102/2604213 — Christoph
@Christoph i want to plot 3rd and 4th column but i want to do the mean for both column based on column 1 — user2650277
Then you cannot do this with gnuplot, but you must preprocess your data with some script — Christoph

theozh theozh · Accepted Answer · 2021-01-20T10:59:03

If I understand your question correctly you want to do statistics on filtered data. You can define a function using the ternary operator (check help ternary)

myFilter(colData,colFilter,valueFilter) = column(colFilter) == valueFilter ? column(colData) : NaN

which returns the value from a certain data column (colData) if another column (colFilter) is equal to valueFilter and NaN otherwise.

Furthermore, you need to create a unique list of your values in column1 which you want to use as filter and as x-values in the plot. You can achieve this with plotting the data to a table and use the smooth option (see help smooth), e.g. frequency or unique, it doesn't matter in this case.

If you are interested in the medians as tabled values you need to do stats on the filtered columns. You don't necessarily need this part for the plot because as I understand the plotting style with boxplot does all that for you.

Now, simply use with boxplot together with the filtered data.

Code:

### filtered statistics
reset session

$Data <<EOD
0.5,187713,4.96,0.724973,0.01481065,72.84,15.53
0.6,167717,4.43,0.733582,0.01796329,75.73,24.52
0.7,152797,4.03,0.756354,0.02086922,77.89,31.24
0.8,136517,3.60,0.799969,0.02446527,80.25,38.56
0.9,123985,3.27,0.899915,0.02825937,82.06,44.20
1.0,115901,3.06,0.999732,0.03093004,83.23,47.84
1.1,108449,2.86,1.099942,0.03345339,84.31,51.20
1.2,101089,2.67,1.199958,0.03645456,85.37,54.51
1.3,95801,2.53,1.299897,0.03908459,86.14,56.89
1.4,90617,2.39,1.399975,0.04149375,86.89,59.22
1.5,86261,2.28,1.499799,0.04465310,87.52,61.18
1.6,81665,2.16,1.599730,0.04746546,88.18,63.25
1.7,75541,1.99,1.699975,0.05180608,89.07,66.00
1.8,71753,1.89,1.799943,0.05508243,89.62,67.71
1.9,68641,1.81,1.899839,0.05791313,90.07,69.11
2.0,65809,1.74,1.999994,0.06138851,90.48,70.38
2.1,62457,1.65,2.099834,0.06460564,90.96,71.89
2.2,59921,1.58,2.199455,0.06766316,91.33,73.03
2.3,57153,1.51,2.299838,0.07162909,91.73,74.28
2.4,54133,1.43,2.399758,0.07701663,92.17,75.64
2.5,51997,1.37,2.499986,0.08052349,92.48,76.60
2.6,49929,1.32,2.599958,0.08450297,92.78,77.53
2.7,47749,1.26,2.699994,0.08875672,93.09,78.51
2.8,46117,1.22,2.799693,0.09129460,93.33,79.25
2.9,44541,1.18,2.899123,0.09506496,93.56,79.96
3.0,42449,1.12,2.999615,0.10022962,93.86,80.90
0.5,363958,4.99,0.893074,0.00910343,68.40,-7.52
0.6,327814,4.49,0.885607,0.01095668,71.54,3.16
0.7,293146,4.02,0.854532,0.01319982,74.55,13.40
0.8,262754,3.60,0.825171,0.01558911,77.19,22.38
0.9,228338,3.13,0.899962,0.01902715,80.18,32.54
1.0,209250,2.87,0.999932,0.02151380,81.83,38.18
1.1,191994,2.63,1.099992,0.02434067,83.33,43.28
1.2,176146,2.42,1.199958,0.02678609,84.71,47.96
1.3,163674,2.24,1.299986,0.02972868,85.79,51.65
1.4,150718,2.07,1.399852,0.03245382,86.92,55.47
1.5,140770,1.93,1.499975,0.03466649,87.78,58.41
1.6,131222,1.80,1.599923,0.03772851,88.61,61.23
1.7,123078,1.69,1.699973,0.04065058,89.31,63.64
1.8,115958,1.59,1.799857,0.04318717,89.93,65.74
1.9,108574,1.49,1.899959,0.04667913,90.57,67.92
2.0,102922,1.41,1.999690,0.04887618,91.06,69.59
2.1,97694,1.34,2.099395,0.05191641,91.52,71.14
2.2,92662,1.27,2.199871,0.05443381,91.96,72.63
2.3,88414,1.21,2.299978,0.05684952,92.32,73.88
2.4,84422,1.16,2.399668,0.05921618,92.67,75.06
2.5,80010,1.10,2.499896,0.06153083,93.05,76.36
2.6,76902,1.05,2.599916,0.06406729,93.32,77.28
2.7,74738,1.02,2.699690,0.06577387,93.51,77.92
2.8,71402,0.98,2.799920,0.06812163,93.80,78.91
2.9,69494,0.95,2.899854,0.07040185,93.97,79.47
3.0,67802,0.93,2.999359,0.07110504,94.11,79.97
0.5,534550,4.14,0.734336,0.00835252,76.01,0.14
0.6,476090,3.69,0.720035,0.00992851,78.63,11.06
0.7,428958,3.33,0.725414,0.01170297,80.75,19.86
0.8,380154,2.95,0.799996,0.01400433,82.94,28.98
0.9,346950,2.69,0.899954,0.01620170,84.43,35.18
1.0,322358,2.50,0.999959,0.01831071,85.53,39.78
1.1,298446,2.31,1.099999,0.02039410,86.60,44.25
1.2,277966,2.15,1.199981,0.02250517,87.52,48.07
1.3,260462,2.02,1.299975,0.02448279,88.31,51.34
1.4,243746,1.89,1.399996,0.02647294,89.06,54.46
1.5,229794,1.78,1.499931,0.02863376,89.69,57.07
1.6,216470,1.68,1.599929,0.03059017,90.28,59.56
1.7,201230,1.56,1.699958,0.03353148,90.97,62.41
1.8,196614,1.52,1.799805,0.03449286,91.17,63.27
1.9,184150,1.43,1.899865,0.03673122,91.73,65.60
2.0,174902,1.36,1.999945,0.03896826,92.15,67.33
2.1,165938,1.29,2.100006,0.04116238,92.55,69.00
2.2,152798,1.18,2.199811,0.04479913,93.14,71.45
2.3,144942,1.12,2.299993,0.04762763,93.49,72.92
2.4,138230,1.07,2.399992,0.04986823,93.80,74.18
2.5,132038,1.02,2.499928,0.05213663,94.07,75.33
2.6,127214,0.99,2.599795,0.05376293,94.29,76.23
2.7,120298,0.93,2.699970,0.05624358,94.60,77.53
2.8,114262,0.89,2.799894,0.05943829,94.87,78.65
2.9,109318,0.85,2.899960,0.06168545,95.09,79.58
3.0,104310,0.81,2.999739,0.06380893,95.32,80.51
0.5,89890,1.69,0.755600,0.00422898,80.95,18.51
0.6,85038,1.60,0.748339,0.00456993,81.98,22.91
0.7,72838,1.37,0.759211,0.00544418,84.56,33.97
0.8,58858,1.11,0.800069,0.00675947,87.53,46.64
0.9,51682,0.97,0.899958,0.00792572,89.05,53.15
1.0,45974,0.87,0.999927,0.00903898,90.26,58.32
1.1,40886,0.77,1.099930,0.01020669,91.34,62.94
1.2,37006,0.70,1.199869,0.01139348,92.16,66.45
1.3,34054,0.64,1.299873,0.01247451,92.78,69.13
1.4,31186,0.59,1.399789,0.01361101,93.39,71.73
1.5,29202,0.55,1.499947,0.01491799,93.81,73.53
1.6,27118,0.51,1.599840,0.01589767,94.25,75.42
1.7,25534,0.48,1.699513,0.01710643,94.59,76.85
1.8,24302,0.46,1.799406,0.01799776,94.85,77.97
1.9,22534,0.42,1.899377,0.01915440,95.22,79.57
2.0,21586,0.41,1.999911,0.02004283,95.43,80.43
2.1,20946,0.39,2.099712,0.02191064,95.56,81.01
2.2,20170,0.38,2.199660,0.02283212,95.73,81.72
2.3,19134,0.36,2.299889,0.02365377,95.95,82.65
2.4,18362,0.35,2.399900,0.02465888,96.11,83.35
2.5,17638,0.33,2.500004,0.02573230,96.26,84.01
2.6,17282,0.33,2.599989,0.02678808,96.34,84.33
2.7,16334,0.31,2.698711,0.02664875,96.54,85.19
2.8,15910,0.30,2.799017,0.02837672,96.63,85.58
2.9,15514,0.29,2.899958,0.02954554,96.71,85.94
3.0,15234,0.29,2.998002,0.03020222,96.77,86.19
0.5,287898,5.11,0.726749,0.01140151,68.65,3.52
0.6,251562,4.47,0.740393,0.01399680,72.61,15.70
0.7,224914,3.99,0.771689,0.01705783,75.51,24.63
0.8,197218,3.50,0.799995,0.02070237,78.53,33.91
0.9,178274,3.16,0.899829,0.02424657,80.59,40.26
1.0,163942,2.91,0.999974,0.02702463,82.15,45.06
1.1,151798,2.69,1.099961,0.02969011,83.47,49.13
1.2,140698,2.50,1.199965,0.03246072,84.68,52.85
1.3,132298,2.35,1.299974,0.03494901,85.59,55.67
1.4,124078,2.20,1.400006,0.03771346,86.49,58.42
1.5,117798,2.09,1.499890,0.04049115,87.17,60.52
1.6,111338,1.98,1.599910,0.04290295,87.88,62.69
1.7,104162,1.85,1.699918,0.04631012,88.66,65.09
1.8,102090,1.81,1.799761,0.04713748,88.88,65.79
1.9,93682,1.66,1.899958,0.05178072,89.80,68.61
2.0,89590,1.59,1.999850,0.05435155,90.24,69.98
2.1,85438,1.52,2.099739,0.05697525,90.70,71.37
2.2,81782,1.45,2.199392,0.05973938,91.10,72.59
2.3,78194,1.39,2.299670,0.06251692,91.49,73.80
2.4,81362,1.44,2.399894,0.06098039,91.14,72.73
2.5,72966,1.30,2.499983,0.06687551,92.06,75.55
2.6,71042,1.26,2.599894,0.06841763,92.26,76.19
2.7,66342,1.18,2.699802,0.07292893,92.78,77.77
2.8,65978,1.17,2.799538,0.07353166,92.82,77.89
2.9,63942,1.14,2.899680,0.07552722,93.04,78.57
3.0,59486,1.06,2.999513,0.08029330,93.52,80.07
EOD

set datafile separator comma

# get unique values of column 1
set table $Dummy
    plot [*:*] $Data u 1:(1) smooth freq
unset table
set datafile separator whitespace
# get unique values to string, then it works also gnuplot 5.0.0
set table $Dummy2
    plot UniqueList='' $Dummy u (UniqueList=UniqueList." ".strcol(1),0) w table
unset table
set datafile separator comma

myFilter(colData,colFilter,valueFilter) = column(colFilter) == valueFilter ? column(colData) : NaN
Unique(i) = real(word(UniqueList,i))

# in case you need a table for the medians
# can be skipped for the boxplot
set print $myStats
    do for [i=1:words(UniqueList)] {
        stats $Data u (myFilter(3,1,Unique(i))) name "A" nooutput
        stats $Data u (myFilter(4,1,Unique(i))) name "B" nooutput
        print sprintf("%.1f, %.2f, %g",Unique(i),A_median, B_median)
    }
set print
print $myStats

plot for [i=1:words(UniqueList)] $Data u (Unique(i)): \
         (myFilter(3,1,Unique(i))):(0.05) w boxplot lc "red" title (i==1?'Column3':''), \
     for [i=1:words(UniqueList)] $Data u (Unique(i)): \
         (myFilter(4,1,Unique(i))):(0.05) w boxplot lc "web-green" title (i==1?'Column4':'')
### end of code

Result:

0.5, 4.96, 0.734336
0.6, 4.43, 0.740393
0.7, 3.99, 0.759211
0.8, 3.50, 0.799996
0.9, 3.13, 0.899954
1.0, 2.87, 0.999932
1.1, 2.63, 1.09996
1.2, 2.42, 1.19996
1.3, 2.24, 1.29997
1.4, 2.07, 1.39997
1.5, 1.93, 1.49993
1.6, 1.80, 1.59991
1.7, 1.69, 1.69996
1.8, 1.59, 1.79981
1.9, 1.49, 1.89986
2.0, 1.41, 1.99991
2.1, 1.34, 2.09974
2.2, 1.27, 2.19966
2.3, 1.21, 2.29989
2.4, 1.16, 2.39989
2.5, 1.10, 2.49998
2.6, 1.05, 2.59992
2.7, 1.02, 2.6998
2.8, 0.98, 2.79969
2.9, 0.95, 2.89985
3.0, 0.93, 2.99951

Average column values in gnuplot

1 Answers