0

I have a table that looks like this:

+---------+--------------+----------------+-------------+----------------+
| Product | Prior Period | Current Period | Next Period | Values_Current |
+---------+--------------+----------------+-------------+----------------+
| AAA     | DEC21        | JAN22          | FEB22       | 1234           |
+---------+--------------+----------------+-------------+----------------+
| AAA     | JAN22        | FEB22          | MAR22       | ABCD           |
+---------+--------------+----------------+-------------+----------------+
| AAA     | FEB22        | MAR22          | APR22       | abcd           |
+---------+--------------+----------------+-------------+----------------+
| BBB     | DEC21        | JAN22          | FEB22       | 5678           |
+---------+--------------+----------------+-------------+----------------+
| BBB     | JAN22        | FEB22          | MAR22       | EFGH           |
+---------+--------------+----------------+-------------+----------------+
| BBB     | DEC22        | MAR22          | APR22       | efgh           |
+---------+--------------+----------------+-------------+----------------+

I would like to generate the following table, where I can have the value of current period, previous period, and next period all in the same row.

+---------+--------------+----------------+-------------+--------------+----------------+-------------+
| Product | Prior Period | Current Period | Next Period | Values_Prior | Values_Current | Values_Next |
+---------+--------------+----------------+-------------+--------------+----------------+-------------+
| AAA     | DEC21        | JAN22          | FEB22       | N/A          | 1234           | ABCD        |
+---------+--------------+----------------+-------------+--------------+----------------+-------------+
| AAA     | JAN22        | FEB22          | MAR22       | 1234         | ABCD           | abcd        |
+---------+--------------+----------------+-------------+--------------+----------------+-------------+
| AAA     | FEB22        | MAR22          | APR22       | ABCD         | abcd           | N/A         |
+---------+--------------+----------------+-------------+--------------+----------------+-------------+
| BBB     | DEC21        | JAN22          | FEB22       | N/A          | 5678           | EFGH        |
+---------+--------------+----------------+-------------+--------------+----------------+-------------+
| BBB     | JAN22        | FEB22          | MAR22       | 5678         | EFGH           | efgh        |
+---------+--------------+----------------+-------------+--------------+----------------+-------------+
| BBB     | DEC22        | MAR22          | APR22       | EFGH         | efgh           | N/A         |
+---------+--------------+----------------+-------------+--------------+----------------+-------------+

How exactly do I achieve that?

Right now I am thinking about doing self join for 2 times:

  • 1st time with df[prior period] = df[current period] as matching key
  • 2nd time with df[next period] = df[current period] as matching key ...

Is this a right approach? If not, is there a better way to do this?

Much appreciation for your help!

DPatrick
  • 59
  • 1
  • 7
  • why not just use `.shift()` – Chris May 04 '22 at 17:32
  • 1
    Does this answer your question? [Use pandas.shift() within a group](https://stackoverflow.com/questions/53335567/use-pandas-shift-within-a-group) – Ynjxsjmh May 04 '22 at 17:33
  • @Ynjxsjmh Does it matter that the real dataset where I have "JAN22" and "FEB22" etc. are not integers nor timestamps, but only strings? – DPatrick May 04 '22 at 17:41
  • @DPatrick It doesn't matter. But it occurs to me that your example data may not clear to illustrate what you want to do. If there is only `JAN22` and `MAR22` in column, will the logic still the same? – Ynjxsjmh May 04 '22 at 17:43

0 Answers0