how can i sum first day value of each id together?

Question

I have a data frame like this:

 id                 Date       Volume      Price     Values(Volume*Price)
56033738624803469  20170111    1          943339    943339
56033738624803469  20170111    10         919410    9194100
56033738624803469  20170112    1          919410    919410
56033738624803469  20170112    5          954999    4774955
4659957480182399   20170207    1          1000000   1000000
4659957480182399   20170208    5          1000000   5000000
4659957480182399   20170208    40         1000000   40000000

I want to compute and plot the following computation for the first 100 days for each ID:

compute average values per day over the first 100 days Plot this for all of Ids Afterwards, the plot should like this: Desired plot (https://i.stack.imgur.com/2cozR.png)

This is what I've done so far:

df2 = df.groupby(['Id', 'Date']).sum()

The result is :

                           Index   Volume        Price        Values
Id               Date                                               
1745829084228393 20170207      1     1000    1000000.0  1.000000e+09
                 20170208   5151   999000  101000000.0  9.990000e+11 
                 20170403      1       12    1000100.0  1.200120e+07
                 20170408      1       12    1000000.0  1.200000e+07
                 20170417      1      500    1000000.0  5.000000e+08
                 20170423      3    14500    2000000.0  1.450000e+10
                 20170507     10    35000    4000000.0  3.500000e+10
                 20170510     21    49051    6000000.0  4.905100e+10
                 20170529      1        4    1000000.0  4.000000e+06
2888358730233310 20170212    820  2000000   40000000.0  2.000000e+12
2929948497881810 20170207   1830  1500000   60000000.0  1.500000e+12
                 20170208    903   700000   42000000.0  7.000000e+11
                 20170212   1176   800000   48000000.0  8.000000e+11
3715246194918044 20150509     66     1008   11000000.0  1.008000e+09

Now I want to calculate sum of first , second ,... of each ID for Values, for example:

 Date_Order     Sum_Values(= summation first date of each id )

 first_Date     (1.000000e+09 + 2.000000e+12 + 1.500000e+12 + 1.008000e+09)

 second_Date    (9.990000e+11 + 7.000000e+11)

ids are important i want summation of first day of values of each id(ids are different and may start on different days)

Answer 1

Just a suggestion:

Let the length of the most transaction over all ID be $n$.

Prepare a list of size $n$, $A$, initialize all entries to $0$.

Now go through each ID, suppose a particular ID has $m$ transaction. We add the value of the $i$-th transaction to the $i$-th entry of $A$.

The list $A$ would have your desired information. The $i$-entry stores the sum of values of the $i$-th day.

Alternatively, you can also introduce a new feature that encode which transaction is it for each ID and then use groupby to sum it up again.

Answer 2

The easiest solution that comes to my mind is to create date indices to count the distinct dates per ID, and then perform a group by over those indices.

First let's make your question reproducible:

from pandas import DataFrame

df = DataFrame({'ID':[1,1,1,2,2,3,3,4],\
                'date':[201610,201610,201611,201505,201505,201609,201702,\
                        201709],\
                'values': [10,20,30,40,50,60,70,80]})

Hence df is the following:

   ID    date  values
0   1  201610      10
1   1  201610      20
2   1  201611      30
3   2  201505      40
4   2  201505      50
5   3  201609      60
6   3  201702      70
7   4  201709      80

Now your groupby returns this:

In [137]: df.groupby(['ID', 'date']).sum()
Out[137]: 
           values
ID date          
1  201610      30
   201611      30
2  201505      90
3  201609      60
   201702      70
4  201709      80

And your question is how you can sum up the values at the 1st, 2nd, ... lines per ID. Your desired output is this:

first_date: 30+90+60+80 = 260

second_date: 30+70 = 100

OK. You don't have to do your groupby. You can just create indices of dates per ID, and groupby over them. Those indices will start from 1 for each ID, and will increase only when there is a new date for the same ID. Let's create and append them back to the df:

date = 201610
ID = 1
date_index = 1
date_indices = []
for i in range(df.shape[0]):
    if date != df['date'][i] and ID == df['ID'][i]:
        date_index += 1
    else:
        date_index = 1
    date_indices.append(date_index)
    date = df['date'][i]
    ID = df['ID'][i]

df['date_indices'] = date_indices

Which gives us the following:

In [140]: df
Out[140]: 
   ID    date  values  date_indices
0   1  201610      10             1
1   1  201610      20             1
2   1  201611      30             2
3   2  201505      40             1
4   2  201505      50             1
5   3  201609      60             1
6   3  201702      70             2
7   4  201709      80             1

Groupby gives us the desired result:

In [141]: df.groupby(['date_indices']).sum()[['values']]
Out[141]: 
              values
date_indices        
1                260
2                100

Please let me know if you need any further clarification/modification.