How to pivot columns so they turn into rows using PySpark or pandas?-CodePudding

I have a dataframe that looks like the one bellow, but with hundreds of rows. I need to pivot it, so that each column after Region would be a row, like the other table bellow.

 -------------- ---------- --------------------- ---------- ------------------ ------------------ ----------------- 
|city          |city_tier | city_classification |  Region  | Jan-2022-orders  | Feb-2022-orders  |  Mar-2022-orders|
 -------------- ---------- --------------------- ---------- ------------------ ------------------ ----------------- 
|new york      | large    |    alpha            |   NE     | 100000           |195000            | 237000          |
|los angeles   | large    |    alpha            |   W      | 330000           |400000            | 580000          |

I need to pivot it using PySpark, so I end up with something like this:

 -------------- ---------- --------------------- ---------- ----------- --------- 
|city          |city_tier | city_classification |  Region  | month     | orders  |
 -------------- ---------- --------------------- ---------- ----------- --------- 
|new york      | large    |    alpha            |   NE     | Jan-2022  | 100000  |
|new york      | large    |    alpha            |   NE     | Fev-2022  | 195000  |
|new york      | large    |    alpha            |   NE     | Mar-2022  | 237000  |
|los angeles   | large    |    alpha            |   W      | Jan-2022  | 330000  |
|los angeles   | large    |    alpha            |   W      | Fev-2022  | 400000  |
|los angeles   | large    |    alpha            |   W      | Mar-2022  | 580000  |

P.S.: A solution using pandas would work too.

CodePudding user response：

In PySpark, your current example:

from pyspark.sql import functions as F

df = spark.createDataFrame(
    [('new york', 'large', 'alpha', 'NE', 100000, 195000, 237000),
     ('los angeles', 'large', 'alpha', 'W', 330000, 400000, 580000)],
    ['city', 'city_tier', 'city_classification', 'Region', 'Jan-2022-orders', 'Feb-2022-orders', 'Mar-2022-orders']
)

df2 = df.select(
    'city', 'city_tier', 'city_classification', 'Region',
    F.expr("stack(3, 'Jan-2022', `Jan-2022-orders`, 'Fev-2022', `Feb-2022-orders`, 'Mar-2022', `Mar-2022-orders`) as (month, orders)")
)
df2.show()
#  ----------- --------- ------------------- ------ -------- ------ 
# |       city|city_tier|city_classification|Region|   month|orders|
#  ----------- --------- ------------------- ------ -------- ------ 
# |   new york|    large|              alpha|    NE|Jan-2022|100000|
# |   new york|    large|              alpha|    NE|Fev-2022|195000|
# |   new york|    large|              alpha|    NE|Mar-2022|237000|
# |los angeles|    large|              alpha|     W|Jan-2022|330000|
# |los angeles|    large|              alpha|     W|Fev-2022|400000|
# |los angeles|    large|              alpha|     W|Mar-2022|580000|
#  ----------- --------- ------------------- ------ -------- ------

The function which enables it is stack. It does not have a dataframe API, so you need to use expr to access it.

BTW, this is not pivoting, it's the opposite - unpivoting.

CodePudding user response：

In pandas :

df.melt(df.columns[:4], var_name = 'month', value_name = 'orders')

      city city_tier city_classification Region            month  orders
0     york     large               alpha     NE  Jan-2022-orders  100000
1  angeles     large               alpha      W  Jan-2022-orders  330000
2     york     large               alpha     NE  Feb-2022-orders  195000
3  angeles     large               alpha      W  Feb-2022-orders  400000
4     york     large               alpha     NE  Mar-2022-orders  237000
5  angeles     large               alpha      W  Mar-2022-orders  580000

or even

df.melt(['city', 'city_tier', 'city_classification', 'Region'], 
         var_name = 'month', value_name = 'orders')


      city city_tier city_classification Region            month  orders
0     york     large               alpha     NE  Jan-2022-orders  100000
1  angeles     large               alpha      W  Jan-2022-orders  330000
2     york     large               alpha     NE  Feb-2022-orders  195000
3  angeles     large               alpha      W  Feb-2022-orders  400000
4     york     large               alpha     NE  Mar-2022-orders  237000
5  angeles     large               alpha      W  Mar-2022-orders  580000