python extract data from excel file

Question

I have an excel file which has data like below :

Message Type    Message Text    Message Local Date/Time
Card Admitted   Admitted 'Gulevata, Lena [Default]' (Card: 447430)   at 'EMEA-ISR-HZY 01.02.02 GF BACK DOOR [Default]' (IN).    9/17/2018 5:19
Card Admitted   Admitted 'Gulevata, Lena [Default]' (Card: 447430)   at 'EMEA-ISR-HZY 01.02.02 GF BACK DOOR [Default]' (IN).    9/17/2018 5:57
Card Admitted   Admitted 'Gulevata, Lena [Default]' (Card: 447430)   at 'EMEA-ISR-HZY 01.01.01 GF MAIN ENTRANCE [Default]' (IN).    9/17/2018 5:58
Card Admitted   Admitted 'Gulevata, Lena [Default]' (Card: 447430)   at 'EMEA-ISR-HZY 01.02.01 GF NORTH ELEVATOR LOBBY [Default]' (IN). 9/17/2018 5:59

And I want it to convert it to a dataframe like below for further processing. Basically I want to extract the Card Number from the Message Text . Any pointers using Python and Pandas would be helpful

Message Type    Message Text    Message Local Date/Time
Card Admitted   447430  9/17/2018 5:19
Card Admitted   447430  9/17/2018 5:57
Card Admitted   447430  9/17/2018 5:58
Card Admitted   447430  9/17/2018 5:59

Ezer K · Accepted Answer · 2019-09-19 19:08:11Z

1

you could do something along the lines of:

import pandas as pd
df = pd.read_excel(<file_name>)
df['Meassge Text'] = [x.split('card: ')[1].split(')')[0] for x in df['Meassge Text']]

answered Sep 19, 2019 at 19:08

Ezer K

3,7615 gold badges25 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

python extract data from excel file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related