CODEWITHSUNDEEP
pythonpandasnumpy
Lynn
Lynn

Reputation: 4388

Transform values in column using python

I would like to transform a string by removing the white space, replacing with a period and remove the first two values.

Data

ID  Date    Stat
AA  Q1 2022 ok
CC  Q2 2022 yes

Desired

ID  Date    Stat
AA  Q1.22   ok
CC  Q2.22   yes

Doing

df['Date'].str[-2:]

I am not sure how to expand on this, any suggestion is appreciated.

Upvotes: 0

Views: 139

Answers (5)

BENY
BENY

Reputation: 323226

We can do

df['Date'] = pd.to_datetime(df['Date'],format = 'Q%d %Y').dt.strftime('Q%d.%y')
Out[624]: 
0    Q01.22
1    Q02.22
Name: Date, dtype: object

Upvotes: 1

DYZ
DYZ

Reputation: 57033

Simply replace the space and two digits with a period:

df.Date = df.Date.str.replace(" \d\d", ".")

Upvotes: 1

Nick ODell
Nick ODell

Reputation: 25190

I think the most straightforward way would be to take the first two characters, a period, and the last two characters, and concatenate them. Something like this:

df['Date'] = df['Date'].str[:2] + "." + df['Date'].str[-2:]

Upvotes: 1

Toothpick Anemone
Toothpick Anemone

Reputation: 4644

Suppose that our string is as follows:

s = "Q1 2022"

We can remove the white-space characters and split the string into pieces as follows:

pieces = s.split()
print(pieces)

The result is:

pieces = ['Q1', '2022']

You can change the year '2022' to '22' as follows:

pieces = ['Q1', '2022']
# pieces[0] == "Q1"
# pieces[1] == '2022'
pieces[1] = pieces[1][-2:]

In the end, we have something like this:

def foobar(old_string:str) -> str:
    """
        EXAMPLE
            INPUT:  "Q1 2022"
            OUTPUT: "Q1.22"
    """
    old_string = "".join(str(ch) for ch in old_string)
    pieces = old_string.split()
    pieces[1] = pieces[1][-2:]
    new_string = ".".join(pieces)
    return new_string
    
result = foobar("Q1 2022")
print(result)
# prints "Q1.22"

Upvotes: 1

lwashington27
lwashington27

Reputation: 367

I would suggest using df.apply to adjust the values. df["Date"].apply(transform) iterates over every value in the column Date, and will split the string on each space, keeping only the last 2 characters in the second string, and then join them using a period.

def transform(string):
    lst = string.split(" ")
    lst[1] = lst[1][-2:]
    return ".".join(lst)

df["Date"] = df["Date"].apply(transform)

Upvotes: 1

Related Questions