Pandas transform moving two rows (of x,y) into feature columns

user_18485193 0
Viewed 66

I'm trying to transform a pandas df that looks like:

import numpy as np
import pandas as pd

test_data = {'run': ['X', 'Y', 'A', 'B', 'C', 'D'],
             'time': [np.nan, np.nan, 10, 20, 40, 60],
             'temp': [np.nan, np.nan, 100, 120, 140, 160],
             'press': [np.nan, np.nan, 200, 220, 240, 260],
             'twist': [np.nan, np.nan, 300 ,320, 340, 360],
             'output_site_1': [0, 0, 11, 12, 13, 14],
             'output_site_2': [0, 1, 15, 16, 17, 18],
             'output_site_3': [1, 0, 19, 20, 21, 22],
             'output_site_4': [1, 1, 23, 24, 25, 26]
            }
pd.DataFrame(test_data)
run time temp press twist output_site_1 output_site_2 output_site_3 output_site_4
X NaN NaN NaN NaN 0 0 1 1
Y NaN NaN NaN NaN 0 1 0 1
A 10.0 100.0 200.0 300.0 11 15 19 23
B 20.0 120.0 220.0 320.0 12 16 20 24
C 40.0 140.0 240.0 340.0 13 17 21 25
D 60.0 160.0 260.0 360.0 14 18 22 26

into a df that looks like the one below:

transform_test_data = {'run': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C', 'D', 'D', 'D', 'D'],
                       'X': [0, 0, 1, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 0, 1, 1],
                       'Y': [0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1],
                       'time': [10, 10, 10, 10, 20, 20, 20, 20, 40, 40, 40, 40, 60, 60, 60, 60],
                       'temp': [100, 100, 100, 100, 120, 120, 120, 120, 140, 140, 140, 140, 160, 160, 160, 160],
                       'press': [200, 200, 200, 200, 220, 220, 220, 220, 240, 240, 240, 240, 260, 260, 260, 260],
                       'twist': [300 ,300, 300, 300, 320, 320, 320, 320, 340, 340, 340, 340, 360, 360, 360, 360],
                       'output': [11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26]
                      }
pd.DataFrame(transform_test_data)
run X Y time temp press twist output
A 0 0 10 100 200 300 11
A 0 1 10 100 200 300 12
A 1 0 10 100 200 300 13
A 1 1 10 100 200 300 14
B 0 0 20 120 220 320 15
B 0 1 20 120 220 320 16
B 1 0 20 120 220 320 17
B 1 1 20 120 220 320 18
C 0 0 40 140 240 340 19
C 0 1 40 140 240 340 20
C 1 0 40 140 240 340 21
C 1 1 40 140 240 340 22
D 0 0 60 160 260 360 23
D 0 1 60 160 260 360 24
D 1 0 60 160 260 360 25
D 1 1 60 160 260 360 26

I would like to take the rows 'X' and 'Y' and turn them into two seperate columns in the df and then remove them from the df. Doing so would add two new columns ('X' and 'Y') while also increasing the number for each run by run*(number-output-sites).

I need the method to be generalized such that it takes in a dataframe (like the first one) and returns a df (like the last one), can handle arbitrary number of inputs, and sites. There will always be a delineation between inputs and outputs as the first output will always be labeled 'output_site_1'.

