I have a dataframe with different values in a column (about 6,000 rows), which I need to replace with similar (but differents) values found in another dataframe, which has fewer rows.
Store | Values to replace |
---|---|
Store A 05/15/21 | Store A |
The Store B 04/01/21 | Store B |
Store letter B 11/12/21 | Store C |
Store C 10/24/21 | Store D |
Store D 09/30/21 | |
the Store C 05/13/21 | |
Store letter D 07/01/21 | |
Store letter A 08/29/21 |
The goal is for the final dataframe to look like this:
Store |
---|
Store A |
Store B |
Store B |
Store C |
Store D |
Store C |
Store D |
Store A |
Advertisement
Answer
Given df
and df2
:
JavaScript
x
16
16
1
Store
2
0 Store A 05/15/21
3
1 The Store B 04/01/21
4
2 Store letter B 11/12/21
5
3 Store C 10/24/21
6
4 Store D 09/30/21
7
5 the Store C 05/13/21
8
6 Store letter D 07/01/21
9
7 Store letter A 08/29/21
10
11
replace_values
12
0 Store A
13
1 Store B
14
2 Store C
15
3 Store D
16
Doing:
JavaScript
1
5
1
df.Store = df.Store.str.split()
2
df2.replace_values = df2.replace_values.str.split()
3
df.Store = df.Store.apply(lambda row: ' '.join(x for x in row if any(x in i for i in df2.replace_values)))
4
print(df)
5
Output:
JavaScript
1
10
10
1
Store
2
0 Store A
3
1 Store B
4
2 Store B
5
3 Store C
6
4 Store D
7
5 Store C
8
6 Store D
9
7 Store A
10