0

I've the below code

import pandas as pd

Orders = pd.read_excel (r"C:\Users\Bharath Shana\Desktop\Python\Sample.xls", sheet_name='Orders')

Returns = pd.read_excel (r"C:\Users\Bharath Shana\Desktop\Python\Sample.xls", sheet_name='Returns')

Sum_value = pd.DataFrame(Orders['Sales']).sum

Orders_Year = pd.DatetimeIndex(Orders['Order Date']).year

Orders.merge(Returns, how="inner", on="Order ID")

which gives the output as below

enter image description here

My Requirement is i would like to use groupby and would like to see the output as below

enter image description here

Can some one please help me how to use groupby in my above code, it means i would like to see everything in the single line by using groupby

Regards,

Bharath

dm2
  • 4,053
  • 3
  • 17
  • 28
Vikas
  • 199
  • 1
  • 7
  • ``df.groupby(['Year', 'Segment'])['Sales'].sum()`` ?? – sushanth Jul 24 '20 at 15:17
  • Does this answer your question? [Pandas group-by and sum](https://stackoverflow.com/questions/39922986/pandas-group-by-and-sum) – sushanth Jul 24 '20 at 15:18
  • I've modified you code as `Orders.groupby(['Year', 'Segment'])['Sales'].sum()` but inner join is not working if I use this. It is showing all the values instead of matching values. – Vikas Jul 24 '20 at 15:28
  • what don you mean by all values and matching values? – Amit Jul 24 '20 at 16:13
  • I’m using two sheets ‘Orders’ and ‘Returns’ and if i do inner join i only get the matching records from both the sheets. Instead of showing the matching records it is showing all the records. – Vikas Jul 24 '20 at 16:22

1 Answers1

0

You can do by selecting column then define to a new dataframe

grouped = pd.DataFrame()
groupby = ['Year','Segment','Sales']

for i in groupby:
    grouped[i] = Orders[i]
Philip Purwoko
  • 407
  • 5
  • 20