Restructuring Pandas DataFrame
<p>I have been suggested to move from the class structure, defining my own class, to the pandas DataFrame realm as I envision to have many operations with my data.</p>

<p>At this point I have a dataframe that looks like this:</p>

<pre><code>   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
</code></pre>

<p>This is ok at the time, however, I would like to group all the X Y Z after loading the files/arrays (add columns) and, in addition to that, add new column with products of the array manipulation (e.g. FFT).</p>

<p>Finally I would like a DataFrame that would look like this:</p>

<pre><code>    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
</code></pre>

<p>Any idea how?</p>

<p>Unfortunately, not all my cells have this nice structure.</p>

<p>Instead of</p>

<p>HDX HDY HDZ</p>

<p>I can have "random names". However, I know that they are in this order:</p>

<p>First is Z, second is Y, and third is X always. Each record has those three signals and then the next record comes.</p>

<p>I was thinking something along the lines of:</p>

<pre><code>k =1
for row in df:
    if k % 3 == 0:
    # Do something
    elif k % 3 == 2:
    # Do something
    else:
    # Do something
    k += 1
</code></pre>

<p>However, I don't know if there is an option to add an empty column to an already existing dataframe and fill it through a loop. If there is such an option, please let me know.</p>

<p>Consider concatenating a list of <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html" rel="nofollow">pandas.pivot_tables</a>. However, prior to concatenating, the dataframe must be sliced by the <em>Raw</em> value common stems --<em>HW.txt</em>, <em>HD.txt</em>, <em>CD.txt</em>-- grouped using regex:</p>

<pre><code>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']]
</code></pre>

<p><strong>Output</strong></p>

<pre><code># 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
</code></pre>

python pandas dataframe