How to iterate over a DF[ 'column'] with mixed datatypes values?

by Deepesh Nair   Last Updated September 12, 2018 23:26 PM

I have imported an excel file and having a trouble with the pandas iteration. The data looks like below after the import.

Acct            Time        Typ     Name   
01/02/2018      NaN         NaN     NaN  
52              07:58:34    1       John  
53              07:58:35    1       Jack   
54              07:58:35    1       Ron  
55              07:58:35    1       Lux   
01/03/2018      NaN         NaN     NaN  
79              12:39:25    1       Mike    
01/06/2018      NaN         NaN     NaN         
371             12:15:47    1       Eric   
422             17:07:33    1       Shawn  
01/07/2018      NaN         NaN     NaN         
492             12:43:54    1       George

I want to iterate over the string of the first column ("Acct"), check if its a date or a number .

I created an additional column in a new data frame and tried to populate with the date field , however getting few Series errors.

Expecting an output like this :

Date_New        Acct        Time        Typ     Name 

01/02/2018      52          07:58:34    1       John  
01/02/2018      53          07:58:35    1       Jack   
01/02/2018      54          07:58:35    1       Ron  
01/02/2018      55          07:58:35    1       Lux   
01/03/2018      79          12:39:25    1       Mike    
01/06/2018      371         12:15:47    1       Eric   
01/06/2018      422         17:07:33    1       Shawn  
01/07/2018      492         12:43:54    1       George

I would appreciate if someone can send me some direction as I'm new to pandas foundations.



Answers 1


To find proper dates, you can convert the Acct column to datetime, and use the argument errors = 'coerce'. Then, forward fill that column with the proper dates, and get rid of columns in which the Time, Typ and Name columns are all NaN. Finally you can reorder your columns:

# Find proper dates, create new column:
df['Date_New'] = pd.to_datetime(df['Acct'], errors='coerce')
# Fill non-valid dates:
df['Date_New'].ffill(inplace=True)
# Get rid of `NaN` rows:
df = df[~df[['Time','Typ', 'Name']].isnull().all(1)]
# Reorder Columns
df = df[['Date_New', 'Acct', 'Time', 'Typ', 'Name']]

>>> df
     Date_New Acct      Time  Typ    Name
1  2018-01-02   52  07:58:34  1.0    John
2  2018-01-02   53  07:58:35  1.0    Jack
3  2018-01-02   54  07:58:35  1.0     Ron
4  2018-01-02   55  07:58:35  1.0     Lux
6  2018-01-03   79  12:39:25  1.0    Mike
8  2018-01-06  371  12:15:47  1.0    Eric
9  2018-01-06  422  17:07:33  1.0   Shawn
11 2018-01-07  492  12:43:54  1.0  George

Further explanation:

For easier understanding, here are the results after each step:

>>> df['Date_New'] = pd.to_datetime(df['Acct'], errors='coerce')
>>> df
          Acct      Time  Typ    Name   Date_New
0   01/02/2018       NaN  NaN     NaN 2018-01-02
1           52  07:58:34  1.0    John        NaT
2           53  07:58:35  1.0    Jack        NaT
3           54  07:58:35  1.0     Ron        NaT
4           55  07:58:35  1.0     Lux        NaT
5   01/03/2018       NaN  NaN     NaN 2018-01-03
6           79  12:39:25  1.0    Mike        NaT
7   01/06/2018       NaN  NaN     NaN 2018-01-06
8          371  12:15:47  1.0    Eric        NaT
9          422  17:07:33  1.0   Shawn        NaT
10  01/07/2018       NaN  NaN     NaN 2018-01-07
11         492  12:43:54  1.0  George        NaT

>>> df['Date_New'].ffill(inplace=True)
>>> df
          Acct      Time  Typ    Name   Date_New
0   01/02/2018       NaN  NaN     NaN 2018-01-02
1           52  07:58:34  1.0    John 2018-01-02
2           53  07:58:35  1.0    Jack 2018-01-02
3           54  07:58:35  1.0     Ron 2018-01-02
4           55  07:58:35  1.0     Lux 2018-01-02
5   01/03/2018       NaN  NaN     NaN 2018-01-03
6           79  12:39:25  1.0    Mike 2018-01-03
7   01/06/2018       NaN  NaN     NaN 2018-01-06
8          371  12:15:47  1.0    Eric 2018-01-06
9          422  17:07:33  1.0   Shawn 2018-01-06
10  01/07/2018       NaN  NaN     NaN 2018-01-07
11         492  12:43:54  1.0  George 2018-01-07

>>> df = df[~df[['Time','Typ', 'Name']].isnull().all(1)]
>>> df
   Acct      Time  Typ    Name   Date_New
1    52  07:58:34  1.0    John 2018-01-02
2    53  07:58:35  1.0    Jack 2018-01-02
3    54  07:58:35  1.0     Ron 2018-01-02
4    55  07:58:35  1.0     Lux 2018-01-02
6    79  12:39:25  1.0    Mike 2018-01-03
8   371  12:15:47  1.0    Eric 2018-01-06
9   422  17:07:33  1.0   Shawn 2018-01-06
11  492  12:43:54  1.0  George 2018-01-07
sacul
sacul
September 12, 2018 23:20 PM

Related Questions



pivot table and crostab sum

Updated March 18, 2017 12:26 PM