How to convert rows into columns in python

Question

I have a dataframe like below which contains 4 columns. I want to convert each unique inventory item number (Z15, Z17 and so on) under the "inv" column into new columns with "info" value corresponds to each store and period. Transpose function does not work in this situation. Also, if I use pivot_table or groupby function, I won't be able to get the value for "High", "Medium" and so on.

Be noted, for the "info" column, I have many different combination of categorical values along with numerical values in real dataset. Also in the real dataset, I have over 100+ stores, over 400+ inventory items and 30+ periods. This is a simplified version of the data to demonstrate my idea. Any suggestion or advice are greatly appericated.

import pandas as pd
import numpy as np

inv = ['Z15','Z15','Z15','Z15','Z15','Z15','Z15','Z15','Z15','Z17','Z17','Z17','Z17','Z17','Z17','Z17']
store = ['store1','store1','store1','store2','store2','store2','store2','store2','store2','store3','store4','store5','store6','store7','store1','store2']
period = [2018,2019,2020,2015,2016,2017,2018,2019,2020,2022,2022,2022,2022,2022,2018,2019]
info = ['0.84773','0.8487','0.82254','0.75','0.65','0.432','0.546','0.777','0.1','High','High','Medium','Very Low','Low','High','Low']

df = pd.DataFrame({'inv':inv,
                'store':store,
                'period':period,
                'info':info})

Data looks like this:

The desired output will be like this :

Juan C · Accepted Answer · 2022-09-28 17:14:47Z

2

You're looking for pivot :

df.pivot(index = ['store', 'period'], columns='inv' ,values = 'info').reset_index()

Output:

inv   store  period      Z15       Z17
0    store1    2018  0.84773      High
1    store1    2019   0.8487       NaN
2    store1    2020  0.82254       NaN
3    store2    2015     0.75       NaN
4    store2    2016     0.65       NaN
5    store2    2017    0.432       NaN
6    store2    2018    0.546       NaN
7    store2    2019    0.777       Low
8    store2    2020      0.1       NaN
9    store3    2022      NaN      High
10   store4    2022      NaN      High
11   store5    2022      NaN    Medium
12   store6    2022      NaN  Very Low
13   store7    2022      NaN       Low

answered Sep 28, 2022 at 17:14

Juan C

6,1484 gold badges27 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user032020 Over a year ago

Thanks @Juan C a lot for the this. I just realized in my real dataset, there are duplicated index (meaning the same combination of store and period" which throws me an error saying "Index contains duplicate entries, cannot reshape"

user032020 Over a year ago

I think I will take this as an answer as this is a solution to the question I asked. For the issue I encounter now, I may need to open a new question. Thanks again.

Collectives™ on Stack Overflow

How to convert rows into columns in python

1 Answer 1

Output:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Output:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related