0
$\begingroup$

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)

$\endgroup$

2 Answers 2

1
$\begingroup$

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.

$\endgroup$
1
$\begingroup$

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.

$\endgroup$

Your Answer

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

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