CODEWITHSUNDEEP
pythonpandasdataframe
frazman
frazman

Reputation: 33223

Creating more rows based on condition in pandas

I have a dataframe like following:

id, index, val_1, val_2
1, 1, 0.2, 0
1, 2, 0.4, 0.2
2,2, 0.1, 0.5
2,4, 0.7, 0.0
....

and so on

Now, the complete range of index values that are allowed for each id is 

[1,2,3,4]

So, if any of this index is missing for each id, I want to add those rows. So for the above example, the desired output is

id, index, val_1, val_2
1, 1, 0.2, 0
1, 2, 0.4, 0.2
1, 3, 0, 0 # added because index 3 was missing for id 1
1, 4, 0, 0  # added because index 4 was missing for id 1
2, 1,0,0  # added because index 1 was missing for id 2
2,2, 0.1, 0.5
2, 3, 0, 0
2,4, 0.7, 0.0
....

How do i perform this in operation in pandas?

Upvotes: 2

Views: 65

Answers (2)

Merlin
Merlin

Reputation: 25639

Try this:

Starting with this a df: 

id  index  val_1  val_2
0   1      1    0.2    0.0
1   1      2    0.4    0.2
2   2      2    0.1    0.5
3   2      4    0.7    0.0

Build New DataFrame:

df2 = pd.DataFrame({'id': np.repeat(df.id.unique(),4),'index': np.asarray([1,2,3,4]*len(df.id.unique()))}, columns = [u'id', u'index', u'val_1', u'val_2']).fillna(0)

Append, drop dups and sort DataFrame: 

dfx = df.append(df2).drop_duplicates(subset=['id', 'index'], keep="first")
dfx.sort_values(['id','index']).reset_index(drop=True)

       id  index  val_1  val_2
    0   1      1    0.2    0.0
    1   1      2    0.4    0.2
    2   1      3    0.0    0.0
    3   1      4    0.0    0.0
    4   2      1    0.0    0.0
    5   2      2    0.1    0.5
    6   2      3    0.0    0.0
    7   2      4    0.7    0.0

df2 looks like this: 

   id  index  val_1  val_2
0   1      1      0      0
1   1      2      0      0
2   1      3      0      0
3   1      4      0      0
4   2      1      0      0
5   2      2      0      0
6   2      3      0      0
7   2      4      0      0

Upvotes: -2

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

try this:

In [210]: from itertools import product

In [211]: x = pd.DataFrame(list(product(df.id.unique(), [1,2,3,4])), columns=['id','index']).assign(val_1=0, val_2=0).set_index(['id','index'])

In [212]: x.update(df.set_index(['id','index']))

In [213]: x
Out[213]:
          val_1  val_2
id index
1  1        0.2    0.0
   2        0.4    0.2
   3        0.0    0.0
   4        0.0    0.0
2  1        0.0    0.0
   2        0.1    0.5
   3        0.0    0.0
   4        0.7    0.0

In [214]: x.reset_index()
Out[214]:
   id  index  val_1  val_2
0   1      1    0.2    0.0
1   1      2    0.4    0.2
2   1      3    0.0    0.0
3   1      4    0.0    0.0
4   2      1    0.0    0.0
5   2      2    0.1    0.5
6   2      3    0.0    0.0
7   2      4    0.7    0.0

Explanation:

In [225]: x = (pd.DataFrame(list(product(df.id.unique(), [1,2,3,4])), columns=['id','index'])
   .....:        .assign(val_1=0, val_2=0)
   .....:        .set_index(['id','index']))

In [226]: x
Out[226]:
          val_1  val_2
id index
1  1          0      0
   2          0      0
   3          0      0
   4          0      0
2  1          0      0
   2          0      0
   3          0      0
   4          0      0

In [227]: x.update(df.set_index(['id','index']))

In [228]: x
Out[228]:
          val_1  val_2
id index
1  1        0.2    0.0
   2        0.4    0.2
   3        0.0    0.0
   4        0.0    0.0
2  1        0.0    0.0
   2        0.1    0.5
   3        0.0    0.0
   4        0.7    0.0

In [229]: x.reset_index()
Out[229]:
   id  index  val_1  val_2
0   1      1    0.2    0.0
1   1      2    0.4    0.2
2   1      3    0.0    0.0
3   1      4    0.0    0.0
4   2      1    0.0    0.0
5   2      2    0.1    0.5
6   2      3    0.0    0.0
7   2      4    0.7    0.0

Upvotes: 3

Related Questions