10
$\begingroup$

I have two data frames df1 and df2 which look something like this. 

    cat1    cat2  cat3
0   10       25     12  
1   11       22     14
2   12       30     15

   all_cats  cat_codes
0   10       A     
1   11       B 
2   12       C
3   25       D
4   22       E
5   30       F
6   14       G

I would like a DataFrame where each column in df1 is created but replaced with cat_codes. Column header names are different. I have tried join and merge but my number of rows are inconsistent. I am dealing with huge number of samples (100,000). My output should ideally be this:

    cat1    cat2  cat3
0    A        D     C  
1    B        E     Y
2    C        F     Z

The resulting columns should be appended to df1.

Improve this question
$\endgroup$
8
$\begingroup$

You can convert df2 to a dictionary and use that to replace the values in df1

cat_1 = [10, 11, 12]
cat_2 = [25, 22, 30]
cat_3 = [12, 14, 15]

df1 = pd.DataFrame({'cat1':cat_1, 'cat2':cat_2, 'cat3':cat_3})

all_cats = [10, 11, 12, 25, 22, 30, 15]
cat_codes = ['A', 'B', 'C', 'D', 'E', 'F', 'G']

df2 = pd.DataFrame({'all_cats':all_cats, 'cat_codes':cat_codes})

rename_dict = df2.set_index('all_cats').to_dict()['cat_codes']

df1 = df1.replace(rename_dict)

If you still have some values that aren't in your dictionary and want to replace them with Z, you can use a regex to replace them.

df1.astype('str').replace({'\d+': 'Z'}, regex=True)
Improve this answer
$\endgroup$
3
  • $\begingroup$ Thank you for your response. I want to create columns but not replace them and these data frames are of high cardinality which means cat_1,cat_2 and cat_3 are not the only columns in the data frame. Of course, I can convert these columns into lists and use your solution but I am looking for an elegant way of doing this. Do you think 'joins' would help? $\endgroup$ – Danny Oct 17 '18 at 8:44
  • $\begingroup$ Just to be clear, you wouldn't need to convert these columns into lists. You're simply changing df2 into a dictionary and using that to replace values in the data frame. I have a question: do you have other values in this dataframe that you don't want to replace, but take the same value as something in all_cats? For example, do you only want to replace cat_1, cat_2, and cat_3, but want to leave cat_4 alone? If so, is any value in cat_4 equal to any value in all_cats? Let me know if I'm not making sense... $\endgroup$ – Stephen Witkowski Oct 17 '18 at 12:22
  • $\begingroup$ Yes. You are right. I want to leave the other columns alone but the other columns may or may not match the values in all_cats. $\endgroup$ – Danny Oct 17 '18 at 12:39
4
$\begingroup$ 
df3 = pd.merge(df1,df2,left_on=['cat'+str(i)], right_on = ['cat_codes'], how = 'left')

I would iterate this for cat1,cat2 and cat3. This does not replace the existing column values but appends new columns.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.