TransWikia.com

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

Data Science Asked by Bojan on March 4, 2021

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  |

2 Answers

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  |

Answered by Burger on March 4, 2021

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.

Answered by Acccumulation on March 4, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP