python - 重组 Pandas DataFrame

Question

有人建议我从类结构(定义我自己的类)转移到 pandas DataFrame 领域，因为我设想对我的数据进行许多操作。

此时我有一个如下所示的数据框:

   ID   Name    Recording   Direction   Duration    Distance    Path Raw
    0   129 Houston Woodlands   X   12.3    8   HWX.txt
    1   129 Houston Woodlands   Y   12.3    8   HWY.txt
    2   129 Houston Woodlands   Z   12.3    8   HWZ.txt
    3   129 Houston Downtown    X   11.8    10  HDX.txt
    4   129 Houston Downtown    Y   11.8    10  HDY.txt
    5   129 Houston Downtown    Z   11.8    10  HDZ.txt
    ... ... ... ..  ..  ... ... ...
    2998    333 Chicago Downtown    X   3.4 50  CDX.txt
    2999    333 Chicago Downtown    Y   3.4 50  CDY.txt
    3000    333 Chicago Downtown    Z   3.4 50  CDZ.txt

这在当时是可以的，但是，我想在加载文件/数组(添加列)后对所有 X Y Z 进行分组，除此之外，添加带有数组操作的产品的新列(例如 FFT) .

最后我想要一个如下所示的 DataFrame:

    ID  Name    Recording   Duration    Distance    Rawx    Rawy    Raxz    FFT-Rawx    FFT-Rawy    FFT-Raxz
0   129 Houston Woodlands   12.3    8   HWX.txt HWY.txt HWZ.txt FFT-HWX.txt FFT-HWY.txt FFT-HWZ.txt
1   129 Houston Downtown    11.8    10  HDX.txt HDY.txt HDZ.txt FFT-HDX.txt FFT-HDY.txt FFT-HDZ.txt
... ... ... ..  ... ... ... ... ... ... ... ...
1000    333 Chicago Downtown    3.4 50  CDX.txt CDY.txt CDZ.txt FFT-CDX.txt FFT-CDY.txt FFT-CDZ.txt

知道怎么做吗？

不幸的是，并非我所有的细胞都具有这种良好的结构。

而不是

HDX HDY HDZ

我可以有“随机名称”。但是，我知道它们是按以下顺序排列的:

第一个是 Z，第二个是 Y，第三个始终是 X。每个记录都有这三个信号，然后下一个记录出现。

我在想一些类似的事情:

k =1
for row in df:
    if k % 3 == 0:
    # Do something
    elif k % 3 == 2:
    # Do something
    else:
    # Do something
    k += 1

但是，我不知道是否有一个选项可以将空列添加到现有的数据框中并通过循环填充它。如果有这样的选项，请告诉我。

Answer 1

考虑连接 pandas.pivot_tables 的列表。但是，在连接之前，数据帧必须按 Raw 值公共(public)词干进行切片 --HW.txt、HD.txt、>CD.txt--使用正则表达式分组:

from io import StringIO
import pandas as pd
import re

df = pd.read_csv(StringIO('''
ID,Name,Recording,Direction,Duration,Distance,Path,Raw
0,129,Houston,Woodlands,X,12.3,8,HWX.txt
1,129,Houston,Woodlands,Y,12.3,8,HWY.txt
2,129,Houston,Woodlands,Z,12.3,8,HWZ.txt
3,129,Houston,Downtown,X,11.8,10,HDX.txt
4,129,Houston,Downtown,Y,11.8,10,HDY.txt
5,129,Houston,Downtown,Z,11.8,10,HDZ.txt
6,333,Chicago,Downtown,X,3.4,50,CDX.txt
7,333,Chicago,Downtown,Y,3.4,50,CDY.txt
8,333,Chicago,Downtown,Z,3.4,50,CDZ.txt'''))

# UNIQUE 'RAW' STEM GROUPINGS
grp = set([re.sub(r'X|Y|Z', '', i) for i in df['Raw'].tolist()])

dfList = []
for i in grp:    
    # FILTER FOR 'RAW' VALUES THAT CONTAIN STEMS 
    temp = df[df['Raw'].isin([i.replace('.txt', txt+'.txt') for txt in ['X','Y','Z']])]    
    # RUN PIVOT (LONG TO WIDE)
    temp = temp.pivot_table(values='Raw', 
                            index=['Name', 'Recording', 'Direction','Distance', 'Path'],
                            columns=['Duration'], aggfunc='min')
    dfList.append(temp)

# CONCATENATE (STACK) DFS IN LIST 
finaldf = pd.concat(dfList).reset_index()

# RENAME AND CREATE FFT COLUMNS
finaldf = finaldf.rename(columns={'X': 'Rawx', 'Y': 'Rawy', 'Z': 'Rawz'})
finaldf[['FFT-Rawx', 'FFT-Rawy', 'FFT-Rawz']] = 'FFT-' + finaldf[['Rawx', 'Rawy', 'Rawz']]

输出

# Duration  Name Recording  Direction  Distance  Path     Rawx     Rawy     Rawz     FFT-Rawx     FFT-Rawy     FFT-Rawz
# 0          129   Houston   Downtown      11.8    10  HDX.txt  HDY.txt  HDZ.txt  FFT-HDX.txt  FFT-HDY.txt  FFT-HDZ.txt
# 1          129   Houston  Woodlands      12.3     8  HWX.txt  HWY.txt  HWZ.txt  FFT-HWX.txt  FFT-HWY.txt  FFT-HWZ.txt
# 2          333   Chicago   Downtown       3.4    50  CDX.txt  CDY.txt  CDZ.txt  FFT-CDX.txt  FFT-CDY.txt  FFT-CDZ.txt