Merge two 1-Column Dataframes and alternately merge elements

Question

Currently I'm stuck with a bigger Problem. I got a 2 column, 1000 rows Dataframe:

	Food(str)	Cal(str)
1	Apple	0.2
2	Apple	0.25
3	Strwaberry	1.5
4	Hamburger	3
5	Rice	0.007
6	Strawberry	1.4

For my further calculations, I need a non-nested Json Object, which should look like this:

{'Apple' : '0.2' , 'Apple' : '0.25', 'Strawberry' : '1.5', 'Hamburger' : '3', 'Rice' : '0.007', 'Strawberry' : '1.4'}

I've tried achieving this via a pd.groupby previously:

data = data.groupby('Food').sum().T.to_dict(orient="records")[0]

This is not working since it is not taking duplicated foods into account since it will group them and just sum up the Cal's. I need every data pair though.

My try to receive the desired solution currently is to transpose the Df in a way that at the end I only have one row with a 1000 columns to use the pandas .to_json method to get the desired result.

	1	2	3	4	5	6	7	8	9	10	11	12
Food/Cal	Apple	0.2	Apple	0.25	Strwaberry	1.5	Hamburger	3	Rice	0.007	Strawberry	1.4

My attemt to get this Df was the followig but did not work

    dataFood = data['Food']
    dataFood = dataFood.reset_index()
    dataFood = dataFood.T
    datacal = data['Cal']
    datacal = datacal.reset_index()
    datacal = datacal.T
   
    a = pd.DataFrame([1], columns=['delete'])

    for c1 in dataFrom:
        for c2 in dataprice:
            a = pd.concat([dataFood.iloc[0, c1], datacal.iloc[0, c2]])

Error:

TypeError: cannot concatenate object of type '<class 'int'>'; only Series and DataFrame objs are valid

Does anyone know how to approach this problem?

Thank you for the feedback in advance!

So just to check I’m getting this right − you explicitly want duplicate keys in your output json format? (See [this question](https://stackoverflow.com/questions/21832701/does-json-syntax-allow-duplicate-keys-in-an-object#23195243) on why that’s not recommended) — Cimbali, Jul 07 '21 at 12:51
You need to remember that JSON works like dicts, even you can write duplicate keys, just the second will be considered on execution. A possibility is get a list of values for the same key like `{ "apple": [0.2 ,0.25] }`, but i think that's not you want. — L.Stefan, Jul 07 '21 at 12:56

score 0 · Answer 1 · answered Jul 07 '21 at 13:01

Independently of the nature of dict that takes only unique keys, your proposed approach anyways explicitly groups the values by key as you are doing a groupby. You should have used instead data.set_index('Food')['Cal'].to_dict(). Anyway, as pointed out by @Cimbali, json keys should be unique. This is actually what you get when trying to convert directly to json:

>>> data.set_index('Food(str)')['Cal(str)'].to_json()

ValueError: Series index must be unique for orient='index'

score 0 · Accepted Answer · answered Jul 07 '21 at 14:45

Thanks for the suggestions so far, i managed to make it work with this

dict = data.to_dict(orient="records")
    my_dict = '{'
    for i in range(len(dict)):
        key = str(dict[i].values())
        my_dict += '"' + key[1:9] + '": "' + key[10:18] + '", '
    my_dict = my_dict[:len(my_dict)-2] + '}'
    my_dict = json.loads(my_dict)

Merge two 1-Column Dataframes and alternately merge elements

2 Answers2