Home > Back-end >  Pandas DataFrame Change Values Based on Values in Different Rows
Pandas DataFrame Change Values Based on Values in Different Rows

Time:10-06

I have a DataFrame of store sales for 1115 stores with dates over about 2.5 years. The StateHoliday column is a categorical variable indicating the type of holiday it is. See the piece of the df below. As can be seen, b is the code for Easter. There are other codes for other holidays.

Piece of DF

My objective is to analyze sales before and during a holiday. The way I seek to do this is to change the value of the StateHoliday column to something unique for the few days before a particular holiday. For example, b is the code for Easter, so I could change the value to b- indicating that the day is shortly before Easter. The only way I can think to do this is to go through and manually change these values for certain dates. There aren't THAT many holidays, so it wouldn't be that hard to do. But still very annoying!

CodePudding user response:

Tom, see if this works for you, if not please provide additional information: In the file I have the following data:

Store,Sales,Date,StateHoliday
1,6729,2013-03-25,0
1,6686,2013-03-26,0
1,6660,2013-03-27,0
1,7285,2013-03-28,0
1,6729,2013-03-29,b
1115,10712,2015-07-01,0
1115,11110,2015-07-02,0
1115,10500,2015-07-03,0
1115,12000,2015-07-04,c

import pandas as pd

fname = r"D:\workspace\projects\misc\data\holiday_sales.csv"
df = pd.read_csv(fname)

df["Date"] = pd.to_datetime(df["Date"])

holidays = df[df["StateHoliday"]!="0"].copy(deep=True) # taking only holidays
dictDate2Holiday = dict(zip(holidays["Date"].tolist(), holidays["StateHoliday"].tolist()))

look_back = 2 # how many days back you want to go
holiday_look_back = []
# building a list of pairs (prev days, holiday code)
for dt, h in dictDate2Holiday.items():
    prev = dt
    holiday_look_back.append((prev, h))
    for i in range(1, look_back 1):
        prev = prev - pd.Timedelta(days=1)
        holiday_look_back.append((prev, h))

dfHolidayLookBack = pd.DataFrame(holiday_look_back, columns=["Date", "StateHolidayNew"])

df = df.merge(dfHolidayLookBack, how="left", on="Date")
df["StateHolidayNew"].fillna("0", inplace=True)
print(df)

columns StateHolidayNew should have the info you need to start analyzing your data

CodePudding user response:

Assuming you have a dataframe like this:

    Store  Sales       Date StateHoliday
0       2   4205 2016-11-15            0
1       1    684 2016-07-13            0
2       2   8946 2017-04-15            0
3       1   6929 2017-02-02            0
4       2   8296 2017-10-30            b
5       1   8261 2015-10-05            0
6       2   3904 2016-08-22            0
7       1   2613 2017-12-30            0
8       2   1324 2016-08-23            0
9       1   6961 2015-11-11            0
10      2     15 2016-12-06            a
11      1   9107 2016-07-05            0
12      2   1138 2015-03-29            0
13      1   7590 2015-06-24            0
14      2   5172 2017-04-29            0
15      1    660 2016-06-21            0
16      2   2539 2017-04-25            0

What you can do is group the values between the different alphabets which represent the holidays and then groupby to find out the sales according to each group. An improvement to this would be to backfill the numbers before the groups, exp., groups=0.0 would become b_0 which would make it easier to understand the groups and what holiday they represent, but I am not sure how to do that.

df['StateHolidayBool'] = df['StateHoliday'].str.isalpha().fillna(False).replace({False: 0, True: 1})
df = df.assign(group = (df[~df['StateHolidayBool'].between(1,1)].index.to_series().diff() > 1).cumsum())
df = df.assign(groups = np.where(df.group.notna(), df.group, df.StateHoliday)).drop(['StateHolidayBool', 'group'], axis=1)
df[~df['groups'].str.isalpha().fillna(False)].groupby('groups').sum()

Output:

        Store  Sales
groups              
0.0         6  20764
1.0         7  23063
2.0         9  26206

Final DataFrame:

    Store  Sales       Date StateHoliday groups
0       2   4205 2016-11-15            0    0.0
1       1    684 2016-07-13            0    0.0
2       2   8946 2017-04-15            0    0.0
3       1   6929 2017-02-02            0    0.0
4       2   8296 2017-10-30            b      b
5       1   8261 2015-10-05            0    1.0
6       2   3904 2016-08-22            0    1.0
7       1   2613 2017-12-30            0    1.0
8       2   1324 2016-08-23            0    1.0
9       1   6961 2015-11-11            0    1.0
10      2     15 2016-12-06            a      a
11      1   9107 2016-07-05            0    2.0
12      2   1138 2015-03-29            0    2.0
13      1   7590 2015-06-24            0    2.0
14      2   5172 2017-04-29            0    2.0
15      1    660 2016-06-21            0    2.0
16      2   2539 2017-04-25            0    2.0
  • Related