时间戳

首页 > 解决方案 > 比较数据框中的行

python - 比较数据框中的行

问题描述

我的一项任务遇到了麻烦。在我的第一种情况下，我需要比较数据框中的一些变量，然后如果它们相同，它将返回标识符列的相同值。

这是我的多个排序数据框看起来像

| 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

我想要一些代码来比较这个排序数据框中的每一行，如果某些行的 [age, gender, income_group] 相同，它将复制第一个 [no] 列值来替换其他列

该代码将使我的数据框看起来像这样

| 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

有没有可能在python中这样做？

编辑： 我的第二个案例变得更加复杂，我发现一些相同的 [age, gender, income_group] 变量但具有相同的 [cars] 值，我希望在这种情况下将其视为不同的个体不同的 [no] 值

如果扩展数据框并获得一个列，如下所示

| 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

一个人不能拥有相同的汽车价值，代码将使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 解决方案：

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)

我得到：

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

标签： pythonpandasdataframe

解决方案

GroupBy.transform与 一起使用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

或者通过DataFrame.duplicated然后向前填充缺失值来获取第一个值：

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

编辑：

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

推荐阅读