+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: Change row values by other row values from same df (/thread-34721.html)
Change row values by other row values from same df - JosepMaria - Aug-25-2021
I have the following dataset:
df = pd.DataFrame( {'user': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 2, 6: 2},
'date': {0: '1995-09-01', 1: '1995-09-02', 2: '1995-10-03', 3: '1995-10-04', 4: '1995-10-05', 5: '1995-11-07', 6: '1995-11-08'},
'x': {0: '1995-09-02', 1: '1995-09-02', 2: '1995-09-02', 3: '1995-10-05', 4: '1995-10-05', 5: '1995-10-05', 6: '1995-10-05'},
'y': {0: '1995-10-03', 1: '1995-10-03', 2: '1995-10-03', 3: '1995-11-08', 4: '1995-11-08', 5: '1995-11-08', 6: '1995-11-08'},
'c1': {0: '1', 1: '0', 2: '0', 3: '2', 4: '0', 5: '9', 6: '0'},
'c2': {0: '1', 1: '0', 2: '0', 3: '2', 4: '0', 5: '9', 6: '0'},
'c3': {0: '1', 1: '0', 2: '0', 3: '2', 4: '0', 5: '9', 6: '0'},
'VTX1': {0: 1, 1: 0, 2: 0, 3: 1, 4: 0, 5: 0, 6: 0},
'VTY1': {0: 0, 1: 1, 2: 0, 3: 0, 4: 0, 5: 1, 6: 0}} )which gives me:user date x y c1 c2 c3 VTX1 VTY1 0 1 1995-09-01 1995-09-02 1995-10-03 1 1 1 1 0 1 1 1995-09-02 1995-09-02 1995-10-03 0 0 0 0 1 2 1 1995-10-03 1995-09-02 1995-10-03 0 0 0 0 0 3 2 1995-10-04 1995-10-05 1995-11-08 2 2 2 1 0 4 2 1995-10-05 1995-10-05 1995-11-08 0 0 0 0 0 5 2 1995-11-07 1995-10-05 1995-11-08 9 9 9 0 1 6 2 1995-11-08 1995-10-05 1995-11-08 0 0 0 0 0I want to replaces df[‘c1’] as follows.
- When df[‘date’]=df[‘x’],
change df[‘c1’] for the df[‘c1’] value when df[‘VTX1’]=1
In this example, for user 1, when df[‘date’]=df[‘x’] it happens to be on index 1. Here we want df['c1'] to be 1. Note that 1 is the value that user 1 has on df['c1'] when df['VTX1'] = 1.
So the end result would be:
user date x y c1 c2 c3 VTX1 VTY1 0 1 1995-09-01 1995-09-02 1995-10-03 1 1 1 1 0 1 1 1995-09-02 1995-09-02 1995-10-03 1 0 0 0 1 2 1 1995-10-03 1995-09-02 1995-10-03 1 0 0 0 0 3 2 1995-10-04 1995-10-05 1995-11-08 2 2 2 1 0 4 2 1995-10-05 1995-10-05 1995-11-08 2 0 0 0 0 5 2 1995-11-07 1995-10-05 1995-11-08 9 9 9 0 1 6 2 1995-11-08 1995-10-05 1995-11-08 0 0 0 0 0
RE: Change row values by other row values from same df - Larz60+ - Aug-25-2021
what code have you tried so far?
RE: Change row values by other row values from same df - eddywinch82 - Aug-26-2021
Hi JosepMaria,
Here is a method, to help you get a solution, I havn't got it quite right yet, but one way, is that you can use Numpy select with conditions.
I hope this helps you.
Could someone clean up the following attempt of mine, to give JosepMaria, the answer he needs ? If that is okay ? Why are the last two Rows values of the DataFrame, not changing to 9 and 0 respectively in the 'c1' Column, when the Code is run ?
import pandas as pd
import numpy as np
df = pd.DataFrame( {'user': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 2, 6: 2},
'date': {0: '1995-09-01', 1: '1995-09-02', 2: '1995-10-03', 3: '1995-10-04', 4: '1995-10-05', 5: '1995-11-07', 6: '1995-11-08'},
'x': {0: '1995-09-02', 1: '1995-09-02', 2: '1995-09-02', 3: '1995-10-05', 4: '1995-10-05', 5: '1995-10-05', 6: '1995-10-05'},
'y': {0: '1995-10-03', 1: '1995-10-03', 2: '1995-10-03', 3: '1995-11-08', 4: '1995-11-08', 5: '1995-11-08', 6: '1995-11-08'},
'c1': {0: '1', 1: '0', 2: '0', 3: '2', 4: '0', 5: '9', 6: '0'},
'c2': {0: '1', 1: '0', 2: '0', 3: '2', 4: '0', 5: '9', 6: '0'},
'c3': {0: '1', 1: '0', 2: '0', 3: '2', 4: '0', 5: '9', 6: '0'},
'VTX1': {0: 1, 1: 0, 2: 0, 3: 1, 4: 0, 5: 0, 6: 0},
'VTY1': {0: 0, 1: 1, 2: 0, 3: 0, 4: 0, 5: 1, 6: 0}} )
df['date']= pd.to_datetime(df['date']).dt.strftime('%d-%b-%Y')
df['x']= pd.to_datetime(df['x']).dt.strftime('%d-%b-%Y')
df['y']= pd.to_datetime(df['y']).dt.strftime('%d-%b-%Y')
df = df.astype(str)
col = 'c1'
conditions = [ df['date'].str.contains('Sep|Oct') == df['x'].str.contains('Sep'), df['date'].str.contains('Oct') == df['y'].str.contains('Nov'), df['date'].str.contains('07'), df['date'].str.contains('08')]
#conditions = [ df['date'].eq(df['x']), (df['date'].eq(df['x']) & (df['user'].str.contains('2')))]
choices = ['1', '2', '9', '0']
df['c1'] = np.select(conditions, choices)
dfBest Regards Eddie Winch
RE: Change row values by other row values from same df - eddywinch82 - Aug-28-2021
Hi there,
If I replace '==' with '&' in these two lines of my Code,
The desired DataFrame Output is now achieved :-
df['date'].str.contains('Sep|Oct') == df['x'].str.contains('Sep'), df['date'].str.contains('Oct') == df['y'].str.contains('Nov'),Best RegardsEddie Winch