title: Aggregate time series data to make a scatter plot
body: <p>I want to make time series scatter plot for my time series data, where my data has categorical columns which needs to be aggregated by group to make plotting data first, then make scatter plot either using <code>seaborn</code> or <code>matplotlib</code>. My data is product sales prices time series data, I want to see each product owner' price trend on different market threshold along times. I tried of using <code>pandas.pivot_table</code>, <code>groupby</code> for shaping plotting data, but couldn't get desired plot that I want to make.</p>
  <p><strong>reproducible data</strong>:</p>
  <p>here is <a href="https://jmp.sh/v/BYVkqSjSFP8LZnVvYnKa" rel="nofollow noreferrer">example product data</a> that I used; wheres I want to see each dealer's price trend on different protein type with respect to <code>threshold</code>.</p>
  <p><strong>my attempt</strong></p>
  <p>here is my current attempt to aggregate my data for making plotting data but it is not giving my right plot. I bet the my way of aggregating plotting data is not correct. Can anyone point me out how to make this right to get desired plot?</p>
  <pre><code>import pandas as pd
  import numpy as np
  import matplotlib.pyplot as plt
  import seaborn as sn
  
  mydf = pd.read_csv('foo.csv')
  mydf=mydf.drop(mydf.columns[0], axis=1)
  mydf['expected_price'] = mydf['price']*76/mydf['threshold']
  
  g = mydf.groupby(['dealer','protein_type'])
  newdf= g.apply(lambda x: pd.Series([np.average(x['threshold'])])).unstack()
  </code></pre>
  <p>but above attempt is not working because I want to have plotting data for each dealer's market purchase price on different <code>protein_type</code> with different <code>threshold</code> along the daily time series. I don't know what's best way of dealing with this time series. Can anyone suggest me or correct me how to get this right?</p>
  <p>I also tried <code>pandas/pivot_table</code> for aggregating my data but it is still not representing plotting data.</p>
  <pre><code>pv_df= pd.pivot_table(mydf, index=['date'], columns=['dealer', 'protein_type', 'threshold'],values=['price'])
  pv_df= pv_df.fillna(0)
  pv_df.groupby(['dealer', 'protein_type', 'threshold'])['price'].unstack().reset_index()
  </code></pre>
  <p>but above attempt is still not working. Also in my data, date is not continuous so I assume I could make plot of monthly time series line chart.</p>
  <p><strong>my attempt for making plot</strong>:</p>
  <p>here is my attempt for making plot:</p>
  <pre><code>def scatterplot(x_data, y_data, x_label, y_label, title):
      fig, ax = plt.subplots()
      ax.scatter(x_data, y_data, s = 30, color = '#539caf', alpha = 0.75)
  
      ax.set_title(title)
      ax.set_xlabel(x_label)
      ax.set_ylabel(y_label)
      fig.autofmt_xdate()
  </code></pre>
  <p><strong>desired output</strong>:</p>
  <p>I want either line chart or scatter plot where x-axis shows monthly time series while y-axis shows price of each different <code>protein_type</code> on different <code>threshold</code> value for each different dealer along monthly time series. Here is the example possible line chart I want to have:</p>
  <p><a href="https://i.stack.imgur.com/qL56n.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qL56n.png" alt="example line chart" /></a></p>