Python Forum
Change row values by other row values from same df
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Change row values by other row values from same df
#1
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      0 
I 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
Reply
#2
what code have you tried so far?
Reply
#3
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) 
 
df
Best Regards

Eddie Winch
Reply
#4
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 Regards

Eddie Winch
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Assigning conditional values in Pandas Scott 3 725 Dec-19-2023, 03:10 AM
Last Post: Larz60+
  attempt to split values from within a dataframe column mbrown009 8 2,217 Apr-10-2023, 02:06 AM
Last Post: mbrown009
  Increase df column values decimals SriRajesh 2 1,084 Nov-14-2022, 05:20 PM
Last Post: deanhystad
  replace sets of values in an array without using loops paul18fr 7 1,629 Jun-20-2022, 08:15 PM
Last Post: paul18fr
  Changing Values in a List DaveG 1 1,248 Apr-04-2022, 03:38 PM
Last Post: jefsummers
Question How does one clean a populated table in MySQL/MariaDB? Copying values across tables? BrandonKastning 2 1,539 Jan-17-2022, 05:46 AM
Last Post: BrandonKastning
  Matplotlib scatter plot in loop with None values ivan_sc 1 2,234 Nov-04-2021, 11:25 PM
Last Post: jefsummers
  pandas: Compute the % of the unique values in a column JaneTan 1 1,756 Oct-25-2021, 07:55 PM
Last Post: jefsummers
  Write a dictionary with arrays as values into JSON format paul18fr 3 5,508 Oct-20-2021, 10:38 AM
Last Post: buran
  Remove specific values from dataframe jonah88888 0 1,687 Sep-24-2021, 05:09 AM
Last Post: jonah88888

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020