CODEWITHSUNDEEP
pythonpandasdataframe
Will Wild
Will Wild

Reputation: 137

How do I create new columns from duplicate obeservations and transpose the related data with Python in a Pandas DataFrame?

I have a DataFrame with a column named Stat that has duplicate observations. The Valuecolumn has data respective to the Stat column in its same row. How do I create the new columns name loca and IDnu while transposing their respective data from the aforementioned Value column?

What I have:

     Stat | Value
---------------------
0  | name |   cobras
1  | loca |   DC
2  | IDnu |   2
3  | name |   pythons
4  | loca |   LA
5  | IDnu |   1

What I want:

   name |loca| IDnu
---------------------
cobras  | DC | 2
pythons | LA | 1

Upvotes: 2

Views: 63

Answers (2)

ansev
ansev

Reputation: 30920

Use DataFrame.pivot_table and to get index GroupBy.cumcount:

new_df = (df.pivot_table(index=df.groupby('Stat').cumcount(),
                         columns='Stat',
                         values='Value',
                         aggfunc=''.join)
             .rename_axis(columns=None)
             .sort_index(ascending=False ,axis=1)
         )
print(new_df)

Output

      name loca IDnu
0   cobras   DC    2
1  pythons   LA    1

Upvotes: 2

Umar.H
Umar.H

Reputation: 23099

IIUC,

we can agg and then your values into a list, we then pass this into a dictionary to create a new dataframe.

new_df = pd.DataFrame(df.groupby("Stat")["Value"].agg(list).to_dict())
# your column order.
cols = df['Stat'].unique()

print(new_df[cols])
      name loca IDnu
0   cobras   DC    2
1  pythons   LA    1

Upvotes: 1

Related Questions