Merge one column from multiple dataframes to another dataframe based on multiple conditions in PythonPandas Merging 101Does Python have a ternary conditional operator?How to sort a dataframe by multiple column(s)Selecting multiple columns in a pandas dataframeAdding new column to existing DataFrame in Python pandasHow to change the order of DataFrame columns?Delete column from pandas DataFrameSelect rows from a DataFrame based on values in a column in pandasGet list from pandas DataFrame column headersCreating a pandas DataFrame from columns of other DataFrames with similar indexesPandas left join DataFrames by two columns
Validation and verification of mathematical models
Deadlock Priority High Chosen as deadlock victim
Can external light meter replace the need for push/pull?
ESTA declined to the US
Capacitors with a "/" on schematic
Where in ש״ס who one find the adage, “He who suggests the idea should carry it out”?
Getting an entry level IT position later in life
I was contacted by a private bank overseas to get my inheritance
Does the length of a password for Wi-Fi affect speed?
Making pause in a diagram
Did silent film actors actually say their lines or did they simply improvise “dialogue” while being filmed?
What does VB stand for?
What is the German idiom or expression for when someone is being hypocritical against their own teachings?
How to check a file was encrypted (really & correctly)
Can ads on a page read my password?
Is there a drawback to Flail Snail's Shell defense?
What are the examples (applications) of the MIPs in which the objective function has nonzero coefficients for only continuous variables?
Is DC heating faster than AC heating?
Why do proponents of guns oppose gun competency tests?
"How do you solve a problem like Maria?"
Is it a bad idea to offer variants of a final exam based on the type of allowed calculators?
How many years before enough atoms of your body are replaced to survive the sudden disappearance of the original body’s atoms?
Is this cheap "air conditioner" able to cool a room?
Why don't the open notes matter in guitar chords?
Merge one column from multiple dataframes to another dataframe based on multiple conditions in Python
Pandas Merging 101Does Python have a ternary conditional operator?How to sort a dataframe by multiple column(s)Selecting multiple columns in a pandas dataframeAdding new column to existing DataFrame in Python pandasHow to change the order of DataFrame columns?Delete column from pandas DataFrameSelect rows from a DataFrame based on values in a column in pandasGet list from pandas DataFrame column headersCreating a pandas DataFrame from columns of other DataFrames with similar indexesPandas left join DataFrames by two columns
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
1
Let's say I have a combined dataframe named df as follows. Each row has buildings' info and their matched buildings' info. I hope to merge id of each building from df1, df2 and df3 (see below). The columns of df_num or matched_df_num is there to distingue which dataframe the building info come from, if it's equals to 1, means it's from df1, 2 means from df2, 3 means from df3.
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio
0 Shenzhen Ping An Finance Centre 51
1 Guangzhou Guangzhou CTF Finance Centre 66
2 Shanghai Shanghai World Financial Center 59
3 Shanghai Shanghai World Financial Center 56
4 Changsha Changsha IFS Tower T1 57
I want to merge the column of ids from df1, df2 and df3 below for building names and matched names:
df1 = pd.DataFrame(np.array([
[1010667747, 'Suzhou', 'Suzhou IFS'],
[1010667356, 'Shenzhen', 'Kingkey 100'],
[1010667289, 'Wuhan', 'Wuhan Center']]),
columns=['id', 'city', 'name']
)
df2 = pd.DataFrame(np.array([
[190010, 'Shenzhen', 'Ping An Finance Centre'],
[190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
[190015, 'Beijing', 'China Zun']]),
columns=['id', 'city', 'name']
)
df3 = pd.DataFrame(np.array([
['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
['ZY-15', 'Hong Kong', 'International Commerce Centre'],
['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
columns=['id', 'city', 'name']
)
This is my expected result:
df_num city name id
0 1 Shenzhen Kingkey 100 1010667356
1 2 Shenzhen Ping An Finance Centre 190010
2 2 Shenzhen Ping An Finance Centre 190010
3 2 Guangzhou Guangzhou CTF Finance Centre 190012
4 3 Shanghai Shanghai World Financial Center ZY-13
matched_df_num matched_city matched_name
0 2 Shenzhen Ping An Finance Centre
1 2 Guangzhou Guangzhou CTF Finance Centre
2 3 Shanghai Shanghai World Financial Center
3 3 Shanghai Shanghai World Financial Center
4 3 Changsha Changsha IFS Tower T1
similarity_ratio matched_id
0 51 190010
1 66 190010
2 59 ZY-13
3 56 ZY-13
4 57 ZY-16
How could I insert two new columns id and matched_id and their values in df using Pandas? Thanks for helps at advance.
Update: my solution:
df = df.merge(df1, on = ['city', 'name'], how = 'left').merge(df2, on = ['city', 'name'], how = 'left').merge(df3, on = ['city', 'name'], how = 'left')
final_df = df.merge(df1, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left').merge(df2, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left').merge(df3, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left')
df_num city_x name_x matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id_x
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 NaN
2 Shanghai Shanghai World Financial Center 59 NaN
3 Shanghai Shanghai World Financial Center 56 NaN
4 Changsha Changsha IFS Tower T1 57 NaN
id_y id_x id_y city_y name_y id_x city_x
0 NaN NaN NaN NaN NaN 190010 Shenzhen
1 190010 NaN NaN NaN NaN 190012 Guangzhou
2 190010 NaN NaN NaN NaN NaN NaN
3 190012 NaN NaN NaN NaN NaN NaN
4 NaN ZY-13 NaN NaN NaN NaN NaN
name_x id_y city_y
0 Ping An Finance Centre NaN NaN
1 Guangzhou CTF Finance Centre NaN NaN
2 NaN ZY-13 Shanghai
3 NaN ZY-13 Shanghai
4 NaN ZY-16 Changsha
name_y
0 NaN
1 NaN
2 Shanghai World Financial Center
3 Shanghai World Financial Center
4 Changsha IFS Tower T1
python pandas dataframe
asked Mar 27 at 5:19
ahbonahbon
7888 silver badges17 bronze badges
|
show 3 more comments
1
Let's say I have a combined dataframe named df as follows. Each row has buildings' info and their matched buildings' info. I hope to merge id of each building from df1, df2 and df3 (see below). The columns of df_num or matched_df_num is there to distingue which dataframe the building info come from, if it's equals to 1, means it's from df1, 2 means from df2, 3 means from df3.
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio
0 Shenzhen Ping An Finance Centre 51
1 Guangzhou Guangzhou CTF Finance Centre 66
2 Shanghai Shanghai World Financial Center 59
3 Shanghai Shanghai World Financial Center 56
4 Changsha Changsha IFS Tower T1 57
I want to merge the column of ids from df1, df2 and df3 below for building names and matched names:
df1 = pd.DataFrame(np.array([
[1010667747, 'Suzhou', 'Suzhou IFS'],
[1010667356, 'Shenzhen', 'Kingkey 100'],
[1010667289, 'Wuhan', 'Wuhan Center']]),
columns=['id', 'city', 'name']
)
df2 = pd.DataFrame(np.array([
[190010, 'Shenzhen', 'Ping An Finance Centre'],
[190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
[190015, 'Beijing', 'China Zun']]),
columns=['id', 'city', 'name']
)
df3 = pd.DataFrame(np.array([
['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
['ZY-15', 'Hong Kong', 'International Commerce Centre'],
['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
columns=['id', 'city', 'name']
)
This is my expected result:
df_num city name id
0 1 Shenzhen Kingkey 100 1010667356
1 2 Shenzhen Ping An Finance Centre 190010
2 2 Shenzhen Ping An Finance Centre 190010
3 2 Guangzhou Guangzhou CTF Finance Centre 190012
4 3 Shanghai Shanghai World Financial Center ZY-13
matched_df_num matched_city matched_name
0 2 Shenzhen Ping An Finance Centre
1 2 Guangzhou Guangzhou CTF Finance Centre
2 3 Shanghai Shanghai World Financial Center
3 3 Shanghai Shanghai World Financial Center
4 3 Changsha Changsha IFS Tower T1
similarity_ratio matched_id
0 51 190010
1 66 190010
2 59 ZY-13
3 56 ZY-13
4 57 ZY-16
How could I insert two new columns id and matched_id and their values in df using Pandas? Thanks for helps at advance.
Update: my solution:
df = df.merge(df1, on = ['city', 'name'], how = 'left').merge(df2, on = ['city', 'name'], how = 'left').merge(df3, on = ['city', 'name'], how = 'left')
final_df = df.merge(df1, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left').merge(df2, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left').merge(df3, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left')
df_num city_x name_x matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id_x
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 NaN
2 Shanghai Shanghai World Financial Center 59 NaN
3 Shanghai Shanghai World Financial Center 56 NaN
4 Changsha Changsha IFS Tower T1 57 NaN
id_y id_x id_y city_y name_y id_x city_x
0 NaN NaN NaN NaN NaN 190010 Shenzhen
1 190010 NaN NaN NaN NaN 190012 Guangzhou
2 190010 NaN NaN NaN NaN NaN NaN
3 190012 NaN NaN NaN NaN NaN NaN
4 NaN ZY-13 NaN NaN NaN NaN NaN
name_x id_y city_y
0 Ping An Finance Centre NaN NaN
1 Guangzhou CTF Finance Centre NaN NaN
2 NaN ZY-13 Shanghai
3 NaN ZY-13 Shanghai
4 NaN ZY-16 Changsha
name_y
0 NaN
1 NaN
2 Shanghai World Financial Center
3 Shanghai World Financial Center
4 Changsha IFS Tower T1
python pandas dataframe
asked Mar 27 at 5:19
ahbonahbon
7888 silver badges17 bronze badges
Have you taken a look at the "merging multiple DataFrames" section in stackoverflow.com/questions/53645882/pandas-merging-101?
– cs95
Mar 27 at 5:34
Thanks. I update my solution in question, which is not as expected result, could help me to improve it? Thanks.
– ahbon
Mar 27 at 5:49
What is wrong with it?
– cs95
Mar 27 at 5:55
Too much columns with_xand_y, I want only two more columns fordf,idandmatched_id.idis for first three columns, andmatched_idis for last three columns.
– ahbon
Mar 27 at 5:57
"matched_id" is not in any of your original dataframes, where does it come from?
– cs95
Mar 27 at 5:59
|
show 3 more comments
1
1
1
Let's say I have a combined dataframe named df as follows. Each row has buildings' info and their matched buildings' info. I hope to merge id of each building from df1, df2 and df3 (see below). The columns of df_num or matched_df_num is there to distingue which dataframe the building info come from, if it's equals to 1, means it's from df1, 2 means from df2, 3 means from df3.
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio
0 Shenzhen Ping An Finance Centre 51
1 Guangzhou Guangzhou CTF Finance Centre 66
2 Shanghai Shanghai World Financial Center 59
3 Shanghai Shanghai World Financial Center 56
4 Changsha Changsha IFS Tower T1 57
I want to merge the column of ids from df1, df2 and df3 below for building names and matched names:
df1 = pd.DataFrame(np.array([
[1010667747, 'Suzhou', 'Suzhou IFS'],
[1010667356, 'Shenzhen', 'Kingkey 100'],
[1010667289, 'Wuhan', 'Wuhan Center']]),
columns=['id', 'city', 'name']
)
df2 = pd.DataFrame(np.array([
[190010, 'Shenzhen', 'Ping An Finance Centre'],
[190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
[190015, 'Beijing', 'China Zun']]),
columns=['id', 'city', 'name']
)
df3 = pd.DataFrame(np.array([
['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
['ZY-15', 'Hong Kong', 'International Commerce Centre'],
['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
columns=['id', 'city', 'name']
)
This is my expected result:
df_num city name id
0 1 Shenzhen Kingkey 100 1010667356
1 2 Shenzhen Ping An Finance Centre 190010
2 2 Shenzhen Ping An Finance Centre 190010
3 2 Guangzhou Guangzhou CTF Finance Centre 190012
4 3 Shanghai Shanghai World Financial Center ZY-13
matched_df_num matched_city matched_name
0 2 Shenzhen Ping An Finance Centre
1 2 Guangzhou Guangzhou CTF Finance Centre
2 3 Shanghai Shanghai World Financial Center
3 3 Shanghai Shanghai World Financial Center
4 3 Changsha Changsha IFS Tower T1
similarity_ratio matched_id
0 51 190010
1 66 190010
2 59 ZY-13
3 56 ZY-13
4 57 ZY-16
How could I insert two new columns id and matched_id and their values in df using Pandas? Thanks for helps at advance.
Update: my solution:
df = df.merge(df1, on = ['city', 'name'], how = 'left').merge(df2, on = ['city', 'name'], how = 'left').merge(df3, on = ['city', 'name'], how = 'left')
final_df = df.merge(df1, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left').merge(df2, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left').merge(df3, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left')
df_num city_x name_x matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id_x
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 NaN
2 Shanghai Shanghai World Financial Center 59 NaN
3 Shanghai Shanghai World Financial Center 56 NaN
4 Changsha Changsha IFS Tower T1 57 NaN
id_y id_x id_y city_y name_y id_x city_x
0 NaN NaN NaN NaN NaN 190010 Shenzhen
1 190010 NaN NaN NaN NaN 190012 Guangzhou
2 190010 NaN NaN NaN NaN NaN NaN
3 190012 NaN NaN NaN NaN NaN NaN
4 NaN ZY-13 NaN NaN NaN NaN NaN
name_x id_y city_y
0 Ping An Finance Centre NaN NaN
1 Guangzhou CTF Finance Centre NaN NaN
2 NaN ZY-13 Shanghai
3 NaN ZY-13 Shanghai
4 NaN ZY-16 Changsha
name_y
0 NaN
1 NaN
2 Shanghai World Financial Center
3 Shanghai World Financial Center
4 Changsha IFS Tower T1
python pandas dataframe
asked Mar 27 at 5:19
ahbonahbon
7888 silver badges17 bronze badges
Let's say I have a combined dataframe named df as follows. Each row has buildings' info and their matched buildings' info. I hope to merge id of each building from df1, df2 and df3 (see below). The columns of df_num or matched_df_num is there to distingue which dataframe the building info come from, if it's equals to 1, means it's from df1, 2 means from df2, 3 means from df3.
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio
0 Shenzhen Ping An Finance Centre 51
1 Guangzhou Guangzhou CTF Finance Centre 66
2 Shanghai Shanghai World Financial Center 59
3 Shanghai Shanghai World Financial Center 56
4 Changsha Changsha IFS Tower T1 57
I want to merge the column of ids from df1, df2 and df3 below for building names and matched names:
df1 = pd.DataFrame(np.array([
[1010667747, 'Suzhou', 'Suzhou IFS'],
[1010667356, 'Shenzhen', 'Kingkey 100'],
[1010667289, 'Wuhan', 'Wuhan Center']]),
columns=['id', 'city', 'name']
)
df2 = pd.DataFrame(np.array([
[190010, 'Shenzhen', 'Ping An Finance Centre'],
[190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
[190015, 'Beijing', 'China Zun']]),
columns=['id', 'city', 'name']
)
df3 = pd.DataFrame(np.array([
['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
['ZY-15', 'Hong Kong', 'International Commerce Centre'],
['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
columns=['id', 'city', 'name']
)
This is my expected result:
df_num city name id
0 1 Shenzhen Kingkey 100 1010667356
1 2 Shenzhen Ping An Finance Centre 190010
2 2 Shenzhen Ping An Finance Centre 190010
3 2 Guangzhou Guangzhou CTF Finance Centre 190012
4 3 Shanghai Shanghai World Financial Center ZY-13
matched_df_num matched_city matched_name
0 2 Shenzhen Ping An Finance Centre
1 2 Guangzhou Guangzhou CTF Finance Centre
2 3 Shanghai Shanghai World Financial Center
3 3 Shanghai Shanghai World Financial Center
4 3 Changsha Changsha IFS Tower T1
similarity_ratio matched_id
0 51 190010
1 66 190010
2 59 ZY-13
3 56 ZY-13
4 57 ZY-16
How could I insert two new columns id and matched_id and their values in df using Pandas? Thanks for helps at advance.
Update: my solution:
df = df.merge(df1, on = ['city', 'name'], how = 'left').merge(df2, on = ['city', 'name'], how = 'left').merge(df3, on = ['city', 'name'], how = 'left')
final_df = df.merge(df1, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left').merge(df2, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left').merge(df3, left_on = ['matched_city', 'matched_name'], right_on = ['city', 'name'], how = 'left')
df_num city_x name_x matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id_x
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 NaN
2 Shanghai Shanghai World Financial Center 59 NaN
3 Shanghai Shanghai World Financial Center 56 NaN
4 Changsha Changsha IFS Tower T1 57 NaN
id_y id_x id_y city_y name_y id_x city_x
0 NaN NaN NaN NaN NaN 190010 Shenzhen
1 190010 NaN NaN NaN NaN 190012 Guangzhou
2 190010 NaN NaN NaN NaN NaN NaN
3 190012 NaN NaN NaN NaN NaN NaN
4 NaN ZY-13 NaN NaN NaN NaN NaN
name_x id_y city_y
0 Ping An Finance Centre NaN NaN
1 Guangzhou CTF Finance Centre NaN NaN
2 NaN ZY-13 Shanghai
3 NaN ZY-13 Shanghai
4 NaN ZY-16 Changsha
name_y
0 NaN
1 NaN
2 Shanghai World Financial Center
3 Shanghai World Financial Center
4 Changsha IFS Tower T1
python pandas dataframe
python pandas dataframe
asked Mar 27 at 5:19
ahbonahbon
7888 silver badges17 bronze badges
asked Mar 27 at 5:19
ahbonahbon
7888 silver badges17 bronze badges
edited Mar 27 at 5:48
ahbon
asked Mar 27 at 5:19
ahbonahbon
7888 silver badges17 bronze badges
asked Mar 27 at 5:19
ahbonahbon
7888 silver badges17 bronze badges
asked Mar 27 at 5:19
ahbonahbon
7888 silver badges17 bronze badges
7888 silver badges17 bronze badges
Have you taken a look at the "merging multiple DataFrames" section in stackoverflow.com/questions/53645882/pandas-merging-101?
– cs95
Mar 27 at 5:34
Thanks. I update my solution in question, which is not as expected result, could help me to improve it? Thanks.
– ahbon
Mar 27 at 5:49
What is wrong with it?
– cs95
Mar 27 at 5:55
Too much columns with_xand_y, I want only two more columns fordf,idandmatched_id.idis for first three columns, andmatched_idis for last three columns.
– ahbon
Mar 27 at 5:57
"matched_id" is not in any of your original dataframes, where does it come from?
– cs95
Mar 27 at 5:59
|
show 3 more comments
Have you taken a look at the "merging multiple DataFrames" section in stackoverflow.com/questions/53645882/pandas-merging-101?
– cs95
Mar 27 at 5:34
Thanks. I update my solution in question, which is not as expected result, could help me to improve it? Thanks.
– ahbon
Mar 27 at 5:49
What is wrong with it?
– cs95
Mar 27 at 5:55
Too much columns with_xand_y, I want only two more columns fordf,idandmatched_id.idis for first three columns, andmatched_idis for last three columns.
– ahbon
Mar 27 at 5:57
"matched_id" is not in any of your original dataframes, where does it come from?
– cs95
Mar 27 at 5:59
Have you taken a look at the "merging multiple DataFrames" section in stackoverflow.com/questions/53645882/pandas-merging-101?
– cs95
Mar 27 at 5:34
Have you taken a look at the "merging multiple DataFrames" section in stackoverflow.com/questions/53645882/pandas-merging-101?
– cs95
Mar 27 at 5:34
Thanks. I update my solution in question, which is not as expected result, could help me to improve it? Thanks.
– ahbon
Mar 27 at 5:49
Thanks. I update my solution in question, which is not as expected result, could help me to improve it? Thanks.
– ahbon
Mar 27 at 5:49
What is wrong with it?
– cs95
Mar 27 at 5:55
What is wrong with it?
– cs95
Mar 27 at 5:55
Too much columns with
_x and _y, I want only two more columns for df, id and matched_id. id is for first three columns, and matched_id is for last three columns.– ahbon
Mar 27 at 5:57
Too much columns with
_x and _y, I want only two more columns for df, id and matched_id. id is for first three columns, and matched_id is for last three columns.– ahbon
Mar 27 at 5:57
"matched_id" is not in any of your original dataframes, where does it come from?
– cs95
Mar 27 at 5:59
"matched_id" is not in any of your original dataframes, where does it come from?
– cs95
Mar 27 at 5:59
|
show 3 more comments
2 Answers
2
active
oldest
votes
1
You can use concat with merge and left join:
dff = pd.concat([df1, df2, df3])
print (dff)
id city name
0 1010667747 Suzhou Suzhou IFS
1 1010667356 Shenzhen Kingkey 100
2 1010667289 Wuhan Wuhan Center
0 190010 Shenzhen Ping An Finance Centre
1 190012 Guangzhou Guangzhou CTF Finance Centre
2 190015 Beijing China Zun
0 ZY-13 Shanghai Shanghai World Financial Center
1 ZY-15 Hong Kong International Commerce Centre
2 ZY-16 Changsha Changsha IFS Tower T1
df = df.merge(dff,on = ['city', 'name'], how = 'left')
print (df)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
Then merge again, for avoid duplicated columns use rename:
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id'
df5 = df.merge(dff.rename(columns=d),on = ['matched_city', 'matched_name'], how = 'left')
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
EDIT: You can add new values to each DataFrame by DataFrame.assign first, and then merge also by this column:
dff = pd.concat([df1.assign(df_num=1), df2.assign(df_num=2), df3.assign(df_num=3)])
df = df.merge(dff,on = ['city', 'name','df_num'], how = 'left')
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id','df_num':'matched_df_num'
df5 = (df.merge(dff.rename(columns=d),
on = ['matched_city', 'matched_name','matched_df_num'],
how = 'left'))
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
answered Mar 27 at 6:29
jezraeljezrael
396k29 gold badges410 silver badges479 bronze badges
Thanks a lot. Can I get one more column namedmatche_idfor building ofmatched_df_num, matched_city, matched_name. Please note for each row, there are two buildings' info: building and its matches, so I want get all theirids but seperate toidandmatched_id.
– ahbon
Mar 27 at 6:37
Please check expected result part in my question. :)
– ahbon
Mar 27 at 6:39
Sorry my question is little bit tricky.
– ahbon
Mar 27 at 6:40
@ahbon - please check edited answer.
– jezrael
Mar 27 at 6:45
1
Cool and perfect. Thanks a lot.
– ahbon
Mar 27 at 7:09
|
show 2 more comments
0
Try this, it may help you to solve your problem
df1 = pd.DataFrame(np.array([
[1010667747, 'Suzhou', 'Suzhou IFS'],
[1010667356, 'Shenzhen', 'Kingkey 100'],
[1010667289, 'Wuhan', 'Wuhan Center']]),
columns=['id', 'city', 'name']
)
df2 = pd.DataFrame(np.array([
[190010, 'Shenzhen', 'Ping An Finance Centre'],
[190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
[190015, 'Beijing', 'China Zun']]),
columns=['id', 'city', 'name']
)
df3 = pd.DataFrame(np.array([
['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
['ZY-15', 'Hong Kong', 'International Commerce Centre'],
['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
columns=['id', 'city', 'name']
)
df1['df_type'] = 1
df2['df_type'] = 2
df3['df_type'] = 3
df = pd.concat([df1,df2,df3])
df
answered Mar 27 at 7:49
HimmatHimmat
915 bronze badges
It is only part of solution, check expected output.
– jezrael
Mar 27 at 7:50
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55370265%2fmerge-one-column-from-multiple-dataframes-to-another-dataframe-based-on-multiple%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can use concat with merge and left join:
dff = pd.concat([df1, df2, df3])
print (dff)
id city name
0 1010667747 Suzhou Suzhou IFS
1 1010667356 Shenzhen Kingkey 100
2 1010667289 Wuhan Wuhan Center
0 190010 Shenzhen Ping An Finance Centre
1 190012 Guangzhou Guangzhou CTF Finance Centre
2 190015 Beijing China Zun
0 ZY-13 Shanghai Shanghai World Financial Center
1 ZY-15 Hong Kong International Commerce Centre
2 ZY-16 Changsha Changsha IFS Tower T1
df = df.merge(dff,on = ['city', 'name'], how = 'left')
print (df)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
Then merge again, for avoid duplicated columns use rename:
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id'
df5 = df.merge(dff.rename(columns=d),on = ['matched_city', 'matched_name'], how = 'left')
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
EDIT: You can add new values to each DataFrame by DataFrame.assign first, and then merge also by this column:
dff = pd.concat([df1.assign(df_num=1), df2.assign(df_num=2), df3.assign(df_num=3)])
df = df.merge(dff,on = ['city', 'name','df_num'], how = 'left')
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id','df_num':'matched_df_num'
df5 = (df.merge(dff.rename(columns=d),
on = ['matched_city', 'matched_name','matched_df_num'],
how = 'left'))
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
answered Mar 27 at 6:29
jezraeljezrael
396k29 gold badges410 silver badges479 bronze badges
Thanks a lot. Can I get one more column namedmatche_idfor building ofmatched_df_num, matched_city, matched_name. Please note for each row, there are two buildings' info: building and its matches, so I want get all theirids but seperate toidandmatched_id.
– ahbon
Mar 27 at 6:37
Please check expected result part in my question. :)
– ahbon
Mar 27 at 6:39
Sorry my question is little bit tricky.
– ahbon
Mar 27 at 6:40
@ahbon - please check edited answer.
– jezrael
Mar 27 at 6:45
1
Cool and perfect. Thanks a lot.
– ahbon
Mar 27 at 7:09
|
show 2 more comments
1
You can use concat with merge and left join:
dff = pd.concat([df1, df2, df3])
print (dff)
id city name
0 1010667747 Suzhou Suzhou IFS
1 1010667356 Shenzhen Kingkey 100
2 1010667289 Wuhan Wuhan Center
0 190010 Shenzhen Ping An Finance Centre
1 190012 Guangzhou Guangzhou CTF Finance Centre
2 190015 Beijing China Zun
0 ZY-13 Shanghai Shanghai World Financial Center
1 ZY-15 Hong Kong International Commerce Centre
2 ZY-16 Changsha Changsha IFS Tower T1
df = df.merge(dff,on = ['city', 'name'], how = 'left')
print (df)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
Then merge again, for avoid duplicated columns use rename:
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id'
df5 = df.merge(dff.rename(columns=d),on = ['matched_city', 'matched_name'], how = 'left')
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
EDIT: You can add new values to each DataFrame by DataFrame.assign first, and then merge also by this column:
dff = pd.concat([df1.assign(df_num=1), df2.assign(df_num=2), df3.assign(df_num=3)])
df = df.merge(dff,on = ['city', 'name','df_num'], how = 'left')
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id','df_num':'matched_df_num'
df5 = (df.merge(dff.rename(columns=d),
on = ['matched_city', 'matched_name','matched_df_num'],
how = 'left'))
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
answered Mar 27 at 6:29
jezraeljezrael
396k29 gold badges410 silver badges479 bronze badges
Thanks a lot. Can I get one more column namedmatche_idfor building ofmatched_df_num, matched_city, matched_name. Please note for each row, there are two buildings' info: building and its matches, so I want get all theirids but seperate toidandmatched_id.
– ahbon
Mar 27 at 6:37
Please check expected result part in my question. :)
– ahbon
Mar 27 at 6:39
Sorry my question is little bit tricky.
– ahbon
Mar 27 at 6:40
@ahbon - please check edited answer.
– jezrael
Mar 27 at 6:45
1
Cool and perfect. Thanks a lot.
– ahbon
Mar 27 at 7:09
|
show 2 more comments
1
1
1
You can use concat with merge and left join:
dff = pd.concat([df1, df2, df3])
print (dff)
id city name
0 1010667747 Suzhou Suzhou IFS
1 1010667356 Shenzhen Kingkey 100
2 1010667289 Wuhan Wuhan Center
0 190010 Shenzhen Ping An Finance Centre
1 190012 Guangzhou Guangzhou CTF Finance Centre
2 190015 Beijing China Zun
0 ZY-13 Shanghai Shanghai World Financial Center
1 ZY-15 Hong Kong International Commerce Centre
2 ZY-16 Changsha Changsha IFS Tower T1
df = df.merge(dff,on = ['city', 'name'], how = 'left')
print (df)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
Then merge again, for avoid duplicated columns use rename:
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id'
df5 = df.merge(dff.rename(columns=d),on = ['matched_city', 'matched_name'], how = 'left')
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
EDIT: You can add new values to each DataFrame by DataFrame.assign first, and then merge also by this column:
dff = pd.concat([df1.assign(df_num=1), df2.assign(df_num=2), df3.assign(df_num=3)])
df = df.merge(dff,on = ['city', 'name','df_num'], how = 'left')
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id','df_num':'matched_df_num'
df5 = (df.merge(dff.rename(columns=d),
on = ['matched_city', 'matched_name','matched_df_num'],
how = 'left'))
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
answered Mar 27 at 6:29
jezraeljezrael
396k29 gold badges410 silver badges479 bronze badges
You can use concat with merge and left join:
dff = pd.concat([df1, df2, df3])
print (dff)
id city name
0 1010667747 Suzhou Suzhou IFS
1 1010667356 Shenzhen Kingkey 100
2 1010667289 Wuhan Wuhan Center
0 190010 Shenzhen Ping An Finance Centre
1 190012 Guangzhou Guangzhou CTF Finance Centre
2 190015 Beijing China Zun
0 ZY-13 Shanghai Shanghai World Financial Center
1 ZY-15 Hong Kong International Commerce Centre
2 ZY-16 Changsha Changsha IFS Tower T1
df = df.merge(dff,on = ['city', 'name'], how = 'left')
print (df)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
Then merge again, for avoid duplicated columns use rename:
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id'
df5 = df.merge(dff.rename(columns=d),on = ['matched_city', 'matched_name'], how = 'left')
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
EDIT: You can add new values to each DataFrame by DataFrame.assign first, and then merge also by this column:
dff = pd.concat([df1.assign(df_num=1), df2.assign(df_num=2), df3.assign(df_num=3)])
df = df.merge(dff,on = ['city', 'name','df_num'], how = 'left')
d = 'city':'matched_city','name':'matched_name', 'id':'matched_id','df_num':'matched_df_num'
df5 = (df.merge(dff.rename(columns=d),
on = ['matched_city', 'matched_name','matched_df_num'],
how = 'left'))
print (df5)
df_num city name matched_df_num
0 1 Shenzhen Kingkey 100 2
1 2 Shenzhen Ping An Finance Centre 2
2 2 Shenzhen Ping An Finance Centre 3
3 2 Guangzhou Guangzhou CTF Finance Centre 3
4 3 Shanghai Shanghai World Financial Center 3
matched_city matched_name similarity_ratio id
0 Shenzhen Ping An Finance Centre 51 1010667356
1 Guangzhou Guangzhou CTF Finance Centre 66 190010
2 Shanghai Shanghai World Financial Center 59 190010
3 Shanghai Shanghai World Financial Center 56 190012
4 Changsha Changsha IFS Tower T1 57 ZY-13
matched_id
0 190010
1 190012
2 ZY-13
3 ZY-13
4 ZY-16
answered Mar 27 at 6:29
jezraeljezrael
396k29 gold badges410 silver badges479 bronze badges
edited Mar 27 at 7:00
answered Mar 27 at 6:29
jezraeljezrael
396k29 gold badges410 silver badges479 bronze badges
answered Mar 27 at 6:29
jezraeljezrael
396k29 gold badges410 silver badges479 bronze badges
answered Mar 27 at 6:29
jezraeljezrael
396k29 gold badges410 silver badges479 bronze badges
396k29 gold badges410 silver badges479 bronze badges
Thanks a lot. Can I get one more column namedmatche_idfor building ofmatched_df_num, matched_city, matched_name. Please note for each row, there are two buildings' info: building and its matches, so I want get all theirids but seperate toidandmatched_id.
– ahbon
Mar 27 at 6:37
Please check expected result part in my question. :)
– ahbon
Mar 27 at 6:39
Sorry my question is little bit tricky.
– ahbon
Mar 27 at 6:40
@ahbon - please check edited answer.
– jezrael
Mar 27 at 6:45
1
Cool and perfect. Thanks a lot.
– ahbon
Mar 27 at 7:09
|
show 2 more comments
Thanks a lot. Can I get one more column namedmatche_idfor building ofmatched_df_num, matched_city, matched_name. Please note for each row, there are two buildings' info: building and its matches, so I want get all theirids but seperate toidandmatched_id.
– ahbon
Mar 27 at 6:37
Please check expected result part in my question. :)
– ahbon
Mar 27 at 6:39
Sorry my question is little bit tricky.
– ahbon
Mar 27 at 6:40
@ahbon - please check edited answer.
– jezrael
Mar 27 at 6:45
1
Cool and perfect. Thanks a lot.
– ahbon
Mar 27 at 7:09
Thanks a lot. Can I get one more column named
matche_id for building of matched_df_num, matched_city, matched_name. Please note for each row, there are two buildings' info: building and its matches, so I want get all their ids but seperate to id and matched_id.– ahbon
Mar 27 at 6:37
Thanks a lot. Can I get one more column named
matche_id for building of matched_df_num, matched_city, matched_name. Please note for each row, there are two buildings' info: building and its matches, so I want get all their ids but seperate to id and matched_id.– ahbon
Mar 27 at 6:37
Please check expected result part in my question. :)
– ahbon
Mar 27 at 6:39
Please check expected result part in my question. :)
– ahbon
Mar 27 at 6:39
Sorry my question is little bit tricky.
– ahbon
Mar 27 at 6:40
Sorry my question is little bit tricky.
– ahbon
Mar 27 at 6:40
@ahbon - please check edited answer.
– jezrael
Mar 27 at 6:45
@ahbon - please check edited answer.
– jezrael
Mar 27 at 6:45
1
1
Cool and perfect. Thanks a lot.
– ahbon
Mar 27 at 7:09
Cool and perfect. Thanks a lot.
– ahbon
Mar 27 at 7:09
|
show 2 more comments
0
Try this, it may help you to solve your problem
df1 = pd.DataFrame(np.array([
[1010667747, 'Suzhou', 'Suzhou IFS'],
[1010667356, 'Shenzhen', 'Kingkey 100'],
[1010667289, 'Wuhan', 'Wuhan Center']]),
columns=['id', 'city', 'name']
)
df2 = pd.DataFrame(np.array([
[190010, 'Shenzhen', 'Ping An Finance Centre'],
[190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
[190015, 'Beijing', 'China Zun']]),
columns=['id', 'city', 'name']
)
df3 = pd.DataFrame(np.array([
['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
['ZY-15', 'Hong Kong', 'International Commerce Centre'],
['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
columns=['id', 'city', 'name']
)
df1['df_type'] = 1
df2['df_type'] = 2
df3['df_type'] = 3
df = pd.concat([df1,df2,df3])
df
answered Mar 27 at 7:49
HimmatHimmat
915 bronze badges
It is only part of solution, check expected output.
– jezrael
Mar 27 at 7:50
add a comment |
0
Try this, it may help you to solve your problem
df1 = pd.DataFrame(np.array([
[1010667747, 'Suzhou', 'Suzhou IFS'],
[1010667356, 'Shenzhen', 'Kingkey 100'],
[1010667289, 'Wuhan', 'Wuhan Center']]),
columns=['id', 'city', 'name']
)
df2 = pd.DataFrame(np.array([
[190010, 'Shenzhen', 'Ping An Finance Centre'],
[190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
[190015, 'Beijing', 'China Zun']]),
columns=['id', 'city', 'name']
)
df3 = pd.DataFrame(np.array([
['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
['ZY-15', 'Hong Kong', 'International Commerce Centre'],
['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
columns=['id', 'city', 'name']
)
df1['df_type'] = 1
df2['df_type'] = 2
df3['df_type'] = 3
df = pd.concat([df1,df2,df3])
df
answered Mar 27 at 7:49
HimmatHimmat
915 bronze badges
It is only part of solution, check expected output.
– jezrael
Mar 27 at 7:50
add a comment |
0
0
0
Try this, it may help you to solve your problem
df1 = pd.DataFrame(np.array([
[1010667747, 'Suzhou', 'Suzhou IFS'],
[1010667356, 'Shenzhen', 'Kingkey 100'],
[1010667289, 'Wuhan', 'Wuhan Center']]),
columns=['id', 'city', 'name']
)
df2 = pd.DataFrame(np.array([
[190010, 'Shenzhen', 'Ping An Finance Centre'],
[190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
[190015, 'Beijing', 'China Zun']]),
columns=['id', 'city', 'name']
)
df3 = pd.DataFrame(np.array([
['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
['ZY-15', 'Hong Kong', 'International Commerce Centre'],
['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
columns=['id', 'city', 'name']
)
df1['df_type'] = 1
df2['df_type'] = 2
df3['df_type'] = 3
df = pd.concat([df1,df2,df3])
df
answered Mar 27 at 7:49
HimmatHimmat
915 bronze badges
Try this, it may help you to solve your problem
df1 = pd.DataFrame(np.array([
[1010667747, 'Suzhou', 'Suzhou IFS'],
[1010667356, 'Shenzhen', 'Kingkey 100'],
[1010667289, 'Wuhan', 'Wuhan Center']]),
columns=['id', 'city', 'name']
)
df2 = pd.DataFrame(np.array([
[190010, 'Shenzhen', 'Ping An Finance Centre'],
[190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
[190015, 'Beijing', 'China Zun']]),
columns=['id', 'city', 'name']
)
df3 = pd.DataFrame(np.array([
['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
['ZY-15', 'Hong Kong', 'International Commerce Centre'],
['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
columns=['id', 'city', 'name']
)
df1['df_type'] = 1
df2['df_type'] = 2
df3['df_type'] = 3
df = pd.concat([df1,df2,df3])
df
answered Mar 27 at 7:49
HimmatHimmat
915 bronze badges
answered Mar 27 at 7:49
HimmatHimmat
915 bronze badges
answered Mar 27 at 7:49
HimmatHimmat
915 bronze badges
answered Mar 27 at 7:49
HimmatHimmat
915 bronze badges
915 bronze badges
It is only part of solution, check expected output.
– jezrael
Mar 27 at 7:50
add a comment |
It is only part of solution, check expected output.
– jezrael
Mar 27 at 7:50
It is only part of solution, check expected output.
– jezrael
Mar 27 at 7:50
It is only part of solution, check expected output.
– jezrael
Mar 27 at 7:50
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55370265%2fmerge-one-column-from-multiple-dataframes-to-another-dataframe-based-on-multiple%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have you taken a look at the "merging multiple DataFrames" section in stackoverflow.com/questions/53645882/pandas-merging-101?
– cs95
Mar 27 at 5:34
Thanks. I update my solution in question, which is not as expected result, could help me to improve it? Thanks.
– ahbon
Mar 27 at 5:49
What is wrong with it?
– cs95
Mar 27 at 5:55
Too much columns with
_xand_y, I want only two more columns fordf,idandmatched_id.idis for first three columns, andmatched_idis for last three columns.– ahbon
Mar 27 at 5:57
"matched_id" is not in any of your original dataframes, where does it come from?
– cs95
Mar 27 at 5:59