Transform values in column using python-CodePudding

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.

CodePudding user response：

Simply replace the space and two digits with a period:

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

CodePudding user response：

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)

CodePudding user response：

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"

CodePudding user response：

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

CodePudding user response：

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