CODEWITHSUNDEEP
pythonpandas
Róbert Attila Nagy
Róbert Attila Nagy

Reputation: 35

Pandas Dataframe dictionary of list values to merged rows

I have a dictionary like this:

my_dict = {'Manchester United' : [[2021-04-26, 2], [2021-04-28, 1]], 'Chelsea' : [[2021-04-26, 1], [2021-04-28, 0]], 'Liverpool' : [[2021-04-26, 0], [2021-04-28, 2], [2021-05-02, 1]]}

With Pandas Dataframe I would like to do the following:

enter image description here

How it can be achived?

Thank you.

Upvotes: 1

Views: 37

Answers (2)

Scott Boston
Scott Boston

Reputation: 153460

Use list comprehension with pd.DataFrame constructor:

pd.concat([pd.DataFrame(v, columns = ['Date','Score'], index=[k]*len(v)) for k,v in my_dict.items()])

Output:

                         Date  Score
Manchester United  2021-04-26      2
Manchester United  2021-04-28      1
Chelsea            2021-04-26      1
Chelsea            2021-04-28      0
Liverpool          2021-04-26      0
Liverpool          2021-04-28      2
Liverpool          2021-05-02      1

Upvotes: 1

SeaBean
SeaBean

Reputation: 23217

You can use pd.Series + .explode() + .reset_index(), as follows:

df = pd.Series(my_dict).explode().apply(pd.Series).reset_index()
df.columns = ['Team', 'Date', 'Score']

Result

print(df)

                Team        Date  Score
0  Manchester United  2021-04-26      2
1  Manchester United  2021-04-28      1
2            Chelsea  2021-04-26      1
3            Chelsea  2021-04-28      0
4          Liverpool  2021-04-26      0
5          Liverpool  2021-04-28      2
6          Liverpool  2021-05-02      1

Source Data

Corrected the source data to make it work: modified date in numeric expression (e.g. 2021-04-06 i.e. 2021 - 04 - 06) to string '2021-04-06':

my_dict = {'Manchester United' : [['2021-04-26', 2], ['2021-04-28', 1]], 'Chelsea' : [['2021-04-26', 1], ['2021-04-28', 0]], 'Liverpool' : [['2021-04-26', 0], ['2021-04-28', 2], ['2021-05-02', 1]]}

Upvotes: 1

Related Questions