Python - search spreadsheet for cell containing datetime string

myinnernerd 0
Viewed 63

I'm a Python newbie and this is my first SO post. I'm trying to use python to extract a datestamp from a cell in a spreadsheet. I tried the following:

    df = pd.read_excel(fileName, sheet_name=0)
    df_columns = dict(zip(df.columns,range(len(df.columns))))
    df_start = df.rename(columns=df_columns)
    for i in range(0, len(df.columns)):
        for j in range(0, 4):
            if isinstance(df.iloc[i,j],str) and ':' in df.loc[i,j]:
                datestamp = datetime.datetime.strptime(df.iloc[i,j], '%d/%m/%Y %H:%M:%S')
                break

I'm getting an error message "Error at 0".

Dataframe looks something like this:

| 0 | 1 | 2 |...| 10 | 11 | 12 |

|---- | ----| --- |...|---- | ------------------------| --- |

| NaN | NaN | NaN |...| NaN | 2022-09-16 16:47:21.852 | NaN |

| NaN | NaN | NaN |...| NaN | 2022-09-16 16:47:21.852 | NaN |

| NaN | NaN | NaN |...| NaN | NaN | NaN |

| NaN | NaN | NaN |...| NaN | NaN | NaN |

| NaN |ClientName |Client Number |...|Core | Core Description | Status |

| NaN |AB09403880 |9403880|...|NaN | NaN | Active |

| NaN |AB09403881 |9403881|...|NaN | NaN | Active |

| NaN |AB09403882 |9403883|...|NaN | NaN | Active |

EDIT: I want to extract the datestamp in this spreadsheet to add as a column to a different dataframe which will eventually be written to CSV file. I should also add that the column where the date stamp is located is not necessarily going to be in column 11 (row 1 & 2) in the spreadsheet hence my attempt to loop through the cells. Hope that makes sense.

EDIT 2: Updated additional rows of dataframe

Expected Output:

| Datestamp|ClientName |Client Number |...|Core | Core Description | Status |

| 2022-09-16 |AB09403880 |9403880|...|NaN | NaN | Active |

| 2022-09-16 |AB09403881 |9403881|...|NaN | NaN | Active |

| 2022-09-16 |AB09403882 |9403883|...|NaN | NaN | Active |

datetime string pandas excel python
1 Answers

Considering that your Excel files has only timestamps values distributed in multiple rows/cols (see example/dataframe below) :

import pandas as pd

df = pd.read_excel("myinnernerd.xlsx")

print(df)

                         0    1    2    3                        4                        5    6    7    8    9                        10                       11                       12
0                       NaN  NaN  NaN  NaN                      NaN                      NaN  NaN  NaN  NaN  NaN                      NaN  2022-09-16 16:47:21.852                      NaN
1                       NaN  NaN  NaN  NaN                      NaN                      NaN  NaN  NaN  NaN  NaN                      NaN  2022-09-16 16:47:21.852                      NaN
2                       NaN  NaN  NaN  NaN                      NaN  2022-09-16 16:47:21.852  NaN  NaN  NaN  NaN                      NaN                      NaN                      NaN
3                       NaN  NaN  NaN  NaN                      NaN                      NaN  NaN  NaN  NaN  NaN                      NaN                      NaN                      NaN
4   2022-09-16 16:47:21.852  NaN  NaN  NaN                      NaN                      NaN  NaN  NaN  NaN  NaN  2022-09-16 16:47:21.852                      NaN                      NaN
5                       NaN  NaN  NaN  NaN                      NaN                      NaN  NaN  NaN  NaN  NaN                      NaN                      NaN                      NaN
6                       NaN  NaN  NaN  NaN                      NaN                      NaN  NaN  NaN  NaN  NaN                      NaN                      NaN                      NaN
7                       NaN  NaN  NaN  NaN  2022-09-16 16:47:21.852  2022-09-16 16:47:21.852  NaN  NaN  NaN  NaN                      NaN                      NaN                      NaN
8                       NaN  NaN  NaN  NaN  2022-09-16 16:47:21.852                      NaN  NaN  NaN  NaN  NaN                      NaN                      NaN                      NaN
9                       NaN  NaN  NaN  NaN                      NaN                      NaN  NaN  NaN  NaN  NaN                      NaN                      NaN                      NaN
10                      NaN  NaN  NaN  NaN                      NaN                      NaN  NaN  NaN  NaN  NaN                      NaN                      NaN  2022-09-16 16:47:21.852

You can use pandas.DataFrame.stack to intersect all the columns then pandas.DataFrame.explode to explode the rows with eventually multiple timestamps :

f = lambda x: list(x) if len(x) > 1 else x
df['datestamp'] = df.stack().groupby(level=0).agg(f)
df = df.pop('datestamp').dropna().explode().to_frame()

After that, convert the column datestamp to a datetime object by using pandas.to_datetime then floor it by seconds 'S'.

df['datestamp'] = pd.to_datetime(df['datestamp']).dt.floor('S')

print(df)

             datestamp
0  2022-09-16 16:47:21
1  2022-09-16 16:47:21
2  2022-09-16 16:47:21
4  2022-09-16 16:47:21
4  2022-09-16 16:47:21
7  2022-09-16 16:47:21
7  2022-09-16 16:47:21
8  2022-09-16 16:47:21
10 2022-09-16 16:47:21

print(df.dtypes)
datestamp    datetime64[ns]
dtype: object
abokey0
Related