Mapping keys in multiple columns with pandas

Question

My df1 data frame

df1

prod_cat_code   prod_cat    prod_sub_cat_code   prod_subcat
1              Clothing                     4   Mens
2              Footwear                     1   Women
1              Clothing                     3   Kids

My df2 data frame

df2

customer_Id prod_sub_cat_code   prod_cat_code
268408                    4     1
268408                    1     1
268408                    1     2
268408                    3     1
268408                    4     2

I want to replace df2 prod_subcat_code with prod_subcat from df1 ;
I want to replace df2 prod_cat_code with prod_cat from df1 ;

Q: Do I need a dictionary and map the values or is there any alternative?

(df2.merge(df1[['prod_cat_code', 'prod_cat']], how='left') .merge(df1[['prod_sub_cat_code', 'prod_subcat']], how='left'))

throw merge error

https://i.stack.imgur.com/CqADR.png

Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — abdoulsn, Jan 04 '20 at 04:34
How many such "xyz" and "xyz_code" column pairs do you have? — cs95, Jan 04 '20 at 04:34
@abdoulsn, no i won't answer. i dont want merge/join need to replace the the df2 from df1, if merge i woul have to df2.merge(df1, left_on=, right_on=) — , Jan 04 '20 at 04:56
@cs95, right now some 25 rows are there, but i also want to if there are some thousand of rows, what need to be done — , Jan 04 '20 at 04:57
I was asking about the columns, not rows - do you only have these 4 columns, or would there be more in your actual data, this would influence the nature of the solution presented. — cs95, Jan 04 '20 at 05:01
Assuming prod_sub_cat_code -> prod_subcat_code is a typo. FTFY. — cs95, Jan 04 '20 at 05:03
@cs95 thanks, no i have lot of columns in df2. in df1 only 4 columns are there. i just put minimal reproducible code. typo has edited — , Jan 04 '20 at 05:31
Merge does indeed solve your problem. Try: `(df2.merge(df1[['prod_cat_code', 'prod_cat']], how='left') .merge(df1[['prod_sub_cat_code', 'prod_subcat']], how='left'))` — cs95, Jan 04 '20 at 05:43
@cs95 MergeError: No common columns to perform merge on. Merge options: left_on=None, right_on=None, left_index=False, right_index=False — , Jan 04 '20 at 06:05
There's an error with the column names: for example, df1 has "prod_sub_cat_code" and df2 has "prod_subcat_code" (no underscore). — cs95, Jan 04 '20 at 06:06
@cs95 that was typo have edited that time itself df2 > `['customer_Id','prod_subcat_code','prod_cat_code']`, df1 > `['prod_cat_code', 'prod_cat', 'prod_sub_cat_code', 'prod_subcat']` — , Jan 04 '20 at 06:09
@cs95, can you remove duplicated from the tag, so that other people can answer. thanks — , Jan 04 '20 at 06:11
Merge answers your question, the fact that there is something wrong with your actual data not shown here is not a sufficient reason to reopen it! If you can edit your question to show what the issue is and why the merge does not work, I'd be happy to reconsider. Cheers! — cs95, Jan 04 '20 at 06:12
I have already explained that your column names are the issue, please read my previous two comments. Change df2 column names so it is identical to those in df1 for the matching columns, then this will work. — cs95, Jan 04 '20 at 07:00

Mapping keys in multiple columns with pandas

0 Answers0