Calculate Overlapping & Non-Overlapping Data Points in a Data frame across Years

user_11239328 0
Viewed 118

I have a single Dataframe and I need to find how many toys with different color are same and how many are changing across years.

For Example: Toy1 color remain intact from 2019 to 2020 but in year 2021 there were two toys one with red and other with green color. Hence there is no change in 2019 to 2020 stating overlap of 1 and new count as 0. However for year 2020 to 2021 overlap count though will remain 1 (due to red color), new count will get the value as 1 (due to addition of green color of toy)

Attaching a sample data, original data has million of records.

Input data -

input_data = pd.DataFrame({'Toy': ['Toy1',  'Toy1', 'Toy1', 'Toy1', 'Toy2', 'Toy2', 'Toy2', 'Toy2', 'Toy2', 'Toy3', 'Toy3', 'Toy3'],
           'Toy_year': [2019,   2020,   2021,   2021,   2019,   2020,   2020,   2021,   2021,   2019,   2020,   2021],
            'Color': ['Red',    'Red',  'Red',  'Green ',   'Green ',   'Green ',   'Red',  'Green ',   'Red',  'Blue', 'Yellow',   'Yellow']})

Output data -

output_data = pd.DataFrame({'Year': ['2019-2020',   '2019-2020',    '2019-2020',    '2020-2021',    '2020-2021',    '2020-2021'],
            'Toy': ['Toy1', 'Toy2', 'Toy3', 'Toy1', 'Toy2', 'Toy3'],
            'overlap_count': [1,    1,  0,  1,  1,  1],
            'new_count': [0,    1,  1,  1,  1,  0]})

I am trying the below method but it is very slow -

toy_list = ['Toy1','Toy2','Toy3']
year_list = [2019,2020]
for i in toy_list:
    for j in year_list:
        y1 = j
        y2 = j+1
        x1 = input_data[(input_data['Toy']==i)&(input_data['Toy_year']==y1)]
        x2 = input_data[(input_data['Toy']==i)&(input_data['Toy_year']==y2)]
        z1 = list(set(x1.Color) & set(x2.Color))
        print (x1)
        print (x2)
        print (z1)

Any leads is really appreciated

pandas dataframe numpy python
1 Answers

A few steps here. First we unstack the data to have a cross table of toy/year vs color, where 1 indicates that that color was in force for that toy/year

df1 = input_data.assign(count=1).set_index(['Toy','Toy_year','Color']).unstack(level=2)
df1

df1 looks like this:


count
    Color    Blue   Green   Red Yellow
Toy Toy_year                
Toy1    2019 NaN    NaN     1.0 NaN
        2020 NaN    NaN     1.0 NaN
        2021 NaN    1.0     1.0 NaN
Toy2    2019 NaN    1.0     NaN NaN
        2020 NaN    1.0     1.0 NaN
        2021 NaN    1.0     1.0 NaN
Toy3    2019 1.0    NaN     NaN NaN
        2020 NaN    NaN     NaN 1.0
        2021 NaN    NaN     NaN 1.0

Now we can aggregate these, by row, to come up with summary statistics 'overlap_count' and 'new_count'. Overlap_count is the sum of matches between each row and its next (within each toy/year group), and new_count is the sum across the next row minus the overlap from the current row

ccols= df1.columns
df2 = df1.copy()
df2['overlap_count'] = df1.groupby(['Toy'], group_keys = False).apply(lambda g: (g[ccols] == g[ccols].shift(-1)).sum(axis=1))
df2['new_count']= df2.groupby(['Toy'], group_keys = False).apply(lambda g: g[ccols].shift(-1).sum(axis=1) - g['overlap_count'])

Now we just massage the result into the required form:

df3 = df2[['overlap_count','new_count']].reset_index().droplevel(1,axis=1)
df3['Year'] =  df3['Toy_year'].astype(str) + '-' + df3['Toy_year'].astype(str).shift(-1)
df3 = df3[df3['Toy_year'] != 2021].drop(columns = ['Toy_year'])
df3

output:

    Toy      overlap_count    new_count  Year
--  -----  ---------------  -----------  ---------
 0  Toy1                 1            0  2019-2020
 1  Toy1                 1            1  2020-2021
 3  Toy2                 1            1  2019-2020
 4  Toy2                 2            0  2020-2021
 6  Toy3                 0            1  2019-2020
 7  Toy3                 1            0  2020-2021
user_145514260
Related