How to group rows with same ID but different values in two columns into a single row the different v-CodePudding

I have a dataset that looks like this:

df = pd.DataFrame([[1,1,5,4],[1,1,6,3]], columns =['date','site','chemistry','measurement']) df

I'm looking to transform this dataset so that the values in the chemistry and measurement columns become separate columns and the repeated values in the other columns become a single row like this:

new_df = pd.DataFrame([[1,1,4,3]], columns=['date','site','5','6']) new_df

I've tried some basic things like df.transpose() pd.pivot() but this doesn't get me what I need.

The pivot is closer but still not the format I'm looking for.

I'm imagining there's a way to loop through the dataframe to this but I'm not sure how to do it. Any suggestions?

CodePudding user response：

Try this:

 df.set_index(['date','site','chemistry'])['measurement'].unstack().reset_index()

Output:

chemistry  date  site  5  6
0             1     1  4  3