Getting timestamp from one column and put it in another one with pandas

Question

I'm using watchdog to monitor some folders and create a csv file to see when a file is created and modified. Now, I got a dataframe like this from the csv:

       full_path             name             created           modified
0    C:\T1\1.txt            1.txt            14:04:30             NaN
1    C:\T1\1.txt            1.txt              NaN              14:04:30
2    C:\T1\T2\1.txt         1.txt            14:10:30              NaN
3    C:\T1\T2\1.txt         1.txt              NaN              14:10:30
4    C:\T1\T2\T3\1.txt      1.txt            14:15:30             NaN
5    C:\T1\T2\T3\1.txt      1.txt              NaN              14:15:30
6    C:\T1\T2\T3\T4\1.txt   1.txt            14:20:30             NaN
7    C:\T1\T2\T3\T4\1.txt   1.txt              NaN              14:20:30
8    C:\T1\2.txt            2.txt            14:25:30             NaN
9    C:\T1\2.txt            2.txt              NaN              14:25:30
10   C:\T1\T2\2.txt         2.txt            14:30:30             NaN
11   C:\T1\T2\2.txt         2.txt              NaN              14:30:30
12   C:\T1\T2\T3\2.txt      2.txt            14:35:30             NaN
13   C:\T1\T2\T3\2.txt      2.txt              NaN              14:35:30
14   C:\T1\T2\T3\T4\2.txt   2.txt              NaN              14:40:30

Watchdog, usually gives two timestamps when a file is moved to another folder (created and modified), but somehow for the last file when it moved to T4 folder it gave only the modified timestamp. I use this code to convert this dataframe in only two rows and I put the timestamps for each folder in other columns:

