Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dealing with data gaps while using moving averages with rolling

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

Need Help

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi.

      How can I do that?

       

      Tiago

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        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.