2

I am trying to populate column 'C' with values from column 'A' based on conditions in column 'B'. Example: If column 'B' equals 'nan', then row under column 'C' equals the row in column 'A'. If column 'B' does NOT equal 'nan', then leave column 'C' as is (ie 'nan'). Next, the values in column 'A' to be removed (only the values that were copied from column A to C).

Original Dataset:

index   A   B    C
0       6   nan  nan
1       6   nan  nan
2       9   3    nan
3       9   3    nan
4       2   8    nan
5       2   8    nan
6       3   4    nan
7       3   nan  nan
8       4   nan  nan

Output:

index   A   B    C
0       nan nan  6
1       nan nan  6
2       9   3    nan
3       9   3    nan
4       2   8    nan
5       2   8    nan
6       3   4    nan
7       nan nan  3
8       nan nan  4

Below is what I have tried so far, but its not working. 

def impute_unit(cols):
    Legal_Block = cols[0]
    Legal_Lot = cols[1]
    Legal_Unit = cols[2]

    if pd.isnull(Legal_Lot):
       return 3
    else:
       return Legal_Unit

bk_Final_tax['Legal_Unit'] = bk_Final_tax[['Legal_Block', 'Legal_Lot', 
                          'Legal_Unit']].apply(impute_unit, axis = 1)
mark
  • 95
  • 11
  • What do you mean by remove values from `A`? Set it to `nan` ? – harvpan Jul 24 '18 at 19:02
  • @HarvIpan sorry for the confusion. I want to replace the current values with 'nan'. But only the values that where pasted in column 'C'. I have updated the Output shown above. – mark Jul 24 '18 at 19:09

1 Answers1

4

Seems like you need

df['C'] = np.where(df.B.isna(), df.A, df.C)
df['A'] = np.where(df.B.isna(), np.nan, df.A)

A different, maybe fancy way to do it would be to swap A and C values only when B is np.nan

m = df.B.isna()
df.loc[m, ['A', 'C']] = df.loc[m, ['C', 'A']].values

In other words, change

bk_Final_tax['Legal_Unit'] = bk_Final_tax[['Legal_Block', 'Legal_Lot', 
                      'Legal_Unit']].apply(impute_unit, axis = 1)

for

bk_Final_tax['Legal_Unit'] = np.where(df.Legal_Lot.isna(), df.Legal_Block, df.Legal_Unit)
bk_Final_tax['Legal_Block'] = np.where(df.Legal_Lot.isna(), np.nan, df.Legal_Block)
rafaelc
  • 57,686
  • 15
  • 58
  • 82
  • Thanks @RafaelC and Harvlpan for the quick response. I am getting an error. ValueError: For argument "inplace" expected type bool, received type DataFrame – mark Jul 24 '18 at 19:22
  • Im not using the argument `inplace` anywhere.. but seems like you are and you're doing `inplace=df`, but you probably want `inplace=True` – rafaelc Jul 24 '18 at 19:23
  • In previous code, I have inplace = True, but not with the code you provided. The only changes I made was renaming the dataframe and columns. – mark Jul 24 '18 at 19:34
  • I have tried the second code, and I am getting the following error: Cannot index with multidimensional key – mark Jul 24 '18 at 19:34
  • @mark , I cant understand your problem. Just copy paste my code... `df` in my code is `bk_Final_tax` in yours. All the rest remains the same. There is no `inplace`, there is no `apply`, just one statement: `numpy.where` – rafaelc Jul 24 '18 at 19:35
  • that is exactly what I did. I do have `inplace` in previous code that runs without error (could that be the issue?). The only changes I made to your code is replacing df and column names with different names. – mark Jul 24 '18 at 19:40
  • @mark updated, tried to help you out ;) If the error is raised in other line of code that has not yet been posted, then this is another question :} Would be glad to help, just edit your code – rafaelc Jul 24 '18 at 19:45
  • 1
    I appreciate your efforts. There must be issues with my previous code that is causing it to error now. Ill have to play around a bit. Thanks again for your help. – mark Jul 24 '18 at 19:48
  • Its working now without an error. But I am getting a warning. SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead – mark Jul 24 '18 at 19:54
  • Nice! Probably you are getting a copy from a `df` at some point and changing this copy. Take a look: https://stackoverflow.com/questions/20625582/how-to-deal-with-settingwithcopywarning-in-pandas – rafaelc Jul 24 '18 at 19:55