0

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

Bill Armstrong
  • 1,615
  • 3
  • 23
  • 47
Jason
  • 3
  • 4
  • `df.sum(axis=1)`? – user3483203 Jul 03 '18 at 04:14
  • 1
    Possible duplicate of [Pandas: sum DataFrame rows for given columns](https://stackoverflow.com/questions/25748683/pandas-sum-dataframe-rows-for-given-columns) – user3483203 Jul 03 '18 at 04:16
  • df['sum'] = df['1day','2day','3day','4day','5day','6day','7day'].sum(axis=1) returns KeyError on ['1day', '2day' ...] – Jason Jul 03 '18 at 04:28
  • 1
    You're indexing wrong. Literally just do `df.sum(axis=1)` . Or add an additional bracket, so `df[['1day', '2day', '3day', '4day', '5day', '6day', '7day']].sum(axis=1)` – user3483203 Jul 03 '18 at 04:31
  • just presses string together. 9.34 9.34 9.34 become 9.349.349.34 – Jason Jul 03 '18 at 04:35
  • What is your dtypes for the columns? - can you post the results of `df.dtypes`. What @user3483203 should work. – Bill Armstrong Jul 03 '18 at 04:43
  • – Jason Jul 03 '18 at 05:01
  • you should try resetting the index and then try the given solutions also if the dataframe has string values u can change them into integers by df.astype attached links to documentations https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.astype.html – Inder Jul 03 '18 at 05:41

1 Answers1

1

.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
Bill Armstrong
  • 1,615
  • 3
  • 23
  • 47
  • I'm downvoting this because A), this is an obvious duplicate, and B), you just copied an answer from the comments. – user3483203 Jul 03 '18 at 04:45
  • I was drafting the response when you were posting in the comments - so didn't see your notes. The OP asked for "I want to add all the values in row 4 together and have a row total at end" which `df.sum()` doesn't provide without `df['Totals']` assignment. – Bill Armstrong Jul 03 '18 at 04:49
  • @user3483203, If you'd like to post your answer - i'd be happy to delete this one... – Bill Armstrong Jul 03 '18 at 04:55
  • Bill, you are on the money as I have exhausted everyone's posts, tried them, and have had no luck. Your example is great, but the data is in str format, and .sum(axis=1) seems to just press them together. Appreciate the help. – Jason Jul 03 '18 at 05:05
  • 1
    I didn't post as an answer because of the dupe, but with your edit, this answer is relevant, and correct. My -1 has been changed to a +1 – user3483203 Jul 03 '18 at 05:59
  • Winner winner! The winning move was to add the .apply(pd.to_numeric) to the end of my df.concat line, bingo. srt to int, and math happens. Thanks all, huge time save. – Jason Jul 03 '18 at 14:47