Dealing with data gaps while using moving averages with rolling

[Anonymous]

Good evening.
I'm trying to calculate moving averages on geophysical data, a bunch of drillholes, where the first column is the hole name, the second is depth and third is the specific weight.

My x-axis is depth and y-axis is specific weight. The problem is that I have an absence of information, that is, I have data for shallow depths and for higher depths, with a hole in the middle. When plotting, Power BI “connects” the two ends with a line.

The objective is to plot the raw data and a line, representing the moving average, only in the portions that I have information.

Here is the current situationusing the following code:

import matplotlib.pyplot as plt

dataset.set_index(dataset.columns[0], inplace=True)

for i in dataset.index.unique():

df = dataset.loc[i]

plt.plot(dataset.columns[1], dataset.columns[2], data=df, label=i)

plt.plot(dataset.columns[1], dataset.columns[2], data=df.rolling(1000, min_periods=100, center=True, win_type='boxcar').mean(), label=i, linewidth=2, color='black')

plt.show()

situation

And this  tis the aim!

So...any help?

Please.

Att

Tiago

[v-lionel-msft]

Hi @Anonymous ,

Or directly import the data into Power BI and use DAX for modeling, maybe you can give us a sample data.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi.

How can I do that?

Tiago

[Greg_Deckler]

@Anonymous If you are referring to providing sample data, you can post a PBIX file to a file sharing service like OneDrive or Box and share a link. Or you can paste sample data as text using the table tool in the ribbon when posting.

[Greg_Deckler]

I did this once: https://community.powerbi.com/t5/Quick-Measures-Gallery/Mind-the-Gap-Irregular-Time-Series/m-p/99179...

[mahoneypat]

Not sure if there is a simple fix for this.  Initial thought is to add a column (in DAX or M) that puts the data into dynamic groups/categories (e.g., breaking on where there are big gaps in x axis data).  You could then use that column as a legend.  They would start different color but you could change the colors to match I think.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat