python - 从 pandas DataFrame 中高效扩展行

Question

我是 pandas 的新手，我正在尝试将一个奇怪的格式文件读入 DataFrame。原始文件如下所示:

; No   Time   Date  MoistAve  MatTemp  TDRConduct  TDRAve  DeltaCount  tpAve  Moist1  Moist2  Moist3  Moist4  TDR1  TDR2  TDR3  TDR4
1  11:38:17   11.07.2012  11.37  48.20  5.15  88.87  15  344.50  11.84  11.35  11.59  15.25  89.0  89.0  89.0  88.0
2  11:38:18   11.07.2012  11.44  48.20  5.13  88.88  2  346.22  12.08  11.83  -1.00  -1.00  89.0  89.0  -1.0  -1.0
3  11:38:19   11.07.2012  11.10  48.20  4.96  89.00  3  337.84  11.83  11.59  10.62  -1.00  89.0  89.0  89.0  -1.0
4  11:38:19   11.07.2012  11.82  48.20  5.54  88.60  3  355.92  11.10  13.54  12.32  -1.00  89.0  88.0  88.0  -1.0

我设法获得了一个结构相同的 DataFrame:

In [42]: date_spec = {'FetchTime': [1, 2]}

In [43]: df = pd.read_csv('MeasureCK32450-20120711114050.mck', header=7, sep='\s\s+',
                          parse_dates=date_spec, na_values=['-1.0', '-1.00'])

In [44]: df
Out[52]: 
               FetchTime  ; No  MoistAve  MatTemp  TDRConduct  TDRAve  DeltaCount   tpAve  Moist1  Moist2  Moist3  Moist4  TDR1  TDR2  TDR3  TDR4
0    2012-11-07 11:38:17     1     11.37     48.2        5.15   88.87          15  344.50   11.84   11.35   11.59   15.25    89    89    89    88
1    2012-11-07 11:38:18     2     11.44     48.2        5.13   88.88           2  346.22   12.08   11.83     NaN     NaN    89    89   NaN   NaN
2    2012-11-07 11:38:19     3     11.10     48.2        4.96   89.00           3  337.84   11.83   11.59   10.62     NaN    89    89    89   NaN
3    2012-11-07 11:38:19     4     11.82     48.2        5.54   88.60           3  355.92   11.10   13.54   12.32     NaN    89    88    88   NaN

但现在我必须扩展这个 DataFrame 的每一行

  .... Moist1  Moist2  Moist3  Moist4  TDR1  TDR2  TDR3  TDR4
1 ....  11.84   11.35   11.59   15.25    89    89    89    88
2 ....  12.08   11.83     NaN     NaN    89    89   NaN   NaN

分为四行(三个索引No、FetchTime和MeasureNo):

                                  .... Moist  TDR
No           FetchTime  MeasureNo
 0 2012-11-07 11:38:17          1 .... 11.84   89 # from line 1, Moist1 and TDR1
 1                              2 .... 11.35   89 # from line 1, Moist2 and TDR2
 2                              3 .... 11.59   89 # from line 1, Moist3 and TDR3
 3                              4 .... 15.25   88 # from line 1, Moist4 and TDR4
 4 2012-11-07 11:38:18          1 .... 12.08   89 # from line 2, Moist1 and TDR1
 5                              2 .... 11.83   89 # from line 2, Moist2 and TDR2
 6                              3 ....   NaN  NaN # from line 2, Moist3 and TDR3
 7                              4 ....   NaN  NaN # from line 2, Moist4 and TDR4

通过保留其他列和最重要的列，保留条目的顺序。我知道我可以用 for row in df.iterrows(): ... 遍历每一行，但我读到这是不是很快。我的第一个方法是这样的:

