Converting VBA script to Python Script

Question

I'm trying to convert some VBA scripts into a python script, and I have been having troubles trying to figure some few things out, as the results seem different from what the excel file gives. So I have an example dataframe like this :

|Name    |  A_Date    |
_______________________
|RAHEAL  |  04/30/2020|
|GIFTY   |  05/31/2020|
||ERIC   |  03/16/2020|
|PETER   |  05/01/2020|
|EMMANUEL|  12/15/2019| 
|BABA    |  05/23/2020|

and I would want to achieve this result(VBA script result) :

|Name    |  A_Date    | Sold
__________________________________
|RAHEAL  |  04/30/2020| No
|GIFTY   |  05/31/2020| Yes
||ERIC   |  03/16/2020| No
|PETER   |  05/01/2020| Yes
|EMMANUEL|  12/15/2019| No
|BABA    |  05/23/2020| Yes

By converting this VBA script :

Range("C2").Select
    Selection.Value = _
        "=IF(RC[-1]>=(INT(R2C2)-DAY(INT(R2C2))+1),""Yes"",""No"")"
    Selection.AutoFill Destination:=Range("C2", "C" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range("C1").Value = "Sold"
    ActiveSheet.Columns("C").Copy
    ActiveSheet.Columns("C").PasteSpecial xlPasteValues

Simply :=IF(B2>=(INT($B$2)-DAY(INT($B$2))+1),"Yes","No")

To this Python script:

sales['Sold']=np.where(sales['A_Date']>=(sales['A_Date'] - pd.to_timedelta(sales.A_Date.dt.day, unit='d'))+ timedelta(days=1),'Yes','No')

But I keep getting a "Yes" throughout.... could anyone help me spot out where I might have made some kind of mistake

Why not try an automated converter? Thats what I'd try first, and then see if it works and maybe clean up the code — wamster
– wamster, Commented Jan 10, 2021 at 16:28
@wamster can you suggest any of the good ones out there, please ? — Dela
– Dela, Commented Jan 10, 2021 at 16:32
Sorry I have no experience with this, but a quick google search shows this might fit your bill. The main thing here is to make sure its been recently updated, and this has been. Otherwise if its like a 5 years old it might convert to python2 instead — wamster
– wamster, Commented Jan 10, 2021 at 16:39
Your VBA is referencing column L but your dataframe only has 2 columns initially. And your VBA fomula doesn't match with your Excel formula. If your Excel formula is correct then your first desired output Sold value is wrong. — QHarr
– QHarr, Commented Jan 10, 2021 at 19:58

buran · Accepted Answer · 2021-01-12 11:38:24Z

2

import pandas as pd

df = pd.DataFrame({'Name':['RAHEAL','GIFTY','ERIC','PETER','EMMANUEL','BABA'],
                   'A_Date':['04/30/2020','05/31/2020','03/16/2020',
                             '05/01/2020','12/15/2019','05/23/2020']})

df['A_Date'] = pd.to_datetime(df['A_Date'])
print(df)
df['Sold'] = df['A_Date'] >= df['A_Date'].iloc[0].replace(day=1)
df['Sold'] = df['Sold'].map({True:'Yes', False:'No'})
print(df)

output:

       Name     A_Date
0    RAHEAL 2020-04-30
1     GIFTY 2020-05-31
2      ERIC 2020-03-16
3     PETER 2020-05-01
4  EMMANUEL 2019-12-15
5      BABA 2020-05-23

       Name     A_Date Sold
0    RAHEAL 2020-04-30  Yes
1     GIFTY 2020-05-31  Yes
2      ERIC 2020-03-16   No
3     PETER 2020-05-01  Yes
4  EMMANUEL 2019-12-15   No
5      BABA 2020-05-23  Yes

If I read the formula right - if A_Date value >= 04/01/2020 (i.e. first day of month for date in B2), so RAHEAL should be Yes too

I don't know if you noticed (and if this is intended), but if A_Date value has a fractional part (i.e. time), when you calculate the value for 1st of the month, there is room for error. If the time in B2 is let's say 10:00 AM, when you calculate cut value, it will be 04/1/2020 10:00. Then if you have another value, let's say 04/01/2020 09:00, it will be evaluated as False/No. This is how it works also in your Excel formula.

EDIT (12 Jan 2021): Note, values in column A_Date are of type datetime.datetime or datetime.date. Presumably they are converted when reading the Excel file or explicitly afterwards.

edited Jan 12, 2021 at 11:38

answered Jan 10, 2021 at 19:58

buran

14.4k13 gold badges45 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

buran Over a year ago

@QHarr, Based on the formula using DAY I assume it's a Date in Excel, but in any case I would have converted it (and I did) to datetime object in the dataframe. They can always cast it to str if prefer.

buran Over a year ago

@YasserKhalil it's difficult to tell what your problem is without seeing your code. What I show is my code and the output. If you can share some code and full traceback. Note that, values of A_Date columns are datetime.datetime or datetime.date objects, not str. I use datetime.datetime.replace()

buran Over a year ago

@YasserKhalil, check stackoverflow.com/q/28133018/4046632 I edited my answer to explicitly mention it.

buran Over a year ago

@YasserKhalil, you are right, iloc(0) will return first value (i.e. index 0) from the column (see this). The formula they want to convert from excel reference $B$2 (cell B1 is the header). replace() - there is link in my previous comment.

buran Over a year ago

@YasserKhalil, yes, it can be done in one line, but it's less readable and readability counts. It will be easier to follow the code in slow motion instead of one-liner. At least that was the reason why I did it on separate lines.

|

QHarr · Accepted Answer · 2021-01-12 12:05:50Z

1

Very much embarassed I didn't see the simple elegant solution that buran gave +. I did more of a literal translation.

first_date.toordinal() - 693594 is the integer date value for your initial date, current_date.toordinal() - 693594 is the integer date value for the current iteration of the dates column. I apply your cell formula logic to each A_Date row value and output as the corresponding Sold column value.

import pandas as pd
from datetime import datetime

def is_sold(current_date:datetime, first_date:datetime, day_no:int)->str:
    # use of toordinal idea from @rjha94 https://stackoverflow.com/a/47478659
    if current_date.toordinal() - 693594 >= first_date.toordinal() - 693594 - day_no + 1:
        return "Yes" 
    else:
        return "No"

sales = pd.DataFrame({'Name':['RAHEAL','GIFTY','ERIC','PETER','EMMANUEL','BABA'],
                      'A_Date':['2020-04-30','2020-05-31','2020-03-16','2020-05-01','2019-12-15','2020-05-23']})
sales['A_Date'] = pd.to_datetime(sales['A_Date'], errors='coerce')
sales['Sold'] = sales['A_Date'].apply(lambda x:is_sold(x, sales['A_Date'][0],  x.day))
print(sales)

edited Jan 12, 2021 at 12:05

answered Jan 10, 2021 at 20:10

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

2 Comments

YasserKhalil Over a year ago

df should be sales or the vice-versa :)

QHarr Over a year ago

Correct. I wrote originally for df and then saw OP used sales and didn't update throughout. Well spotted @YasserKhalil

Collectives™ on Stack Overflow

Converting VBA script to Python Script

2 Answers 2

10 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related