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