Pandas - How to combine duplicate items into one with several columns

Question

I have the below DataFrame

As you can see, ItemNo 1 is duplicated three times, and each column has a value corresponding to it.

I am looking for a method to check against all columns, and if they match then put Price, Sales, and Stock as one entry, not three.

Any help will be greatly appreciated.

Answer 1

Simply remove all the NaN instances and redefine the column names

df = df1.apply(lambda x: pd.Series(x.dropna().values), axis=1)
df.columns = ['ItemNo','Category','SIZE','Model','Customer','Week Date','<New col name>']

For converging to one row, you can use groupby like this

df.groupby('ItemNo', as_index=False).first()