CODEWITHSUNDEEP
pythonpandasnumpy
Lynn
Lynn

Reputation: 4408

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: 143

Answers (5)

BENY
BENY

Reputation: 323396

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: 57135

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: 25504

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