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 = lincoln@gmx.net
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   lincoln@gmail.net
2   obama@gmail.net
3   trump@gmail.net
4   franklin@gmail.net

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   lincoln@gmail.net   on
2   obama@gmail.net     on
3   trump@gmail.net     off
4   franklin@gmail.net  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 'trump@gmail.net' and assign them 'on' and vice versa.

df.loc[df['Email']!='trump@gmail.net', 'Protection']='on'
df.loc[df['Email']=='trump@gmail.net', 'Protection']='off'

using np.where:

df['Protection'] = np.where((df['Email']!='trump@gmail.net'),'on','off')

or:

df['Protection'] = np.where((df['Email']=='trump@gmail.net'),'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']!='trump@gmail.net']` to `df.loc[df['Email']!='trump@gmail.net', '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   lincoln@gmail.net
1     obama@gmail.net
2     trump@gmail.net
3  franklin@gmail.net

Result:

>>> df['Protection'] = ['On' if x !="trump@gmail.net"  else 'Off' for x in df['Email']]
 # df['Protection'] = ['Off' if x =="trump@gmail.net"  else 'On' for x in df['Email']]
>>> df
                Email Protection
0   lincoln@gmail.net         On
1     obama@gmail.net         On
2     trump@gmail.net        Off
3  franklin@gmail.net         On

Conditional writing to xlsx

2 Answers2

Linked