python - Pandas, create dictionary from df, with one column as replacing another

Question

I have an unknown number of DataFrames. two for example:

         date  week_score  daily_score site_name
0  2014-07-04         100           90    demo 2
1  2014-07-05          80           55    demo 2
2  2015-07-06          70           60    demo 2

         date  week_score  daily_score site_name
0  2014-07-04          85          100    demo 1
1  2014-07-05          50           80    demo 1
2  2015-07-06          45           30    demo 1

I know the data frames all have the same shape and columns names.

I want to combine them into a list of dictionaries (df.to_dict(orient='records') but have the site_name as key and to do this for every score.

the desired output is a bit tricky:

{'week_score: [{'date': '2014-07-04', 'demo 2': 100, 'demo 1': 85},
               {'date': '2014-07-05', 'demo 2': 80, 'demo 1': 50}, 
               {'date': '2014-07-06', 'demo 2': 70, 'demo 1': 45}],

'daily_score: [{'date': '2014-07-04', 'demo 2': 90, 'demo 1': 100},
               {'date': '2014-07-05', 'demo 2': 55, 'demo 1': 80}, 
               {'date': '2014-07-06', 'demo 2': 60, 'demo 1': 30}],

}

Answer 1

you can try this code :

d = dict()
for col in df.columns[1:-1].tolist():
    new_df = pd.DataFrame({'date':dfs[0]['date']})
    for df in dfs:
        site_name = df['site_name'].unique()[0]
        dropped = df.drop('site_name',axis='columns')
        new_df[site_name] = df[col]
    d[col] = new_df.to_dict('records')
>>>d

output:

{'week_score': [{'date': '2014-07-04', 'demo1': 85, 'demo2': 100},
  {'date': '2014-07-05', 'demo1': 50, 'demo2': 80},
  {'date': '2015-07-06', 'demo1': 45, 'demo2': 70}],
 'daily_score': [{'date': '2014-07-04', 'demo1': 100, 'demo2': 90},
  {'date': '2014-07-05', 'demo1': 80, 'demo2': 55},
  {'date': '2015-07-06', 'demo1': 30, 'demo2': 60}]}