CODEWITHSUNDEEP
pythonpandasdataframe
Tartaglia
Tartaglia

Reputation: 1041

Randomly selecting rows from dataframe column by date

For a given dataframe column, I would like to randomly select by day roughly 60% and add to a new column, add the remaining 40% to another column, multiply the 40% column by (-1), and create a new column that merges these back together for each day (so that each day I have a ratio of 60/40):

I have asked the same question without the daily specification here: Randomly selecting rows from dataframe column

Example below illustrates this (although my ratio is not exactly 60/40 there):

dict0 = {'date':[1/1/2019,1/1/2019,1/1/2019,1/2/2019,1/1/2019,1/2/2019],'x1': [1,2,3,4,5,6]}
df = pd.DataFrame(dict0)### 
df['date']      = pd.to_datetime(df['date']).dt.date 

dict1 = {'date':[1/1/2019,1/1/2019,1/1/2019,1/2/2019,1/1/2019,1/2/2019],'x1': [1,2,3,4,5,6],'x2': [1,'nan',3,'nan',5,6],'x3': ['nan',2,'nan',4,'nan','nan']}
df = pd.DataFrame(dict1)### 
df['date']      = pd.to_datetime(df['date']).dt.date 

dict2 = {'date':[1/1/2019,1/1/2019,1/1/2019,1/2/2019,1/1/2019,1/2/2019],'x1': [1,2,3,4,5,6],'x2': [1,'nan',3,'nan',5,6],'x3': ['nan',-2,'nan',-4,'nan','nan']}
df = pd.DataFrame(dict2)### 
df['date']      = pd.to_datetime(df['date']).dt.date 

dict3 = {'date':[1/1/2019,1/1/2019,1/1/2019,1/2/2019,1/1/2019,1/2/2019],'x1': [1,2,3,4,5,6],'x2': [1,'nan',3,'nan',5,6],'x3': ['nan',-2,'nan',-   4,'nan','nan'],'x4': [1,-2,3,-4,5,6]}
df = pd.DataFrame(dict3)### 
df['date']      = pd.to_datetime(df['date']).dt.date

Upvotes: 0

Views: 70

Answers (1)

Ben.T
Ben.T

Reputation: 29635

you can use groupby and sample, get the index values, then create the column x4 with loc, and fillna with the -1 multiplied column like:

idx= df.groupby('date').apply(lambda x: x.sample(frac=0.6)).index.get_level_values(1)
df.loc[idx, 'x4'] = df.loc[idx, 'x1']
df['x4'] = df['x4'].fillna(-df['x1'])

Upvotes: 2

Related Questions