Is there a dependent variable?
The trend line in Excel is from the regression of the dependent variable "lat" on independent variable "lon." What you call a "common sense line" can be obtained when you don't designate dependent variable, and treat both the latitude and longitude equally. The latter can be obtained by applying PCA. In particular, it's one of the eigen vectors of the covariance matrix of these variables. You can think of it as a line minimizing the shortest distance from any given $(x_i,y_i)$ point to a line itself, i.e. you draw a perpendicular to a line, and minimize the sum of those for each observation.
Here's how you could do it in R:
> para <- read.csv("para.csv")
> plot(para)
>
> # run PCA
> pZ=prcomp(para,rank.=1)
> # look at 1st PC
> pZ$rotation
PC1
lon 0.09504313
lat 0.99547316
>
> colMeans(para) # PCA was centered
lon lat
-0.7129371 53.9368720
> # recover the data from 1st PC
> pc1=t(pZ$rotation %*% t(pZ$x) )
> # center and show
> lines(pc1 + t(t(rep(1,123))) %*% c)
The trend line that you got from Excel is as a common sense as the eigen vector from PCA when you understand that in the Excel regression the variables are not equal. Here you're minimizing a vertical distance from $y_i$ to $y(x_i)$, where y-axis is latitude and x-axis is a longitude.
Whether you want to treat the variables equally or not depends on the objective. It's not the inherent quality of the data. You have to pick the right statistical tool to analyze the data, in this case choose between the regression and PCA.
An answer to a question that wasn't asked
So, why in your case a (regression) trend line in Excel doesn't seem to be a suitable tool for your case? The reason is that the trend line is an answer to a question that wasn't asked. Here's why.
Excel regression is trying to estimate the parameters of a line $lat=a+b \times lon$. So, the first problem is the latitude is not even a function of a longitude, strictly speaking (see the note at the end of the post), and it's not even the main issue. The real trouble is that you're not even interested in paraglider's location, you're interested in the wind.
Imagine that there was no wind. A paraglider would be making the same circle over and over. What would be the trend line? Obviously, it would be flat horizontal line, its slope would be zero, yet it doesn't mean that the wind is blowing in horizontal direction!
Here's a simulated plot for when there's a strong wind along y-axis, while a paraglider is making perfect circles. You can see how linear regression $y\sim x$ produces nonsensical result, a horizontal trend line. Actually, it's even slightly negative, but not significant. The wind direction is shown with a red line:
R code for the simulation:
t=1:123
a=1 #1
b=0 #1/10
y=10*sin(t)+a*t
x=10*cos(t)+b*t
plot(x,y,xlim=c(-60,60))
xp=-60:60
lines(b*t,a*t,col='red')
model=lm(y~x)
lines(xp,xp*model$coefficients[2]+model$coefficients[1])
So, the direction of the wind clearly is not aligned with the trend line at all. They're linked, of course, but in a nontrivial way. Hence, my statement that the Excel trend line is an answer to some question, but not the one you asked.
Why PCA?
As you noted there are at least two components of the motion of a paraglider: the drift with a wind and circular motion controlled by a paraglider. This is clearly seen when you connect the dots on your plot:
On one hand, the circular motion is really a nuisance to you: you're interested in the wind. Though on the other hand, you don't observe the wind speed, you only observe the paraglider. So, your objective is to infer the unobservable wind from observable paraglider's location reading. This is exactly the situation where tools such as factor analysis and PCA can be useful.
The aim of PCA is to isolate a few factors that determine the multiple outputs by analyzing the correlations in outputs. It's effective when the output is linked to factors linearly, which happens to be the case in your data: wind drift simply adds to the coordinates of the circular motion, that's why PCA is working here.
PCA setup
So, we established that PCA should have a chance here, but how will we actually set it up? Let's start with adding a third variable, time. We're going to assign time 1 to 123 to each 123 observation, assuming the constant sampling frequency. Here's how the 3D plot looks like of the data, revealing its spiral structure:
The next plot shows the imaginary center of rotation of a paraglider as brown circles. You can see how it drifts on lat-lon plane with the wind, while paraglider shown with a blue dot is circling around it. The time is on vertical axis. I connected the center of rotation to a corresponding location of a paraglider showing only the first two circles.
The corresponding R code:
library(plotly)
para <- read.csv("para.csv")
n=24
para$t=1:123 # add time parameter
# run PCA
pZ3=prcomp(para)
c3=colMeans(para) # PCA was centered
# look at PCs in columns
pZ3$rotation
# get the imaginary center of rotation
pc31=t(pZ3$rotation[,1] %*% t(pZ3$x[,1]) )
eye = pc31 + t(t(rep(1,123))) %*% c3
eyedata = data.frame(eye)
p = plot_ly(x=para[1:n,1],y=para[1:n,2],z=para[1:n,3],mode="lines+markers",type="scatter3d") %>%
layout(showlegend=FALSE,scene=list(xaxis = list(title = 'lat'),yaxis = list(title = 'lon'),zaxis = list(title = 't'))) %>%
add_trace(x=eyedata[1:n,1],y=eyedata[1:n,2],z=eyedata[1:n,3],mode="markers",type="scatter3d")
for( i in 1:n){
p = add_trace(p,x=c(eyedata[i,1],para[i,1]),y=c(eyedata[i,2],para[i,2]),z=c(eyedata[i,3],para[i,3]),color="black",mode="lines",type="scatter3d")
}
subplot(p)
The drift of the center of paraglider's rotation is caused mainly by the wind, and the path and speed of the drift is correlated with the direction and the speed of the wind, unobservable variables of interest. This is how the drift looks like when projected to lat-lon plane:
PCA Regression
So, earlier we established that regular linear regression doesn't seem to work very well here. We also figured why: because it doesn't reflect the underlying process, because paraglider's motion is highly nonlinear. It's a combination of circular motion and a linear drift. We also discussed that in this situation factor analysis might be helpful. Here's an outline of one possible approach to modeling this data: PCA regression. But fist I'll show you the PCA regression fitted curve:
This has been obtained as follows. Run PCA on the data set which has extra column t=1:123, as discussed earlier. You get three principal components. The first one is simply t. The second corresponds to the lon column, and the third to lat column.
I fit the latter two principal components to a variable of the form $a\sin(\omega t+\varphi)$, where $\omega,\varphi$ are extracted from spectral analysis of the components. They happen to have the same frequency but different phases, which is not surprising given the circular motion.
That's it. To get the fitted values you recover the data from fitted components by plugging the transpose of the PCA rotation matrix into the predicted principal components. My R code above shows parts of the procedure, and the rest you can figure out easily.
Conclusion
It's interesting to see how powerful is PCA and other simple tools when it comes to physical phenomena where the underlying processes are stable, and the inputs translate into outputs via linear (or linearized) relationships. So in our case the circular motion is very nonlinear but we easily linearized it by using sine/cosine functions on a time t parameter. My plots were produced with just a few lines of R code as you saw.
The regression model should reflect the underlying process, then only you can expect that its parameters are meaningful. If this is a paraglider drifting in the wind, then a simple scatter plot like in the original question will hide the time structure of the process.
Also Excel regression was a cross sectional analysis, for which the linear regression works best, while your data is a time series process, where the observations are ordered in time. Time series analysis must be applied here, and it was done in PCA regression.
Notes on a function
Since a paraglider is making circles, there will be multiple latitudes corresponding to a single longitude. In mathematics a function $y=f(x)$ maps a value $x$ to a single value $y$. It's many-to-one relationship, meaning that multiple $x$ may correspond to $y$, but not multiple $y$ correspond to a single $x$. That is why $lat=f(lon)$ is not a function, strictly speaking.
