CODEWITHSUNDEEP
pythonpandasdataframe
Hana
Hana

Reputation: 1470

How to compare values in a column by date in pandas?

I have the following sample DataFrame:

 Item Num       Price Type       Sales Date    
 456                    4           2017-12                 
 456                    1           2018-01                 
 340                    1           2017-12                 
 340                    1           2018-01                 
 500                    2           2017-12                 
 500                    1           2018-01

I want to check if for each item number that is price type 1 in january, if the price type was different in december, and flag that row. So I want my output to be this: 

 Item_Num       Price Type       Sales Date    Flag
 456                    4           2017-12    price type change
 500                    2           2017-12    price type change

I'm thinking of something like this: 

 for num in df.Item_Num:
     print(desired rows)

Upvotes: 2

Views: 1283

Answers (1)

Vaishali
Vaishali

Reputation: 38415

Is this what you want?

df['Sales Date'] = pd.to_datetime(df['Sales Date'])

cond = (df['Item Num'] == df['Item Num'].shift(-1)) & (df['Price Type'] != df['Price Type'].shift(-1))

df['Flag'] = np.where(cond, 'Price Type Change', '')

You get

    Item Num    Price Type  Sales Date  Flag
0   456         4           2017-12-01  Price Type Change
1   456         1           2018-01-01  
2   340         1           2017-12-01  
3   340         1           2018-01-01  
4   500         2           2017-12-01  Price Type Change
5   500         1           2018-01-01

If you want to compare only the decemeber 2017 and January 2018 values, you can filter the dataframe first using

df = df[((df['Sales Date'].dt.year == 2018) & (df['Sales Date'].dt.month == 1)) | ((df['Sales Date'].dt.year == 2017) & (df['Sales Date'].dt.month == 12))]

Upvotes: 2

Related Questions