Shlomo Rothschild Shlomo Rothschild - 2 months ago 6
Python Question

Populating DataFrame from tuple with different size

I have data that is spread over the day.
I clustered it and then i calculated the ratio (weight) of each cluster per hour (not all the clusters exists in all hours).
(dataframe time_df )

cluster Date
0 1 2014-02-28 14:24:59.535000+02:00
1 1 2014-02-28 14:26:35.019000+02:00
2 1 2014-02-28 14:27:37.213000+02:00
3 2 2014-02-28 14:28:35.246000+02:00
4 2 2014-02-28 14:29:37.283000+02:00


I group by hour and use np bincount to calculate the weight of each cluster:

group_by_hour = time_df.groupby(time_df.Date.dt.hour)
cluster_ids_hour = group_by_hour.cluster.\
apply(lambda arr: list(range(0,(arr+1).max()+1)))
cluster_ratio_hour = group_by_hour.cluster.\
apply(lambda arr: 1.0*np.bincount(arr+1)/len(arr))


This gives per hour a different array size of clusters and their weight
It tried to construct a dataframe

pd.DataFrame(temp, columns=['hour','clusters','ratios'])

But I got the following:

hour clusters weights
0 14 [0] [1.0]
1 15 [0, 1] [0.488888888889, 0.511111111111]
2 16 [0, 1, 2] [0.302325581395, 0.162790697674, 0.53488372093]
3 17 [0, 1, 2] [0.0, 0.0, 1.0]
4 18 [0, 1, 2] [0.0, 0.0, 1.0]
5 19 [0, 1, 2] [0.0, 0.0, 1.0]
6 20 [0, 1, 2] [0.0, 0.0, 1.0]
7 21 [0, 1, 2] [0.0, 0.0, 1.0]
8 22 [0, 1, 2] [0.0, 0.0, 1.0]
9 23 [0, 1, 2] [0.0, 0.0, 1.0]


How can I make it to have the cluster as index and hours as columns?

0 1 2 3 4 ...
0 0.2 0.6 0.4 0.0 0.6
1 0.0 0.4 0.1 0.0 0.4
2 0.8 0.0 0.5 1.0 0.0

Answer

I think you can use:

import pandas as pd
import numpy as np

time_df = pd.DataFrame({'cluster': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 1, 6: 1, 7: 2}, 
                        'Date': {0: pd.Timestamp('2014-02-28 12:24:59.535000'),
                                 1: pd.Timestamp('2014-02-28 12:26:35.019000'), 
                                 2: pd.Timestamp('2014-02-28 12:27:37.213000'), 
                                 3: pd.Timestamp('2014-02-28 12:28:35.246000'), 
                                 4: pd.Timestamp('2014-02-28 12:29:37.283000'), 
                                 5: pd.Timestamp('2014-02-28 13:27:37.213000'), 
                                 6: pd.Timestamp('2014-02-28 14:28:35.246000'), 
                                 7: pd.Timestamp('2014-02-28 14:29:37.283000')}})

print (time_df)
                     Date  cluster
0 2014-02-28 12:24:59.535        1
1 2014-02-28 12:26:35.019        1
2 2014-02-28 12:27:37.213        1
3 2014-02-28 12:28:35.246        2
4 2014-02-28 12:29:37.283        2
5 2014-02-28 13:27:37.213        1
6 2014-02-28 14:28:35.246        1
7 2014-02-28 14:29:37.283        2
group_by_hour = time_df.groupby(time_df.Date.dt.hour)
cluster_ids_hour = group_by_hour.cluster.\
    apply(lambda arr: list(range(0,(arr+1).max()+1)))
cluster_ratio_hour = group_by_hour.cluster.\
    apply(lambda arr: 1.0*np.bincount(arr+1)/len(arr))

print (cluster_ids_hour)
Date
12    [0, 1, 2, 3]
13       [0, 1, 2]
14    [0, 1, 2, 3]
Name: cluster, dtype: object

print (cluster_ratio_hour)
Date
12    [0.0, 0.0, 0.6, 0.4]
13         [0.0, 0.0, 1.0]
14    [0.0, 0.0, 0.5, 0.5]
Name: cluster, dtype: object

#create DataFrames from both columns and concate them
df1 = pd.DataFrame(cluster_ids_hour.values.tolist(), index=cluster_ids_hour.index)
#print (df1)

df2 = pd.DataFrame(cluster_ratio_hour.values.tolist(), index=cluster_ratio_hour.index)
#print (df2)
df = pd.concat([df1, df2], axis=1, keys=('clusters','weights'))
print (df)
     clusters            weights               
            0  1  2    3       0    1    2    3
Date                                           
12          0  1  2  3.0     0.0  0.0  0.6  0.4
13          0  1  2  NaN     0.0  0.0  1.0  NaN
14          0  1  2  3.0     0.0  0.0  0.5  0.5
#reshape, cast clusters column to integer    
df = df.stack().reset_index(level=1, drop=True).reset_index()
df['clusters'] = df['clusters'].astype(int)
#pivoting, fill NaN by 0
df = df.pivot(index='clusters', columns='Date', values='weights').fillna(0)

df.index.name = None
df.columns.name = None
print (df)
    12   13   14
0  0.0  0.0  0.0
1  0.0  0.0  0.0
2  0.6  1.0  0.5
3  0.4  0.0  0.5
Comments