How to initiate a new columns with None and update its value with tuple conditionally?

user_13592838 0
Viewed 68

I have the following code

import pandas as pd
d = [{'points': 50, 'time': '5:00', 'year': 2010},
     {'points': 25, 'time': '6:00', 'month': "february"},
     {'points': 90, 'time': '9:00', 'month': 'january'},
     {'points_h1': 20, 'month': 'june'}]
df = pd.DataFrame(d)
df['auditor'] = None
df.loc[df['points'] == 50, 'auditor'] = (1, 2)
print(df)
print(df.loc[df['points'] == 50, 'auditor'])

I want to initiate a new column and with None and conditionally update its value with a tuple but it occurs the following error.

ValueError: cannot set using a multi-index selection indexer with a different length than the value

my desire result is

      month  points  points_h1  time  year  auditor
0       NaN      50        NaN  5:00  2010  (1,2)
1  february      25        NaN  6:00   NaN  None
2   january      90        NaN  9:00   NaN  None
3      june     NaN         20   NaN   NaN  None

how may I do so?

pandas dataframe python
2 Answers

Since you cant be sure that the condition would return just 1 row or multiple rows, it is better to create a series of tuples and repeat it according to the number of rows returned by the condition:

condition = df['points'] == 50
df.loc[condition, 'auditor'] = pd.Series([(1, 2)]).repeat(condition.sum()).values

print(df)

   points  time    year     month  points_h1 auditor
0    50.0  5:00  2010.0       NaN        NaN  (1, 2)
1    25.0  6:00     NaN  february        NaN    None
2    90.0  9:00     NaN   january        NaN    None
3     NaN   NaN     NaN      june       20.0    None

To see what I mean , let's consider the second row also had points as 50:

d = [{'points': 50, 'time': '5:00', 'year': 2010},
 {'points': 50, 'time': '6:00', 'month': "february"},
 {'points': 90, 'time': '9:00', 'month': 'january'},
 {'points_h1': 20, 'month': 'june'}]
df = pd.DataFrame(d)
df['auditor'] = None
print(df,'\n\n')

condition = df['points'] == 50
df.loc[condition, 'auditor'] = pd.Series([(1, 2)]).repeat(condition.sum()).values
print(df)

   points  time    year     month  points_h1 auditor
0    50.0  5:00  2010.0       NaN        NaN    None
1    50.0  6:00     NaN  february        NaN    None
2    90.0  9:00     NaN   january        NaN    None
3     NaN   NaN     NaN      june       20.0    None 


   points  time    year     month  points_h1 auditor
0    50.0  5:00  2010.0       NaN        NaN  (1, 2)
1    50.0  6:00     NaN  february        NaN  (1, 2)
2    90.0  9:00     NaN   january        NaN    None
3     NaN   NaN     NaN      june       20.0    None
user_98406370

You could also use np.where() which is a good function for conditions:

df['auditor'] = np.where((df['points'] == 50), pd.Series([(1, 2)]), None)

OR in one line when creating the dataframe with .assign():

df = pd.DataFrame(d).assign(auditor=np.where((df['points'] == 50), pd.Series([(1, 2)]), None))

import pandas as pd, numpy as np
d = [{'points': 50, 'time': '5:00', 'year': 2010},
     {'points': 25, 'time': '6:00', 'month': "february"},
     {'points': 90, 'time': '9:00', 'month': 'january'},
     {'points_h1': 20, 'month': 'june'}]
df = pd.DataFrame(d).assign(auditor=np.where((df['points'] == 50), pd.Series([(1, 2)]), None))
df

Out[34]: 
   points  time    year     month  points_h1 auditor
0    50.0  5:00  2010.0       NaN        NaN  (1, 2)
1    25.0  6:00     NaN  february        NaN    None
2    90.0  9:00     NaN   january        NaN    None
3     NaN   NaN     NaN      june       20.0    None

Per your comment, if you want to manually create conditions and results and then loop through the np.where(), then this is how you would do it:

import pandas as pd, numpy as np
d = [{'points': 50, 'time': '5:00', 'year': 2010},
     {'points': 25, 'time': '6:00', 'month': "february"},
     {'points': 90, 'time': '9:00', 'month': 'january'},
     {'points_h1': 20, 'month': 'june'}]
df = pd.DataFrame(d)

#Manually Set Conditions and Rsults
c1 = (df['points'] == 50)
r1 =  pd.Series([(1, 2)])
c2 = (df['points'] == 25)
r2 = pd.Series([(1, 3)])
conditions = [c1,c2]
results = [r1,r2]

df['auditor'] = None
for c, r in zip(conditions, results):
    df['auditor'] = np.where(c, r, df['auditor'])
df

Out[39]: 
   points  time    year     month  points_h1 auditor
0    50.0  5:00  2010.0       NaN        NaN  (1, 2)
1    25.0  6:00     NaN  february        NaN  (1, 3)
2    90.0  9:00     NaN   january        NaN    None

See Anky's comments. Instead of:

df['auditor'] = None
    for c, r in zip(conditions, results):
        df['auditor'] = np.where(c, r, df['auditor'])

you can use np.select to avoid looping. This is a more pythonic. efficient way to do this:

df['auditor'] = np.select(conditions,results,None)
user_63667700
Related