Add values of a list to pandas DataFrame columns based on matching value per DataFrame row

Question

I have a pandas DataFrame which looks like this:

            0
2013-09-20  0
2013-09-21  1
2013-09-22  2
2013-09-23  3
2013-09-24  4
2013-09-25  5
2013-09-26  6
2013-09-27  7
2013-09-28  8
2013-09-29  9
...

and a list of lists in the following structure:

[[0, 125.77, 126.28, 125.77, 126.16],
 [3, 126.06, 126.31, 125.84, 125.86],
 [4, 126.04, 126.93, 126.0, 126.93],
 [5, 126.51, 126.51, 126.0, 126.18],
 [6, 127.63, 128.04, 127.57, 128.04],
 [7, 127.58, 127.8, 126.42, 126.94],
 [10, 126.59, 126.77, 125.14, 125.51],
 [11, 125.38, 125.88, 125.38, 125.88],
 [12, 124.3, 124.3, 123.3, 123.81],
 [13, 123.38, 123.55, 123.09, 123.23],
 [14, 122.71, 122.85, 122.5, 122.79],
 [17, 121.19, 121.74, 121.0, 121.74],
 [18, 122.09, 122.09, 121.12, 121.13],
 [19, 123.31, 123.88, 123.17, 123.58],
 [20, 124.57, 125.83, 124.57, 125.83],
 ...

I would like to add the last four values of each list to the DataFrame where the number of the DataFrame's column "0" equals the first value of the respective list in the collection of lists.

E.g.:

            0 1      2      3      4
2013-09-20  0 125.77 126.28 125.77 126.16
2013-09-21  1 nan
2013-09-22  2 nan
2013-09-23  3 126.06 126.31 125.84 125.86
...

Recommendations for renaming the title are very welcome..

trbck
– trbck

2018-09-21 00:19:33 +00:00
Commented Sep 21, 2018 at 0:19 — trbck
– trbck, Commented Sep 21, 2018 at 0:19

sacuL · Accepted Answer · 2018-09-21 00:25:42Z

1

Turn your list into a dataframe and use merge. In this example, I called your list by the variable name mylist:

>>> df.merge(pd.DataFrame(mylist), how='left')
   0       1       2       3       4
0  0  125.77  126.28  125.77  126.16
1  1     NaN     NaN     NaN     NaN
2  2     NaN     NaN     NaN     NaN
3  3  126.06  126.31  125.84  125.86
4  4  126.04  126.93  126.00  126.93
5  5  126.51  126.51  126.00  126.18
6  6  127.63  128.04  127.57  128.04
7  7  127.58  127.80  126.42  126.94
8  8     NaN     NaN     NaN     NaN
9  9     NaN     NaN     NaN     NaN

If you need to specify the column names to merge on, use the arguments left_on=whatever_column_name, right_on=0.

df.merge(pd.DataFrame(mylist), left_on=0, right_on=0,how='left')

In your case, the column name of your original df is 0, so there is no need.

answered Sep 21, 2018 at 0:25

sacuL

51.6k9 gold badges88 silver badges115 bronze badges

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

Collectives™ on Stack Overflow

Add values of a list to pandas DataFrame columns based on matching value per DataFrame row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related