Set value into pandas DataFrame cell

user_1821692 0
Viewed 66

I have two DataFrames:

source_df (source_df.shape == (1008, 27797)):

|id   |field_1|  pubs  | users |...|user_1    |user_2    |user_3    |...|user_27769    |
|-----|-------|--------|-------|...|----------|----------|----------|...|--------------|
| 1   |-------|[7, 10] |[1,2,3]|...| x_1_1    | x_2_1    | x_3_1    |...| x_27769_1    |
| 2   |-------|[13, 15]|[2,10] |...| x_1_2    | x_2_2    | x_3_2    |...| x_27769_2    |
|..   |.......|........|[1,2,9]|...|..........|..........|..........|...|..............|
| 1008|-------|[1,2,13]|[7,8,9]|...| x_1_1008 | x_2_1008 | x_3_1008 |...| x_27769_1008 |

user_pub_df (user_pub_df.shape = (21, 27769)):

|id| user_1 | user_2 | user_3 |...| user_27769 |
|--|--------|--------|--------|...|------------|
| 1|   10   |   0    |   7    |...|     0      |
| 2|   0    |   0    |   0    |...|     1      |
| 3|   0    |   8    |   4    |...|     0      |
|..|   .    |   .    |   .    |...|     .      |
| 7|   13   |   1    |   6    |...|     0      |
|10|   1    |   1    |   0    |...|     0      |
|13|   1    |   1    |   0    |...|     0      |
|15|   1    |   1    |   0    |...|     19     |

Id here is an ids from pubs column from source_df.

The task is to fill source_df with values from user_pub_df: 

source_df.loc[1, 'user_1'] = user_pub_df.loc[7, 'user_1'] + user_pub_df.loc[10, 'user_1'] # 11
source_df.loc[1, 'user_2'] = user_pub_df.loc[7, 'user_2'] + user_pub_df.loc[10, 'user_2'] # 2
source_df.loc[1, 'user_3'] = user_pub_df.loc[7, 'user_3'] + user_pub_df.loc[10, 'user_3'] # 6
source_df.loc[2, 'user_2'] = user_pub_df.loc[13, 'user_2'] + user_pub_df.loc[15, 'user_2'] # 2
source_df.loc[2, 'user_10'] = user_pub_df.loc[13, 'user_10'] + user_pub_df.loc[15, 'user_10'] # 0
# and so on

I did it with the loop:

for index, row in source_df.iterrows():
    for user_id in row['users']:
        source_df.loc[index, 'user_{}'.format(user_id)] = user_pub_df.loc[row['pubs'], user_id].sum()

Naive code work too slow for 27769 users and 21 pubs (~16 minutes).

I tried change .loc to .at same result.

PS: source_df can change so I can't just save all user/pubs combinations into dictionary/hashmap with key user+pubs and precomputed value.

pandas dataframe algorithm python
3 Answers

If you use df.iloc(index) instead of df.loc(index) it should be faster

user_125232340

I've adjusted the code to use a numpy array in the loop and then set this into the dataframe once at the end. This skips a lot of the index checking etc that occurs in pandas dataframes. I think both my method one and two will be faster but method two should perform better with the large number of users

import numpy as np
n_users = user_pub_df.shape[1]
n_rows = source_df.shape[0]
arr = np.zeros((n_rows, n_users))
for index, row in source_df.iterrows():
    for user_id in row['users']:
        arr[index, user_id] = user_pub_df.iloc[row['pubs'], user_id].sum()

source_df.loc[:, 'user_1': 'user_' + str(n_users)] = arr

Here's my test code:

import pandas as pd
import numpy as np
import numpy.ma as ma
import timeit

source_df = pd.DataFrame({
    'pubs': [[1,2],[0,2],[1,0]],
    'users': [[1,2],[0,2],[1,0]],
    'user_1': [1,2,3],
    'user_2': [1,2,3],
    'user_3': [3,2,1]
    })

user_pub_df = pd.DataFrame({
    'user_1': [1,2,3],
    'user_2': [1,2,3],
    'user_3': [3,2,1]
    })
n_users = user_pub_df.shape[1]
n_rows = source_df.shape[0]

def one() :
    global source_df
    arr = []
    for index, row in source_df.iterrows():
        mx = np.ones((len(row['pubs']), n_users))
        mx[:,row['users']] = 0
        arr.append(ma.masked_array(user_pub_df.iloc[row['pubs'],:].values,mask = mx).sum())

    source_df.loc[:, 'user_1': 'user_' + str(n_users)] = arr
    source_df = source_df.fillna(0).copy()

def two() :
    arr = np.zeros((n_rows, n_users))
    for index, row in source_df.iterrows():
        for user_id in row['users']:
            arr[index, user_id] = user_pub_df.iloc[row['pubs'], user_id].sum()

    source_df.loc[:, 'user_1': 'user_' + str(n_users)] = arr

def old() :
    for index, row in source_df.iterrows():
        for user_id in row['users']:
            source_df.loc[index, 'user_{}'.format(user_id)] = user_pub_df.iloc[row['pubs'], user_id].sum()

print(timeit.timeit(old, number =1000))
print(timeit.timeit(one, number =1000))
print(timeit.timeit(two, number =1000))

Results are:

5.25ms

3.83ms

3.65ms

user_111553820

Your data structure prevents any vectorization, you cannot expect full speed operations :-(.

The best you can try is to directly use the underlying numpy arrays to avoid pandas to build a new Series per each row:

for i, index in enumerate(source_df.index):
    for user_id in df['users'].values[i]:
        source_df.loc[index, 'user_{}'.format(user_id)] = user_pub_df.loc[df['pubs'].values[i],
                                                                          user_id].sum()

But I should not expect too much from it...

user_35452730
Related