0

Suppose I have the following table,

+-------+----------+-------+-------+
| Date  | Exchange | price |     x | 
+-------+----------+-------+-------+
| 02/12 | LSE      |     1 |  s    |
| 03/12 | LSE      |     2 |  d    |
| 03/12 | LSE      |     2 |  f    |
| 03/12 | CBOE     |     3 |  q    |
| 04/12 | CBOE     |     4 |  w    |
| 04/12 | CBOE     |     5 |  t    |
+-------+----------+-------+-------+

I am want to get a table that adds the columns price when the two columns date and exchanges are the same, i.e. the table would look like 

+-------+----------+-------+
| Date  | Exchange | price |  
+-------+----------+-------+
| 02/12 | LSE      |     1 |  
| 03/12 | LSE      |     4 | 
| 03/12 | CBOE     |     3 |  
| 04/12 | CBOE     |     9 |  
+-------+----------+-------+

I tried something but it doesnt work

spread_per_isin_size_2500.groupby(['exchange', 'date']).size().groupby(level=1).max()

I can i acheive this?

Thanks

Johny
  • 129
  • 11
  • Use `spread_per_isin_size_2500.groupby(['Exchange', 'Date'], as_index=False, sort=False).sum()` – jezrael Feb 19 '20 at 09:59
  • This seems to sort by the last column on the table. However, the value that needs to be added is not the last column on the table. Can i reference that column? – Johny Feb 19 '20 at 10:07
  • If is added `sort=False` it not sorting – jezrael Feb 19 '20 at 10:08
  • Sorry, meant it ADDS the last column, but the price in my real df is not the last column. – Johny Feb 19 '20 at 10:08
  • Not understand, can you create [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) ? – jezrael Feb 19 '20 at 10:10
  • Please look at the edited table. I added a column at the end, so now the sum() will do this on the last column. But i need to sum the price columm. Hope that makes sense? – Johny Feb 19 '20 at 10:13
  • Understand, how is changed expected output? – jezrael Feb 19 '20 at 10:14
  • It is not, same output. The x column should be ignored – Johny Feb 19 '20 at 10:16
  • hmmm, so `spread_per_isin_size_2500.groupby(['Exchange', 'Date'], as_index=False, sort=False).sum()` not working? If not, use `spread_per_isin_size_2500.groupby(['Exchange', 'Date'], as_index=False, sort=False)['price'].sum()` – jezrael Feb 19 '20 at 10:17
  • I have strong feeling it should be duplicate, answered before, so rather not. – jezrael Feb 19 '20 at 10:27

0 Answers0