substring function to create new column - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Data Science (https://python-forum.io/forum-44.html) +--- Thread: substring function to create new column (/thread-24421.html) |
substring function to create new column - Chandan - Feb-13-2020 Hi There, I am new to python so please be kind to me. Below is the data frame and the requirement: data = {'id': ['aa11bc', 'bb22cd', 'cc33ef', 'dd44gh', 'ee55ij','ff66kl','gg77mn','hh88op'], 'direction': ["north, south, east, west", "north, south, east, west", "north, south, east, west", "north, south, east, west", "north, south, east, west","north, south, east, west","north, south, east, west" ,"north, south, east, west"]} df = pd.DataFrame(data, columns = ['id','direction']) df Requirement: #if id (2nd and 3rd letter) in ('a1','b2') then new_col should have north as an observation from direction column #else if id (2nd and 3rd letter) in ('c3','d4') then new_col should have south as an observation from direction column #else if id (2nd and 3rd letter) in ('e5','f6') then new_col should have east as an observation from direction column #else if id (2nd and 3rd letter) in ('g7','h8') then new_col should have west as an observation from direction columnAppreciate if you guys can assist me with this. RE: substring function to create new column - Larz60+ - Feb-13-2020 What have you tried so far? please post latest code working or not, and specify where it is failing Thank you RE: substring function to create new column - Chandan - Feb-14-2020 Hi There, I tried with the below code, but the results are not what I am expecting. new_col=[] for i in df["id"]: if i[1:3].lower()in ('a1','b2'): new_col=df["direction"].str.split(',').str[0] elif i[1:3].lower()in ('c3','d4'): new_col=df["direction"].str.split(',').str[1] elif i[1:3].lower()in ('e5','f6'): new_col=df["direction"].str.split(',').str[2] elif i[1:3].lower()in ('g7','h8'): new_col=df["direction"].str.split(',').str[3] df["position"]=new_col print(df) Please advise.
RE: substring function to create new column - Larz60+ - Feb-14-2020 what were you expecting? RE: substring function to create new column - Chandan - Feb-14-2020 Hi There, I am expecting the final table should be like below: Please advise.
RE: substring function to create new column - Larz60+ - Feb-14-2020 well I'm no expert at pandas, but you can make your code more readable by adding just after line 3: key = i[1:3].lower()then for your elif's if key in ...Another moderator can help with the pandas part RE: substring function to create new column - scidam - Feb-14-2020 I would solve the problem as follows: import pandas as pd data = {'id': ['aa11bc', 'bb22cd', 'cc33ef', 'dd44gh', 'ee55ij','ff66kl','gg77mn','hh88op'], 'direction': ["north, south, east, west", "north, south, east, west", "north, south, east, west", "north, south, east, west", "north, south, east, west","north, south, east, west","north, south, east, west" ,"north, south, east, west"]} df = pd.DataFrame(data, columns = ['id','direction']) def get_direction(row): mapper = {'%s%s' % (a, n): k // 2 for k, (a, n) in enumerate(zip('abcdefgh', '12345678'))} index = mapper.get(row['id'][1:3].lower()) if index is not None: return row['direction'].split(',')[index] df['position'] = df.apply(get_direction, axis=1)However, direction column doesn't change, this probably can be used to get completely vectorized solution of the problem,which would be faster. |