As @DSM points out, you can do this more directly using the vectorised string methods:

df['Date'].str[-4:].astype(int)

Or using extract (assuming there is only one set of digits of length 4 somewhere in each string):

df['Date'].str.extract('(?P<year>\d{4})').astype(int)

An alternative slightly more flexible way, might be to use apply (or equivalently map) to do this:

df['Date'] = df['Date'].apply(lambda x: int(str(x)[-4:]))
             #  converts the last 4 characters of the string to an integer

The lambda function, is taking the input from the Date and converting it to a year.
You could (and perhaps should) write this more verbosely as:

def convert_to_year(date_in_some_format):
    date_as_string = str(date_in_some_format)  # cast to string
    year_as_string = date_in_some_format[-4:] # last four characters
    return int(year_as_string)

df['Date'] = df['Date'].apply(convert_to_year)

Perhaps 'Year' is a better name for this column...

Answer from Andy Hayden on Stack Overflow
🌐
Pandas
pandas.pydata.org › docs › reference › api › pandas.DataFrame.transform.html
pandas.DataFrame.transform — pandas 3.0.2 documentation
>>> df = pd.DataFrame({"A": range(3), "B": range(1, 4)}) >>> df A B 0 0 1 1 1 2 2 2 3 >>> df.transform(lambda x: x + 1) A B 0 1 2 1 2 3 2 3 4
🌐
GeeksforGeeks
geeksforgeeks.org › python › python-pandas-dataframe-transform
Python | Pandas DataFrame.transform - GeeksforGeeks
February 21, 2019 - Syntax: DataFrame.transform(func, axis=0, *args, **kwargs) Parameter : func : Function to use for transforming the data axis : {0 or ‘index’, 1 or ‘columns’}, default 0 *args : Positional arguments to pass to func. **kwargs : Keyword arguments to pass to func. Returns : DataFrame Example #1 : Use DataFrame.transform() function ...
🌐
Skytowner
skytowner.com › explore › pandas_dataframe_transform_method
Pandas DataFrame | transform method with Examples
Pandas DataFrame.transform(~) method applies a function to transform the rows or columns of the source DataFrame.
🌐
W3Schools
w3schools.com › python › pandas › ref_df_transform.asp
Pandas DataFrame transform() Method
import pandas as pd def eur_to_nok(x): return x * 10 data = { "for1": [2, 6, 3], "for5": [8, 20, 12] } df = pd.DataFrame(data) newdf = df.transform(eur_to_nok) print(newdf) Try it Yourself »
🌐
Delft Stack
delftstack.com › home › howto › python pandas › apply function to column pandas
How to Apply a Function to a Column in Pandas Dataframe | Delft Stack
February 5, 2025 - In Pandas, columns and dataframes can be transformed and manipulated using methods such as apply() and transform(). The desired transformations are passed in as arguments to the methods as functions.
Top answer
1 of 2
1

First of all, if you need to sort over datetimes, I would suggest to either use the YYYYMMDD string representation of dates (e.g. 20191108 for the first record) or to use actual datetime data types. Using the American notation is confusing and not easy to sort on.

In any case, to solve your issue I would advise to use pandas pivot function first, followed by a fill NaN (i.e. fillna) function with a backfill (i.e. bfill) method.

EDIT: If you want to keep the Country column, it seems that using it as a multi-index with the Date column won't work with pivot. What you can do is to keep the original df and join it with the new one on the Date column.

import pandas as pd
import datetime as dt    

# Create DataFrame similar to example
df = pd.DataFrame(data={'Date': ['11/8/2019','2/20/2019','9/22/2017','6/28/2016','6/27/2016','6/24/2016','6/12/2015','6/13/2014'], 
                        'Team': ['Team A','Team B','Team A','Team B','Team C','Team A','Team C','Team C'], 
                        'Rating': [95,90,90,90,90,95,100,100]})


# Convert strings to datetimes
df['Date'] = df['Date'].map(lambda x: dt.datetime.strptime(x, '%m/%d/%Y'))
df['Country'] = 'United Kingdom'

# Pivot DataFrame
dfp = df.pivot(columns='Team', values='Rating')

# Join with Country from original df
dfp = df[['Date', 'Country']].join(dfp)

# sort descending on Date
dfp.sort_values(by='Date', ascending=False, inplace=True)

# dfp is:
# Date        Country         Team A  Team B  Team C
# 2019-11-08  United Kingdom  95.0     NaN     NaN
# 2019-02-20  United Kingdom   NaN    90.0     NaN
# 2017-09-22  United Kingdom  90.0     NaN     NaN
# ...

