Conditional writing to xlsx

Question

Folks,

I'm currently working with a huge excel sheet, python 3.7.1 and pandas 0.23.4. My task is to write to cells based on conditional matching. Something like this:

val = [email protected]
if val in Cell_3A:
    write something to Cell_3B

To make a complete example, let's say the following is my dataframe:

    Email               Protection
1   [email protected]
2   [email protected]
3   [email protected]
4   [email protected]

I know want to write down that all of the emails are protected, except for the email in row 3. So the finished dataframe should look like this:

    Email               Protection
1   [email protected]   on
2   [email protected]     on
3   [email protected]     off
4   [email protected]  on

How do I achieve this?

Could you explain this any further, anky_91? – Mowgli Dec 31 '18 at 08:13 — Mowgli, Dec 31 '18 at 08:13

anky · Accepted Answer · 2019-01-24T07:27:43.090

6

filter the Protection column where the email is not '[email protected]' and assign them 'on' and vice versa.

df.loc[df['Email']!='[email protected]', 'Protection']='on'
df.loc[df['Email']=='[email protected]', 'Protection']='off'

using np.where:

df['Protection'] = np.where((df['Email']!='[email protected]'),'on','off')

or:

df['Protection'] = np.where((df['Email']=='[email protected]'),'off','on')

edited Jan 24 '19 at 07:27

answered Dec 31 '18 at 08:18

anky

74,114
11
41
70

1

anky_91, i was just about to post np.where solution you beated me fast :-) +1 , easiest and fater as well. – Karn Kumar Dec 31 '18 at 08:38
ha ha. :D yeah, thought to post this since this is faster. :) – anky Dec 31 '18 at 08:38
Thanks! This seems to solve my problem, BUT: " A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead" We're getting a SettingWithCopyWarning here! – Mowgli Dec 31 '18 at 10:14
Yes, I'm using the numpy-solution. I changed the method of accessing to the suggested .iloc-method, but I keep getting the same 'SettingWithCopyWarning'-Error. – Mowgli Dec 31 '18 at 10:19
even this : https://stackoverflow.com/questions/40033471/getting-settingwithcopywarning-warning-even-after-using-loc-in-pandas – anky Dec 31 '18 at 10:38
1

Thanks! Best fucking served, sir. [This](https://stackoverflow.com/questions/20625582/how-to-deal-with-settingwithcopywarning-in-pandas) helped me a lot.Took some time, but found out that I was declaring slices of my dataframe the wrong way for this situation. – Mowgli Dec 31 '18 at 11:38
oops, solution is wrong, need change `df['Protection'][df['Email']!='[email protected]']` to `df.loc[df['Email']!='[email protected]', 'Protection']` and similar for second row – jezrael Jan 24 '19 at 07:25
@jezrael yes, i trusted the np.where solution more , thank you, changed. Bad use of slicers. :p – anky Jan 24 '19 at 07:28
1

@anky_91 - Ya, it is common mistake if someone start working with pandas, +1 – jezrael Jan 24 '19 at 07:28

Karn Kumar · Answer 2 · 2018-12-31T09:11:58.287

Just another Solution around based on if and else condition:

DataFrame:

>>> df
                Email Protection
0   [email protected]
1     [email protected]
2     [email protected]
3  [email protected]

Result:

>>> df['Protection'] = ['On' if x !="[email protected]"  else 'Off' for x in df['Email']]
 # df['Protection'] = ['Off' if x =="[email protected]"  else 'On' for x in df['Email']]
>>> df
                Email Protection
0   [email protected]         On
1     [email protected]         On
2     [email protected]        Off
3  [email protected]         On

Conditional writing to xlsx

2 Answers2

Linked