Repeat pandas rows based on content of a list

user_1243255 0
Viewed 294

I have a large pandas dataframe df as:

Col1    Col2
2       4
3       5

I have a large list as:

['2020-08-01', '2021-09-01', '2021-11-01']

I am trying to achieve the following:

Col1    Col2    StartDate
2       4       8/1/2020
3       5       8/1/2020
2       4       9/1/2021
3       5       9/1/2021
2       4       11/1/2021
3       5       11/1/2021

Basically tile the dataframe df while adding the elements of list as a new column. I am not sure how to approach this?

python-3.8 pandas
5 Answers

Let use list comprehension with assign and pd.concat:

l = ['2020-08-01', '2021-09-01', '2021-11-01']
pd.concat([df1.assign(startDate=i) for i in l], ignore_index=True)

Output:

   Col1  Col2   startDate
0     2     4  2020-08-01
1     3     5  2020-08-01
2     2     4  2021-09-01
3     3     5  2021-09-01
4     2     4  2021-11-01
5     3     5  2021-11-01
user_63615310

You can try a combination of np.tile and np.repeat:

df.loc[np.tile(df.index,len(lst))].assign(StartDate=np.repeat(lst,len(df)))

Output:

   Col1  Col2   StartDate
0     2     4  2020-08-01
1     3     5  2020-08-01
0     2     4  2021-09-01
1     3     5  2021-09-01
0     2     4  2021-11-01
1     3     5  2021-11-01
user_42384080

You can also cross join using merge after creating a df from the list:

l = ['2020-08-01', '2021-09-01', '2021-11-01']

(df.assign(k=1).merge(pd.DataFrame({'StartDate':l, 'k':1}),on='k')
   .sort_values('StartDate').drop("k",1))

   Col1  Col2   StartDate
0     2     4  2020-08-01
3     3     5  2020-08-01
1     2     4  2021-09-01
4     3     5  2021-09-01
2     2     4  2021-11-01
5     3     5  2021-11-01
user_98406370

I would use concat:

df = pd.DataFrame({'col1': [2,3], 'col2': [4, 5]})
dict_dfs = {k: df for k in ['2020-08-01', '2021-09-01', '2021-11-01']}
pd.concat(dict_dfs)

Then you can rename and clean the index.

              col1  col2
2020-08-01 0     2     4
           1     3     5
2021-09-01 0     2     4
           1     3     5
2021-11-01 0     2     4
           1     3     5
user_27839770

I may do itertools, notice the order can be down with sort_values based on column 1

import itertools
df=pd.DataFrame([*itertools.product(df.index,l)]).set_index(0).join(df)
            1  Col1  Col2
0  2020-08-01     2     4
0  2021-09-01     2     4
0  2021-11-01     2     4
1  2020-08-01     3     5
1  2021-09-01     3     5
1  2021-11-01     3     5
user_79645270
Related