CODEWITHSUNDEEP
pythonpandas
Jason
Jason

Reputation: 3

Pandas DF: Sum rows of numbers cast as strings into a new column

Ok, I'm stumped and have surfed all available pages, followed it all, and still ugh! So here is my df. I want to add all the values in row 4 together and have a row total at end, then do it for 5, 6, 7... I use .concat() and .sum() and get key errors or too many arguments, tried .groupby, and even .add() (worth a try) as the columns are 'lists', nothing gives totals.

     1day     2day     3day     4day     5day     6day     7day
4   2.979    2.979    2.979    2.979    2.979    2.979    2.979
5   9.543    9.543    9.543    9.543    9.543    9.543    9.543
6   5.222    5.222    5.222    5.222    5.222    5.222    5.222
7   0.319    0.319    0.319    0.319    0.319    0.319    0.319
10 -4.491   -4.491   -4.491   -4.491   -4.491   -4.491   -4.491
14  2.178    2.178    2.178    2.178    2.178    2.178    2.178
15  7.507    7.507    7.507    7.507    7.507    7.507    7.507
16  0.612    0.612    0.612    0.612    0.612    0.612    0.612
17  4.488    4.488    4.488    4.488    4.488    4.488    4.488

I had some luck with df.groupby(df.index)[['1day','2day'...'7day'].sum() but it just pushes them together, not adds them. (I am aware that all the values are equal) In excel, sum(a1:g1) but in pandas, I'm just writing into a deeper hole, please help! screen shot of df, may be a better view

Upvotes: 0

Views: 1752

Answers (1)

Bill Armstrong
Bill Armstrong

Reputation: 1777

.sum() applied to DF of strings

Without Altering the original Q&A... Here is an updated answer for your revised question:

The issue is that you'll need to convert the strings to integers. That just makes it a two step process. I'll show all the steps so it's clear:

In [83]: str_lst =[[ '7',  '3',  '2', '-1',  '5',  '8',  '0'],
                [ '6',  '8',  '4',  '0',  '8',  '7',  '6'],
                [ '6',  '1',  '2',  '8',  '6', '-1',  '2'],
                [ '0', '-1',  '3',  '5',  '1',  '3',  '7'],
                [ '6',  '5',  '2',  '8',  '4',  '2',  '3'],
                [ '4', '-1', '-1',  '3',  '2',  '3', '-1'],
                [ '8',  '6',  '6',  '0',  '8',  '0',  '0'],
                ['-1',  '3',  '3',  '0',  '4',  '2',  '4'],
                [ '2',  '1',  '2',  '7',  '0',  '8',  '5'],
                [ '3',  '8',  '1',  '0',  '8',  '1',  '8']]

In [85]: df = pd.DataFrame(str_lst, columns=['1day', '2day', 
                  '3day', '4day', '5day', '6day', '7day'])

In [93]: df = df.apply(pd.to_numeric)

In [94]: df['Totals'] = df.sum(axis =1)

If you do the total before the pd.to_numeric it is the summation of strings. If you do the total after teh pd.to_numeric it is the summation of integers.

Original Response

After you've created your DF. Try:

df['Totals'] = df.sum(axis=1)

This will add a column at the far right with a sum of the rows.

Example

In [63]: df = pd.DataFrame(np.random.randint(-1,9,size=(10, 7)),
             columns=['1day', '2day', '3day', '4day', 
                      '5day', '6day', '7day'])

In [64]: df
Out[64]: 
   1day  2day  3day  4day  5day  6day  7day
0     0     0     7    -1     2     2    -1
1     0     3     3     2    -1     3     6
2     0     8     0    -1     8    -1     1
3     3     8    -1     2     6     3     8
4     5     0     1     8     8    -1     3
5     7     8     1     4     8     1     8
6     5     7     5    -1     7    -1     1
7     6     3     2     2     8     1     5
8     3     7     3     3     3    -1     1
9     2    -1     8     1     1     0     5

In [65]: df['Totals'] = df.sum(axis=1)

In [66]: df
Out[66]: 
   1day  2day  3day  4day  5day  6day  7day  Totals
0     0     0     7    -1     2     2    -1       9
1     0     3     3     2    -1     3     6      16
2     0     8     0    -1     8    -1     1      15
3     3     8    -1     2     6     3     8      29
4     5     0     1     8     8    -1     3      24
5     7     8     1     4     8     1     8      37
6     5     7     5    -1     7    -1     1      23
7     6     3     2     2     8     1     5      27
8     3     7     3     3     3    -1     1      19
9     2    -1     8     1     1     0     5      16

Upvotes: 1

Related Questions