Home > database >  How to concatenate columns of dataframes in a dictionary to a new dataframe
How to concatenate columns of dataframes in a dictionary to a new dataframe

Time:02-19

I have a dictionary of eight very similar looking dataframes. I'd like to pick the equally named column from all these dataframes and concatenate them into a new dataframe, where the columns get the name of the key to the dataframe which they are from. In small it looks like this:

d1 = {'DE': [1, 2, 3], 'BE': [3, 4, 5], 'AT': [5, 6, 7]}
df1 = pd.DataFrame(data=d1)

d2 = {'DE': [5, 7, 9], 'BE': [4, 6, 2], 'AT': [3, 5, 2]}
df2 = pd.DataFrame(data=d2)

d3 = {'DE': [1, 5, 4], 'BE': [5, 2, 1], 'AT': [3, 6, 1]}
df3 = pd.DataFrame(data=d3)

technology = {'solar' : df1, 'wind_onshore' : df2, 'wind_offshore' : df3}

Now I'd like to pick the 'DE' column of each dataframe and concatenate it into a new dataframe, where each column gets the name it comes from e.g. solar, wind_onshore, wind_offshore.

I hope this is not a trivial question and I'm just not getting it :D Thanks everyone :)

Edit: I accidentally constructed a dictionary of dictionaries rather than a dictionary of dataframes

CodePudding user response:

You can first add a technology column to each df and then combine the separate dfs using pd.concat into a single long df. You can then use pd.pivot to make the columns be the technology

d1 = {'DE': [1, 2, 3], 'BE': [3, 4, 5], 'AT': [5, 6, 7]}
df1 = pd.DataFrame(data=d1)
df1['technology'] = 'solar'

d2 = {'DE': [5, 7, 9], 'BE': [4, 6, 2], 'AT': [3, 5, 2]}
df2 = pd.DataFrame(data=d2)
df2['technology'] = 'wind_onshore'

d3 = {'DE': [1, 5, 4], 'BE': [5, 2, 1], 'AT': [3, 6, 1]}
df3 = pd.DataFrame(data=d3)
df3['technology'] = 'wind_offshore'

combined_df = pd.concat((df1,df2,df3))

wide_df = combined_df.pivot(
    values='DE',
    columns='technology',
)

wide_df
  • Related