By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,276 Members | 1,951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,276 IT Pros & Developers. It's quick & easy.

using substring and if elif condition to create new column

P: 7
Hi There,
I am new to python so please be kind to me.
Below is the data frame and the requirement:
Expand|Select|Wrap|Line Numbers
  1. data = {'id': ['aa11bc', 'bb22cd', 'cc33ef', 'dd44gh', 'ee55ij','ff66kl','gg77mn','hh88op'], 
  2.         'direction': ["north, south, east, west", "north, south, east, west", "north, south/, *east, \west%", "north, south, east, west",
  3.                       "north, south, east, west","north, south, east, west","north, south, east, west"
  4.                      ,"north, south, east, west"]}
  5. df = pd.DataFrame(data, columns = ['id','direction'])
  6. df
  7.  
  8. Requirement:
  9. #if id (2nd and 3rd letter) in ('a1','b2') then new_col should have north as an observation from direction column
  10. #else if id (2nd and 3rd letter) in ('c3','d4') then new_col should have south as an observation from direction column
  11. #else if id (2nd and 3rd letter) in ('e5','f6') then new_col should have east as an observation from direction column
  12. #else if id (2nd and 3rd letter) in ('g7','h8') then new_col should have west as an observation from direction column
I tried with the below code: but the output is not correct it's not meeting the desired output.

Expand|Select|Wrap|Line Numbers
  1. new_col=[]
  2.  
  3. for i in df["id"]:
  4.     if i[1:3].lower()in ('a1','b2'):
  5.         new_col=df["direction"].str.split(',').str[0].str.replace('\W+',' ').str.strip()
  6.     elif i[1:3].lower()in ('c3','d4'):
  7.         new_col=df["direction"].str.split(',').str[1].str.replace('\W+',' ').str.strip()
  8.     elif i[1:3].lower()in ('e5','f6'):
  9.         new_col=df["direction"].str.split(',').str[2].str.replace('\W+',' ').str.strip()
  10.     elif i[1:3].lower()in ('g7','h8'):
  11.         new_col=df["direction"].str.split(',').str[3].str.replace('\W+',' ').str.strip()
  12.  
  13. df["position"]=new_col
  14. print(df)
  15.  
  16. And the output as follows:
  17.        id                     direction position
  18. 0  aa11bc      north, south, east, west     west
  19. 1  bb22cd      north, south, east, west     west
  20. 2  cc33ef  north, south/, *east, \west%     west
  21. 3  dd44gh      north, south, east, west     west
  22. 4  ee55ij      north, south, east, west     west
  23. 5  ff66kl      north, south, east, west     west
  24. 6  gg77mn      north, south, east, west     west
  25. 7  hh88op      north, south, east, west     west
Please advise.
1 Week Ago #1

✓ answered by SioSio

I didn't understand what you wanted to do, but is that OK?
Do you need to replace non-alphabets in direction?
Expand|Select|Wrap|Line Numbers
  1. new_col=[]
  2. j = 0
  3. for i in df["id"]:
  4.     if i[1:3].lower()in ('a1','b2'):
  5.         new_col.append(re.sub(r'\W+','',df.loc[j, 'direction'].split(',')[0]))
  6.     elif i[1:3].lower()in ('c3','d4'):
  7.         new_col.append(re.sub(r'\W+','',df.loc[j, 'direction'].split(',')[1]))
  8.     elif i[1:3].lower()in ('e5','f6'):
  9.         new_col.append(re.sub(r'\W+','',df.loc[j, 'direction'].split(',')[2]))
  10.     elif i[1:3].lower()in ('g7','h8'):
  11.         new_col.append(re.sub(r'\W+','',df.loc[j, 'direction'].split(',')[3]))
  12.     j = j + 1
  13. df ["position"] = new_col
  14. print(df)
  15.  

Share this Question
Share on Google+
3 Replies


P: 94
I didn't understand what you wanted to do, but is that OK?
Do you need to replace non-alphabets in direction?
Expand|Select|Wrap|Line Numbers
  1. new_col=[]
  2. j = 0
  3. for i in df["id"]:
  4.     if i[1:3].lower()in ('a1','b2'):
  5.         new_col.append(re.sub(r'\W+','',df.loc[j, 'direction'].split(',')[0]))
  6.     elif i[1:3].lower()in ('c3','d4'):
  7.         new_col.append(re.sub(r'\W+','',df.loc[j, 'direction'].split(',')[1]))
  8.     elif i[1:3].lower()in ('e5','f6'):
  9.         new_col.append(re.sub(r'\W+','',df.loc[j, 'direction'].split(',')[2]))
  10.     elif i[1:3].lower()in ('g7','h8'):
  11.         new_col.append(re.sub(r'\W+','',df.loc[j, 'direction'].split(',')[3]))
  12.     j = j + 1
  13. df ["position"] = new_col
  14. print(df)
  15.  
1 Week Ago #2

P: 7
Hi There,
Thanks for the help.
Yes, I am getting the desired output from your code.
I want to try one more condition from your code, I let you know the status. Until such time I will keep this question as open.
Hope this is fine with you.
Once again thanks for your help.
Cheers!!
1 Week Ago #3

P: 7
HI SioSio,
Thanks for the help with the code.
1 Week Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.