Populate value for another column based on category python

Question

I have two columns:

           number
apple         2
banana        3
grape         25
cat           4
jelly         1

I need to find unique values that each category contains. This is how you could create a new df

import numpy as np
import pandas as pd    
df = pd.DataFrame({'category':['apple', 'banana', 'grape', 'cat', 'jelly'],
                       'number':[2,3,25,4,1]})

I have this another df which needs to be filled with the value.

category    name number
apple       aliya   2
banana      reeva   3
apple       dev     2
grape       ruby    25
cat         cosmo   4
jelly       jack    1
cat         goldy   4
grape       frr    25

Do you want the count of unique values in each category and then merge it with other dataframe? — Muhammad Hassan, Oct 05 '21 at 07:23
I just want the number column to populate the second df, according to category. — chixcy, Oct 05 '21 at 07:24
@Muhammadhassan ahh the first df is a bit different, I have edited it. It is the index. How do I name this index as category? — chixcy, Oct 05 '21 at 07:40
Try df1 = df1.reset_index().rename(columns={'index': 'category'}), then merge. — Muhammad Hassan, Oct 05 '21 at 07:45
@Muhammadhassan AttributeError: 'tuple' object has no attribute 'reset_index' — chixcy, Oct 05 '21 at 07:48
Are you sure df1 is a dataframe. Check the output of type(df1) — Muhammad Hassan, Oct 05 '21 at 07:48
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/237813/discussion-between-chixcy-and-muhammad-hassan). — chixcy, Oct 05 '21 at 07:53

score 0 · Answer 1 · edited Oct 05 '21 at 15:16

You can apply:

import pandas as pd
df = pd.DataFrame({'category':['apple', 'banana', 'grape', 'cat', 'jelly'],
                       'number':[2,3,25,4,1]})

df2 = pd.DataFrame({'category':['apple', 'banana', 'apple', 'grape', 'cat', 'jelly', 'cat', 'grape'],
                    'name':['a', 'b', 'ple', 'rap', 'ct', 'jly', 't', 'gape']})

new_col = []
for index, row in df2.iterrows():
    new_col.append(df.loc[df['category'] == row['category'], 'number'].values[0])
df2['number'] = new_col
print(df2)

Output:

  category  name  number
0    apple     a       2
1   banana     b       3
2    apple   ple       2
3    grape   rap      25
4      cat    ct       4
5    jelly   jly       1
6      cat     t       4
7    grape  gape      25

Populate value for another column based on category python

1 Answers1