I supplied 'test_data' and 'transformed_test_data' as dictionaries so if anyone felt kind enough to help out a stuck human (that's me!) it would be easy to create the two dataframes.

pandas dataframe python
3 Answers

one option is to transform into long form, split into different tables, munge, then recombine. As suggested in Tim's solution, if you can do this before dumping into Pandas, that is preferable, and should be more efficient.

This solution uses pivot_longer from pyjanitor for the wide to long form; you can do this using pd.wide_to_long; pivot_longer offers an easier abstraction and is comparably more efficient in a number of cases.

# pip install pyjanitor
import pandas as pd
import janitor as jn

# reshape to long form
extract = (test_data
          .pivot_longer(index = slice('run','twist'), 
                        names_to = ('.value', 'site', 'num'), 
                        names_sep='_')
          .drop(columns='site'))

# extract X and Y rows
runs = (extract
       .loc[extract.run.isin(['X','Y'])]
       .pivot(columns='run', values='output', index='num'))

# extract non X and Y rows
others = extract.loc[~extract.run.isin(['X','Y'])]

# merge the two dataframes
(runs
.merge(others, on='num')
.drop(columns='num')
)
    X  Y run  time   temp  press  twist  output
0   0  0   A  10.0  100.0  200.0  300.0      11
1   0  0   B  20.0  120.0  220.0  320.0      12
2   0  0   C  40.0  140.0  240.0  340.0      13
3   0  0   D  60.0  160.0  260.0  360.0      14
4   0  1   A  10.0  100.0  200.0  300.0      15
5   0  1   B  20.0  120.0  220.0  320.0      16
6   0  1   C  40.0  140.0  240.0  340.0      17
7   0  1   D  60.0  160.0  260.0  360.0      18
8   1  0   A  10.0  100.0  200.0  300.0      19
9   1  0   B  20.0  120.0  220.0  320.0      20
10  1  0   C  40.0  140.0  240.0  340.0      21
11  1  0   D  60.0  160.0  260.0  360.0      22
12  1  1   A  10.0  100.0  200.0  300.0      23
13  1  1   B  20.0  120.0  220.0  320.0      24
14  1  1   C  40.0  140.0  240.0  340.0      25
15  1  1   D  60.0  160.0  260.0  360.0      26

If your data has just two rows for X and Y, you can avoid flipping to wide form(pivot), by doing some preprocessing; this way you get to do the flip once, and even avoid the merge (and you don't need another library :)):

index = ['run', 'time', 'temp', 'press', 'twist']
temp = test_data.set_index(index)

# preprocessing begins here
cols = temp.loc[['X','Y']]
# get the output header name
output_only = cols.columns.str.split('_').str[0].tolist()
# combine and generate a MultiIndex
cols = list(cols.to_numpy()) + [output_only]
cols = pd.MultiIndex.from_arrays(cols, names = ['X','Y', None])
# get rid of X and Y rows
# sorting is to avoid performance drop on MultiIndex
temp = temp.sort_index().drop(['X', 'Y'])
temp.columns = cols
# flip to long form
temp.stack(['X','Y']).reset_index()
   run  time   temp  press  twist  X  Y  output
0    A  10.0  100.0  200.0  300.0  0  0      11
1    A  10.0  100.0  200.0  300.0  0  1      15
2    A  10.0  100.0  200.0  300.0  1  0      19
3    A  10.0  100.0  200.0  300.0  1  1      23
4    B  20.0  120.0  220.0  320.0  0  0      12
5    B  20.0  120.0  220.0  320.0  0  1      16
6    B  20.0  120.0  220.0  320.0  1  0      20
7    B  20.0  120.0  220.0  320.0  1  1      24
8    C  40.0  140.0  240.0  340.0  0  0      13
9    C  40.0  140.0  240.0  340.0  0  1      17
10   C  40.0  140.0  240.0  340.0  1  0      21
11   C  40.0  140.0  240.0  340.0  1  1      25
12   D  60.0  160.0  260.0  360.0  0  0      14
13   D  60.0  160.0  260.0  360.0  0  1      18
14   D  60.0  160.0  260.0  360.0  1  0      22
15   D  60.0  160.0  260.0  360.0  1  1      26

Again, just wide to long form was used, so no need to flip back to wide (potentially expensive) and more importantly, merging is avoided (merging on duplicate indexes is potentially expensive as well)

user_71757130

Like many situations, this is easier handled before moving into pandas. You really should have two different arrays there, but we can handle that in code.

import pandas as pd

test_data = {'run': ['X', 'Y', 'A', 'B', 'C', 'D'],
             'time': [np.nan, np.nan, 10, 20, 40, 60],
             'temp': [np.nan, np.nan, 100, 120, 140, 160],
             'press': [np.nan, np.nan, 200, 220, 240, 260],
             'twist': [np.nan, np.nan, 300 ,320, 340, 360],
             'output_site_1': [0, 0, 11, 12, 13, 14],
             'output_site_2': [0, 1, 15, 16, 17, 18],
             'output_site_3': [1, 0, 19, 20, 21, 22],
             'output_site_4': [1, 1, 23, 24, 25, 26]
            }

rows = []
store = {}
for key,row in test_data.items():
    if key == 'run':
        runs = row
    elif row[0] is np.nan:
        store[key] = { a:b for a,b in zip(runs,row) }
    else:
        row = { a:b for a,b in zip(runs, row) }
        for r in runs[2:]:
            new = [r, row['X'], row['Y']]
            new.extend( [v[r] for v in store.values()] )
            new.append( row[r] )
            rows.append( new )

columns = ['run', 'X', 'Y'] + list(store.keys()) + ['output']
df = pd.DataFrame( rows, columns=columns )
print(df)

Output:

   run  X  Y  time  temp  press  twist  output
0    A  0  0    10   100    200    300      11
1    B  0  0    20   120    220    320      12
2    C  0  0    40   140    240    340      13
3    D  0  0    60   160    260    360      14
4    A  0  1    10   100    200    300      15
5    B  0  1    20   120    220    320      16
6    C  0  1    40   140    240    340      17
7    D  0  1    60   160    260    360      18
8    A  1  0    10   100    200    300      19
9    B  1  0    20   120    220    320      20
10   C  1  0    40   140    240    340      21
11   D  1  0    60   160    260    360      22
12   A  1  1    10   100    200    300      23
13   B  1  1    20   120    220    320      24
14   C  1  1    40   140    240    340      25
15   D  1  1    60   160    260    360      26

If you're bothered that the order doesn't match yours, add

rows.sort()

just before making the DataFrame.

user_18833160 
def setter(df):
    tmp = pd.concat([df.run, df.filter(like="output_")], axis=1).set_index("run")
    df2 = tmp[2:].stack().reset_index()[["run", 0]].rename(columns={0: "output"})

    df3 = tmp[:2].T.reset_index(drop=True)

    return pd.concat([pd.concat([df3] * (df2.size// df3.size), ignore_index=True), pd.merge(df[2:][['run', 'time',  'temp', 'press',    'twist']], df2)], axis=1)
user_186944000
Related