How can i create a column from 2 related columns of lists in python?

user_15600388 0
Viewed 76
sampleID testnames results
23939332 [32131,34343,35566] [NEGATIVE,0.234,3.331]
32332323 [34343,96958,39550,88088] [0,312,0.008,0.1,0.2]

The table above is what I have, and the one below is what I want to achieve:

sampleID 32131 34343 39550 88088 96985 35566
23939332 NEGATIVE 0.234 NaN NaN NaN 3.331
32332323 NaN 0,312 0.1 0.2 0.008 NaN

So I need to create columns of unique values from the testnames column and fill the cells with the corresponding values from the results column.

Considering this is as a sample from a very large dataset (table).

pandas dataframe
1 Answers

Here is a commented solution:

(df.set_index(['sampleID'])  # keep sampleID out of the expansion
   .apply(pd.Series.explode) # expand testnames and results
   .reset_index()            # reset the index
   .groupby(['sampleID', 'testnames']) # 
   .first()                            # set the expected shape
   .unstack())                         #

It gives the result you expected, though with a different column order:

            results                                 
testnames     32131  34343  35566 39550 88088  96958
sampleID                                            
23939332   NEGATIVE  0.234  3.331   NaN   NaN    NaN
32332323        NaN  0.312    NaN   0.1   0.2  0.008

Let's see how it does on generated data:

def build_df(n_samples, n_tests_per_sample, n_test_types):
    df = pd.DataFrame(columns=['sampleID', 'testnames', 'results'])
    test_types = np.random.choice(range(0,100000), size=n_test_types, replace=False)
    for i in range(n_samples):
        testnames = list(np.random.choice(test_types,size=n_tests_per_sample))
        results = list(np.random.random(size=n_tests_per_sample))
        df = df.append({'sampleID': i, 'testnames':testnames, 'results':results}, ignore_index=True)
    return df

def reshape(df):
    df2 = (df.set_index(['sampleID'])  # keep the sampleID out of the expansion
             .apply(pd.Series.explode) # expand testnames and results
             .reset_index()            # reset the index
             .groupby(['sampleID', 'testnames']) # 
             .first()                            # set the expected shape
             .unstack())   
    return df2

%time df = build_df(60000, 10, 100)
# Wall time: 9min 48s (yes, it was ugly)

%time df2 = reshape(df)
# Wall time: 1.01 s

reshape() breaks when n_test_types becomes too large, with ValueError: Unstacked DataFrame is too big, causing int32 overflow.

user_47864400
Related