I have a dataframe:
id
id1
id2
id3
id8
id9
I want to add a new column new with conditional row values as follows:
If a row from id == id1, then the new row is id1 is cat 1
If a row from id == id2, then the new row is id2 is cat 2
If a row from id == id3, then the new row is id3 is cat 3
else, idx is cat 0, where x is the id that is not one of id1, id2, or id3
This is what I tried so far. I think the solution should be to wrap the for loop inside a function and use that function with apply and/or lambda.
import pandas as pd
df = pd.DataFrame({
'id': ['id1', 'id2', 'id3', 'id8', 'id9']
})
df
dict = {'id1': '1', 'id2': '2', 'id3': '3'}
for k, val in dict.items():
if k == "id1" or k == "id2" or k == "id3" in df['state']:
print(str(k) + " is cat " + str(val))
else:
print(str(k) + " is cat 0")
Desired result:
id new
id1 id1 is cat 1
id2 id2 is cat 2
id3 id3 is cat 3
id8 id8 is cat 0
id9 id9 is cat 0
>Solution :
You can get number and save in m then use numpy.where and if number m.isin(['1','2','3']) use number of m else use 0.
import numpy as np
m = df['id'].str[2:]
tf = m.isin(['1','2','3'])
df['new'] = np.where(tf,
df['id'] + " is cat " + m ,
df['id'] + " is cat 0")
print(df)
Output:
id new
0 id1 id1 is cat 1
1 id2 id2 is cat 2
2 id3 id3 is cat 3
3 id8 id8 is cat 0
4 id9 id9 is cat 0