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 |
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP