1
$\begingroup$

I've created a Python code that reads the data from an excel file using Pandas.

Code for your reference:-

import pandas as pd

def myFunc():
   file = r"C:\Documents\myFile.xlsx"
   new_dataframe = pd.read_excel(file,'Sheet1')
   new_dataframe.fillna(value="No Data Found",inplace=True)
   print new_dataframe

myFunc

Current Output:-

             Name        date    amount_used
0             P1   2018-07-01          40.0
1             P1   2018-07-01          40.0
2             P1   2018-07-15          40.0
3             P2   2018-08-01          20.0
4             P2   2018-09-15          50.0
5             P2   2018-08-15          40.0
6             P3   2018-08-10          20.0
7             P3   2018-08-10          50.0
8             P3   2018-08-10          40.0

In the final output, I need to sum the amount_used column based on Name and date column.

Expected Output:-

             Name        date    amount_used
0             P1   2018-07-01          80.0
1             P1   2018-07-15          40.0
2             P2   2018-07-01          20.0
3             P2   2018-08-15          90.0
4             P3   2018-08-10         110.0

How can I achieve this using pandas ?

$\endgroup$
3
$\begingroup$

You can use groupby and then sum Take a look at https://stackoverflow.com/questions/39922986/pandas-group-by-and-sum

$\endgroup$
  • $\begingroup$ Welcome to our community :) You may want to elaborate your answer to make it a self-explanatory one. $\endgroup$ – Media Jun 27 at 5:34
2
$\begingroup$

Based on the @Alex Z solution, I was able to solve this question.

Below is the reference code:-

import pandas as pd

def myFunc():
   file = r"C:\Documents\myFile.xlsx"
   new_dataframe = pd.read_excel(file,'Sheet1')
   new_dataframe.fillna(value="No Data Found",inplace=True)
   new_dataframe.groupby(['Name','date'])['amount_used'].sum().reset_index()  #new line added.
   print new_dataframe

myFunc

Output Generated:-

            Name        date    amount_used
0             P1   2018-07-01          80.0
1             P1   2018-07-15          40.0
2             P2   2018-07-01          20.0
3             P2   2018-08-15          90.0
4             P3   2018-08-10         110.0

Following is the reference link to the actual solution:- Link to the solution

Hope this helps someone.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.