How to create a Dataframe from a list of dictionary?-CodePudding

I have list of dictionary like this:

collection = [{'item': ['Policy Master 1-2022-2023-P0003 - 5 Days - Plan 3', 'Policy Master 2-2022-2023-P0009 - 5 Days - Plan 3', 'Policy Master 3-2022-2023-P0012 - 5 Days - Plan 3', 'Policy Master 1-2022-2023-P0003 - 5 Days - Plan 2', 'Policy Master 2-2022-2023-P0009 - 5 Days - Plan 2'], 'Mar': [2800.0, 600.0, 600.0, 1000.0, 200.0]}, {'item': ['Policy Master 1-2022-2023-P0003 - 5 Days - Plan 3', 'Policy Master 2-2022-2023-P0009 - 5 Days - Plan 3', 'Policy Master 3-2022-2023-P0012 - 5 Days - Plan 3', 'Policy Master 1-2022-2023-P0003 - 5 Days - Plan 2'], 'Jun': [2800.0, 600.0, 600.0, 1000.0]}]

I want to item from show monthly data corresponding to it. If item was not present in previous month it should show NaN in previous month. How to do it in pandas? Also more months can be added in future. So it should consider all months dynamically.

CodePudding user response：

It appears you have a list of possible dataframes that can be merged together:

for i, x in enumerate(collection):
    if not i:
        df = pd.DataFrame(x)
    else:
        df = df.merge(pd.DataFrame(x), 'outer')

print(df)

Output:

                                                item     Mar     Jun
0  Policy Master 1-2022-2023-P0003 - 5 Days - Plan 3  2800.0  2800.0
1  Policy Master 2-2022-2023-P0009 - 5 Days - Plan 3   600.0   600.0
2  Policy Master 3-2022-2023-P0012 - 5 Days - Plan 3   600.0   600.0
3  Policy Master 1-2022-2023-P0003 - 5 Days - Plan 2  1000.0  1000.0
4  Policy Master 2-2022-2023-P0009 - 5 Days - Plan 2   200.0     NaN

Or:

from functools import reduce

reduce(lambda x, y: pd.merge(pd.DataFrame(x), pd.DataFrame(y), 'outer'), collection)

CodePudding user response：

Continue and complete the previous code:

for i, x in enumerate(collection):
    if not i:
        df = pd.DataFrame(x)
    else:
        df = df.merge(pd.DataFrame(x), 'outer')
df_1 = df['item'].str.split("-", expand=True)
df = df.join(df_1)
df.drop(['item'], axis=1, inplace=True)

The input: