Adding correction column to dataframe

user_396803 0
Viewed 100

I have a pandas dataframe I read from a csv file with df = pd.read_csv("data.csv"):

date,location,value1,value2
2020-01-01,place1,1,2
2020-01-02,place2,5,8
2020-01-03,place2,2,9

I also have a dataframe with corrections df_corr = pd.read_csv("corrections .csv")

date,location,value
2020-01-02,place2,-1
2020-01-03,place2,2

How do I apply these corrections where date and location match to get the following?

date,location,value1,value2
2020-01-01,place1,1,2
2020-01-02,place2,4,8
2020-01-03,place2,4,9

EDIT: I got two good answers and decided to go with set_index(). Here is how I did it 'non-destructively'.

df = pd.read_csv("data.csv")
df_corr = pd.read_csv("corr.csv")

idx = ['date', 'location']

df_corrected = df.set_index(idx).add(
    df_corr.set_index(idx).rename(
        columns={"value": "value1"}), fill_value=0
    ).astype(int).reset_index()
pandas dataframe python
2 Answers

It looks like you want to join the two DataFrames on the date and location columns. After that its a simple matter of applying the correction by adding the value1 and value columns (and finally dropping the column containing the corrections).

# Join on the date and location columns.
df_corrected = pd.merge(df, df_corr, on=['date', 'location'], how='left')

# Apply the correction by adding the columns.
df_corrected.value1 = df_corrected.value1 + df_corrected.value

# Drop the correction column.
df_corrected.drop(columns='value', inplace=True)
user_62633170

Set date and location as index in both dataframes, add the two and fillna

df.set_index(['date','location'], inplace=True)
df1.set_index(['date','location'], inplace=True)
df['value1']=(df['value1']+df1['value']).fillna(df['value1'])
user_89869750
Related