Sorting Pandas dataframe based on a list value on a per row basis

Question

This dataframe has the columns ["Monthly","Weekly","Daily"] and each row is comprised of lists or None type.

How do I align the list entries with the correct columns?

For example on row 1 the lists containing the daily and monthly strings should be swapped with each other. On row 0 the list should be moved to the Daily column. Weekly is correct for both rows.

Data

{'Monthly': {0: [1.352129999999999, '20', 'daily'], 1: [1.344164999999999, '12', 'daily']}, 'Weekly': {0: [1.35305, '12', 'weekly'], 1: [1.34481, '11', 'weekly']}, 'Daily': {0: None, 1: [1.3449900000000001, '4', 'monthly']}}

piRSquared · Accepted Answer · 2021-03-16 23:56:28Z

4

Recreate your data

dat = {i: {f: [*_, f] for *_, f in filter(None, lists)} for i, *lists in df.itertuples()}

pd.DataFrame.from_dict(dat, 'index').mask(pd.isna, None)

                            daily                 weekly                           monthly
0  [1.352129999999999, 20, daily]  [1.35305, 12, weekly]                              None
1  [1.344164999999999, 12, daily]  [1.34481, 11, weekly]  [1.3449900000000001, 4, monthly]

Just noticed that column names weren't capitalized.

dat = {i: {f.title(): [*_, f] for *_, f in filter(None, lists)} for i, *lists in df.itertuples()}

pd.DataFrame.from_dict(dat, 'index').mask(pd.isna, None)

                            Daily                 Weekly                           Monthly
0  [1.352129999999999, 20, daily]  [1.35305, 12, weekly]                              None
1  [1.344164999999999, 12, daily]  [1.34481, 11, weekly]  [1.3449900000000001, 4, monthly]

edited Mar 16, 2021 at 23:56

answered Mar 16, 2021 at 21:37

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BENY · Accepted Answer · 2021-03-16 21:45:02Z

3

Why not use the last item in the list recreate the data frame

#df = pd.DataFrame(d)
out = df.applymap(lambda x: {x[-1]: x} if x is not None else None).stack().reset_index(level=1,drop=True).apply(pd.Series).groupby(level=0).first()
Out[276]: 
                            daily  ...                           monthly
0  [1.352129999999999, 20, daily]  ...                               NaN
1  [1.344164999999999, 12, daily]  ...  [1.3449900000000001, 4, monthly]
[2 rows x 3 columns]

answered Mar 16, 2021 at 21:45

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Sorting Pandas dataframe based on a list value on a per row basis

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related