0

Current dataframe:

Current Dataframe

Expected dataframe:

Expected Output

I've tried different things like pivot_table, set_index, unstack, etc. but it's not working as expected. My goal is to have a new dataframe with each row based on a unique combination of 'Col 1', 'Col 2' and 'Col 3'. Also, I need to somehow transpose my data in 'Col 4' and 'Col 5' into different columns with the values in 'Col 4'serving as my new columns. I'm having trouble reshaping the dataframe as I not only have duplicate index values but I also need to transpose on duplicate values in 'Col 4'. Any ideas on how could we accomplish this?

[Apologies for the links of the images for current dataframe and expected result. I could only upload a link in the post, as I'm new to this platform]

Ari Cooper-Davis
  • 3,374
  • 3
  • 26
  • 43
KK045
  • 1
  • 3
  • Does this answer your question? [how-can-i-pivot-a-dataframe](https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe) – Anurag Dabas Aug 17 '21 at 14:00
  • Not exactly, as Col 4 in the Current Dataframe could have duplicate values for each unique value in Col 1. – KK045 Aug 17 '21 at 14:15

1 Answers1

0

Try this:

df = pd.DataFrame({'Col 1': [1, 2, 3], 'Col 2': ['abc'] * 3, 'Col 3': ['xyz']*3, 'Col 4': ['a', 'b', 'a'], 'Col 5': pd.date_range("2021-08-17", periods=3, freq="D")})
df = df.sort_values('Col 4')
df[['Col 4']].groupby(['Col 4']).cumcount()+1
df[['tmp']] = df[['Col 4']].groupby(['Col 4']).cumcount()+1
df[['tmp']] = df[['tmp']].astype('str')
df[['Col 4']] = df['Col 4'] + ' ( ' + df['tmp'] +' )'
pd.pivot(df, values='Col 5', index=['Col 1', 'Col 2', 'Col 3'], columns=['Col 4'])
elouassif
  • 308
  • 1
  • 10