Compare column from a dataframa and put it in another

user_14073111 0
Viewed 60

I have two data frames like this:

df1:

     col1    col2          time
0    A        A_1          05:02:03
1    A        A_2          15:36:14
2    A        A_1          28:21:47
3    A        A_1          47:21:17
4    A        A_1          52:28:01
5    A        A_2          72:27:14

I want to compare if the time in column "time" is <24h, >24 but <48, >48 but <72h and >72 and put these results to another dataframe like this:

df2:

     col1    col2          time <24       24<time<48       48<time<72    time>72
0    A        A_1              1              2               1             NaN
1    A        A_2              1              NaN             NaN            1

So, basically what I want in this df2 is to have the count of files that meet comparison, like for example there are two files in "time" column that belong to A and A_1 and the time is <24, and we just put 2 in the "time<24" folumns

EDIT: Thanks @Andreas and @Nk03, it worked great when I had all the scenarios there, but for example when I don't have time that is >72 it is failing because that 'time3':'time>72' it is not created and i get an error that says:

AttributeError: 'DataFrame' object has no attribute 'str'

How should i fix this one? When i have a dataframe and it doesnt have any value that is >72 or any doenst meet other comparisons. Lets say i have a dataframe like this.

df1:

     col1    col2          time
0    A        A_1          05:02:03
1    A        A_2          15:36:14
2    A        A_1          28:21:47
3    A        A_1          47:21:17
4    A        A_1          32:28:01
5    A        A_2          37:27:14

And still have this df2 desirable output:

   col1    col2          time <24       24<time<48       48<time<72   time>72
0    A        A_1              1              3             NaN        NaN
1    A        A_2              1              1             NaN        NaN

EDIT2: This is the error im getting after puting that extra line in your answer:

df[list(set(d.values()).difference(df.columns))] = np.nan

My dataframe Error I'm getting

pandas dataframe compare python-3.x python
2 Answers

Please modify the time-bin, if required:

df['time2'] = pd.cut(df.time.str.split(':').str[0].astype(int), bins=[-np.inf, 24, 48, 72, np.inf],
                     include_lowest=True, labels=['time <24', '24<time<48', '48<time<72', 'time>72'], right=False)
result = df.pivot_table(index=['col1', 'col2'],
                        columns='time2', aggfunc='count').reset_index()
user_154380330

You could split the time by :, then divide the hours by 24 and round it. After that you can pivotize the whole dataframe. After that rename the columns in the way you want.

df['day'] = (df['time'].str.split(':').str[0].astype(int)/24).astype(int)
df = df.pivot_table(index=['col1', 'col2'], columns=['day'], values=['time'], aggfunc='count').reset_index()

d = {'time0':'time <24', 'time1':'24<time<48', 'time2':'48<time<72', 'time3':'time>72'}
df.columns = [d.get(''.join(map(str, x)), ''.join(map(str, x))) for x in df.columns]

#   col1 col2  time <24  24<time<48  48<time<72  time>72
# 0    A  A_1       1.0         2.0         1.0      NaN
# 1    A  A_2       1.0         NaN         NaN      1.0

If you want to have a safety check afterwards to see if all columns are in there you can use this as last line:

for col in d.values():
    if col not in df.columns:
        df[col] = np.nan
user_119717850
Related