Issue
I'd like to create a mock dataframe that has a random number of rows per user in Pandas. I attempted it in a for loop (below), but it doesn't work. Here's how I attempted to set it up and what I'd like my ideal dataframe to be.
Let's say I have a much smaller dataframe that looks like this. The key columns are userid
and the seq
.
If a user had 3 instances of baseline
value, it should have 3 rows in the dataframe. The user_id should be the same in each row
df = pd.DataFrame({
'user_id':
{0: 'user122',
1: 'user122',
2: 'user122',
3: 'user124',
4: 'user125',
5: 'user125',
6: 'user126',
7: 'user126',
8: 'user126'},
'baseline':
{0: 4.0,
1: 4.0,
2: 4.0,
3: 2,
4: 4,
5: 4,
6: 5,
7: 5,
8: 5},
'seq':
{0: 1,
1: 2,
2: 3,
3: 1,
4: 1,
5: 2,
6: 1,
7: 2,
8: 3},
})
In the mock dataset, some users have 3 instances, some have less.
I tried to create just the user and seq columns in a for loop below in a very small dataframe, but the user_id doesn't always match correctly with the number of rows it should have. I imagine there's a much better way of creating this dataframe. I'm open to any approaches
import random
lis = []
randomrange = [2,3]
user_experience_column = []
usernumber = []
i=0
for i in range (2):
print ('this is i, ', i)
usernumber_id = 'user'+str(i)
print ('userid', str(i) )
i = i + 1
for user_rows in range (random.randint(1, 5)):
print ('userid selected in random loop', usernumber_id )
print ('user rows random', user_rows)
user_experience_column.append(user_rows)
print ('user experience column', user_experience_column)
usernumber.extend([usernumber_id for i in range(user_rows)])
print ('user number', usernumber_id)
df = pd.DataFrame(list(zip(user_experience_column, usernumber)),
columns =['experiencenumber', 'userid'])
df
Now, in my ideal dataframe, I'll have 1000 users, each will have between 3-8 rows, and 60% will have 6 rows or more. But, for now, I'd be happy with code that could correctly create 100 users with two columns: the user_id and the correct sequence number (so if a user has 2 instances, the user_id would appear in 2 rows and they would be labled "1" and "2" respectively).
Thanks and please let me know if I can make this question any more clear.
Solution
Is this what you're looking for?
import numpy as np
import pandas as pd
# Define the number of User IDs to generate
num_users = 4
# First condition occurs with a probability of 40%
min_rows_per_user_cound1 = 3
max_rows_per_user_cound1 = 6
# Second condition occurs with a probability of 60%
min_rows_per_user_cound2 = 6
max_rows_per_user_cound2 = 8
# Create the user IDs
user_ids = list(map(lambda value: f"user{value + 1}", range(num_users)))
# Create the rows for each user, where each user can appear between 1 and 4 times.
user_rows = [
user_id
for user in user_ids
for user_id in [user]
* (
np.random.randint(min_rows_per_user_cound2, max_rows_per_user_cound2)
if np.random.random() >= 0.4
else np.random.randint(
min_rows_per_user_cound1, max_rows_per_user_cound1
)
)
]
# Create the mock dataframe
mock_df = (
pd.DataFrame(
zip(user_rows, [0] * len(user_rows)),
columns=["userid", "experiencenumber"],
)
.assign(
experiencenumber=lambda xdf: xdf.groupby("userid")[
"experiencenumber"
].cumcount()
+ 1
)
.assign(
baseline=lambda xdf: xdf.groupby("userid")[
"experiencenumber"
].transform("count")
)
)
print(mock_df)
# Prints:
#
# userid experiencenumber baseline
# 0 user1 1 3
# 1 user1 2 3
# 2 user1 3 3
# 3 user2 1 6
# 4 user2 2 6
# 5 user2 3 6
# 6 user2 4 6
# 7 user2 5 6
# 8 user2 6 6
# 9 user3 1 3
# 10 user3 2 3
# 11 user3 3 3
# 12 user4 1 7
# 13 user4 2 7
# 14 user4 3 7
# 15 user4 4 7
# 16 user4 5 7
# 17 user4 6 7
# 18 user4 7 7
Edit
Answering tom's questions:
Question 1: What [user] * number
means?
[user] * number
enables you to create lists. The user
inside the code is one of the user IDs ("user1"
, "user2"
, etc.). So [user] * number
is just a fancy way of repeating a given User ID $n$ times. There are other ways you can use to generate the same end result:
# -- Using For/Loop --------------------
l = []
for _ in range(some_number):
l.append(some_value)
# Or (not recommended):
l = []
for _ in range(some_number):
l = [*l, some_value]
# Example:
l = []
for _ in range(4):
l.append('user1')
print(l)
# Prints:
#
# ['user1', 'user1', 'user1', 'user1']
l = []
for _ in range(4):
l = [*l, 'user1']
print(l)
# Prints:
#
# ['user1', 'user1', 'user1', 'user1']
# -- Using List Comprehension ----------
[some_value for _ in range(some_number)]
# Or:
[some_value for i in range(some_number)]
# Example:
l = ['user1' for _ in range(4)]
print(l)
# Prints:
#
# ['user1', 'user1', 'user1', 'user1']
# -- Using `[user] * number` syntax ---------------
l = ['user1'] * 4
print(l)
# Prints:
#
# ['user1', 'user1', 'user1', 'user1']
# -- Using `map` (not recommended - Hard to read) ---------------
#
l = list(map(lambda _: 'user1', range(4)))
print(l)
print(l)
# Prints:
#
# ['user1', 'user1', 'user1', 'user1']
Question 2: Why didn't I indent the for/loop
?
Python, offers a shorter syntax when you want to create a new list based on the values of an existing list, called list comprehensions. To build the user_rows
list, we're actually iterating through the list user_ids
(which was created beforehand and contains the IDs for each user). Then we take each user ID and create another list repeating them a random number of times. Admittedly this way of writing code is confusing and hard to read. Here's the same code, but using for/loop
instead:
user_rows = []
# For each user ID we've created ('user1', 'user2', ...)
for user in user_ids:
# np.random.random() returns a number between 0 and 1 following
# a normal distribution. Threfore, we can expect that about
# 60% of the time, the number being generated will be greater
# than 0.4. This means that the first condition will be
# satisfied about 60% of the time, and the second will be
# satisfied about 40% of the time.
# This means that 60% of the users IDs will be repeated 6 to 8 times.
if np.random.random() >= 0.4: # Same as `if random.random() >= 0.4:...`
user_repeats = np.random.randint(6, 9)
# This means that 40% of the users IDs will be repeated 3 to 5 times.
else:
user_repeats = np.random.randint(3, 6)
# Now that we have randomly generated the number of times
# that the user ID will be repeated, we can add that user ID
# user_repeats times to our list.
for _ in range(user_repeats):
user_rows.append(user)
# [OPTIONAL] The for/loop above is equivalent to the following:
# user_rows.extend(user for _ in range(user_repeats))
print(user_rows)
# Prints:
#
# ['user1', 'user1', 'user1', 'user1', 'user1', 'user1', 'user1', 'user2', 'user2', ...]
Question 3: .transform("count")
transform
lets you broadcast a groupby
operation across all values from the same group. This allows you to return something with the same index as what you started with. Here's an example:
Without .transform
:
mock_df.groupby('userid', as index=False)['experiencenumber'].count()
# Prints:
#
# userid experiencenumber
# 0 user1 3
# 1 user2 3
# 2 user3 6
With .transform
:
mock_df.groupby('userid')['experiencenumber'].transform('count')
# Prints:
# userid experiencenumber
# 0 user1 3
# 1 user1 3
# 2 user1 3
# 3 user2 3
# 4 user2 3
# 5 user2 3
# 6 user3 6
# 7 user3 6
# 8 user3 6
# 9 user3 6
# 10 user3 6
# 11 user3 6
Answered By - Ingwersen_erik
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.