how to categorise data depending upon a category column in another worksheet and displaying them in a new column

Question

I want to use the column 'Qual' in Table 1 and categorise them according to the 'Level' in Table 2 in a separate worksheet, further displaying a separate column 'Qual level' of those levels in table 1

Table1:

ID	Qual
1234	a
3454	b
2767	c
2098	a

Table2:

Qual	level
a	AA
b	BB
c	CC

Final output required:

ID	Qual	Qual Level
1234	a	AA
3454	b	BB
2767	c	CC
2098	a	AA

this is what I have tried so far:

df.groupby(['Qual','Level'])['Qual Level']

its been giving me an error and im not sure if this is the right method.

score 1 · Answer 1 · answered Apr 14 '22 at 15:30

1

This is a typical join operation. You can use pd.merge:

new_df = df.merge(other_df, on='Qual', how='left')

Then to rename the level column,

new_df = new_df.rename(columns={'level': 'Qual Level'})

answered Apr 14 '22 at 15:30

rcshon

907
1
7
12

Ynjxsjmh · Answer 2 · 2022-04-14T15:34:23.767

0

You can try pd.merge

df1['Qual Level'] = df1.merge(df2, on='Qual', how='left')['level']

print(df1)

     ID Qual Qual Level
0  1234    a         AA
1  3454    b         BB
2  2767    c         CC
3  2098    a         AA

edited Apr 14 '22 at 15:34

answered Apr 14 '22 at 15:29

Ynjxsjmh

28,441
6
34
52

both the tables are in the same file on separate sheets. Do I need to assign the tables separately if so how? – Jibran Khatib Apr 14 '22 at 15:32
@JibranKhatib Sorry, I don't quite understand what does it means. – Ynjxsjmh Apr 14 '22 at 15:34
the data in table 1 and table 2 are both in one excel sheet. since you have defined df1 and df2. do i need to assign the tables as such ? – Jibran Khatib Apr 14 '22 at 15:37
yes, we assumed you have already loaded the tables in as separate dataframes – rcshon Apr 14 '22 at 15:39
`df1` is the dataframe converted from your table 1, `df2` is the dataframe converted from your table 2. – Ynjxsjmh Apr 14 '22 at 15:39

how to categorise data depending upon a category column in another worksheet and displaying them in a new column

2 Answers2