Create new column by mapping to previous values in dataset using Python

user_5942100 0
Viewed 69

I have a dataset, df, where I would like to form a new column by taking first letter of column values and mapping values from additional columns within the dataset.

Data

id  Date    Model   type    status   
aa  Q1 2022 orion   hi01    o_hi    
aa  Q1 2022 orion   hi02    o_hi    
aa  Q1 2022 orion   hello01 o_hello 
aa  Q1 2022 orion   go01    o_go    
bb  Q1 2022 orion   hi01    o_hi    
bb  Q1 2022 orion   hi02    o_hi    
bb  Q1 2022 orion   hi03    o_hi

Desired

id  Date    Model   type    status  NEW
aa  Q1 2022 orion   hi01    o_hi    aa o_hi01 Q1 2022
aa  Q1 2022 orion   hi02    o_hi    aa o_hi02 Q1 2022
aa  Q1 2022 orion   hello01 o_hello aa o_hello01 Q1 2022
aa  Q1 2022 orion   go01    o_go    aa o_go01 Q1 2022
bb  Q1 2022 orion   hi01    o_hi    bb o_hi01 Q1 2022
bb  Q1 2022 vorion  hi02    v_hi    bb v_hi02 Q1 2022
bb  Q1 2022 vorion  hi03    v_hi    bb v_hi03 Q1 2022

Doing

df['NEW'] = df['id'].map(str) + ' ' + df['Model'].astype(str).str[0] + df['type'].map(str) + ' ' + df['Date'].map(str)

However, I am troubleshooting on how to incorporate the first letter of column values within the newly formed column.

Believe I can incorporate .loc

 df['Model'].astype(str).str[0]

Any suggestion is appreciated.

pandas numpy python
3 Answers

Try this:

df['NEW'] = df['id'].map(str) + ' ' + df['status'].astype(str) + df.groupby(['id', 'status']).cumcount().add(1).astype(str).str.zfill(2) + ' ' + df['Date']

Output:

>>> df
   id     Date     type   status                   NEW
0  aa  Q1 2022     hi01     o_hi     aa o_hi01 Q1 2022
1  aa  Q1 2022     hi02     o_hi     aa o_hi02 Q1 2022
2  aa  Q1 2022  hello01  o_hello  aa o_hello01 Q1 2022
3  aa  Q1 2022     go01     o_go     aa o_go01 Q1 2022
4  bb  Q1 2022     hi01     o_hi     bb o_hi01 Q1 2022
5  bb  Q1 2022     hi02     o_hi     bb o_hi02 Q1 2022
6  bb  Q1 2022     hi03     o_hi     bb o_hi03 Q1 2022
Admin1

You don't need to use .map(str) because all of your fields are already strings. If needed use df[column].astype(str) instead of df[column].map(str).

df['New'] = df['id'] + ' ' + df['Model'].str[0] + '_' + df['type'] + ' ' + df['Date']
print(df)

# Output
   id     Date   Model     type   status                   New
0  aa  Q1 2022   orion     hi01     o_hi     aa o_hi01 Q1 2022
1  aa  Q1 2022   orion     hi02     o_hi     aa o_hi02 Q1 2022
2  aa  Q1 2022   orion  hello01  o_hello  aa o_hello01 Q1 2022
3  aa  Q1 2022   orion     go01     o_go     aa o_go01 Q1 2022
4  bb  Q1 2022   orion     hi01     o_hi     bb o_hi01 Q1 2022
5  bb  Q1 2022  vorion     hi02     o_hi     bb v_hi02 Q1 2022
6  bb  Q1 2022  vorion     hi03     o_hi     bb v_hi03 Q1 2022
user_152399510 
df['NEW'] = df['id'].map(str) + ' ' + df['Model'].astype(str).str[0] + '_' + df['type'].map(str) + ' ' + df['Date'].map(str)
user_59421000
Related