CODEWITHSUNDEEP
pythonexcelpandas
NILESH SUTHAR
NILESH SUTHAR

Reputation: 95

Insert value in panda dataframe

I have data in an Excel sheet. I want to check one column value for a range and if that value lies in that range(5000-15000) then I want to insert value in another column(Correct or Flag).

I have three columns: City, rent, status.

I have tried append and insert method but that didn't work. How should I do this?

Here is my code:

for index, row in df.iterrows():

if row['city']=='mumbai':

    if 5000<= row['rent']<=15000:

        pd.DataFrame.append({'Status': 'Correct'})

It shows this error:

TypeError: append() missing 1 required positional argument: 'other'

What procedure should I follow to insert data row by row in a column?

Upvotes: 3

Views: 17140

Answers (1)

jezrael
jezrael

Reputation: 862451

I think you can use numpy.where with boolean mask created by between and comparing with city:

mask = (df['city']=='mumbai') & df['rent'].between(5000,15000)
df['status'] = np.where(mask, 'Correct', 'Uncorrect')

Sample:

df = pd.DataFrame({'city':['mumbai','mumbai','mumbai', 'a'],
                   'rent':[1000,6000,10000,10000]})
mask = (df['city']=='mumbai') & df['rent'].between(5000,15000)
df['status'] = np.where(mask, 'Correct', 'Flag')
print (df)
     city   rent   status
0  mumbai   1000     Flag
1  mumbai   6000  Correct
2  mumbai  10000  Correct
3       a  10000     Flag

Another solution with loc:

mask = (df['city']=='mumbai') & df['rent'].between(5000,15000)
df['status'] = 'Flag'
df.loc[mask, 'status'] =  'Correct'
print (df)
     city   rent   status
0  mumbai   1000     Flag
1  mumbai   6000  Correct
2  mumbai  10000  Correct
3       a  10000     Flag

For write to excel use to_excel, if need remove index column add index=False:

df.to_excel('file.xlsx', index=False)

EDIT:

For multiple masks is possible use:

df = pd.DataFrame({'city':['Mumbai','Mumbai','Delhi', 'Delhi', 'Bangalore', 'Bangalore'],
                   'rent':[1000,6000,10000,1000,4000,5000]})
print (df)
        city   rent
0     Mumbai   1000
1     Mumbai   6000
2      Delhi  10000
3      Delhi   1000
4  Bangalore   4000
5  Bangalore   5000

m1 = (df['city']=='Mumbai') & df['rent'].between(5000,15000)
m2 = (df['city']=='Delhi') & df['rent'].between(1000,5000)
m3 = (df['city']=='Bangalore') & df['rent'].between(3000,5000)

m = m1 | m2 | m3
print (m)
0    False
1     True
2    False
3     True
4     True
5     True
dtype: bool

from functools import reduce
mList = [m1,m2,m3]
m = reduce(lambda x,y: x | y, mList)
print (m)
0    False
1     True
2    False
3     True
4     True
5     True
dtype: bool

print (df[m])
        city  rent
1     Mumbai  6000
3      Delhi  1000
4  Bangalore  4000
5  Bangalore  5000

Upvotes: 1

Related Questions