使用groupby对子集进行数据反向排序

Question

我有一个names = ['id','t','metric_1','metric_2','metric_3']的数据格式。我正在对每个grp in groupby('id')运行一些信号处理。我需要逆转另一个进程的时间，即接收整个数据帧并在幕后进行处理。简单地说，给定一个grp，我只需要反转时间列，保留所有其他列不变，而grp中的所有行都不完整。

输入数据：

    id  t   metric_1    metric_2    metric_3
0   0   86  13.333  61.989  0.017444
1   0   87  13.333  61.993  0.017569
2   0   88  13.333  61.992  0.017711
3   0   89  13.333  61.998  0.017746
4   0   90  13.333  61.993  0.017871
5   1   32  13.333  61.964  0.018511
6   1   33  20.000  61.913  0.020058
7   1   34  20.000  61.864  0.022475
8   1   35  26.667  61.802  0.025995
9   1   36  33.123  61.563  0.032345
10  1   37  33.763  61.836  0.060189
11  2   2   13.333  61.964  0.018511
12  2   3   20.000  61.613  0.020058
13  2   4   20.000  61.164  0.027475
14  2   5   26.667  61.802  0.024995
15  2   6   33.333  61.736  0.030689

我希望使用这样的操作来生成一个数据文件：

    id  t   metric_1    metric_2    metric_3
0   0   90  13.333  61.989  0.017444
1   0   89  13.333  61.993  0.017569
2   0   88  13.333  61.992  0.017711
3   0   87  13.333  61.998  0.017746
4   0   86  13.333  61.993  0.017871
5   1   37  13.333  61.964  0.018511
6   1   36  20.000  61.913  0.020058
7   1   35  20.000  61.864  0.022475
8   1   34  26.667  61.802  0.025995
9   1   33  33.333  61.736  0.030689
10  1   32  33.763  61.836  0.060189
11  2   6   13.333  61.964  0.018511
12  2   5   20.000  61.613  0.020058
13  2   4   20.000  61.164  0.027475
14  2   3   26.667  61.802  0.024995
15  2   2   33.333  61.736  0.030689

Answer 1

UPDATE2:排序/替换t列中的值，但仅用于id == 0 (as described in this comment)：

In [373]: df
Out[373]:
   id   t  metric_1  metric_2  metric_3
0   0  86    13.333    61.989  0.017444
1   0  87    13.333    61.993  0.017569
2   0  88    13.333    61.992  0.017711
3   0  89    13.333    61.998  0.017746
4   0  90    13.333    61.993  0.017871
5   1  86    13.333    61.964  0.018511
6   1  87    20.000    61.913  0.020058
7   1  88    20.000    61.864  0.022475
8   1  89    26.667    61.802  0.025995
9   1  90    33.333    61.736  0.030689

In [374]: df.loc[df.id == 0, 't'] = df.loc[df.id == 0, 't'].sort_values(ascending=0).values

In [375]: df
Out[375]:
   id   t  metric_1  metric_2  metric_3
0   0  90    13.333    61.989  0.017444
1   0  89    13.333    61.993  0.017569
2   0  88    13.333    61.992  0.017711
3   0  87    13.333    61.998  0.017746
4   0  86    13.333    61.993  0.017871
5   1  86    13.333    61.964  0.018511
6   1  87    20.000    61.913  0.020058
7   1  88    20.000    61.864  0.022475
8   1  89    26.667    61.802  0.025995
9   1  90    33.333    61.736  0.030689

更新:用于更新数据集的

原始DF：

In [363]: df
Out[363]:
   id   t  metric_1  metric_2  metric_3
0   0  86    13.333    61.989  0.017444
1   0  87    13.333    61.993  0.017569
2   0  88    13.333    61.992  0.017711
3   0  89    13.333    61.998  0.017746
4   0  90    13.333    61.993  0.017871
5   1  86    13.333    61.964  0.018511
6   1  87    20.000    61.913  0.020058
7   1  88    20.000    61.864  0.022475
8   1  89    26.667    61.802  0.025995
9   1  90    33.333    61.736  0.030689

对完整行进行排序：

In [364]: df.sort_values(['id','t'], ascending=[1,0])
Out[364]:
   id   t  metric_1  metric_2  metric_3
4   0  90    13.333    61.993  0.017871
3   0  89    13.333    61.998  0.017746
2   0  88    13.333    61.992  0.017711
1   0  87    13.333    61.993  0.017569
0   0  86    13.333    61.989  0.017444
9   1  90    33.333    61.736  0.030689
8   1  89    26.667    61.802  0.025995
7   1  88    20.000    61.864  0.022475
6   1  87    20.000    61.913  0.020058
5   1  86    13.333    61.964  0.018511   # <--

对两列(['id','t'])的值进行排序，替换它们的值：

In [366]: df[['id','t']] = df[['id','t']].sort_values(['id','t'], ascending=[1,0]).values

In [367]: df
Out[367]:
   id   t  metric_1  metric_2  metric_3
0   0  90    13.333    61.989  0.017444
1   0  89    13.333    61.993  0.017569
2   0  88    13.333    61.992  0.017711
3   0  87    13.333    61.998  0.017746
4   0  86    13.333    61.993  0.017871
5   1  90    13.333    61.964  0.018511
6   1  89    20.000    61.913  0.020058
7   1  88    20.000    61.864  0.022475
8   1  87    26.667    61.802  0.025995
9   1  86    33.333    61.736  0.030689  # <--

旧答案：

您可以简单地按两列对数据进行排序：

In [349]: df.sort_values(['id','t'], ascending=[1,1])
Out[349]:
   id   t  metric_1  metric_2  metric_3
4   0  86    13.333    61.993  0.017871
3   0  87    13.333    61.998  0.017746
2   0  88    13.333    61.992  0.017711
1   0  89    13.333    61.993  0.017569
0   0  90    13.333    61.989  0.017444
9   1  86    33.333    61.736  0.030689
8   1  87    26.667    61.802  0.025995
7   1  88    20.000    61.864  0.022475
6   1  89    20.000    61.913  0.020058
5   1  90    13.333    61.964  0.018511

如果希望按照所需的数据集(替换t列值)对其进行排序：

In [357]: df[['id','t']] = df[['id','t']].sort_values(['id','t']).values

In [358]: df
Out[358]:
   id   t  metric_1  metric_2  metric_3
0   0  86    13.333    61.989  0.017444
1   0  87    13.333    61.993  0.017569
2   0  88    13.333    61.992  0.017711
3   0  89    13.333    61.998  0.017746
4   0  90    13.333    61.993  0.017871
5   1  86    13.333    61.964  0.018511
6   1  87    20.000    61.913  0.020058
7   1  88    20.000    61.864  0.022475
8   1  89    26.667    61.802  0.025995
9   1  90    33.333    61.736  0.030689   # 1 90 33.333 61.736 0.030689 as in your desired DF

Answer 2

如果要反转保留所有其他列的“t”列，可以尝试以下代码：

df.t=df['t'].sort_values(ascending=False)