0
  • The data frame shows the date with the amount of import and export and it is further bifurcated into coastal and regional data per day of one month.
  • What I wish to achieve is to club i.e sum all the data presented, which is of one month in this case, in the end, it will show only one entry that will be of month ending date and adding all the corresponding fields.

This is the following code:

df=pd.read_csv('output.csv',
             encoding="utf-8",skipinitialspace=True,engine='python')
datadf = df

datadf = datadf.dropna(axis = 0, how ='any')  

datadf = datadf.astype({'ForeignType' : 'category','ImportType' : 'category','ArrDate' : 'datetime64',
                        'DepDate' : 'datetime64'})

# datadf = datadf.groupby(datadf['ArrDate'].dt.strftime('%B'))['ComoQty'].sum()
datadf1 = datadf.groupby(['ArrDate','ImportType','ForeignType'])['ComoQty'].sum()
datadf2 = datadf1.to_frame()
datadf2.fillna(value=0,inplace=True)
# datadf2 = datadf2.reset_index('ImportType')
# datadf2 = datadf2.reset_index('ForeignType')
# datadf2 = datadf2.reset_index('ArrDate')
datadf2

Output by running the above code

Madhav Parikh
  • 73
  • 2
  • 9
  • Does this answer your question? [pandas dataframe groupby datetime month](https://stackoverflow.com/questions/24082784/pandas-dataframe-groupby-datetime-month) – amain May 11 '20 at 16:53

1 Answers1

0
datadf1 = datadf.drop(columns='Unnamed: 0')
prac = datadf1
prac =prac.set_index('ArrDate')
prac_dates = prac.copy()

prac = prac.resample('D').apply({'ShipName':'count','ComoQty':'sum'}).reset_index()

prac_dates = ((prac_dates.resample('M').apply({'ComoQty':'sum'}))/1000).reset_index()
prac_dates['Month'] = pd.DatetimeIndex(prac_dates['ArrDate']).strftime('%B')
del prac_dates['ArrDate']
# prac_dates

prac['Month'] = pd.DatetimeIndex(prac['ArrDate']).strftime('%B')
# prac['Month'] = pd.to_datetime(prac['Month'], format='%B')
prac['ArrDate'] = pd.DatetimeIndex(prac['ArrDate']).strftime('%d')
Madhav Parikh
  • 73
  • 2
  • 9