I am trying to select three columns["attacktype1","attacktype2","attacktype3"] whose datatypes are integer from a data frame using pandas and want to fillna(0) into those columns and total those columns into a new column.["Total_attacks"]
The dataset can be downloaded from: Click [here]https://s3.amazonaws.com/datasetsgun/data/terror.csv
I have tried applying fillna(0) to one column at a time and then totalling them into a new single column.
My first way:
da1 = pd.read_csv('terror.csv', sep = ',', header=0 , encoding='latin' , na_values=['Missing', ' '])
da1.head()
#Handling missing values
da1['attacktype3'] = da1['attacktype3'].fillna(0)
da1['attacktype2'] = da1['attacktype2'].fillna(0)
da1['attacktype1'] = da1['attacktype1'].fillna(0)
da1['total_attacks'] = da1['attacktype3'] + da1['attacktype2'] + da1['attacktype1']
#country_txt is a column which consists of different countries.Want to find "Total_atacks" only for India. Therefore, the condition applied is country_txt=='India'.
a1 = da1.query("country_txt=='India'").agg({'total_attacks':np.sum})
print(a1)
My second way(which doesnt work):
da1 = pd.read_csv('terror.csv', sep = ',', header=0 , encoding='latin' , na_values=['Missing', ' '])
da1.head()
#Handling missing values
check1=Df.country_txt=="India"
store=Df[["attacktype1","attacktype2","attacktype3"]].apply(lambda x:x.fillna(0))
Total_attack=Df.loc[check1,store].sum(axis=1)
print(Total_attack)
I want to apply fillna(0) to multiple columns in a single line and also total those columns in an alternate and effective way.
The error that I get when I use my second way is:
ValueError: Cannot index with multidimensional key
First filter by boolean indexing with DataFrame.loc and then replace missing values by DataFrame.fillna:
check1 = Df.country_txt == "India"
cols = ["attacktype1","attacktype2","attacktype3"]
Df['Total_attack'] = Df.loc[check1, cols].fillna(0).sum(axis=1)
For scalar, one number output add sum:
Total_attack = Df['Total_attack'].sum()
print (Total_attack)
35065.0