Extract Data from CSV using Python

Python Help
1

PY

I want to use Python and extra data from the 5th line (that is Deal ID, Deal Name, Origination Date, Maturity Date) to the 8th line. Also once I have extracted data I want to save the file in the following format ExtensionReport202205.csv and whole things should be Dynamic. I used skip rows and iloc to get rows but it is not dynamic? So kindly advise.

2

Hi @Cmpunk88.
According to the pandas documentation, you could just use the header keyword argument to specify the header row, and use the other keyword argument usecols to specify which columns you need to extract.
Once you’ve done that, you should be able to use to_csv() of your DataFrame to save it as you want.
Give it a try and let us know if you need further assistance, but please post the code you have :slight_smile:

Cheers.

3

Hi Cmpunk88,
If the word “dynamic” means that you can read and write in an open Excel book, openpyxl is not for you. But if you can use pywin32, you can do it ‘dynamically’.

4

Thanks for your response.
PY

This is the table . I am working with.

I need to get following output:

|Deal ID|Deal Name|Origination Date|Maturity Date|
|PEP|D251|5/1/2019|3/13/2023|
|KO|D351|10/2/2020|4/15/2025|
|MM|D451|11/10/2021|12/1/2026|

I do not need deals that are in queue

Also I need to save csv file in following format .Extension_Report202205.csv

Code I have written

df = pd.read_excel(xls)
fdate = df['Unnamed: 0'].str.extract('^Run Date:(\d{4}/\d\d)/\d\d').dropna().iloc[0,0].replace('/','')
fn = f'ExtensionReport{fdate}.csv'
fn

df4=df.dropna()

After this line I am able to get desired result but I am also getting deals that are in queue?

Could any one advise?