top of page
  • Writer's picturekyle Hailey

Excel doesn’t have 3d charts but R does


 


I wanted to plot a set of data by 3 dimensions.

I wanted to plot I/O read latency by MB/s throughput by number of concurrent readers. Seemed simple. Well it turns out there is no good way to do it in Excel. Sure Excel has 3d charts but, attention, the z axis is treated like rows and not values. For example

Note that the z axis, “users” had 3 values marked  on the axis. Those 3 values are 1,16 and 64. Notice that  16 is as far from 1 as 64 is from 16, ie the distance is not proportional to the value.

There is a free plug-in for Excel called Excel3Dscatterplot, but the data is hard to read, for example

Luckily R saves the day. With R there are a number of easy ways to graph 3d data.

Let’s take a data set that represents network I/O showing  the send size in KB (s_KB),  number of concurrent sender threads (thrds), the MB send throughput (s_MB/s) and then avg, min and max latency (mn_ms, avg_ms, max_ms).

s_KB ,thrds ,mn_ms ,avg_ms , max_ms , s_MB/s       ,    1 ,  .02 ,   .05 ,   3.06 ,   .010        ,    8 ,  .04 ,   .12 ,   5.27 ,   .024        ,   64 ,  .20 ,   .82 ,  12.03 ,   .025      8 ,    1 ,  .03 ,   .06 ,   8.47 , 70.529      8 ,    8 ,  .04 ,   .17 ,  21.64 ,150.389      8 ,   64 ,  .23 ,  1.31 ,  20.50 , 48.604     32 ,    1 ,  .06 ,   .10 ,   1.82 ,149.405     32 ,    8 ,  .07 ,   .32 ,  16.78 ,321.392     32 ,   64 ,  .18 ,  5.32 , 380.02 , 73.652    128 ,    1 ,  .03 ,   .28 ,   2.01 ,226.457    128 ,    8 ,  .01 ,   .80 ,  54.78 ,557.196    128 ,   64 ,  .06 , 11.77 ,  77.96 ,180.176  1024 ,    1 ,  .01 ,  1.49 ,   5.76 ,335.587 1024 ,    8 ,  .01 ,  5.35 , 118.48 ,726.876  1024 ,   64 ,  .02 , 40.50 , 221.59 ,714.162

