0
$\begingroup$

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  |
$\endgroup$
0
$\begingroup$

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  |
$\endgroup$
0
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.