CODEWITHSUNDEEP
pythonpandas
Padfoot123
Padfoot123

Reputation: 1117

Pandas transpose rows to columns based on first column

I have the below dataframe.

Column_1   Column_2
Name       Xxxx
Age        28
Gender     M
Name       yyyy
Age        26
Gender     F

My expected output is

Name   Age   Gender
Xxxx   28    M
yyyy   26    F

I tried df.T(), but it's writing each name, age and gender to separate columns.

How to achieve the above output in python/pandas.

Upvotes: 4

Views: 961

Answers (3)

not_speshal
not_speshal

Reputation: 23146

Try with groupby and pivot:

df["idx"] = df.groupby("Column_1").cumcount()

>>> df.pivot("idx", "Column_1", "Column_2").reset_index(drop=True).rename_axis(columns=None)
  Age Gender  Name
0  28      M  Xxxx
1  26      F  Yyyy

Upvotes: 2

U13-Forward
U13-Forward

Reputation: 71560

Or try groupby with agg and pd.Series.explode:

>>> df.groupby('Column_1').agg(list).T.apply(pd.Series.explode).reset_index(drop=True).rename_axis(columns=None)
  Age Gender  Name
0  28      M  Xxxx
1  26      F  yyyy
>>>

Upvotes: 3

I'mahdi
I'mahdi

Reputation: 24049

try this:

df1 = pd.DataFrame( {
    'Column_1': ['Name', 'Age', 'Gender', 'Name', 'Age', 'Gender'],
    'Column_2': ['Xxxx', '28', 'M', 'yyyy', '26', 'F']
})
df2 = pd.DataFrame(df1.groupby('Column_1')['Column_2'].apply(list).to_dict())
print(df2)

Output:

   Age Gender  Name
0  28      M   Xxxx
1  26      F   yyyy

Upvotes: 2

Related Questions