I am pretty new to Python and Pandas and I struggle with combining a messy dataframe from excel with a mapping. I have tried to find some solutions on the Internet, however with no success.
My first df_1 is as followed:
As you can see the information in columns Val_1 - Val_4 are inserted in a random order. What I would like to achieve is to make all the Vals in the same order as it is in the df_mapping, so that I could merge these data frames together using eg. pd.merge and also possibly create some pivot table, etc.
The df_mapping table is as followed:
and the df_result would be as followed:
The Val data which is not in the mapping could be deleted from the df_result. I dealt with the problem by creating all possible variations in the mapping manually and then merging the data frames, however, the number of products and possible combinations are growing. What is more current df_result is still messy.
I would be very grateful for any support.