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>I think I have a partial answer! I got a little confused about what you wanted with regard to the FFT (fast fourier transform?) and where the data were coming from. </p>

<p>HOWEVER, I got everything else. </p>

<p>First, I'm gonna make some sample data. </p>

<pre><code>import pandas as pd

df = pd.DataFrame({"ID": [0, 1, 2, 3, 4, 5], "Name":[129, 129, 129, 129, 129, 129], 
         "Recording":['Houston Woodlands', 'Houston Woodlands', 'Houston Woodlands', 
                     'Houston Downtown', 'Houston Downtown', 'Houston Downtown'], 
         "Direction": ["X", "Y", "Z", "X", "Y", "Z"], "Duration":[12.3, 12.3, 12.3, 11.8, 11.8, 11.8], 
         "Path_Raw":["HWX.txt", "HWY.txt", "HWZ.txt", 'HDX.txt', 'HDY.txt', 'HDZ.txt'], 
         "Distance": [8, 8, 8, 10, 10, 10]})
</code></pre>

<p>NOW I'll define some new functions. I've split these apart, so they'll be a little easier to customize. Basically, I'm calling .unique and saving each Path Raw as a new variable. </p>

<pre><code>def splitunique0(group):
    ulist = group.unique()
    return(ulist[0])


def splitunique1(group):
    ulist = group.unique()
    return(ulist[1])


def splitunique2(group):
    ulist = group.unique()
    return(ulist[2])


dothis = {"Duration":"first", "Distance":"first", 'Path_Raw': {'Rawx': splitunique0, 
                                                           'Rawy': splitunique1, 
                                                          'Raxz': splitunique2}}

new = df.groupby(["Name", "Recording"]).agg(dothis)

new.columns = ["Duration", "Distance", "Raxz", "Rawx", "Rawy"]
</code></pre>

<p>Here's the finished dataframe!
<code>
                        Duration Distance Raxz  Rawx    Rawy
Name    Recording<br>
129     Houston Downtown    11.8    10  HDZ.txt HDX.txt HDY.txt
        Houston Woodlands   12.3    8   HWZ.txt HWX.txt HWY.txt</code></p>

python pandas dataframe