Create a new column from existing pandas dataframe to find the version of a file

user_13126794 0
Viewed 24

I have pandas dataframe which includes s3 path and timestamp:

    key                                last_modified_time
0  input/sales/1234_1.snappy.parquet   2021-07-26 01:17:51+00:00
1  input/sales/2345_2.snappy.parquet   2021-07-26 01:17:53+00:00
2  input/sales/123456_3.snappy.parquet 2021-07-26 01:17:55+00:00

How can I create a new column which will tell what is the version of the file? The output would be:

 key                                version    sales_id
0  input/sales/1234_1.snappy.parquet   1            1234
1  input/sales/2345_2.snappy.parquet   2            2345
2  input/sales/123456_3.snappy.parquet 3            123456
pandas dataframe
1 Answers

Try .str.extract:

df[["sales_id", "version"]] = df["key"].str.extract(r"(\d+)_(\d+)\.")
print(df[["key", "version", "sales_id"]])

Prints:

                                   key version sales_id
0    input/sales/1234_1.snappy.parquet       1     1234
1    input/sales/2345_2.snappy.parquet       2     2345
2  input/sales/123456_3.snappy.parquet       3   123456
user_100359850
Related