CODEWITHSUNDEEP
pythonpython-2.7pandasdataframe
Prachi Verma
Prachi Verma

Reputation: 101

Updating existing dataframe columns

I have a data frame which has the structure as follows

code      value
1          red
2          blue 
3          yellow
1
4          
4          pink
2          blue

so basically i want to update the value column so that the blank rows are filled with values from other rows. So I know the code 4 refers to value pink, I want it to be updated in all the rows where that value is not present.

Upvotes: 0

Views: 60

Answers (5)

BENY
BENY

Reputation: 323396

Using reindex

df.dropna().drop_duplicates('code').set_index('code').reindex(df.code).reset_index()
Out[410]: 
   code   value
0     1     red
1     2    blue
2     3  yellow
3     1     red
4     4    pink
5     4    pink
6     2    blue

Upvotes: 2

Zero
Zero

Reputation: 77027

You could use first value of the given code group

In [379]: df.groupby('code')['value'].transform('first')
Out[379]:
0       red
1      blue
2    yellow
3       red
4      pink
5      pink
6      blue
Name: value, dtype: object

To assign back

In [380]: df.assign(value=df.groupby('code')['value'].transform('first'))
Out[380]:
   code   value
0     1     red
1     2    blue
2     3  yellow
3     1     red
4     4    pink
5     4    pink
6     2    blue

Or

df['value'] = df.groupby('code')['value'].transform('first')

Upvotes: 4

jpp
jpp

Reputation: 164843

You can sort_values, ffill and then sort_index. The last step may not be necessary if order is not important. If it is, then the double sort may be unreasonably expensive.

df = df.sort_values(['code', 'value']).ffill().sort_index()

print(df)

   code   value
0     1     red
1     2    blue
2     3  yellow
3     1     red
4     4    pink
5     4    pink
6     2    blue

Upvotes: 2

user3483203
user3483203

Reputation: 51185

Using groupby and ffill and bfill

df.groupby('code').value.ffill().bfill()


0       red
1      blue
2    yellow
3       red
4      pink
5      pink
6      blue
Name: value, dtype: object

Upvotes: 4

sacuL
sacuL

Reputation: 51425

You can create a series of your code-value pairs, and use that to map:

my_map = df[df['value'].notnull()].set_index('code')['value'].drop_duplicates()

df['value'] = df['code'].map(my_map)

>>> df
   code   value
0     1     red
1     2    blue
2     3  yellow
3     1     red
4     4    pink
5     4    pink
6     2    blue

Just to see what is happening, you are passing the following series to map:

>>> my_map
code
1       red
2      blue
3    yellow
4      pink
Name: value, dtype: object

So it says: "Where you find 1, give the value red, where you find 2, give blue..."

Upvotes: 3

Related Questions