m0 = (df["full_path"].ne(df["full_path"].shift(1, fill_value=df["full_path"].iloc[0])) & df["name"].eq(df["name"].shift(fill_value=df["name"].iloc[0])))
m1 = df["full_path"].eq(df.loc[df["full_path"].str.rsplit("\", 2).str[-2] == 'T1', 'full_path'])
m2 = df["full_path"].eq(df.loc[df["full_path"].str.rsplit("\", 2).str[-2] == 'T2', 'full_path'])
m3 = df["full_path"].eq(df.loc[df["full_path"].str.rsplit("\", 2).str[-2] == 'T3', 'full_path'])
m4 = df["full_path"].eq(df.loc[df["full_path"].str.rsplit("\", 2).str[-2] == 'T4', 'full_path'])

df['T1'] = np.where(m0 & m1, df['created'], "")
df['T2'] = np.where(m0 & m2, df['created'], "")
df['T3'] = np.where(m0 & m3, df['created'], "")
df['T4'] = np.where(m0 & m4, df['created'], "")

df = df.groupby(['name'], sort=False).agg({'full_path':'last','created':'first', 'modified':'last','T1':'first', 'T2':lambda x: ' '.join(set(x)), 'T3':lambda x: ' '.join(set(x)), 'T4':'last'}).reset_index()

And it gives me a dataframe like this one:

   full_path             name   created    modified      T1       T2        T3          T4
0  C:\T1\T2\T3\T4\1.txt  1.txt  14:04:30   14:20:30  14:04:30   14:10:30  14:15:30   14:20:30  
1  C:\T1\T2\T3\T4\2.txt  2.txt  14:25:30   14:40:30  14:25:30   14:30:30  14:35:30      NaN

How can I modify my code to detect for example for the file 2.txt when it moved to folder T4, if the "created" column in NaN, get the timestamp from the "modified" column, and have a dataframe like this one:

   full_path             name   created    modified      T1       T2        T3          T4
0  C:\T1\T2\T3\T4\1.txt  1.txt  14:04:30   14:20:30  14:04:30   14:10:30  14:15:30   14:20:30  
1  C:\T1\T2\T3\T4\2.txt  2.txt  14:25:30   14:40:30  14:25:30   14:30:30  14:35:30   14:40:30

user_15497888 · Answer

So to answer the specific question. Use fillna and pass in the values from 'modified' before the groupby which will fill the NaN values in T4 with values from modified:

df['T4'] = df['T4'].fillna(df['modified'])

As an alternative maybe use something like pathlib to get the name of the parent folder.

Imports

from pathlib import PurePath

import numpy as np
import pandas as pd

Sample Frame

df = pd.DataFrame({
    'full_path': {0: 'C:\T1\1.txt', 1: 'C:\T1\1.txt',
                  2: 'C:\T1\T2\1.txt', 3: 'C:\T1\T2\1.txt',
                  4: 'C:\T1\T2\T3\1.txt',
                  5: 'C:\T1\T2\T3\1.txt',
                  6: 'C:\T1\T2\T3\T4\1.txt',
                  7: 'C:\T1\T2\T3\T4\1.txt',
                  8: 'C:\T1\2.txt', 9: 'C:\T1\2.txt',
                  10: 'C:\T1\T2\2.txt',
                  11: 'C:\T1\T2\2.txt',
                  12: 'C:\T1\T2\T3\2.txt',
                  13: 'C:\T1\T2\T3\2.txt',
                  14: 'C:\T1\T2\T3\T4\2.txt'},
    'name': {0: '1.txt', 1: '1.txt', 2: '1.txt', 3: '1.txt',
             4: '1.txt', 5: '1.txt', 6: '1.txt', 7: '1.txt',
             8: '2.txt', 9: '2.txt', 10: '2.txt', 11: '2.txt',
             12: '2.txt', 13: '2.txt', 14: '2.txt'},
    'created': {0: '14:04:30', 1: np.nan, 2: '14:10:30', 3: np.nan,
                4: '14:15:30', 5: np.nan, 6: '14:20:30', 7: np.nan,
                8: '14:25:30', 9: np.nan, 10: '14:30:30', 11: np.nan,
                12: '14:35:30', 13: np.nan, 14: np.nan},
    'modified': {0: np.nan, 1: '14:04:30', 2: np.nan, 3: '14:10:30',
                 4: np.nan, 5: '14:15:30', 6: np.nan, 7: '14:20:30',
                 8: np.nan, 9: '14:25:30', 10: np.nan, 11: '14:30:30',
                 12: np.nan, 13: '14:35:30', 14: '14:40:30'}
})

Get Folder Names

# Get Parent Folder Name From Each Path
df['folder'] = df['full_path'].apply(lambda x: PurePath(x).parent.name)
print(df.to_string())

               full_path   name   created  modified folder
0            C:\T1\1.txt  1.txt  14:04:30       NaN     T1
1            C:\T1\1.txt  1.txt       NaN  14:04:30     T1
2         C:\T1\T2\1.txt  1.txt  14:10:30       NaN     T2
3         C:\T1\T2\1.txt  1.txt       NaN  14:10:30     T2
4      C:\T1\T2\T3\1.txt  1.txt  14:15:30       NaN     T3
5      C:\T1\T2\T3\1.txt  1.txt       NaN  14:15:30     T3
6   C:\T1\T2\T3\T4\1.txt  1.txt  14:20:30       NaN     T4
7   C:\T1\T2\T3\T4\1.txt  1.txt       NaN  14:20:30     T4
8            C:\T1\2.txt  2.txt  14:25:30       NaN     T1
9            C:\T1\2.txt  2.txt       NaN  14:25:30     T1
10        C:\T1\T2\2.txt  2.txt  14:30:30       NaN     T2
11        C:\T1\T2\2.txt  2.txt       NaN  14:30:30     T2
12     C:\T1\T2\T3\2.txt  2.txt  14:35:30       NaN     T3
13     C:\T1\T2\T3\2.txt  2.txt       NaN  14:35:30     T3
14  C:\T1\T2\T3\T4\2.txt  2.txt       NaN  14:40:30     T4

Then the end result can be more easily accomplished:

# Get Parent Folder Name From Each Path
df['folder'] = df['full_path'].apply(lambda x: PurePath(x).parent.name)
# Keep Groupby Name handy for later
g = df.groupby('name')
# Transform each group to be the last path
df['full_path'] = g['full_path'].transform('last')
# combine_first created with modified (new column to not affect data)
df['c_m'] = df['created'].combine_first(df['modified'])

index_cols = ['full_path', 'name']
# Pivot to wide format
df = df.pivot_table(index=index_cols,
                    columns='folder',
                    values='c_m',
                    aggfunc='first')

# Add Summary Columns
summary_cols = ['created', 'modified']
# Merge on name
df = df.reset_index() \
    .merge(g[summary_cols].agg({'created': 'first', 'modified': 'last'}),
           on='name')

# Re-order Columns and axis names
df = df[[*index_cols,
         *summary_cols,
         *df.columns.difference(summary_cols + index_cols)]] \
    .rename_axis(None, axis=1)

# Output
print(df.to_string(index=False))

df:

           full_path  name  created modified       T1       T2       T3       T4
C:\T1\T2\T3\T4\1.txt 1.txt 14:04:30 14:20:30 14:04:30 14:10:30 14:15:30 14:20:30
C:\T1\T2\T3\T4\2.txt 2.txt 14:25:30 14:40:30 14:25:30 14:30:30 14:35:30 14:40:30

Getting timestamp from one column and put it in another one with pandas

1 Answers

Imports

Sample Frame

Get Folder Names