Add values from n dataframes if match ID of another dataframe

Question

I currently have n number of dataframes (dfs[n]) with IDs and X values and one dataframe (df) with IDs.

I would like to add X values to a new column in df if IDs of the X value match the ID of df and add 'X values n' as the column name.

for n in range(len(files)):
    if dfs[n]['ID'] == df['ID']:
        df = df.join(dfs[n]['X values'], on='ID', rsuffix = int(r))
    print(df)

I tried this code but doesn't work.

Thank you for your help!

is there a reason why you are not simply iterating like for frame in dfs; df=df.join(frame...) — Quickbeam2k1
– Quickbeam2k1, Commented Oct 18, 2017 at 5:49
Not quick sure what you mean sorry, I'm pretty new with Python. But I did get an answer which works below. Thank though! — Flora
– Flora, Commented Oct 18, 2017 at 6:20

piRSquared · Accepted Answer · 2017-10-18 05:54:49Z

1

Setup

dfs = [pd.DataFrame({'ID': [1, 2, 3], 'X values': list('XYZ')}) for _ in range(4)]
df = pd.DataFrame(dict(ID=[1, 2, 3]))

Solution
You need to pd.concat after having pd.DataFrame.set_index

df.join(
    pd.concat(
        [d.set_index('ID')['X values'] for d in dfs],
        axis=1, keys=list(range(len(dfs)))
    ), on='ID')

   ID  0  1  2  3
0   1  X  X  X  X
1   2  Y  Y  Y  Y
2   3  Z  Z  Z  Z

edited Oct 18, 2017 at 5:54

answered Oct 18, 2017 at 5:49

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Flora Over a year ago

Thank you so much, this helped me a lot !

Flora Over a year ago

I also wanted to put 'X values of n' as a name for each new columns. I tried changing your code a bit by putting keys = 'X values' + str(list(range(len(files))))) however it doesn't come out as a whole but only one letter per column. Do you think you have a solution for that ?

Collectives™ on Stack Overflow

Add values from n dataframes if match ID of another dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related