I have a dataframe in the following structure:
cNames | cValues | number [a,b,c] | [1,2,3] | 10 [a,b,d] | [55,66,77]| 20
I would like to transpose – create columns from the names in cNames.
But I can’t manage to achieve this with transpose because I want a column for each value in the list.
The needed output:
a | b | c | d | number 1 | 2 | 3 | NaN | 10 55 | 66 | NaN | 77 | 20
How can I achieve this result?
Thanks!
The code to create the DF:
d = {'cNames': [['a','b','c'], ['a','b','d']], 'cValues': [[1,2,3], [55,66,77]], 'number': [10,20]} df = pd.DataFrame(data=d)
Advertisement
Answer
One option is concat
:
pd.concat([pd.Series(x['cValues'], x['cNames'], name=idx) for idx, x in df.iterrows()], axis=1 ).T.join(df.iloc[:,2:])
Or a DataFrame construction:
pd.DataFrame({idx: dict(zip(x['cNames'], x['cValues']) ) for idx, x in df.iterrows() }).T.join(df.iloc[:,2:])
Output:
a b c d number 0 1.0 2.0 3.0 NaN 10 1 55.0 66.0 NaN 77.0 20
Update Performances sort by run time on sample data
DataFrame
%%timeit pd.DataFrame({idx: dict(zip(x['cNames'], x['cValues']) ) for idx, x in df.iterrows() }).T.join(df.iloc[:,2:]) 1.29 ms ± 36.8 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
concat:
%%timeit pd.concat([pd.Series(x['cValues'], x['cNames'], name=idx) for idx, x in df.iterrows()], axis=1 ).T.join(df.iloc[:,2:]) 2.03 ms ± 86.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
KJDII’s new series
%%timeit df['series'] = df.apply(lambda x: dict(zip(x['cNames'], x['cValues'])), axis=1) pd.concat([df['number'], df['series'].apply(pd.Series)], axis=1) 2.09 ms ± 65.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
Scott’s apply(pd.Series.explode)
%%timeit df.apply(pd.Series.explode) .set_index(['number', 'cNames'], append=True)['cValues'] .unstack() .reset_index() .drop('level_0', axis=1) 4.9 ms ± 135 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
wwnde’s set_index.apply(explode)
%%timeit g=df.set_index('number').apply(lambda x: x.explode()).reset_index() g['cValues']=g['cValues'].astype(int) pd.pivot_table(g, index=["number"],values=["cValues"],columns=["cNames"]).droplevel(0, axis=1).reset_index() 7.27 ms ± 162 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
Celius’ double explode
%%timeit df1 = df.explode('cNames').explode('cValues') df1['cValues'] = pd.to_numeric(df1['cValues']) df1.pivot_table(columns='cNames',index='number',values='cValues') 9.42 ms ± 189 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)