Renaming pandas columns by slicing, causing merge to fail

user_10964685 0
Viewed 80

I've got two data frames that represent similar data but I want to merge after changing the col names. There are a few ways to achieve this but given the size of my actual data frames, I'd like to use the following method. I'm returning nan values for the second df.

import pandas as pd

df1 = pd.DataFrame({
    'time': ['2012-08-02 09:50:20.0','2012-08-02 09:50:32.5','2012-08-02 09:50:34.8'],  
    'Val': ['1,2,3','1,2,3','1,2,3'],
    'Val2': [1,2,3],
    'Val3': [1.1,2.1,3.1]    
    })

df2 = pd.DataFrame({
    'time': ['2012-08-02 09:50:20.0','2012-08-02 09:50:32.5','2012-08-02 09:50:34.8'],
    'Val': ['1,2,3','1,2,3','1,2,3'],
    'Val2': [1,2,3],
    'Val3': [1.1,2.1,3.1]
    })

df1['time'] = pd.to_datetime(df1['time'])
df2['time'] = pd.to_datetime(df2['time'])

df1.columns.values[1:4] = ['first_' + str(x) for x in df1.columns[1:4]]
df2.columns.values[1:4] = ['second_' + str(x) for x in df2.columns[1:4]]

df3 = pd.merge(df1, df2, on = 'time')

print(df3)

                     time first_Val  first_Val2  first_Val3  second_Val  second_Val2  second_Val3
0 2012-08-02 09:50:20.000     1,2,3           1         1.1         NaN          NaN          NaN
1 2012-08-02 09:50:32.500     1,2,3           2         2.1         NaN          NaN          NaN
2 2012-08-02 09:50:34.800     1,2,3           3         3.1         NaN          NaN          NaN

Intended output:

                      time first_Val  first_Val2  first_Val3 second_Val  second_Val2  second_Val3
0  2012-08-02 09:50:20.000     1,2,3           1         1.1      1,2,3            1          1.1
1  2012-08-02 09:50:32.500     1,2,3           2         2.1      1,2,3            2          2.1
2  2012-08-02 09:50:34.800     1,2,3           3         3.1      1,2,3            3          3.1
pandas merge python
2 Answers
  • The issue is slice assignment of the column names.
    • df1.columns.values[1:4] = new values
    • Fails in pandas 1.1.1 and 1.1.2
    • Works in 1.0.1 and 1.0.5
  • 'time' is set as the index, then reset, after changing the column names in a list-comprehension.
    • This demonstrates, it's okay to rename the columns with a list comprehension, but not by slicing df.columns.
  • .reset_index() can be removed, to leave 'time' as the index, in which case, use df.join, instead of pd.merge.
  • The options are to set the column, which won't have a new name, as the index, or use .rename for the specific columns.
df1 = pd.DataFrame({
    'time': ['2012-08-02 09:50:20.0','2012-08-02 09:50:32.5','2012-08-02 09:50:34.8'],  
    'first_Val': ['1,2,3','1,2,3','1,2,3'],
    'first_Val2': [1,2,3],
    'first_Val3': [1.1,2.1,3.1]    
    })
df1['time'] = pd.to_datetime(df1['time'])
df1.set_index('time', inplace=True)
df1.columns = ['first_' + str(x) for x in df1.columns]
df1.reset_index(inplace=True)

df2 = pd.DataFrame({
    'time': ['2012-08-02 09:50:20.0','2012-08-02 09:50:32.5','2012-08-02 09:50:34.8'],
    'Val': ['1,2,3','1,2,3','1,2,3'],
    'Val2': [1,2,3],
    'Val3': [1.1,2.1,3.1]
    })
df2['time'] = pd.to_datetime(df2['time'])
df2.set_index('time', inplace=True)
df2.columns = ['second_' + str(x) for x in df2.columns]
df2.reset_index(inplace=True)

# merge
df3 = pd.merge(df1, df2, on = 'time', how='left')

                     time first_first_Val  first_first_Val2  first_first_Val3 second_Val  second_Val2  second_Val3
0 2012-08-02 09:50:20.000           1,2,3                 1               1.1      1,2,3            1          1.1
1 2012-08-02 09:50:32.500           1,2,3                 2               2.1      1,2,3            2          2.1
2 2012-08-02 09:50:34.800           1,2,3                 3               3.1      1,2,3            3          3.1
user_77588040

Okay, let's try this a different way:

df1 = df1.set_index('time').add_prefix('first_')
df2 = df2.set_index('time').add_prefix('second_')

df3 = pd.merge(df1, df2, on = 'time')

print(df3)
user_63615310
Related