pandas pivot data frame, make values of one column as a column and concatenate values of another column

user_8375624 0
Viewed 544

I have the following dataframe

df = pd.DataFrame(
   {
       "id": [1, 1, 2, 2],
       "name": ["alpha", "alpha", "bravo","bravo"],
       "term_type": ["hist", "hist", "hist", "bio"] ,
       "term": ["delta9", "delta10", "delta1", "alpha1"]

    })

id    name   term_type  type
1     Alpha  hist       delta9
1     Alpha  hist       delta10
2     Bravo  hist       delta1
2     Bravo  bio        alpha1

What I am trying to do is group by the first two columns and create a cross tab where the values in term_type become column name and type becomes column value and where there same term_type types are concatenated via || .

id    name    hist                      bio
1     Alpha   delta9||delta10           nan
2     Bravo   delta1                    alpha1

I have been trying pandas pivot table but to no avail

pandas python
1 Answers

You can use .pivot_table() with aggfunc (aggregate function) to join the values of column type with |, as follows:

df_out = (df.pivot_table(index=['id', 'name'], columns='term_type', values='term', aggfunc='|'.join)
            .rename_axis(columns=None)
         ).reset_index()

Result:

print(df_out)

   id   name     bio            hist
0   1  alpha     NaN  delta9|delta10
1   2  bravo  alpha1          delta1
user_150706970
Related