Hi and happy new year :)
I have a database in SQLite3 where ID is Primary Key, and if a new row with the same ID number is added, it replaces the row with new data. But the program that generates the CSV-file for this put in ID 0 if a Target is empty. The problem is if there are several rows with ID 0, there will only be one in the database because it replaces ID's if there are several. And that makes a problem in my app when I query for data from database because I have to have both targets, 1 and 2 from a relay with the same number. What I want to do is every ID with 0 has to be converted to a random integer number between 1000 and 2000, and that random number can't be generated twice.
I have tried df.loc[df['ID'] == 0,'ID'] = df['ID'].apply(lambda x: np.random.normal(0,1))
df, but this only makes a random float and I can't use that.
How do I solve this? Thanks in advance for your answers:)
LeonID Relay Target ... Klasse Fyll ID
0 2 1 1 ... 3 0 210
1 2 1 2 ... NaN 0 0
2 2 2 1 ... V55 0 208
3 2 2 2 ... 2 0 211
4 2 3 1 ... V55 0 209
5 2 3 2 ... KIK 0 226
6 2 4 1 ... 4 0 218
7 2 4 2 ... 4 0 212
8 2 5 1 ... NaN 0 0
9 2 5 2 ... 2 0 220
10 2 6 1 ... 2 0 213
11 2 6 2 ... 2 0 225
12 2 7 1 ... EJ 0 219
13 2 7 2 ... 2 0 224
14 2 8 1 ... 2 0 221
15 2 8 2 ... 1 0 206
16 2 9 1 ... NaN 0 0
17 2 9 2 ... 4 0 216
18 2 10 1 ... 2 0 214
19 2 10 2 ... R 0 236
20 2 11 1 ... 4 0 215
21 2 11 2 ... 4 0 217
22 2 12 1 ... 4 0 207
23 2 12 2 ... EJ 0 205
24 2 13 1 ... 4 0 222
25 2 13 2 ... 2 0 223
CodePudding user response：
Calling the sample method on a Series with frac=1 shuffles it. Then we're returning the first N values in that Series where N is the number of 0s in the ID column.
mask = df['ID'] == 0
df.loc[mask,'ID'] = pd.Series(range(1000,2000)).sample(frac=1).head(mask.sum()).values
CodePudding user response：
Is there a way to make this easier like if the ID is 0, relay is 2 and target is 1 it would convert det ID to 21 or something like that? That would solve my problem :)
df.loc[df['ID'] == 0, 'ID'] = \
df[['Relay', 'Target']].astype(str).apply(''.join, axis=1).astype(int)
print(df)
# Output
LeonID Relay Target ... Klasse Fyll ID
0 2 1 1 ... 3 0 210
1 2 1 2 ... NaN 0 12 # HERE
2 2 2 1 ... V55 0 208
3 2 2 2 ... 2 0 211
4 2 3 1 ... V55 0 209
5 2 3 2 ... KIK 0 226
6 2 4 1 ... 4 0 218
7 2 4 2 ... 4 0 212
8 2 5 1 ... NaN 0 51 # HERE
9 2 5 2 ... 2 0 220
10 2 6 1 ... 2 0 213
11 2 6 2 ... 2 0 225
12 2 7 1 ... EJ 0 219
13 2 7 2 ... 2 0 224
14 2 8 1 ... 2 0 221
15 2 8 2 ... 1 0 206
16 2 9 1 ... NaN 0 91 # HERE
17 2 9 2 ... 4 0 216
18 2 10 1 ... 2 0 214
19 2 10 2 ... R 0 236
20 2 11 1 ... 4 0 215
21 2 11 2 ... 4 0 217
22 2 12 1 ... 4 0 207
23 2 12 2 ... EJ 0 205
24 2 13 1 ... 4 0 222
25 2 13 2 ... 2 0 223