Mapping values in a pandas dataframe column to a sequence of numbers

user_1845408 0
Viewed 3100

I have a dataframe like this (please discard the first column):

    user_id created_at  count
1   12136   2017-02-19  4
2   12136   2017-02-16  4
3   12136   2017-02-17  2
4   72349   2017-02-17  8
5   72349   2017-02-19  2
7   72672   2017-02-20  3
8   72672   2017-02-19  2

So, I want to map this values to integer values starting from 0:

12136 -> 0
72349 -> 1 
72672 -> 2

And similarly, for the created_at column (starting from the smallest value)

2017-02-16 -> 0
2017-02-17 -> 1
2017-02-19 -> 2
2017-02-20 -> 3

At the end I should have this dataframe (note that 0 values are added for dates where there is no user activity):

user_id created_at  count
0       0           4
0       1           2
0       2           4
0       3           0
1       0           0
1       1           8
1       2           2
1       3           0
2       0           0
2       1           0
2       2           2
2       3           3

Also I need to obtain these lists:

label1 = [12136, 72349, 72672]
label2 = ['2017-02-16', '2017-02-17', '2017-02-19', '2017-02-20']

I wonder if there are any methods that could assist me in performing this efficiently?

pandas dataframe python
4 Answers

First, get your lists.

list1 = df.user_id.unique()
print(list1)
array([12136, 72349, 72672])

list2 = df.created_at.unique()
print(list2)
array(['2017-02-19', '2017-02-16', '2017-02-17', '2017-02-20'], dtype=object)

Convert the user_id and created_at columns to cat codes.

df['user_id'] = df['user_id'].astype('category').cat.codes
df['created_at'] = df['created_at'].astype('category').cat.codes

print(df)
   user_id  created_at  count
1        0           2      4
2        0           0      4
3        0           1      2
4        1           1      8
5        1           2      2
7        2           3      3
8        2           2      2

Use a groupby and a reindex operation.

df = df.set_index('created_at').groupby('user_id', as_index=False)\
       .apply(lambda x: x.reindex(df.created_at.unique()))\
       .sort_index().reset_index([1])

Clean up your columns.

df.user_id = df.groupby(level=0).user_id.transform(lambda x: x.ffill().bfill())
df['count'] = df['count'].fillna(0)

print(df.astype(int))

   created_at  user_id  count
0           0        0      4
0           1        0      2
0           2        0      4
0           3        0      0
1           0        1      0
1           1        1      8
1           2        1      2
1           3        1      0
2           0        2      0
2           1        2      0
2           2        2      2
2           3        2      3
user_49090870

You can convert the columns to categories and get a mapping dictionary

df['user_id']= df['user_id'].astype('category')
label1 = dict(enumerate(df['user_id'].cat.categories))
df['created_at']= df['created_at'].astype('category')
label2 = dict(enumerate(df['created_at'].cat.categories))

Now convert the columns values to category codes

df[['user_id', 'created_at']] = df[['user_id', 'created_at']].apply(lambda x: x.cat.codes)

You get

    user_id created_at  count
1   0       2           4
2   0       0           4
3   0       1           2
4   1       1           8
5   1       2           2
7   2       3           3
8   2       2           2

label1

{0: 12136, 1: 72349, 2: 72672}

label2

{0: '2017-02-16', 1: '2017-02-17', 2: '2017-02-19', 3: '2017-02-20'}
user_62873080

Here is my answer that I have ended up:

dfx_users = pd.DataFrame({'user_id': df['user_id'].unique(), 'u_id': range(0, len(df['user_id'].unique()))})
dfx_users['key'] = 1

dfx_dates = pd.DataFrame({'created_at': df['created_at'].unique(), 'd_id': range(0, len(df['created_at'].unique()))})
dfx_dates['key'] = 1

dfxx = pd.merge(dfx_users, dfx_dates, on='key').drop('key', 1)
dfxx.sort_values(['user_id', 'created_at'], ascending=[True, True])

dfxx.merge(dfx[['user_id', 'created_at', 'count']], 
           on=['user_id', 'created_at'], how='left').fillna(0)[['u_id', 'd_id', 'count']]


u_id    d_id        count
0       0           4
0       1           2
0       2           4
0       3           0
1       0           0
1       1           8
1       2           2
1       3           0
2       0           0
2       1           0
2       2           2
2       3           3
user_18454080

My solution converts the unique values in each column into a np.array and uses np.argwhere to get the sorted index before placing it back into the DataFrame. You can put each conversion into a one-liner as follows:

# Just creating your DataFrame
df = pd.DataFrame({'user_id': [12136, 12136, 12136, 72349, 72349, 
                                    72672, 72672],
                        'created_at': ['2017-02-19', '2017-02-16',
                                       '2017-02-17', '2017-02-17',
                                       '2017-02-19', '2017-02-20',
                                       '2017-02-19'],
                        'count': [4, 4, 2, 8, 2, 3, 2]})
label1 = np.sort(np.array(df.user_id.unique()))
label2 = pd.to_datetime(np.sort(np.array(df.created_at.unique())))

df['user_id'] = df.apply(lambda x: np.argwhere(label1 == x.user_id)[0][0], 
                         axis=1)
df['created_at'] = df.apply(lambda x: np.argwhere(label2 == x.created_at)[0][0], axis=1)
user_12699500
Related