0

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?

Nick C
  • 3
  • 2
  • 1
    Please see [how to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). – BigBen Jan 03 '22 at 18:59
  • 1
    Please [edit] to include sample input and expected output in the text of your question, not as a link or image, so that we can reproduce it. See [How to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – G. Anderson Jan 03 '22 at 19:00

1 Answers1

0

Try this:

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

Output:

chemistry  date  site  5  6
0             1     1  4  3
Scott Boston
  • 147,308
  • 15
  • 139
  • 187