6
$\begingroup$

I've been gathering data at work to determine the losses in some cables that connect rooms, and now I need to take that data (~1500 points) and, because of software limitations, reduce it to 72 points that still keep the shape of the curve.

I've been working in excel and have tried looking this up, but the issue is that I can't just take "every nth row" and be done with it, since that would distort the curve's shape.

One of the curves I need to reduce to 72 points

A chart with the points of data I gathered by hand before looking for a better method EDIT: I tried to not make the question over complicated by explaining exactly how this is being used and that was my mistake, since it makes my question quite ambiguous. This data is the insertion losses for some distance of cables on both the inside and outside of a chamber (There is a high frequency connector that connects them in the middle). We use software in order to operate test equipment, and, at least for tests that the software goes from frequency to frequency, the software takes account of these losses by looking them up from a database for a specific cable (or group of cables) and uses linear interpolation between specified points to better estimate them. The issue is that this software does not allow a cable to have 1500 points for its losses, instead limiting it to 72 maximum points.

What I need to do is take this data and get 72 points from it that would allow this software to most accurately calculate the losses. I could do this by hand and will if I cannot find a better solution, but I would rather avoid the time it would take to process 12 or more sets of this data, especially if I could develop a script or small piece of software for my company that would help them in the future when they retake these measurements.

EDIT_2: The second graph with the red line is basically what I need to get from the set of 1500 data points. It isn't the full 72 points I need for it, since I started to look for a way to automate this process before I even got halfway done.

Also, I'm new to here, so I'm not exactly sure if this is the correct place for me to post this question, nor do I know if I used the proper tags for it.

Improve this question
$\endgroup$
13
  • $\begingroup$ Do the data points you keep have to come from the origional data set? If not, a good bet would be to fit a curve to your data, then use that fit curve to generate new data points that exactly capture the trend. $\endgroup$
    – Matthew Drury
    Commented Sep 17, 2016 at 21:40
  • 1
    $\begingroup$ I agree with @MatthewDrury, and for a simple Excel solution it looks like a power law might do OK. If you have to sub-sample the original data, a standard approach is curve simplification, but not easy to do in Excel (presumable would require VBA?). $\endgroup$
    – GeoMatt22
    Commented Sep 17, 2016 at 21:57
  • 1
    $\begingroup$ When you say "keep the shape of the curve", the displayed curve shows a lot of wiggles. You're going to have to be considerably more precise about what you want to keep and what you don't. $\endgroup$
    – Glen_b
    Commented Sep 18, 2016 at 4:46
  • 1
    $\begingroup$ Your question isn't yet clear enough to sure what you need but you might do okay with a natural cubic spline on a log-scale. If you want to be able to do linear interpolation, an ordinary linear spline might do well enough. Here's a by-eye broken-line fit with only 13 points -- if that's the sort of thing you need there are a number of ways you might achieve something like it. $\endgroup$
    – Glen_b
    Commented Sep 18, 2016 at 5:40
  • 3
    $\begingroup$ What would you use to measure the quality of the down-sampled curve? I think that if you can formulate that more specifically other than visual inspection, then we can help you better. $\endgroup$
    – Gumeo
    Commented Sep 21, 2016 at 10:24

2 Answers 2

Reset to default
1
$\begingroup$

I would do the following:

The data very obviously follow a power law. Fit this non-linear model and find the highest N residuals. Re-estimate the model using a linear spline at each of the residuals. Output the predicted values and their inputs as a sequence of N points. This can be N=72 or any value you want (higher is better).

You probably can't do this in Excel. R however... these models are covered elsewhere in SE and can be found by consulting ?nls, ?spline::bs, etc.

Improve this answer
$\endgroup$
1
  • $\begingroup$ It's unclear why the initial power law fit would be of much use, or even in what sense your procedure is any good. Could you provide some justification? $\endgroup$
    – whuber ♦
    Commented Jul 3, 2018 at 20:51
0
$\begingroup$

You should try fitting your data with the equation below:

data_y = data^a + b1*sin(c1*data) + b2*sin(c2*data) + b3*sin(c3*data) + b4*sin(c4*data)+d

This is a combination of a power law and several trigonometric functions. These should be able to capture the characteristics of your data very well, and will reduce your data to only 10 parameters, while also keeping its general shape.

Improve this answer
$\endgroup$
1
  • 2
    $\begingroup$ Because the "wiggles" in this curve do not appear to be truly periodic, this seems unlikely to succeed. Have you applied it to comparable data to check (and see what goes wrong)? $\endgroup$
    – whuber ♦
    Commented Apr 5, 2017 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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