In [54]: data = []
In [55]: for d in range(1,5):
....:     temp = df.ix[:, ['FetchTime', 'MoistAve', 'MatTemp', 'TDRConduct', 'TDRAve', 'DeltaCount', 'tpAve', 'Moist%d' % d, 'TDR%d' % d]]
....:     temp.columns = ['FetchTime', 'MoistAve', 'MatTemp', 'TDRConduct', 'TDRAve', 'DeltaCount', 'tpAve', 'RawMoist', 'RawTDR']
....:     temp['MeasureNo'] = d
....:     data.append(temp)
....:      
In [56]: test = pd.concat(data, ignore_index=True)
In [62]: test.head()
Out[62]: 
             FetchTime  MoistAve  MatTemp  TDRConduct  TDRAve  DeltaCount   tpAve  RawMoist  RawTDR  MeasureNo
0  2012-11-07 11:38:17     11.37     48.2        5.15   88.87          15  344.50     11.84      89          1
1  2012-11-07 11:38:18     11.44     48.2        5.13   88.88           2  346.22     12.08      89          1
2  2012-11-07 11:38:19     11.10     48.2        4.96   89.00           3  337.84     11.83      89          1
3  2012-11-07 11:38:19     11.82     48.2        5.54   88.60           3  355.92     11.10      89          1
4  2012-11-07 11:38:20     12.61     48.2        5.87   88.38           3  375.72     12.80      89          1

但我看不出有什么方法可以影响连接以获得我需要的顺序......是否有另一种方法来获取我需要的结果 DataFrame？

Answer 1

这是一个解决方案，基于 numpy 的重复和数组索引来构建去栈值，以及 pandas 的合并来输出连接的结果。

首先将数据样本加载到 DataFrame 中(稍微更改 read_csv 的参数)。

from cStringIO import StringIO

data = """; No   Time   Date  MoistAve  MatTemp  TDRConduct  TDRAve  DeltaCount  tpAve  Moist1  Moist2  Moist3  Moist4  TDR1  TDR2  TDR3  TDR4
1  11:38:17   11.07.2012  11.37  48.20  5.15  88.87  15  344.50  11.84  11.35  11.59  15.25  89.0  89.0  89.0  88.0
2  11:38:18   11.07.2012  11.44  48.20  5.13  88.88  2  346.22  12.08  11.83  -1.00  -1.00  89.0  89.0  -1.0  -1.0
3  11:38:19   11.07.2012  11.10  48.20  4.96  89.00  3  337.84  11.83  11.59  10.62  -1.00  89.0  89.0  89.0  -1.0
4  11:38:19   11.07.2012  11.82  48.20  5.54  88.60  3  355.92  11.10  13.54  12.32  -1.00  89.0  88.0  88.0  -1.0
"""

date_spec = {'FetchTime': [1, 2]}
df = pd.read_csv(StringIO(data), header=0, sep='\s\s+',parse_dates=date_spec, na_values=['-1.0', '-1.00'])

然后构建一个去堆叠的TDR向量并将其与原始数据框合并

stacked_col_names = ['TDR1','TDR2','TDR3','TDR4']

repeated_row_indexes = np.repeat(np.arange(df.shape[0]),4)
repeated_col_indexes = [np.where(df.columns == c)[0][0] for c in stacked_col_names]

destacked_tdrs = pd.DataFrame(data=df.values[repeated_row_indexes,repeated_col_indexes],index=df.index[repeated_row_indexes],columns=['TDR'])

ouput = pd.merge(left_index = True, right_index = True, left = df, right = destacked_tdrs)

具有所需的输出:

output.ix[:,['TDR1','TDR2','TDR3','TDR4','TDR']]

   TDR1  TDR2  TDR3  TDR4  TDR
0    89    89    89    88   89
0    89    89    89    88   89
0    89    89    89    88   89
0    89    89    89    88   88
1    89    89   NaN   NaN   89
1    89    89   NaN   NaN   89
1    89    89   NaN   NaN  NaN
1    89    89   NaN   NaN  NaN
2    89    89    89   NaN   89
2    89    89    89   NaN   89
2    89    89    89   NaN   89
2    89    89    89   NaN  NaN
3    89    88    88   NaN   89
3    89    88    88   NaN   88
3    89    88    88   NaN   88
3    89    88    88   NaN  NaN