What pandas function is most similar to the transpose procedure in SAS?

Question

I am trying to transpose Amt by Txn_ID across Item_Desc in Python using pandas. Which pandas function(s) is/are most similar to the transpose procedure in SAS?

Below is an example of the input dataframe and the desired output dataframe.

Input DataFrame:

| Txn_ID | Item_Desc | Amt  |
|--------|-----------|------|
| 1      | Apples    | 0.96 |
| 1      | Milk      | 2.10 |
| 2      | Eggs      | 7.00 |
| 2      | Flour     | 2.20 |
| 2      | Salt      | 4.75 |
| 3      | Eggs      | 3.50 |

Output DataFrame:

| Txn_ID | Apples | Eggs | Flour | Milk | Salt |
|--------|--------|------|-------|------|------|
| 1      | 0.96   | NaN  | NaN   | 2.10 | NaN  |
| 2      | NaN    | 7.00 | 2.20  | NaN  | 4.75 |
| 3      | NaN    | 3.50 | NaN   | NaN  | NaN  |

score 1 · Answer 1 · answered Nov 11 '20 at 23:14

The pivot operator can be used to achieve the desired result. As per your example:

Define original data:

import pandas as pd
data = {'Txn_ID': [1, 1, 2, 2, 2, 3], 
        'Item_Desc': ['Apples', 'Milk ', 'Eggs', 'Flour', 'Salt', 'Eggs'],
        'Amt': [0.96, 2.10, 7.00, 2.20, 4.75, 3.50]
}

Create dataframe

df = pd.DataFrame.from_dict(data)

Transpose result using the .pivot operator

df_new = df.pivot(index = 'Txn_ID', columns = 'Item_Desc')



  Txn_ID
  Apples
  Eggs
  Flour
  Milk
  Salt




  1
  0.96
  NaN
  NaN
  2.10
  NaN


  2
  NaN
  7.00
  2.20
  NaN
  4.75


  3
  NaN
  3.50
  NaN
  NaN
  NaN

score 0 · Answer 2 · answered Dec 14 '20 at 00:54

The method to transpose a dataframe is, easily enough, transpose. However, what you are asking about is not transposition, it's reshaping/pivoting. It is unfortunate that SAS has used a term with a well-established meaning to mean something quite different. To pivot in Pandas, you can use pivot or pivot_table. Here is an article on reshaping in Pandas.

What pandas function is most similar to the transpose procedure in SAS?

2 Answers2