CODEWITHSUNDEEP
pythonpandasdataframedata-extraction
zee
zee

Reputation: 31

How to extract particular values from a dataframe col and put them in another column?

I have a dataframe consisting of a column of strings. I want to extract the place, date, and race number from one column.

Dataframe:

- S.no.            FileName
- 0.     Albany17NOV19sectionalRace1.csv
- 1.     Albany22NOV19sectionalRace4.csv
- 2.     New York1NOV19sectionalRace7.csv
- 3.     Aquis Park Gold Coast27NOV19sectionalRace2.csv

Desired dataframe:

- S.no.   Place                     Date     Racenumber
- 0.     Albany                    17NOV19    Race1
- 1.     Albany                    22NOV19    Race4
- 2.     New York                  1NOV19     Race7
- 3.     Aquis park Gold Coast     27NOV19    Race2

Upvotes: 1

Views: 69

Answers (3)

Cainã Max Couto da Silva
Cainã Max Couto da Silva

Reputation: 4929

Not as good as the other answers, but it still accomplishes the job:

extract_info = {
    'Date': lambda x: x.str.findall('\d.+?\d{2}').str[0],
    'Place': lambda x: x.str.findall('^.+?(?=\d)').str[0],
    'Racenumber': lambda x: x.str.findall('Race\d+').str[0]}

df = df.FileName.agg(extract_info.values())
df.columns = extract_info.keys()

print(df)

      Date                  Place Racenumber
0  17NOV19                 Albany      Race1
1  22NOV19                 Albany      Race4
2   1NOV19               New York      Race7
3  27NOV19  Aquis Park Gold Coast      Race2

Upvotes: 0

wwnde
wwnde

Reputation: 26676

Split by;

  1. digit followed by Nondigit and digit that is say 17NOV19

or

  1. sectional

or

3 special character .

After split drop all rows that have None as values and any others not wanted. Can rename columns if you needed to

    df=df.FileName.str.split('(\d+\D+\d+)|(sectional)|(\.)', expand=True).dropna(1).drop(columns=[4,6,11,12])
print(df)
        

          

                    0        1      8
0                 Albany  17NOV19  Race1
1                 Albany  22NOV19  Race4
2               New York   1NOV19  Race7
3  Aquis Park Gold Coast  27NOV19  Race2

Upvotes: 1

Serial Lazer
Serial Lazer

Reputation: 1669

A regex function should do the job:

import re


def split_string_to_groups(s: str):
    temp = re.compile("([a-zA-Z\s]+)([0-9]+[a-zA-Z]+[0-9]+)(sectional)(Race[0-9]+)(\.csv)")
    res = temp.match(s).groups()
    return res

print(split_string_to_groups("Albany17NOV19sectionalRace1.csv"))
print(split_string_to_groups("Aquis Park Gold Coast27NOV19sectionalRace2.csv"))

Output:

('Albany', '17NOV19', 'sectional', 'Race1', '.csv')
('Aquis Park Gold Coast', '27NOV19', 'sectional', 'Race2', '.csv')

Upvotes: 0

Related Questions