I am sequentially merging some data frames by a key column, but I found that the columns aren't being renamed as expected with the suffix argument. I'm using Python 3.5 and Panadas 0.19.2.
import pandas as pd
df0 = pd.DataFrame({'serial_num': ['01', '02', '03'], 'vals': ['A2', 'A3', 'A6']})
df1 = pd.DataFrame({'serial_num': ['01', '02', '03'], 'vals': ['B2', 'B3', 'B6']})
df2 = pd.DataFrame({'serial_num': ['01', '02', '03'], 'vals': ['C2', 'C3', 'C6']})
df_01 = pd.merge(df0, df1, on="serial_num", suffixes=("_df0","_df1"), copy=True)
print(df_01.columns)
df_012 = pd.merge(df_01, df2, on="serial_num", suffixes=("_foobar","_df2"), copy=True)
print(df_012.columns)
The result is:
Index(['serial_num', 'vals_df0', 'vals_df1'], dtype='object')
Index(['serial_num', 'vals_df0', 'vals_df1', 'vals'], dtype='object')
This is odd because the Pandas documentation on merge doesn't suggest, that this should be the result. The expected result of print(df_012.columns) is Index(['serial_num', 'vals_df0_foobar', 'vals_df1_foobar', 'vals_df2'], dtype='object'). It does look like the data results are correct. I tried several things including copy = True, copy.deepcopy(merged_df), and copy.deepcopy(merged_df). Using join (df_01 = df0.join(df1, on="serial_num", lsuffix="_df0", rsuffix="_df1") gives similar results. And it gets stranger, sequential merges eventually do rename the columns:
df3 = pd.DataFrame({'serial_num': ['01', '02', '03'], 'vals': ['D2', 'D3', 'D6']})
df_0123 = pd.merge(df_012, df3, on="serial_num", suffixes=("","_df3"), copy=True)
print(df_0123.columns)
df_01230 = pd.merge(df_0123, df1, on="serial_num", suffixes=("_foo","_bar"), copy=True)
print(df_01230.columns)
Returns:
Index(['serial_num', 'vals_df0', 'vals_df1', 'vals', 'vals_df3'], dtype='object')
Index(['serial_num', 'vals_df0', 'vals_df1', 'vals_foo', 'vals_df3',
'vals_bar'],
dtype='object')
If you followed all of that are you as surprised as me that merge suddenly renamed vals to vals_foo? Is Pandas failing to rename columns by the suffix argument in every other call?