Create a dataframe from multiple list of dictionary values

Question

I have a code as below,

safety_df ={}
for key3,safety in analy_df.items():
    safety = pd.DataFrame({"Year":safety['index'],
                      '{}'.format(key3)+"_CR":safety['CURRENT'],
                      '{}'.format(key3)+"_ICR":safety['ICR'],
                      '{}'.format(key3)+"_D/E":safety['D/E'],
                      '{}'.format(key3)+"_D/A":safety['D/A']})
    safety_df[key3] = safety

Here in this code I'm extracting values from another dictionary. It will looping through the various companies that why I named using format in the key. The output contains above 5 columns for each company(Year,CR, ICR,D/E,D/A).

Output which is printing out is with plenty of NA values where after Here I want common column which is year for all companies and print following columns which is C1_CR, C2_CR, C3_CR, C1_ICR, C2_ICR, C3_ICR,...C3_D/A ..

I tried to extract using following code,

pd.concat(safety_df.values())

Sample output of this..

Here it extracts values for each list, but NA values are getting printed out because of for loops?

I also tried with groupby and it was not worked?..

How to set Year as common column, and print other values side by side.

Thanks

Since key3 will be different each time, your dataframes stores inside the safety_df dict will also be different: each dataframe differs, as they don't have the same columns. — 9769953
– 9769953, Commented Mar 20, 2021 at 7:42
You may also get a good result using the axis argument in pandas.concat, by setting it to concatenate along the columns instead (axis=1). — 9769953
– 9769953, Commented Mar 20, 2021 at 7:45
Note that you have a year with a value of TTM; is that correct? — 9769953
– 9769953, Commented Mar 20, 2021 at 7:49

9769953 · Accepted Answer · 2021-03-20 07:56:06Z

Use axis=1 to concate along the columns:

import numpy as np
import pandas as pd

years = np.arange(2010, 2021)
n = len(years)
c1 = np.random.rand(n)
c2 = np.random.rand(n)
c3 = np.random.rand(n)

frames = {
    'a': pd.DataFrame({'year': years, 'c1': c1}),
    'b': pd.DataFrame({'year': years, 'c2': c2}),
    'c': pd.DataFrame({'year': years[1:], 'c3': c3[1:]}),
}
for key in frames:
    frames[key].set_index('year', inplace=True)

df = pd.concat(frames.values(), axis=1)
print(df)

which results in

            c1        c2        c3
year
2010  0.956494  0.667499       NaN
2011  0.945344  0.578535  0.780039
2012  0.262117  0.080678  0.084415
2013  0.458592  0.390832  0.310181
2014  0.094028  0.843971  0.886331
2015  0.774905  0.192438  0.883722
2016  0.254918  0.095353  0.774190
2017  0.724667  0.397913  0.650906
2018  0.277498  0.531180  0.091791
2019  0.238076  0.917023  0.387511
2020  0.677015  0.159720  0.063264

Note that I have explicitly set the index to be the 'year' column, and in my example, I have removed the first year from the 'c' column. This is to show how the indices of the different dataframes are matched when concatenating. Had the index been left to its standard value, you would have gotten the years out of sync and a NaN value at the bottom of column 'c' instead.

Collectives™ on Stack Overflow

Create a dataframe from multiple list of dictionary values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related