Fill in Data Frame based on Previous Data

user_8533813 0
Viewed 62

I am working on a project with a retailer where we are wanting to clean some data for reporting purposes. The retailer has multiple stores and every week the staff in the stores would scan the different items on different displays (They scan the display first to let us know which display they are talking about). Also, they only scan displays that changed in that week, if a display was not changed then we assume that it stayed the same.

Right now we are working with 2 dataframes:

Hierarchy Data Frame Example: This table basically has weeks 1 to 52 for every end cap (display) in every store. Let's assume the company only has 2 stores and 3 end caps in each store. Also different stores could have different End Cap codes but that shouldn't matter for our purposes (I don't think).

    Week  Store End Cap
0      1      1       A
1      1      1       B
2      1      1       C
3      1      2       A
4      1      2       B
5      1      2       D
6      2      1       A
7      2      1       B
8      2      1       C
9      2      2       A
10     2      2       B
11     2      2       D

Next we have the historical file with actual changes to be used to update the End Caps.

    Week  Store End Cap     UPC
0      1      1       A  123456
1      1      1       B  789456
2      1      1       B  546879
3      1      1       C  423156
4      1      2       A  231567
5      1      2       B  456123
6      1      2       D  689741
7      2      1       A  321654
8      2      1       C  852634
9      2      1       C  979541
10     2      2       A  132645
11     2      2       B  787878
12     2      2       D  615432

To merge the two dataframes I used:

merged_df = pd.merge(hierarchy, hist,  how='left', left_on=['Week','Store', 'End Cap'], right_on = ['Week','Store', 'End Cap'])

Which gave me:

    Week  Store End Cap       UPC
0      1      1       A  123456.0
1      1      1       B  789456.0
2      1      1       B  546879.0
3      1      1       C  423156.0
4      1      2       A  231567.0
5      1      2       B  456123.0
6      1      2       D  689741.0
7      2      1       A  321654.0
8      2      1       B       NaN
9      2      1       C  852634.0
10     2      1       C  979541.0
11     2      2       A  132645.0
12     2      2       B  787878.0
13     2      2       D  615432.0

Except for the one instance where it shows NAN. Store 1 end cap 2 in week 2 did not change and hence was not scanned. So it did not show up in the historical dataframe. In this case I would want to see the latest items that were scanned for that end cap at that store (see row 2 & 3 of the historical dataframe). So technically that could have also been scanned in Week 52 of last year but I just want to fill the NAN with the latest information to show that it did not change. How do I go about doing that?

The desired output would look like:

    Week  Store End Cap       UPC
0      1      1       A  123456.0
1      1      1       B  789456.0
2      1      1       B  546879.0
3      1      1       C  423156.0
4      1      2       A  231567.0
5      1      2       B  456123.0
6      1      2       D  689741.0
7      2      1       A  321654.0
8      2      1       B  789456.0
9      2      1       B  546879.0
10     2      1       C  852634.0
11     2      1       C  979541.0
12     2      2       A  132645.0
13     2      2       B  787878.0
14     2      2       D  615432.0

Thank you!

EDIT: Further to the above, I tried to sort the data and then forward fill which only partially fixed the issue I am having:

sorted_df = merged_df.sort_values(['End Cap', 'Store'], ascending=[True, True])

    Week  Store End Cap       UPC
0      1      1       A  123456.0
7      2      1       A  321654.0
4      1      2       A  231567.0
11     2      2       A  132645.0
1      1      1       B  789456.0
2      1      1       B  546879.0
8      2      1       B       NaN
5      1      2       B  456123.0
12     2      2       B  787878.0
3      1      1       C  423156.0
9      2      1       C  852634.0
10     2      1       C  979541.0
6      1      2       D  689741.0
13     2      2       D  615432.0

sorted_filled = sorted_df.fillna(method='ffill')

Gives me:

    Week  Store End Cap       UPC
0      1      1       A  123456.0
7      2      1       A  321654.0
4      1      2       A  231567.0
11     2      2       A  132645.0
1      1      1       B  789456.0
2      1      1       B  546879.0
8      2      1       B  546879.0
5      1      2       B  456123.0
12     2      2       B  787878.0
3      1      1       C  423156.0
9      2      1       C  852634.0
10     2      1       C  979541.0
6      1      2       D  689741.0
13     2      2       D  615432.0

This output did add the 546879 to week 2 store1 End Cap B but it did not add the 789456 which I also need. I need it to add another row with that value as well.

pandas dataframe
2 Answers

You could try something like this:

# New df without Nan values
df1 = merged_df[~merged_df["name"].isna()]

# New df with Nan values only
df2 = merged_df[merged_df["name"].isna()]

# Set previous week
df2["Week"] = df2["Week"] - 1

# For each W/S/EC in df2, grab corresponding UPC value in df1
# and append a new row (shifted back to current week) to df1
for week in df2["Week"].values:
    for store in df2["Store"].values:
        for cap in df2["Enc Cap"].values:
            mask = (
                (df1["Week"] == week)
                & (df1["Store"] == store)
                & (df1["End Cap"] == cap)
            )
            upc = df1.loc[mask, "UPC"].item()
            row = [week + 1, store, cap, upc]
            df1.loc[len(df1)] = row

sorted_df = df1.sort_values(by=["Week", "Store", "End Cap"])
user_112460560

You can also do it like this creating a helper column to handle duplicate UPC per store/week/end cap.

idxcols=['Week', 'Store', 'End Cap']
hist_idx = hist.set_index(idxcols + [hist.groupby(idxcols).cumcount()])

hier_idx = hierarchy.set_index(idxcols+[hierarchy.groupby(idxcols).cumcount()])

hier_idx.join(hist_idx, how='right')\
        .unstack('Week')\
        .ffill(axis=1)\
        .stack('Week')\
        .reorder_levels([3,0,1,2])\
        .sort_index()\
        .reset_index()\
        .drop('level_3', axis=1)

Output:

    Week  Store End Cap       UPC
0      1      1       A  123456.0
1      1      1       B  789456.0
2      1      1       B  546879.0
3      1      1       C  423156.0
4      1      2       A  231567.0
5      1      2       B  456123.0
6      1      2       D  689741.0
7      2      1       A  321654.0
8      2      1       B  789456.0
9      2      1       B  546879.0
10     2      1       C  852634.0
11     2      1       C  979541.0
12     2      2       A  132645.0
13     2      2       B  787878.0
14     2      2       D  615432.0
user_63615310
Related