-2

I am importing an excel document and creating a dataframe, df3. I want to group by only Name. The other uplicate data should reflect as shown in the output.

Df3 =pd.read_excel('stats')
print (df3)      

Name    ID  Month   Shift
Jon     1   Feb     A
Jon     1   Jan     B
Jon     1   Mar     C
Mike    1   Jan     A
Mike    1   Jan     B
Jon     1   Feb     C
Jon     1   Jan     A

enter image description here

Output Required:

I want to have output like as below in the same format and will save in excel. Please help me on same as I'm stuck here. Note (Month must be ascending order)

enter image description here

Will be greatfull for help and support .

Jacobm001
  • 4,431
  • 4
  • 30
  • 51
Manoj Kumar
  • 47
  • 6
  • 1
    So what have you tried? – Umar.H Jun 27 '20 at 18:28
  • @Datanovice //1st I have imported : df=pd.read_excel(r'C:\Users\dell\Desktop\stats.xlsx',sheet_name='Sheet5') then i am tring to to sort the month by df=df.iloc[pd.to_datetime(df.Month,format='%b').argsort()].. but i am having error as 'DataFrame' object has no attribute 'Month' then i will use to final out put df=pd.concat([pd.DataFrame({'Month':[x] }).append(y).fillna('').append(pd.DataFrame(dict.fromkeys(y.columns,['']))) for x , y in df.groupby('Name')]).drop('Name',1).iloc[:-1] – Manoj Kumar Jun 27 '20 at 18:33
  • 2
    Does this answer your question? [how to crate the group by in pandas only in one level](https://stackoverflow.com/questions/62612488/how-to-crate-the-group-by-in-pandas-only-in-one-level) – Umar.H Jun 27 '20 at 18:44
  • voting to close this closing this as you've asked the same question twice. – Umar.H Jun 27 '20 at 18:45
  • @Datanovice beacuse no one is here to solve this query . i am so disappointed now :( – Manoj Kumar Jun 27 '20 at 18:48

2 Answers2

2

Here is how you can do it.

Solution:

Input:

Name,ID,Month,Shift

Jon,1,Feb,A

Jon,1,Jan,B

Jon,1,Mar,C

Mike,1,Jan,A

Mike,1,Jan,B

Jon,1,Feb,C

Jon,1,Jan,A

I have taken your file input as csv.

Code:

import pandas as pd
filePath="{YourFilePathForReadingcsv}"
df=pd.read_csv(filePath,sep=',')
df['MonthNumber']=pd.to_datetime(df['Month'],format='%b')
df.set_index(['Name','MonthNumber'],inplace=True)
df.sort_index(inplace=True)
df.to_excel("{filePath}\Jon.xlsx")

Output: Please remove column MonthNumber while writing it. enter image description here

Divyaansh Bajpai
  • 232
  • 1
  • 8
  • well i dont know what that vote mean .. since i am new and used very rare .. anywys .. dont take it personaly .. you guys are awesome ... thanks for all – Manoj Kumar Jun 28 '20 at 13:36
0

I have created a sample dataframe as yours. You can load your data instead of this.

Code:

import pandas as pd 
df = pd.DataFrame({'Name':['Jon','Jon','Jon','Mike','Mike','Jon','Jon'],'ID':[1,1,1,1,1,1,1], 'Month':['Feb','Jan','Mar','Jan','Jan','Feb','Jan'], 'Shift':['A','B','C','A','B','C','A']})
#display data
df

initial table by littinrajan

Loading data from Excel. I have created an excel file as you need

enter image description here

Now Reading Data From Excel:

#path is your path of excel file like "C:\test\"
df = pd.read_excel(path+'statsyour.xlsx', sheet_name='Sheet5')
#display data
df

enter image description here

Now we are going to transform the table as you needed(Sorting by Name and Month)

Code

df['Month'] = pd.Categorical(df['Month'],categories=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'],ordered=True)
df = df.sort_values(['Name','Month']).reset_index(drop=True)
#display sorted table data
df

table sorted by month and name by littinrajan

I hope this would be helpful.. (Write comments if any problem persist) :)

Littin Rajan
  • 852
  • 1
  • 10
  • 21
  • Really thank you .. but when i am running in my IDES . there is error . i mean importing this data from excel as df=pd.read_excel(r'C:\Users\dell\Desktop\statsyour.xlsx',sheet_name='Sheet5') and then i tried your command df=df.sort_values(['Name','Month']).reset_index(drop=True) and it showes error and also i want to groupby this data as above i have shown – Manoj Kumar Jun 27 '20 at 19:04
  • After loading the excel file as dataframe please display it. Here I'm not facing any issue – Littin Rajan Jun 27 '20 at 19:17
  • @ManojKumar Have added the loading excel data part to the answer. Please take a look – Littin Rajan Jun 27 '20 at 19:26
  • Really thanks alot .. well that was my mistake as i was having space in month name so when i removed its working .. but the next thing that we need to do is groupby ... have you tried doing that ? i need the same output as above.... – Manoj Kumar Jun 27 '20 at 19:31