合并和排序单个数据帧中单个列中的所有索引列

Question

我有一个看起来像这样的数据帧。它有更多的时间轴直到Time[s].30.

Time[s]    v1   Time[s].1   v2
160.84621   0   160.84808   7
161.14613   0   161.14802   7
161.538245  27  161.540085  7
162.01598   27  162.017865  7
162.31589   27  162.317775  7
162.615855  27  162.617735  7
162.915765  27  162.91765   7
163.21574   27  163.217625  7
163.51569   27  163.517575  7
163.81563   27  163.81751   7
164.11554   27  164.117425  7
164.4155    27  164.41738   9
164.71543   27  164.717315  9
165.015405  27  165.017285  9
165.31532   27  165.317205  9
165.65083   26  165.65272   9
165.95025   26  165.95214   9

我想要一个Time[s].general时间轴，它是所有具有排序值的时间列的合并形式。我已经为所有这些列编制了索引。

df.set_index(keys=list(file_read.filter(like='Time[s]').columns))

更新：

预期产出：

Time[s]      v1     v2
160.84621   0      null 
160.84808   null     7
160.14613   0      null
161.14802   null     7
161.538245  27     null
161.540085  null     7
162.01598   27     null
162.017865  null     7
162.31589   27     null
162.317775  null     7

等等。

更新 2：

Time[s]    v1   Time[s].1   v2      Time[s].2   v3
160.84621   0   160.84808   7   158.538395  Active
161.14613   0   161.14802   7   158.538515  Active
161.538245  27  161.540085  7   159.49455   Active
162.01598   27  162.017865  7   162.352395  Locked
162.31589   27  162.317775  7   163.35075   Locked
162.615855  27  162.617735  7   164.350675  Locked
162.915765  27  162.91765   7   165.350655  Locked
163.21574   27  163.217625  7   166.509695  Locked
163.51569   27  163.517575  7   166.509815  Locked
163.81563   27  163.81751   7   167.50086   Locked
164.11554   27  164.117425  7   168.50085   Locked
164.4155    27  164.41738   9   169.500865  Locked
164.71543   27  164.717315  9   171.502655  Standby
165.015405  27  165.017285  9   185.89923   Forward
165.31532   27  165.317205  9   3273.448065 Forward
165.65083   26  165.65272   9   3274.43487  Forward
165.95025   26  165.95214   9   3275.4348   Forward

Answer 1

我认为需要：

b  = df.filter(like='v').columns
d = {x: 'v.{}'.format(i) for i, x in enumerate(b)}
d['Time[s]'] = 'Time[s].0'
print (d)
{'v1': 'v0', 'v2': 'v1', 'Time[s]': 'Time[s].0'}
df = df.rename(columns=d)
L = [x.set_index(x.columns[0]) for i, x in df.groupby(lambda x: x.split('.')[-1], axis=1)]
df = pd.concat(L, axis=1)
print (df.head(10))
v.0  v.1
160.846210   0.0  NaN
160.848080   NaN  7.0
161.146130   0.0  NaN
161.148020   NaN  7.0
161.538245  27.0  NaN
161.540085   NaN  7.0
162.015980  27.0  NaN
162.017865   NaN  7.0
162.315890  27.0  NaN
162.317775   NaN  7.0

消遣：

1. 前filter列v所有字典列，用于将时间戳与值列配对。
2. renamebydict，也是第一timestamp列
3. 在列表推导式中.后按列的值groupby，按set_index和一起concat创建索引

编辑：

如果数值和重复的时间戳聚合是按mean，如果不是，则聚合first：

b  = df.filter(like='v').columns
d = {x: 'v.{}'.format(i) for i, x in enumerate(b)}
d['Time[s]'] = 'Time[s].0'
print (d)
{'v1': 'v0', 'v2': 'v1', 'Time[s]': 'Time[s].0'}
df = df.rename(columns=d)
L = [x.groupby(x.columns[0]).mean() 
if np.issubdtype(df[x.columns[1]].dtype, np.number)
else x.groupby(x.columns[0]).first() 
for i, x in df.groupby(df.columns.str.split('.').str[-1], axis=1)]
df = pd.concat(L, axis=1)
print (df.head(10))
v.0  v.1     v.2
158.538395   NaN  NaN  Active
158.538515   NaN  NaN  Active
159.494550   NaN  NaN  Active
160.846210   0.0  NaN     NaN
160.848080   NaN  7.0     NaN
161.146130   0.0  NaN     NaN
161.148020   NaN  7.0     NaN
161.538245  27.0  NaN     NaN
161.540085   NaN  7.0     NaN
162.015980  27.0  NaN     NaN