CODEWITHSUNDEEP
pythonpandasdataframedictionary
icatalan
icatalan

Reputation: 101

convert a dataframe into dictionary - python

I have a csv file, which looks as follows:

Country/Region  1/22/20     1/23/20     1/24/20
Afghanistan     100         200          300
Albania         400         500           0
Algeria          20          30           70

(The table shows the amount of cases in certain date and country)

I want to convert it into a dictionary in the format shown bellow:

{
"Country1": {"time": [1/22/20, 1/23/20,...], "cases": [0, 0,...],
"Country2": {"time": [1/22/20, 1/23/20,...], "cases": [0, 0,...],
...
}

By using to_dict('records') looks very similar, but not the same.

[{'Country/Region': 'Afghanistan', '1/22/20': 0, '1/23/20': 0, '1/24/20': 0,...}]

I have been trying to use groupby('Country/Region') and it make no sense.

How would you do it?

Upvotes: 1

Views: 273

Answers (2)

Ynjxsjmh
Ynjxsjmh

Reputation: 29982

apply() on row is enough

df.set_index('Country/Region').apply(lambda row: {row.name: {"time": row.index.tolist(), "cases": row.tolist()}}, axis=1).tolist()

'''
[{'Afghanistan': {'time': ['1/22/20', '1/23/20', '1/24/20'], 'cases': [100, 200, 300]}},
 {'Albania': {'time': ['1/22/20', '1/23/20', '1/24/20'], 'cases': [400, 500, 0]}},
 {'Algeria': {'time': ['1/22/20', '1/23/20', '1/24/20'], 'cases': [20, 30, 70]}}]
'''

Upvotes: 0

Mayank Porwal
Mayank Porwal

Reputation: 34046

Use List Comprehension:

In [2017]: d = [{c: {'time': d.columns.tolist(), 'cases': d.values.tolist()[0]}} for c, d in df.set_index(['Country/Region']).groupby('Country/Region')]

In [2018]: d
Out[2018]: 
[{'Afghanistan': {'time': ['1/22/20', '1/23/20', '1/24/20'],
   'cases': [100, 200, 300]}},
 {'Albania': {'time': ['1/22/20', '1/23/20', '1/24/20'],
   'cases': [400, 500, 0]}},
 {'Algeria': {'time': ['1/22/20', '1/23/20', '1/24/20'],
   'cases': [20, 30, 70]}}]

Upvotes: 1

Related Questions