Analysis of Audacity data in excel


I am completing a project on gunshot frequency analysis using various firearms, ammunition and position wrt to shooter, with the aim of being able to identify an unknown shot from known ones . I used Audacity software and exported the data to excel. My query is to what (moving average, polynomial, etc) chart trendline to use to be able to compare all shots with the relevant confidence levels and correlation.

I would appreciate any ideas/assistance with this

That’s sounds like a rather ambitious project…

Do you know what a spectrum analyzer is, or what a [u]Fourier Transform/FFT[/u] is? I think the frequency spectrum is the key (if this can be done), and perhaps the frequency analysis over time. (I can’t help you with FFT… I understand the concepts, but I’ve never actually used it.)

If the distance is known, the absolute (or relative) loudness will be very helpful. But of course, under uncontrolled conditions the loudness is useless. And to get the absolute loudness, you need to calibrate your set-up with an SPL meter.

That’s a lot of variables and a lot of data for each firearm, ammunition, distance, acoustics variation. Under controlled conditions, I assume you (or the computer) can tell the difference between a .22, a .38, and a 12 gauge. But under random unknown/uncontrolled conditions, I’m not sure if you can tell the difference between a gunshot and a firecracker.


…with the relevant confidence levels and correlation.

You’ll have to collect and organize your data before doing any statistical analysis. Before collecting the data, there’s no way to predict confidence levels or anything like that.

And before you get to far into this… Can you listen to the recording and identify the gun and/or ammo with your ears? The human brain is pretty good at this kind of thing… For example, I don’t think there is any software that can analyze a song and identify the singer as Madonna or Taylor Swift… But a fan of either singer can easily do it! Again, under controlled conditions with Madonna or Taylor Swift speaking a password, voice ID software can work (to some extent).

Thank DVDdoug for your reply.

I have all the data via Audacity exported to excel. This is in two columns - dB level and Frequency. My dilemma is which is the best form to illustrate it in excel and then use it to compare each with each other. 4 different weapons were used with 2 types of ammunition. 3 shot repetitions were completed at positions to the left, right and behind the shooter for all weapons and ammo and all subsequently analysed, plotted and exported using Audacity.

If all of the recordings are recorded using the same equipment in the same environment, at the same distance, then you may be able to get meaningful results from looking at the peak levels from exported spectrum data.

If the recordings are made in different environments or with different equipment, then I doubt that you will be able to determine meaningful results because there will be too many unknown variables.

Sounds quite interesting (although I am opposed to any kind of weapon).

There is presumably some kind of network/relationship between the different samples (shots).
The obvious thing is to look at the cross correlation between all of these.
We would normally arrange the results in matrices. However, this is a bit difficult because we have more than two dimensions.
We have:

  • 4 shot gun types
  • 2 ammo types
  • 3 positions

Thus, you have a 24 x 24 matrix to start with. Each of the 576 cells holds one comparison. The correlation gives a value between -1 and 1. But it might be better to take the coefficient of determination (which is nothing but the correlation squared) because negative values will most likely not occur.
The table would look something like this:

g1a1p1 g1a1p2 g1a1p3 g1a2p1 …
g1a1p1 1.0 0.9 0.7 0.8
g1a1p1 0.9 …
Only 276 values are important because the diagonal line is always 1 and half of the rest is reflected.

You can also use other key features than the spectrum itself.
For instance, to get the spectrum centroid, you’ll multiply all dB levels with the frequency and divide the sum of it by the sum of all levels.
The formula would be (sorry, my Excel is german)


The frequencies are in column A and the levels in column B. You just have to replace the "4"s with the number of bins (e.g. 2048).
The resulting number is the center frequency.
This could be a good indicator for the kind of ammo for instance. There are many more such features.