1

I have a df, and I gropedby it into another dataframe, with sum function, it excluded some columns, but date is vital for further calculation

grdf = df.groupby(['Year', 'Month', 'Percentage']).sum()
grdf['Gross Sales'] = grdf['Gross Sales'].astype(float)
grdf['Sum'] = grdf['Gross Sales'].cumsum()

What a best way for append, merge, reindex, mask, meld, concat, intersect, you_name_it - my datetime column? 


 -                         Net Units  Net Sales  Gross Sales     Sum  Payout
    Year Month Percentage                                                   
    2017 11    70%                 3     147.97       103.58  103.58  103.58
         12    70%                 1      24.99        17.49  121.07   17.49
    2018 1     70%                 2      49.98        34.99  156.06   34.99
         2     70%                 3      74.97        52.48  208.54  104.96
         3     70%                 1      24.99        17.49  226.03   17.49
         4     70%                 1      24.99        17.49  243.52   17.49
         8     88%                 2      89.98        79.18  322.71  114.17
         9     88%                 1      64.99        57.19  379.90   57.19
         10    88%                 3     104.97        92.37  472.27  149.56
         11    88%                 2      79.98        70.38  542.65   70.38
    2019 1     88%                 2      39.98        35.18  577.83  105.56

++++


          Day                     Product Base Price  Net Units  Net Sales  Gross Sales Percentage  Year  Month   Payout     Pay Day
0  2017-11-11         asdasdasdnts     $69.99          1      69.99      48.9930        70%  2017     11        x           x
1  2017-11-13         asdasdasdnts     $69.99          1      69.99      48.9930        70%  2017     11        x           x
2  2017-11-27         asdasdasdnts      $7.99          1       7.99       5.5930        70%  2017     11  103.579  2018-01-11
3  2017-12-06         asdasdasdnts     $24.99          1      24.99      17.4930        70%  2017     12        x           x
4  2018-01-03         asdasdasdnts     $24.99          1      24.99      17.4930        70%  2018      1        x           x
5  2018-01-17         asdasdasdnts     $24.99          1      24.99      17.4930        70%  2018      1        x           x
6  2018-02-10         asdasdasdnts     $24.99          1      24.99      17.4930        70%  2018      2        x           x
7  2018-02-19         asdasdasdnts     $24.99          1      24.99      17.4930        70%  2018      2        x           x
8  2018-02-28         asdasdasdnts     $24.99          1      24.99      17.4930        70%  2018      2  104.958  2018-04-14
9  2018-03-04         asdasdasdnts     $24.99          1      24.99      17.4930        70%  2018      3        x           x
10 2018-04-22         asdasdasdnts     $24.99          1      24.99      17.4930        70%  2018      4        x           x
11 2018-08-01         asdasdasdnts     $24.99          1      24.99      21.9912        88%  2018      8        x           x
12 2018-08-22         asdasdasdial     $64.99          1      64.99      57.1912        88%  2018      8  176.789  2018-10-06
13 2018-09-19         asdasdasdial     $64.99          1      64.99      57.1912        88%  2018      9        x           x
14 2018-10-15         asdasdasdial     $64.99          1      64.99      57.1912        88%  2018     10  114.382  2018-11-29
15 2018-10-23         asdasdasdnts     $24.99          1      24.99      21.9912        88%  2018     10        x           x
16 2018-10-26         asdasdasdock     $14.99          1      14.99      13.1912        88%  2018     10        x           x
17 2018-11-20         asdasdasdial     $64.99          1      64.99      57.1912        88%  2018     11        x           x
18 2018-11-20         asdasdasdock     $14.99          1      14.99      13.1912        88%  2018     11  105.565  2019-01-04
19 2019-01-04         asdasdasdnts     $24.99          1      24.99      21.9912        88%  2019      1        x           x
20 2019-01-04         asdasdasdock     $14.99          1      14.99      13.1912        88%  2019      1        x           x

I group by month, so my data was summed, amount of rows - is not the same

Improve this question
6
  • What does the original dataframe look like? Commented Jan 18, 2019 at 10:47
  • Could you please add an example of your original dataframe? Commented Jan 18, 2019 at 10:48
  • @Ghorich - Updated! Commented Jan 18, 2019 at 11:26
  • @Mr_Z - Updated! Commented Jan 18, 2019 at 11:27
  • 1
    @jezrael - unfortunately not, I group by month(so my data was summed), amount of rows - is not the same Commented Jan 18, 2019 at 11:58

1 Answer 1

Reset to default
2

Use aggregation : https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.aggregate.html

In : df = pd.DataFrame([[1, 2, 3],
                        [4, 5, 6],
                        [1, 5, 7]],
                        columns=['A', 'B', 'C'])                   

In : df
Out: 
   A  B  C
0  1  2  3
1  4  5  6
2  1  5  7

In : df.groupby('A').agg({'B':np.sum, 'C':'first'})
Out: 
   B  C
A      
1  7  3
4  5  6

Hence you can decide which operation to use on each column. You just have to say what you want for the 'date' column (first might be ok).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

with 'last' - works PERFECTLY! just like I want, thank you man ^___^

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.