# Fill NaN values using the "next" row value
dfp.fillna(method='bfill', inplace=True)

# dfp is:
# Date        Country         Team A  Team B  Team C                              
# 2019-11-08  United Kingdom    95.0    90.0    90.0
# 2019-02-20  United Kingdom    90.0    90.0    90.0
# 2017-09-22  United Kingdom    90.0    90.0    90.0
# ...
2 of 2
1

Basically, what you need is:

data.pivot_table(index=['Country', 'Date'], columns='Team', values='Rating').reset_index()\
    .sort_values(['Country', 'Date'], ascending=False).fillna(method='bfill', axis=0)

It will create a pivot_table, sort the values in the irregular order you have, and pull the last existing values where missing.

🌐
pandas
pandas.pydata.org › pandas-docs › dev › reference › api › pandas.DataFrame.transform.html
pandas.DataFrame.transform — pandas documentation
>>> df = pd.DataFrame({"A": range(3), "B": range(1, 4)}) >>> df A B 0 0 1 1 1 2 2 2 3 >>> df.transform(lambda x: x + 1) A B 0 1 2 1 2 3 2 3 4
Find elsewhere
🌐
w3resource
w3resource.com › pandas › dataframe › dataframe-transform.php
Pandas DataFrame: transform() function - w3resource
August 19, 2022 - The transform() function is used to call function (func) on self producing a DataFrame with transformed values and that has the same axis length as self. ... Returns: DataFrame A DataFrame that must have the same length as self.
🌐
Towards Data Science
towardsdatascience.com › home › latest › 8 ways to transform pandas dataframes
8 Ways to Transform Pandas Dataframes | Towards Data Science
January 23, 2025 - For instance, in the first row, the name is Jane and the ctg is A. Thus, the columns that represent these values are 1 and all other columns are 0. The pivot_table function transforms a dataframe to a format that explains the relationship among variables.
🌐
Practical Business Python
pbpython.com › pandas_transform.html
Understanding the Transform Function in Pandas - Practical Business Python
April 4, 2017 - As described in the book, transform is an operation used in conjunction with groupby (which is one of the most useful operations in pandas). I suspect most pandas users likely have used aggregate , filter or apply with groupby to summarize data. However, transform is a little more difficult to understand - especially coming from an Excel world.
🌐
Pandas
pandas.pydata.org › pandas-docs › version › 0.24 › reference › api › pandas.DataFrame.transform.html
pandas.DataFrame.transform — pandas 0.24.2 documentation
>>> df = pd.DataFrame({'A': range(3), 'B': range(1, 4)}) >>> df A B 0 0 1 1 1 2 2 2 3 >>> df.transform(lambda x: x + 1) A B 0 1 2 1 2 3 2 3 4
🌐
Sentry
sentry.io › sentry answers › python › change a column type in a dataframe in python pandas
Change a column type in a DataFrame in Python Pandas | Sentry
If we want to convert a column to a sensible numeric data type (integer or float), we should use the to_numeric function. If we want Pandas to decide which data types to use for each column, we should use the convert_dtypes method.
🌐
Analytics Vidhya
analyticsvidhya.com › home › learn how to use the transform function in pandas (with python code)
Learn How to use the Transform Function in Pandas (with Python code)
November 25, 2024 - This manipulates a single row or column based on axis value and doesn’t manipulate a whole dataframe. So, we can use either the Apply or the Transform function, depending on the requirement.
🌐
Medium
medium.com › @stacymacbrains › when-to-use-pandas-transform-function-e137c89ca070
When to Use Pandas transform() Function | by Ogochukwu Stanley Ikegbo | Medium
January 9, 2025 - The transform() function applies a transformation function element-wise to a DataFrame or Series, returning a DataFrame or Series of the same shape. It's particularly useful in group operations or when modifying columns without changing ...
🌐
GitHub
github.com › pandas-dev › pandas › issues › 342
Enable easier transformations of multiple columns in DataFrame · Issue #342 · pandas-dev/pandas
November 7, 2011 - things like df[cols] = transform(df[cols]) should be possible in a mixed-type DataFrmae, per the mailing list discussion
Author   wesm
🌐
Pandas
pandas.pydata.org › pandas-docs › stable › reference › api › pandas.DataFrame.transpose.html
pandas.DataFrame.transpose — pandas 3.0.1 documentation
Reflect the DataFrame over its main diagonal by writing rows as columns and vice-versa. The property T is an accessor to the method transpose(). ... Accepted for compatibility with NumPy. ... This keyword is now ignored; changing its value will have no impact on the method.