合并和排序单个datafram中单个列中的所有索引列

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. 第一^{}列所有v列用于字典，用于将时间戳与值列配对。你知道吗
2. rename乘dict，也是第一列timestamp
3. groupby根据列表理解中.之后的columna值，通过^{}和^{}一起创建索引

编辑：

如果数值和重复的时间戳聚合按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