Pandas: Conditionally append data to column?

user_2283234 0
Viewed 196

I have two data frames that I need to merge.

In n_df there are several columns, but the ones that are relevant are:

Statement     Codes
Statement 1   CD.Ex-1
Statement 2   CD.Ex-2
Statement 3   CD.Ex-3
Statement 4   CD.Ex-4
Statement 5   CD.Ex-5

{
    "Statement": ["Statement 1", "Statement 2", "Statement 3",
                  "Statement 4", "Statement 5",],
    "Codes": ["CD.Ex-1", "CD.Ex-2", "CD.Ex-3","CD.Ex-4","CD.Ex-5",],
    ...
}

In p_df I have 3 columns:

Statement     Codes     Other_Codes
Statement A   CD.Ex-A   NC.a
Statement X   CD.Ex-2   NC.b
Statement B   CD.Ex-B   NC.c
Statement 4   CD.Ex-4   NC.d
Statement C   CD.Ex-C   NC.e

{
    "Statement": ["Statement A", "Statement X", "Statement B",
                  "Statement 4", "Statement C",],
    "Codes": ["CD.Ex-A", "CD.Ex-2", "CD.Ex-B","CD.Ex-4","CD.Ex-C",],
    "Other_Codes": ["NC.a", "NC.b", "NC.c","NC.d","NC.e",],    
}

So what I need to do is:

  1. Merge the data frames on Codes
  2. If Codes doesn't exist in n_df (such as CD.Ex-A), then append Codes (p_df) to Codes (n_df)
  3. If Codes doesn't exist in n_df (such as CD.Ex-A), then append the associated Statement(p_df) to Statement (n_df)

So far I've done new_df = pd.merge(n_df, p_df, how='outer', on='Codes'). However, there are a few problems with this: I end up with two Statement columns. To mitigate this, I did

s_df = p_df[['Other_Codes', 'Statement']]
p_df = p_df.drop('Statement', 1)

My thinking is that I can do another merge, on Other_Codes to add the Statement back in? But I haven't been able to successfully do that. I always end up with two Statement columns.

The other issue (probably related) is that the "Statements" from each table don't necessarily align. In my example above, the Statement for Code CD.Ex-2 is Statement 2 in n_df and Statement X in p_df. If there's a conflict in the Statements, I need n_df to take precedence.

pandas dataframe python
1 Answers

Use combine_first to update values from Statement (p_df) to Statement (n_df). Values will be update only if n_df values are NaN.

out = n_df.merge(p_df, on='Codes', how='outer', suffixes=('', '_'))
out = out.assign(Statement=out['Statement'].combine_first(out['Statement_'])) \
         .drop(columns='Statement_')

Output:

>>> out
  Statement    Codes Other_Codes
0       1.0  CD.Ex-1         NaN
1       2.0  CD.Ex-2        NC.b
2       3.0  CD.Ex-3         NaN
3       4.0  CD.Ex-4        NC.d
4       5.0  CD.Ex-5         NaN
5         A  CD.Ex-A        NC.a
6         B  CD.Ex-B        NC.c
7         C  CD.Ex-C        NC.e
user_152399510
Related