CODEWITHSUNDEEP
pythonpandasdataframereplace
Monique Gosseling
Monique Gosseling

Reputation: 13

How do you replace certain values from row to row in python pandas based on data in other columns?

Right now I am working with python pandas and cannot find the solution to this issue: I have a dataframe df and I want to copy certain data from one row to another row (replacing) based on on a tag in another colomn of the same dataset. An example is the best way to show it I think:

 Kind  Type       V1      V2    V3 

 AAA      AAA     7       8     4 

 BBB      AAA    4       8     1 

 CCC      AAA    3       1     7 

 AAA      BBB    2       5     3

 BBB      BBB    11      9     8 

 CCC      BBB    7       7     10

 AAA      CCC    5       8     2 

 BBB      CCC    2       3     7 

 CCC      CCC    4       10    6

What I want is replacing kind AAA with kind BBB for each Type but only for row Value3(V3). So that the result will be:

 Kind     Type   V1     V2    V3 

 AAA      AAA    7       8     1 

 BBB      AAA    4       8     1 

 CCC      AAA    3       1     7 

 AAA      BBB    2       5     8

 BBB      BBB    11      9     8 

 CCC      BBB    7       7     10

 AAA      CCC    5       8     7 

 BBB      CCC    2       3     7 

 CCC      CCC    4       10    6

As you can see, these 3 number were replaced now, with values from kind BBB.

With 

df.loc[df['Kind']=='AAA','V3'] = x  # x is numeric

I can only replace each V3 number in combination with kind AAA with number x, but this is not what I want. As the numbers are all different. Can someone help me here? Thanks!

Upvotes: 1

Views: 77

Answers (2)

Ben
Ben

Reputation: 836

Here's an example using shift

df.loc[df['Kind'] == 'AAA', 'V3'] = df['V3'].shift(-1).fillna(0).astype(int)

    Kind    Type    V1  V2  V3
0    AAA     AAA    7   8   1
1    BBB     AAA    4   8   1
2    CCC     AAA    3   1   7
3    AAA     BBB    2   5   8
4    BBB     BBB    11  9   8
5    CCC     BBB    7   7   10
6    AAA     CCC    5   8   7
7    BBB     CCC    2   3   7
8    CCC     CCC    4   10  6

Note that this relies on your dataframe being sorted by kind and type, and that non-matches are given a default value of 0.

Upvotes: 0

jezrael
jezrael

Reputation: 862511

Filter DataFrame by boolean indexing and create Series for map only rows where Kind is AAA:

s = df.loc[df['Kind'] == 'BBB', ['Type', 'V3']].set_index('Type')['V3']
print (s)
Type
AAA    1
BBB    8
CCC    7
Name: V3, dtype: int64

df.loc[df['Kind']=='AAA','V3'] = df['Type'].map(s)
print (df)
  Kind Type  V1  V2  V3
0  AAA  AAA   7   8   1
1  BBB  AAA   4   8   1
2  CCC  AAA   3   1   7
3  AAA  BBB   2   5   8
4  BBB  BBB  11   9   8
5  CCC  BBB   7   7  10
6  AAA  CCC   5   8   7
7  BBB  CCC   2   3   7
8  CCC  CCC   4  10   6

Upvotes: 2

Related Questions