CODEWITHSUNDEEP
pythonpandasdataframe
dustin
dustin

Reputation: 4416

Python: summarizing data in dataframe using a series

I want to reduce dataframe down to more of summary data. I have the following dataframe:

In [8]: df
Out[8]: 
  CTRY_NM  ser_no       date
0       a       1 2016-01-01
1       a       1 2016-01-02
2       b       1 2016-03-01
3       e       2 2016-01-01
4       e       2 2016-01-02
5       a       2 2016-06-05
6       b       2 2016-07-01
7       b       3 2016-01-01
8       b       3 2016-01-02
9       d       3 2016-08-02

I created this with:

import pandas as pd
import numpy as np

df = pd.DataFrame({'ser_no': [1, 1, 1, 2, 2, 2, 2, 3, 3, 3],
    'CTRY_NM': ['a', 'a', 'b', 'e', 'e', 'a', 'b', 'b', 'b', 'd'],
    'day': ['01', '02', '01', '01', '02', '05', '01', '01', '02', '02'],
    'month': ['01', '01', '03', '01', '01', '06', '07', '01', '01', '08'],
    'year': ['2016','2016', '2016', '2016', '2016', '2016', '2016', '2016',\
    '2016', '2016']})
df['date'] = pd.to_datetime(df.day + df.month + df.year, format = "%d%m%Y")
df = df.drop(df.columns[[1,2,4]], axis = 1)

def check(data, key):
    mask = data[key].shift(1) == data[key]
    mask.iloc[0] = np.nan
    return mask

match = df.groupby(by = ['ser_no']).apply(lambda x: check(x, 'CTRY_NM'))

Now the match series tells me when a ser_no is in the same country and when it is not with a NaN at the serial number change location. Match returns:

In [9]: match
Out[9]: 
ser_no   
1       0    NaN
        1    1.0
        2    0.0
2       3    NaN
        4    1.0
        5    0.0
        6    0.0
3       7    NaN
        8    1.0
        9    0.0
Name: CTRY_NM, dtype: float64

I want to use match to summarize my dataframe as 

ser_no  CTRY_NM  start_dt    end_dt      number_of_dt
1       a        2016-01-01  2016-01-02  2
1       b        2016-03-01  2016-03-01  1
2       e        2016-01-01  2016-01-02  2
2       a        2016-06-05  2016-06-05  1
2       b        2016-07-01  2016-07-01  1
3       b        2016-01-01  2016-01-02  2
3       d        2016-08-02  2016-08-02  1

So I get the date the range that ser_no has been in a specific country and how many dates were recorded in that time frame.

I am not sure how to do this summarization in Python.

Upvotes: 1

Views: 109

Answers (1)

Alexander
Alexander

Reputation: 109546

You can use agg and specify an operation for each date value:

>>> df.groupby(['ser_no', 'CTRY_NM']).date.agg(
        {'start_dt': min, 
         'end_dt': max, 
         'number_of_dt': 'count'})
                number_of_dt   start_dt     end_dt
ser_no CTRY_NM                                    
1      a                   2 2016-01-01 2016-01-02
       b                   1 2016-03-01 2016-03-01
2      a                   1 2016-06-05 2016-06-05
       b                   1 2016-07-01 2016-07-01
       e                   2 2016-01-01 2016-01-02
3      b                   2 2016-01-01 2016-01-02
       d                   1 2016-08-02 2016-08-02

Upvotes: 2

Related Questions