CODEWITHSUNDEEP
pythonpandasbigdata
poppytop
poppytop

Reputation: 341

Insert value into column which is named in known column pandas

I'm preparing data for machine learning where data is in pandas DataFrame which looks like this:

Column   v1    v2
first    1      2
second   3      4
third    5      6

now i want to transform it into:

Column  v1  v2  first-v1  first-v2  second-v1  econd-v2  third-v1  third-v2
first   1   2     1        2         Nan        Nan       Nan      Nan
second  3   4     Nan      Nan       3          4         Nan      Nan
third   5   6     Nan      Nan       Nan        Nan       5        6

what i've tried is to do something like this:

# we know how many values there are but 
# length can be changed into length of [1, 2, 3, ...] values
values = ['v1', 'v2']

# data with description from above is saved in data 
for value in values:
    data[ str(data['Column'] + '-' + value)] = data[ value]

Results are a columns with name: ['first-v1' 'second-v1'..], ['first-v2' 'second-v2'..] where there are correct values. What i'm doing wrong? Is there a more optimal way to do this because my data is big?

Thank you for your time!

Upvotes: 1

Views: 92

Answers (1)

jezrael
jezrael

Reputation: 863291

You can use unstack with swaping and sorting MultiIndex in columns:

df = data.set_index('Column', append=True)[values].unstack()
         .swaplevel(0,1, axis=1).sort_index(1)
df.columns = df.columns.map('-'.join)
print (df)
   first-v1  first-v2  second-v1  second-v2  third-v1  third-v2
0       1.0       2.0        NaN        NaN       NaN       NaN
1       NaN       NaN        3.0        4.0       NaN       NaN
2       NaN       NaN        NaN        NaN       5.0       6.0

Or stack + unstack:

df = data.set_index('Column', append=True).stack().unstack([1,2])
df.columns = df.columns.map('-'.join)
print (df)
   first-v1  first-v2  second-v1  second-v2  third-v1  third-v2
0       1.0       2.0        NaN        NaN       NaN       NaN
1       NaN       NaN        3.0        4.0       NaN       NaN
2       NaN       NaN        NaN        NaN       5.0       6.0

Last join to original:

df = data.join(df)
print (df)
   Column  v1  v2  first-v1  first-v2  second-v1  second-v2  third-v1  \
0   first   1   2       1.0       2.0        NaN        NaN       NaN   
1  second   3   4       NaN       NaN        3.0        4.0       NaN   
2   third   5   6       NaN       NaN        NaN        NaN       5.0   

   third-v2  
0       NaN  
1       NaN  
2       6.0

Upvotes: 2

Related Questions