I have 2 dataframes, the [Trade] column in first dataframe has 3 NaN values, I need to fill them with a referenc dataframe, which you might see below this the first df. You can use various methods to populate NaN value. Join, merge, concat, replace, whatever that works easily. thank you
Trade ID Toy_company 0 D255/FE 192 Duplo 1 L217/SN 255 Duplo 2 NaN 195 Lego 3 NaN 256 Duplo 4 D255/FE 192 Duplo 5 D255/FE 192 Duplo 6 D250/AT 192 Duplo 7 D250/AT 192 Duplo 8 L195/AE 195 Lego 9 NaN 195 Lego 10 L256/PE 256 Lego
reference dataframe:
Trade_ID Item Company 0 D255/FE 192 Duplo 1 D217/SN 255 Duplo 2 L12A/BA 195 Lego 3 L999/CC 256 Lego
Requirement: 1. Only fill NaN value according to the info in reference dataframe, on TWO keys: Item+Company. Leave it NaN if can't be referenced. 2. Do not correct any exsiting info, only impact on NaN values
So the final result should be:
Trade ID Toy_company 0 D255/FE 192 Duplo 1 L217/SN 255 Duplo 2 L12A/BA 195 Lego 3 NaN 256 Duplo 4 D255/FE 192 Duplo 5 D255/FE 192 Duplo 6 D250/AT 192 Duplo 7 D250/AT 192 Duplo 8 L195/AE 195 Lego 9 L12A/BA 195 Lego 10 L256/PE 256 Lego
score:2
Accepted answer
you can use merge()
method:
In [347]: d1['Trade'] = (d1['Trade'].fillna(
d1.merge(d2.rename(columns={'Item':'ID','Company':'Toy_company'}),
how='left')['Trade_ID']))
In [348]: d1
Out[348]:
Trade ID Toy_company
0 D255/FE 192 Duplo
1 L217/SN 255 Duplo
2 L12A/BA 195 Lego
3 NaN 256 Duplo
4 D255/FE 192 Duplo
5 D255/FE 192 Duplo
6 D250/AT 192 Duplo
7 D250/AT 192 Duplo
8 L195/AE 195 Lego
9 L12A/BA 195 Lego
10 L256/PE 256 Lego
Credit To: stackoverflow.com
Related Query
- pandas reference NaN value with multiple keys
- Python Pandas replace NaN in one column with value from corresponding row of second column
- Pandas missing values : fill with the closest non NaN value
- Pandas DataFrame.groupby() to dictionary with multiple columns for value
- Replace multiple substrings in a Pandas series with a value
- Pandas Melt with Multiple Value Vars
- Melting pandas data frame with multiple variable names and multiple value names
- Replace a string value with NaN in pandas data frame - Python
- Pandas replace all items in a row with NaN if one value is NaN
- Reducing pandas series with multiple nan values to a set gives multiple nan values
- Replace values in pandas column with default value for missing keys
- convert pandas dataframe to dictionary with multiple keys
- Replace NAN with Dictionary Value for a column in Pandas using Replace() or fillna() in Python
- pandas filling nan with previous row value multiplied with another column
- Map dataframe with multiple columns as keys pandas
- Drop rows with multiple keys in pandas
- Python Pandas replace NaN in one column with value from another column of the same row it has be as list column
- Multiple column groupby with pandas to find maximum value for each group
- How to fill NaN with user defined value in pandas dataframe
- Working with NaN values in multiple columns in Pandas
- Pandas fill nan values with a split of the first available value
- pandas Dataframe Replace NaN values with with previous value based on a key column
- How to summarize a pandas DataFrame by group with value counts of multiple columns?
- Initialize a Pandas DataFrame with a fill value other than NaN
- Pandas Multiindex Rows and Columns: Replace NaN with Value from Matching Row
- Pandas replace nan with mean value for a given grouping
- Pandas melt dataframe with multiple value columns
- How does pandas replace NaN values with mean value using groupby
- convert pandas dataframe of multiple columns with NaN to a nested dictionary
- python pandas fill NaN or blanket with max value
More Query from same tag
- How can I remove features/columns of a data frame if at least 20% of their entries take a specific value, using pandas/numpy?
- Python equivalence of R's match() for indexing
- Python DataFrame sum values in columnA based on conditions in columnsN
- How to un-categoricalize a column in pandas
- Python - Exception: Data must be 1-dimensional when running pd.Series
- How to efficiently disaggregate data from?
- how to string compare two columns in pandas dataframe?
- How Do I Create New Pandas Column Based On Word In A List
- How to manipulate data in arrays using pandas
- Jointplot multiindex columns with seaborn
- Data Manipulation on dummy variables python
- Use pandas to unstack a pivot table and convert to a timestamp format
- Pandas value_counts Into New Columns
- Transforming a Date Column with Pandas
- Does group by on entire dataframe followed by filter column take more memory compared to selecting the columns first?