Convert rows with multiple values to row with single value

Question

I have a dataframe with measurement data of different runs at same conditions. Each row contains both the constant conditions the experiment was conducted and all the results from the different runs.

Since I am not able to provide a real dataset, the code snippet provided below will generate some dummy data.

I was able to achieve the desired output, but my function transform_columns() seems to be unecessary complicated:

import pandas as pd
import numpy as np

np.random.seed(seed=1234)

df = pd.DataFrame(np.random.randint(0, 100, size=(100, 6)), columns=['constant 1', 'constant 2', 1, 2, 3, 4])


def transform_columns(data):
    factor_columns = []
    response_columns = []
    for col in data:
        if isinstance(col, int):
            response_columns.append(col)
        else:
            factor_columns.append(col)
    collected = []
    for index, row in data.iterrows():
        conditions = row.loc[factor_columns]
        data_values = row.loc[response_columns].dropna()
        for val in data_values:
            out = conditions.copy()
            out['value'] = val
            collected.append(out)
    df = pd.DataFrame(collected).reset_index(drop=True)
    return df


print(transform_columns(df))

Is there any Pythonic or Pandas way to do this nicely?

Answer 1

It is probably easier to work with the underlying Numpy array directly than through Pandas. Ensure that all factor columns comes before all data columns, then this code will work:

import pandas as pd
import numpy as np

np.random.seed(seed=1234)

n_rows = 100
n_cols = 6
n_factor_cols = 2
n_data_cols = n_cols - n_factor_cols
arr = np.random.randint(0, 100, size=(n_rows, n_cols))
factor_cols = arr[:,:n_factor_cols]
data_cols = [arr[:,i][:,np.newaxis] for i in range(n_factor_cols, n_cols)]
stacks = [np.hstack((factor_cols, data_col)) for data_col in data_cols]
output = np.concatenate(stacks)

The above code assumes that order is not important. If it is, then use the following instead of np.concatenate:

output = np.empty((n_rows * n_data_cols, n_factor_cols + 1),
                  dtype = arr.dtype)
for i, stack in enumerate(stacks):
    output[i::n_data_cols] = stack

This is the best I can do, but I wouldn't be surprised if someone comes along and rewrites it as a Numpy one-liner. :)

Answer 2

pandas library has rich functionality and allows to build a complex pipelines as a chain of routine calls.
In your case the whole idea is achievable with the following single pipeline:

import pandas as pd
import numpy as np

np.random.seed(seed=1234)
df = pd.DataFrame(np.random.randint(0, 100, size=(100, 6)), 
                  columns=['constant 1', 'constant 2', 1, 2, 3, 4])


def transform_columns(df):
    return df.set_index(df.filter(regex=r'\D').columns.tolist()) \
        .stack().reset_index(name='value') \
        .drop(columns='level_2', axis=1)


print(transform_columns(df))

---

Details:

• df.filter(regex=r'\D').columns.tolist()
df.filter returns a subset of columns enforced by specified regex pattern regex=r'\D' (ensure the column name contains non-digit chars)

• df.set_index(...) - set the input dataframe index (row labels) using column names from previous step

• .stack() - reshape the dataframe from columns to index, having a multi-level index

• .reset_index(name='value')
pandas.Series.reset_index resets/treats index as a column; name='value' points to a desired column name containing the crucial values

• .drop(columns='level_2', axis=1) - drops supplementary label level_2 from columns (axis=1)

You may check/debug each step separately to watch how the intermediate series/dataframe looks like and how it's transformed.

---

Sample output:

     constant 1  constant 2  value
0            47          83     38
1            47          83     53
2            47          83     76
3            47          83     24
4            15          49     23
..          ...         ...    ...
395          16          16     80
396          16          92     46
397          16          92     77
398          16          92     68
399          16          92     83

[400 rows x 3 columns]