i have trouble with one of my task. in my first case, i need to comparing some variables in my dataframe, then if they are the same, it will return a same value of identifier column.

here's my multiple sorted dataframe looks like

| no | age| gender | income_group | cars
| 1  | 15 |  male  |       0      | ford
| 2  | 15 |  male  |       0      | renault
| 3  | 15 |  female|       1      | bmw
| 4  | 16 |  female|       1      | bmw
| 5  | 16 |  female|       1      | mercedes
| 6  | 16 |  female|       1      | honda

i want some code that will compare each rows at this sorted dataframe and if [age, gender, income_group] identically the same for some rows, it will copying the first [no] columns value to replace the others

the code will make my dataframe looks like this

| no | age| gender | income_group | cars
| 1  | 15 |  male  |       0      | ford
| 1  | 15 |  male  |       0      | renault
| 3  | 15 |  female|       1      | bmw
| 4  | 16 |  female|       1      | bmw
| 4  | 16 |  female|       1      | mercedes
| 4  | 16 |  female|       1      | honda

is there any possible way to do like this in python?

Edited: my second case get more complicated where i find some identical [age, gender, income_group] variables but has the same [cars] value, i want it to be consider as different individual in this case different [no] values

if expand the dataframe and get a colomn looks like this

| no | age| gender | income_group | cars
| 1  | 15 |  male  |       0      | ford
| 2  | 15 |  male  |       0      | renault
| 3  | 15 |  female|       1      | bmw
| 4  | 16 |  female|       1      | bmw
| 5  | 16 |  female|       1      | mercedes
| 6  | 16 |  female|       1      | honda

| 7  | 17 |  male  |       0      | bmw
| 8  | 17 |  male  |       0      | honda
| 9  | 17 |  male  |       0      | bmw
| 10 | 17 |  male  |       0      | honda
| 11 | 17 |  male  |       0      | renault

one person can't has the same cars value, the code will make the df:

| 7  | 17 |  male  |       0      | bmw
| 7  | 17 |  male  |       0      | honda
| 9  | 17 |  male  |       0      | bmw
| 9  | 17 |  male  |       0      | honda
| 9  | 17 |  male  |       0      | renault

whit jezrael solution:

df['a'] = df.duplicated(['age','gender','income_group', 'cars'], keep=False).cumsum()

df['no'] = df.groupby(['age','gender','income_group','a'], sort=False)['no'].transform('first')
df = df.drop('a', axis=1)

i get:

no  age  gender  income_group      cars  a
 0   15    male             0      ford  0
 0   15    male             0   renault  0
 2   15  female             1       bmw  0
 3   16  female             1       bmw  0
 3   16  female             1  mercedes  0
 3   16  female             1     honda  0
 6   17    male             0       bmw  1
 7   17    male             0     honda  2
 8   17    male             0       bmw  3
 9   17    male             0     honda  4
 9   17    male             0   reanult  4

1 Answers

6
jezrael On Best Solutions

Use GroupBy.transform with GroupBy.first:

df['no'] = df.groupby(['age','gender','income_group'], sort=False)['no'].transform('first')
print (df)
   no  age  gender  income_group      cars
0   1   15    male             0      ford
1   1   15    male             0   renault
2   3   15  female             1       bmw
3   4   16  female             1       bmw
4   4   16  female             1  mercedes
5   4   16  female             1     honda

Or get first values by DataFrame.duplicated and then forward filling missing values:

df['no'] = df.loc[(~df.duplicated(['age','gender','income_group'])), 'no']
df['no'] = df['no'].ffill().astype(int)
print (df)
   no  age  gender  income_group      cars
0   1   15    male             0      ford
1   1   15    male             0   renault
2   3   15  female             1       bmw
3   4   16  female             1       bmw
4   4   16  female             1  mercedes
5   4   16  female             1     honda

EDIT:

df['a'] = df.duplicated(['age','gender','income_group', 'cars'])
mask = df.groupby(['age','gender','income_group'])['a'].transform('any')

df.loc[mask, 'no'] = df.groupby(df.loc[mask].groupby('cars').cumcount(ascending=False))['no'].transform('first')
df = df.drop('a', axis=1)              
print (df)
     no  age  gender  income_group      cars
0   1.0   15    male             0      ford
1   2.0   15    male             0   renault
2   3.0   15  female             1       bmw
3   4.0   16  female             1       bmw
4   5.0   16  female             1  mercedes
5   6.0   16  female             1     honda
6   7.0   17    male             0       bmw
7   7.0   17    male             0     honda
8   9.0   17    male             0       bmw
9   9.0   17    male             0     honda
10  9.0   17    male             0   reanult