CODEWITHSUNDEEP
pythonpandasindexingmulti-index
Dillinger_92
Dillinger_92

Reputation: 85

Setting value of a column for each multiindex in repeating order

I have a dataframe df with 60 dates, and for each date there are 400 IDs. Looking like this:

 Date       ID
 2018-01-01   1000
 2018-01-01   1001
 ....
 2018-02-01   1000
 2018-02-01   1001

Now what I would like to do is to create a third column. The value of which is the same for all ID's on a given date. I created a seperate one-column dataframe df_value for this that has 60 random generated values in descending order:

     Value 
     350
     340
     337
     323
     ...

In the end it should look like this:

 Date       ID        Value
 2018-01-01   1000     350
 2018-01-01   1001     350
 ....
 2018-02-01   1000     340
 2018-02-01   1001     340

I tried creating a groupby dataframe with Date and ID as indexes to later add the values in the Placeholder column. But couldn't really figure out where to go from there:

                     Placeholder
 Date       ID
 2018-01-01   1000       1
 2018-01-01   1001       1
 ....
 2018-02-01   1000       1
 2018-02-01   1001       1

Probably the groupby idea is a death end. Help is very much appreciated.

Upvotes: 1

Views: 51

Answers (2)

jezrael
jezrael

Reputation: 863166

Use GroupBy.ngroup with Series.map, only necessary default index in df_value:

df['new'] = df.groupby('date').ngroup().map(df_value['value'])

Upvotes: 3

Roberto
Roberto

Reputation: 41

I believe that something like this may work for you:

for i,date in enumerate(df.Date.drop_duplicates()): 
  df.loc[df[df.Date == date].index, 'value'] = value[i]

Upvotes: 0

Related Questions