I'm trying to sum multiple columns into a new sum column using a python pandas dataframe

Question

I'm trying to learn python and have been trying to figure out how to create a sum column of my data. I want to sum all other columns. I create the new column but all sum values are zero. The data can be found here. My code is below, thank you for the help:

import pandas as pd
#Importing csv file to chinaimport_df datafram
filename=r'C:\Users\Ing PC\Documents\Intro to Data Analysis\Final Project\CHINA_DOLLAR_IMPORTS.csv'
chinaimport_df = pd.read_csv(filename)

# Removing all rows that contain only zeros, thresh since since first column is words
chinaimport_df = chinaimport_df.dropna(how='all',axis=0, thresh=2) 

#Convert NANs to zeros
chinaimport_df=chinaimport_df.fillna(0)

#create a list of columns excluding the first column, to make sum func work later

col_list= list(chinaimport_df)
col_list.remove('Commodity')
print(col_list)

#adding column that sums 

chinaimport_df['Total'] = chinaimport_df[col_list].sum(axis=1)




chinaimport_df.to_csv("output.csv", index=False)

I'm trying to find the sum of the columns. Basically the total imports of each commodity for the year until August. — user2606257
– user2606257, Commented Oct 22, 2018 at 2:49

Abhi · Accepted Answer · 2018-10-22 03:29:11Z

2

IIUC this should do it.

import pandas as pd

df = pd.read_csv('CHINA_DOLLAR_IMPORTS.csv')

df['Total'] = df.replace(r',',"", regex=True).iloc[:, 1:].astype(float).sum(axis=1)

df.to_csv('output.csv', index=False)

edited Oct 22, 2018 at 3:29

answered Oct 22, 2018 at 3:24

Abhi

4,2431 gold badge18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

BAKE ZQ Over a year ago

Thank,this could be done by using applymap but your code seems more concise

Abhi Over a year ago

@BAKEZQ Yes we can but I think both replace and sum should be enough to do the work here. :)

user2606257 Over a year ago

Thanks a lot for the help Abhi! I guess part of the problem was the commas which you removed. But what are you doing with .iloc and .astype? Is that just for converting everything to a float? Wasn't the data already set as a float?

Abhi Over a year ago

@user2606257 First of all iloc is slicing the dataframe by excluding the first column which is commodity in our case. Even though we removed the comma from the values the datatype of all the values are stillstrings so we have to convert them to integer for the sum function to work. Here astype converts all the columns to numeric float in this case.

user2606257 Over a year ago

Perfect, thanks a lot for the clarification, that makes a lot of sense.

Collectives™ on Stack Overflow

I'm trying to sum multiple columns into a new sum column using a python pandas dataframe

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related