to plot this in R, first start R up (see http://scs.math.yorku.ca/index.php/R:_Getting_started_with_R#Installing_R )

find out where R’s working directory is

   getwd()

go to a directory with my data and r files

   setwd("C:/Users/Kyle/Documents/GitHub/nio")

list files

   dir()

load and plot    nio <- read.csv("perfibmd1_loopback_short.csv")

   nio s_KB thrds mn_ms avg_ms max_ms  s_MB.s 1    NA     1  0.02   0.05   3.06   0.010 2    NA     8  0.04   0.12   5.27   0.024 3    NA    64  0.20   0.82  12.03   0.025 4     8     1  0.03   0.06   8.47  70.529 5     8     8  0.04   0.17  21.64 150.389 6     8    64  0.23   1.31  20.50  48.604 7    32     1  0.06   0.10   1.82 149.405 8    32     8  0.07   0.32  16.78 321.392 9    32    64  0.18   5.32 380.02  73.652 10  128     1  0.03   0.28   2.01 226.457 11  128     8  0.01   0.80  54.78 557.196 12  128    64  0.06  11.77  77.96 180.176 13 1024     1  0.01   1.49   5.76 335.587 14 1024     8  0.01   5.35 118.48 726.876 15 1024    64  0.02  40.50 221.59 714.162

library(scatterplot3d) MBs=nio[,'s_MB.s'] threads=nio[,'thrds'] IOsize=nio[,'s_KB'] s3d <-scatterplot3d(IOsize,threads,MBs)

That’s a bit simple, but we can add some easy improvements. Here the dots are made solid, with color highlighting and a drop line to the bottom plane

s3d <-scatterplot3d(IOsize,threads,MBs,pch=16, highlight.3d=TRUE, type="h", main="3D Scatterplot")

In this test I’m running five different IO sizes 1 byte, 8k, 32k, 128k and 1024k. It would be nice to draw a line for each I/O size by increasing number of threads. There is no way to use 3 coordinates to identify a point, but there is a conversion routine that converts 3 coordinate point to two

s$xyz.convert(x,y,z)

There are 5 sets of points, i.e. one set per I/O size. Each set had 3 points, i.e. one point for each different number of concurrent threads 1,8,64.

The code loops through the 5 sets and draw a line through the 3 points:

x=nio[,'IOsize'] y=nio[,'threads'] z=nio[,'s_MB.s'] s <- scatterplot3d(x,y,z,xlab="IOsize", ylab="threads", zlab="MBs")    for ( i in  1:5  )  {      j=i*3       p1 <- s$xyz.convert(x[j-2],y[j-2],z[j-2])      p2 <- s$xyz.convert(x[j-1],y[j-1],z[j-1])      p3 <- s$xyz.convert(x[j],y[j],z[j])      segments(p1$x,p1$y,p2$x,p2$y,lwd=2,col=i)      segments(p2$x,p2$y,p3$x,p3$y,lwd=2,col=i)   }

It’s also easy to rotate the data. Here are two larger datasets


example

m=matrix(c(  0 ,    1 , 0.02 ,  0.06 ,   4.72 ,   0 ,  0.008  ,  0.000  ,99.19 , 0.67 , 0.05 , 0.09 , 0.00 , 0.00 , 0.00 , 0.00 , 0.06 , 0.08 ,    1 ,    0 ,    2 , 0.03 ,  0.05 ,   2.35 ,   0 ,  0.020  ,  0.000  ,99.49 , 0.39 , 0.07 , 0.05 , 0.00 , 0.00 , 0.00 , 0.00 , 0.04 , 0.07 ,    0 ,    0 ,    4 , 0.03 ,  0.07 ,  14.27 ,   0 ,  0.026  ,  0.000  ,98.33 , 1.18 , 0.15 , 0.24 , 0.06 , 0.04 , 0.00 , 0.00 , 0.05 , 0.07 ,    0 ,    0 ,    8 , 0.03 ,  0.13 ,  34.08 ,   0 ,  0.024  ,  0.000  ,93.49 , 4.91 , 0.51 , 0.66 , 0.13 , 0.31 , 0.00 , 0.00 , 0.05 , 0.14 ,    0 ,    0 ,   16 , 0.04 ,  0.26 ,  31.27 ,   0 ,  0.025  ,  0.000  ,56.29 ,42.31 , 0.16 , 0.57 , 0.31 , 0.30 , 0.06 , 0.00 , 0.11 , 0.16 ,    0 ,    0 ,   32 , 0.10 ,  0.35 ,   3.57 ,   0 ,  0.029  ,  0.000  ,39.26 ,57.80 , 0.71 , 1.08 , 0.49 , 0.66 , 0.00 , 0.00 , 0.18 , 0.43 ,    0 ,    0 ,   64 , 0.16 ,  0.70 ,  39.93 ,   0 ,  0.032  ,  0.000  , 1.28 ,91.69 , 3.18 , 1.64 , 1.25 , 0.37 , 0.59 , 0.00 , 0.33 , 0.70 ,    0 ,    8 ,    1 , 0.02 ,  0.07 ,   8.17 ,   0 , 59.668  ,  0.000  ,93.41 , 6.34 , 0.13 , 0.11 , 0.01 , 0.00 , 0.00 , 0.00 , 0.05 , 0.10 ,    0 ,    8 ,    2 , 0.03 ,  0.07 ,   9.75 ,   0 ,111.486  ,  0.000  ,98.18 , 1.35 , 0.24 , 0.18 , 0.04 , 0.00 , 0.00 , 0.00 , 0.06 , 0.08 ,    0 ,    8 ,    4 , 0.03 ,  0.09 ,  39.08 ,   0 ,154.623  ,  0.000  ,97.09 , 1.86 , 0.47 , 0.47 , 0.07 , 0.04 , 0.00 , 0.00 , 0.06 , 0.08 ,    0 ,    8 ,    8 , 0.05 ,  0.15 ,  18.48 ,   0 ,171.580  ,  0.000  ,78.14 ,19.78 , 0.74 , 1.05 , 0.11 , 0.19 , 0.00 , 0.00 , 0.09 , 0.29 ,    0 ,    8 ,   16 , 0.05 ,  0.36 ,  37.54 ,   0 ,154.778  ,  0.000  ,51.87 ,44.81 , 0.93 , 0.97 , 0.80 , 0.62 , 0.00 , 0.00 , 0.11 , 0.51 ,    0 ,    8 ,   32 , 0.06 ,  0.62 ,  43.06 ,   0 ,152.741  ,  0.000  ,15.84 ,66.88 ,13.27 , 1.64 , 0.64 , 1.42 , 0.31 , 0.00 , 0.11 , 0.55 ,    0 ,    8 ,   64 , 0.24 ,  1.07 ,  24.72 ,   0 ,166.518  ,  0.000  , 8.00 ,58.29 ,25.47 , 5.75 , 1.99 , 0.45 , 0.04 , 0.00 , 0.43 , 3.34 ,    0 ,   32 ,    1 , 0.05 ,  0.11 ,   8.46 ,   0 ,145.238  ,  0.000  ,40.63 ,58.08 , 1.14 , 0.15 , 0.01 , 0.00 , 0.00 , 0.00 , 0.10 , 0.13 ,    0 ,   32 ,    2 , 0.04 ,  0.12 ,   5.20 ,   0 ,248.208  ,  0.000  ,25.14 ,73.08 , 1.47 , 0.25 , 0.05 , 0.00 , 0.00 , 0.00 , 0.11 , 0.14 ,    0 ,   32 ,    4 , 0.06 ,  0.19 ,  22.94 ,   0 ,297.655  ,  0.000  ,11.05 ,85.37 , 2.40 , 0.97 , 0.15 , 0.06 , 0.00 , 0.00 , 0.12 , 0.22 ,    0 ,   32 ,    8 , 0.05 ,  0.32 ,  21.12 ,   0 ,360.777  ,  0.000  , 5.75 ,87.25 , 3.21 , 3.06 , 0.45 , 0.26 , 0.00 , 0.00 , 0.12 , 0.83 ,    0 ,   32 ,   16 , 0.09 ,  0.62 ,  22.54 ,   0 ,362.603  ,  0.000  , 3.22 ,81.87 , 6.48 , 6.21 , 1.15 , 1.06 , 0.00 , 0.00 , 0.25 , 1.88 ,    0 ,   32 ,   32 , 0.08 ,  1.14 ,  40.08 ,   0 ,369.589  ,  0.000  , 1.85 ,71.97 ,14.28 , 8.46 , 1.24 , 1.95 , 0.25 , 0.00 , 0.42 , 4.55 ,    0 ,   32 ,   64 , 0.38 ,  2.23 ,  33.70 ,   0 ,319.048  ,  0.000  , 0.00 ,36.12 ,39.33 ,18.56 , 2.61 , 3.31 , 0.07 , 0.00 , 0.68 , 4.97 ,    5 ,  128 ,    1 , 0.02 ,  0.29 ,   8.71 ,   0 ,212.555  ,  0.000  , 0.65 ,98.08 , 1.05 , 0.19 , 0.02 , 0.00 , 0.00 , 0.00 , 0.28 , 0.34 ,    0 ,  128 ,    2 , 0.01 ,  0.33 ,   8.54 ,   0 ,376.113  ,  0.000  , 6.95 ,87.98 , 4.28 , 0.76 , 0.02 , 0.00 , 0.00 , 0.00 , 0.28 , 0.64 ,    0 ,  128 ,    4 , 0.01 ,  0.46 ,  35.22 ,   0 ,533.401  ,  0.000  ,31.45 ,62.25 , 5.15 , 1.04 , 0.06 , 0.05 , 0.00 , 0.00 , 0.23 , 0.68 ,    1 ,  128 ,    8 , 0.01 ,  0.83 ,  89.39 ,   0 ,545.988  ,  0.000  ,23.75 ,59.64 ,10.87 , 4.96 , 0.25 , 0.51 , 0.02 , 0.00 , 0.28 , 0.97 ,    0 ,  128 ,   16 , 0.01 ,  1.57 ,  54.30 ,   0 ,554.879  ,  0.000  , 8.77 ,54.71 ,19.95 ,14.16 , 1.36 , 0.98 , 0.06 , 0.00 , 0.37 , 2.52 ,    0 ,  128 ,   32 , 0.04 ,  3.21 ,  86.26 ,   0 ,549.206  ,  0.000  , 2.61 ,41.04 ,26.36 ,23.33 , 3.27 , 3.00 , 0.38 , 0.00 , 0.49 , 5.87 ,    0 ,  128 ,   64 , 0.07 ,  6.93 , 115.71 ,   0 ,502.197  ,  0.000  , 5.93 ,32.15 ,16.22 ,30.94 , 7.48 , 5.84 , 1.26 , 0.17 , 0.79 ,20.10 ,    0 , 1024 ,    1 , 0.04 ,  2.57 ,  12.71 ,   0 ,194.751  ,  0.000  ,39.55 ,47.67 , 0.98 ,11.80 , 0.00 , 0.00 , 0.00 , 0.00 , 0.11 , 2.16 ,    0 , 1024 ,    2 , 0.01 ,  1.97 ,  10.25 ,   0 ,506.611  ,  0.000  ,48.02 ,36.93 , 0.20 ,14.84 , 0.01 , 0.00 , 0.00 , 0.00 , 0.11 , 1.42 ,    0 , 1024 ,    4 , 0.01 ,  2.73 ,  44.98 ,   0 ,724.303  ,  0.000  ,17.76 ,70.16 , 0.64 ,11.37 , 0.04 , 0.03 , 0.00 , 0.00 , 0.11 , 1.66 ,    1 , 1024 ,    8 , 0.01 ,  5.27 ,  91.10 ,   0 ,738.061  ,  0.000  ,16.37 ,66.91 , 2.36 ,12.81 , 0.72 , 0.74 , 0.08 , 0.01 , 0.11 , 1.88 ,    0 , 1024 ,   16 , 0.01 , 10.37 , 157.60 ,   0 ,727.271  ,  0.000  ,13.81 ,58.74 , 8.07 ,14.70 , 2.30 , 2.19 , 0.18 , 0.01 , 0.15 , 4.63 ,    0 , 1024 ,   32 , 0.01 , 20.61 , 116.30 ,   0 ,726.869  ,  0.000  ,11.06 ,49.24 ,13.88 ,16.86 , 3.71 , 4.41 , 0.62 , 0.22 , 0.26 ,10.11 ,    1 , 1024 ,   64 , 0.06 , 39.57 , 316.81 ,   0 ,721.422  ,  0.000  , 2.40 ,40.93 ,21.18 ,20.12 , 5.73 , 7.25 , 1.47 , 0.91 , 0.56 ,22.30 ,    0 ), nrow=35,ncol=19,byrow=TRUE, dimnames=list(NULL,c( 's_KB','thrds','mn_ms','avg_ms','max_ms','r_KB','s_MB/s','r_MB/s',' <100u','<500u','<1ms','<5ms','<10ms','<50ms','<100m',' <1s',' p50',' p95','retrans' )))MBs=m[,'s_MB/s'] threads=m[,'thrds'] IOsize=m[,'s_KB']x=IOsize y=threads z=MBslibrary(scatterplot3d)  s <- scatterplot3d(x,y,z,xlab="IOsize", ylab="threads", zlab="MBs") nthreads=7 for ( i in  1:5  )  {   beg=(1+(i-1)*nthreads)   end=(beg+nthreads-2)   cat( beg," ",end,"\n")   for ( j in beg:end ) {        p1 <- s$xyz.convert(x[j],y[j],z[j])        p2 <- s$xyz.convert(x[j+1],y[j+1],z[j+1])        segments(p1$x,p1$y,p2$x,p2$y,lwd=2,col=i)    } }


rotating the axis

z=IOsize x=threads y=MBs s <- scatterplot3d(x,y,z,pch=16,highlight.3d=TRUE,type="h",zlab="IOsize", xlab="threads", ylab="MBs") nthreads=7 for ( i in  1:5  )  {   beg=(1+(i-1)*nthreads)   end=(beg+nthreads-2)   cat( beg," ",end,"\n")   for ( j in beg:end ) {        p1 <- s$xyz.convert(x[j],y[j],z[j])        p2 <- s$xyz.convert(x[j+1],y[j+1],z[j+1])        segments(p1$x,p1$y,p2$x,p2$y,lwd=2,col=i)    } }


There is more to do.  Wanted to hurry up and post and will add more later. Would be good to make the IOsize axis log scale because all the small sizes 1 byte, 8k, 32k and 128k are all bunched together. The library scatterplot3d has log axis scoped but not implemented. The call will take a log argument but not yet use it, thus in the mean time one can run log10 on the values and update the tick labels.

 All that being said, I’m dubious of the use and utility of 3d charts in most situations. Sure graphing a 3d  function where things are changing seamlessly in all directions can be insightful, but for simple data like the above, 2d is probably clearer

0 views0 comments

Recent Posts

See All
bottom of page