title: Find min and max range with a combination of column values in PySpark
body: <p>I have a pyspark dataframe like this,</p>
  
  <pre><code>+----------+--------+----------+----------+
  |id_       | p      |d1        |  d2      |
  +----------+--------+----------+----------+
  |  1       | A      |2018-09-26|2018-10-26|
  |  2       | B      |2018-06-21|2018-07-19|
  |  2       | C      |2018-07-13|2018-10-07|
  |  2       | B      |2018-12-31|2019-02-27|
  |  2       | A      |2019-01-28|2019-06-25|
  -------------------------------------------
  </code></pre>
  
  <p>From this dataframe I have to make a dataframe like this,</p>
  
  <pre><code>+----------+--------+----------+----------+
  |id_       | q      |d1        |  d2      |
  +----------+--------+----------+----------+
  |  1       | A      |2018-09-26|2018-10-26|
  |  2       | B      |2018-06-21|2018-07-12|
  |  2       | B C    |2018-07-13|2018-07-19|
  |  2       | C      |2018-07-20|2019-10-07|
  |  2       | B      |2018-12-31|2019-01-27|
  |  2       | B A    |2019-01-28|2019-02-27|
  |  2       | A      |2019-02-28|2019-06-25|
  -------------------------------------------
  </code></pre>
  
  <p>It is something like, finding which values of <code>p</code> are present in the data for a particular <code>id_</code> from when to when. If there are multiple <code>p</code> in a same day then both should be present in the data, seperated by a space.</p>
  
  <p>I tried to do this is by creating each and every dates in the range <code>min(d1)</code> and <code>max(d2)</code> and filling them accordingly. From that dataframe, after some melting and grouping I can get the desired result.</p>
  
  <p>But the process takes very long time and is very inefficient. </p>
  
  <p>I am looking for an efficient method for performing this task.</p>
  
  <p>I can also have more complex cases of overlap, ie overlap among more than two p-values.</p>
  
  <p>See a sample data below,</p>
  
  <pre><code>+----------+--------+----------+----------+
  |id_       | p      |d1        |  d2      |
  +----------+--------+----------+----------+
  |  1       | A      |2018-09-26|2018-10-26|
  |  2       | B      |2018-06-21|2018-07-19|
  |  2       | C      |2018-06-27|2018-07-07|
  |  2       | A      |2018-07-02|2019-02-27|
  |  2       | A      |2019-03-28|2019-06-25|
  -------------------------------------------
  </code></pre>
  
  <p>This must be converted to, </p>
  
  <pre><code>+----------+--------+----------+----------+
  |id_       | q      |d1        |  d2      |
  +----------+--------+----------+----------+
  |  1       | A      |2018-09-26|2018-10-26|
  |  2       | B      |2018-06-21|2018-06-26|
  |  2       | B C    |2018-06-27|2018-07-01|
  |  2       | B C A  |2018-07-02|2018-07-07|
  |  2       | A B    |2018-07-08|2018-07-19|
  |  2       | A      |2018-07-20|2019-02-27|
  |  2       | A      |2019-03-28|2019-06-25|
  -------------------------------------------
  </code></pre>
  
  <p>Order of individual items in the q doesn't matter. ie either if A, B and C are in overlap. It can either be shown as A B C, or B C A or A C B so on.</p>
  
  <p>I am also adding an edge case that is hard to comeby, that is <code>d2 == lead(d1).over(window)</code>. In this case it can be safely assumed, the <code>p</code> values are different. ie <code>p != lead(p).over(window)</code>.</p>
  
  <pre><code>+---+---+----------+----------+
  |id_| p |    d1    | d2       |
  +---+---+----------+----------+
  |100| 12|2013-10-16|2014-01-17|
  |100| 12|2014-01-20|2014-04-15|
  |100| 12|2014-04-22|2014-05-19|
  |100| 12|2014-05-22|2014-06-19|
  |100| 12|2014-07-23|2014-09-18|
  |100| 12|2014-09-23|2014-12-18|
  |100| 12|2014-12-20|2015-01-16|
  |100| 12|2015-01-23|2015-02-19|
  |100| 12|2015-02-21|2015-04-20|
  |100| 7 |2015-04-20|2015-05-17|
  |100| 7 |2015-05-19|2015-06-15|
  |100| 7 |2015-06-18|2015-09-01|
  |100| 7 |2015-09-09|2015-11-26|
  +---+---+----------+----------+
  </code></pre>
  
  <p>In the above data, 4th and 5th row from the bottom shows the case. In this case the expected result is,</p>
  
  <pre><code>+---+-----+----------+----------+
  |id_| p   | d1       | d2       |
  +---+-----+----------+----------+
  |100| 12  |2013-10-16|2014-01-17|
  |100| 12  |2014-01-20|2014-04-15|
  |100| 12  |2014-04-22|2014-05-19|
  |100| 12  |2014-05-22|2014-06-19|
  |100| 12  |2014-07-23|2014-09-18|
  |100| 12  |2014-09-23|2014-12-18|
  |100| 12  |2014-12-20|2015-01-16|
  |100| 12  |2015-01-23|2015-02-19|
  |100| 12  |2015-02-21|2015-04-19|
  |100| 12 7|2015-04-20|2015-04-20|
  |100| 7   |2015-04-21|2015-05-17|
  |100| 7   |2015-05-19|2015-06-15|
  |100| 7   |2015-06-18|2015-09-01|
  |100| 7   |2015-09-09|2015-11-26|
  +---+-----+----------+----------+
  </code></pre>
  
  <p>Another example for the same case is given below,</p>
  
  <pre><code>+---+---+----------+----------+
  |id_| p | d1       | d2       |
  +---+---+----------+----------+
  |101| 12|2015-02-24|2015-03-23|
  |101| 12|2015-04-01|2015-05-19|
  |101| 12|2015-05-29|2015-06-25|
  |101| 12|2015-07-03|2015-07-30|
  |101| 12|2015-09-02|2015-09-29|
  |101| 12|2015-10-02|2015-10-29|
  |101| 9 |2015-10-29|2015-11-11|
  |101| 9 |2015-11-25|2015-12-22|
  +---+---+----------+----------+
  </code></pre>
  
  <p>And the expected result for the same is,</p>
  
  <pre><code>+---+-----+----------+----------+
  |id_| q   | d1       | d2       |
  +---+-----+----------+----------+
  |101| 12  |2015-02-24|2015-03-23|
  |101| 12  |2015-04-01|2015-05-19|
  |101| 12  |2015-05-29|2015-06-25|
  |101| 12  |2015-07-03|2015-07-30|
  |101| 12  |2015-09-02|2015-09-29|
  |101| 12  |2015-10-02|2015-10-28|
  |101| 12 9|2015-10-29|2015-10-29|
  |101| 9   |2015-10-30|2015-11-11|
  |101| 9   |2015-11-25|2015-12-22|
  +---+---+------------+----------+
  </code></pre>