Pivot df without introducing NaNs

September 7, 2023

I have a dataframe that I am trying to pivot. However, I am accidentally introducing NaNs in my rows.

Code:

category = ['animal', 'animal', 'animal', 'fruit', 'fruit', 'fruit', 'veggie', 'veggie', 'veggie']
obj = ['animal_1', 'animal_2', 'animal_3', 'fruit_1', 'fruit_2', 'fruit_3', 'veggie_1', 'veggie_2', 'veggie_3']

df = pd.DataFrame(list(zip(category, obj)), columns=['category', 'object'])

# pivot the DataFrame
result = df.pivot(columns='category', values='object')

Instead I need something that would be like the output of this code. Importantly, the below does not have any NaNs.

animals=['animal_1', 'animal_2', 'animal_3']
fruit=['fruit_1', 'fruit_2', 'fruit_3']
veggies=['veggie_1', 'veggie_2', 'veggie_3']

pd.DataFrame(list(zip(animals, fruit, veggies)), columns=['animal', 'fruit', 'veggie'])

>Solution :

IIUC, you can add index:

df["index"] = df.groupby("category").cumcount()

result = df.pivot(index="index", columns="category", values="object")
print(result)

Prints:

category    animal    fruit    veggie
index                                
0         animal_1  fruit_1  veggie_1
1         animal_2  fruit_2  veggie_2
2         animal_3  fruit_3  veggie_3

To get rid of column/index names, you can do then:

result = result.rename_axis(columns=None, index=None)

     animal    fruit    veggie
0  animal_1  fruit_1  veggie_1
1  animal_2  fruit_2  veggie_2
2  animal_3  fruit_3  veggie_3