Randomization of a list with conditions using Pandas

user_8916282 0
Viewed 69

I'm new to any kind of programming as you can tell by this 'beautiful' piece of hard coding. With sweat and tears (not so bad, just a little), I've created a very sequential code and that's actually my problem. My goal is to create a somewhat-automated script - probably including for-loop (I've unsuccessfully tried).

The main aim is to create a randomization loop which takes original dataset looking like this:

dataset

From this data set picking randomly row by row and saving it one by one to another excel list. The point is that the row from columns called position01 and position02 should be always selected so it does not match with the previous pick in either of those two column values. That should eventually create an excel sheet with randomized rows that are followed always by a row that does not include values from the previous pick. So row02 should not include any of those values in columns position01 and position02 of the row01, row3 should not contain values of the row2, etc. It should also iterate in the range of the list length, which is 0-11. Important is also the excel output since I need the rest of the columns, I just need to shuffle the order.

I hope my aim and description are clear enough, if not, happy to answer any questions. I would appreciate any hint or help, that helps me 'unstuck'. Thank you. Code below. (PS: I'm aware of the fact that there is probably much more neat solution to it than this) 

import pandas as pd
import random

dataset = pd.read_excel("C:\\Users\\ibm\\Documents\\Psychopy\\DataInput_Training01.xlsx")

# original data set use for comparisons
imageDataset = dataset.loc[0:11, :]
# creating empty df for storing rows from imageDataset
emptyExcel = pd.DataFrame()


randomPick = imageDataset.sample()          # select randomly one row from imageDataset
emptyExcel = emptyExcel.append(randomPick)  # append a row to empty df
randomPickIndex = randomPick.index.tolist() # get index of the row
imageDataset2 = imageDataset.drop(index=randomPickIndex) # delete the row with index selected before

# getting raw values from the row 'position01'/02 are columns headers
randomPickTemp1 = randomPick['position01'].values[0]
randomPickTemp2 = randomPick 
randomPickTemp2 = randomPickTemp2['position02'].values[0]

# getting a dataset which not including row values from position01 and position02
isit = imageDataset2[(imageDataset2.position01 != randomPickTemp1) & (imageDataset2.position02 != randomPickTemp1) & (imageDataset2.position01 != randomPickTemp2) & (imageDataset2.position02 != randomPickTemp2)]

# pick another row from dataset not including row selected at the beginning - randomPick
randomPick2 = isit.sample()

# save it in empty df
emptyExcel = emptyExcel.append(randomPick2, sort=False) 

# get index of this second row to delete it in next step
randomPick2Index = randomPick2.index.tolist() 
# delete the another row
imageDataset3 = imageDataset2.drop(index=randomPick2Index) 

# AND REPEAT the procedure of comparison of the raw values with dataset already not including the original row:

randomPickTemp1 = randomPick2['position01'].values[0]
randomPickTemp2 = randomPick2
randomPickTemp2 = randomPickTemp2['position02'].values[0]
isit2 = imageDataset3[(imageDataset3.position01 != randomPickTemp1) & (imageDataset3.position02 != randomPickTemp1) & (imageDataset3.position01 != randomPickTemp2) & (imageDataset3.position02 != randomPickTemp2)]

# AND REPEAT with another pick - save - matching - picking again.. until end of the length of the dataset (which is 0-11)
pandas python
2 Answers
import itertools as it
import random
import pandas as pd
# list of pair of numbers 
tmp1 = [x for x in it.permutations(list(range(6)),2)]

df = pd.DataFrame(tmp1, columns=["position01","position02"])
df1 = pd.DataFrame()
i = random.choice(df.index)
df1 = df1.append(df.loc[i],ignore_index = True)
df = df.drop(index = i)
while not df.empty:
    val = list(df1.iloc[-1])
    tmp = df[(df["position01"]!=val[0])&(df["position01"]!=val[1])&(df["position02"]!=val[0])&(df["position02"]!=val[1])]
    if tmp.empty: #looped for 10000 times, was never empty
        print("here")
        break
    i = random.choice(tmp.index)
    df1 = df1.append(df.loc[i],ignore_index = True)
    df = df.drop(index=i)
user_128295450

So at the end I've used a solution provided by David Bridges (post from Sep 19 2019) on psychopy websites. In case anyone is interested, here is a link: https://discourse.psychopy.org/t/how-do-i-make-selective-no-consecutive-trials/9186

I've just adjusted the condition in for loop to my case like this:

remaining = [choices[x] for x in choices if last['position01'] != choices[x]['position01'] and last['position01'] != choices[x]['position02'] and last['position02'] != choices[x]['position01'] and last['position02'] != choices[x]['position02']]

Thank you very much for the helpful answer! and hopefully I did not spam it over here too much.

user_89162820
Related