No 3d charts in Excel? try R

January 2nd, 2013

 

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=MBs

library(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


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. Nannette Halley
    November 25th, 2019 at 14:53 | #1

    Thanks for sharing this info, I really liked your storytelling fashion.

You must be logged in to post a comment.