CODEWITHSUNDEEP
pythonpython-3.xpandasdataframepandas-groupby
user9431057
user9431057

Reputation: 1253

Combine odd and even indexed rows in pandas

I have a data frame df like this,

   Name     Net        Quantity  
0  Auto     1010       10
1  NaN      NaN        12
2  Rtal     4145       18
3  NaN      NaN        14
4  Indl     6223       16
5  NaN      7222       18

My output data frame should be like this,

       Name     Net         Quantity  
    0  Auto     1010        10,12
    1  Rtal     4145        18,14
    2  Indl     6223,7222   16,18

As you can see from above data frame, I want to combine values with even number (index) of rows if there is any values present in odd number (index) so that I can get my output.

I tried the following to extract odd number values,

df.iloc[1::2, :]

this gives me the following,

    Name    Net   Quantity
1   NaN     NaN       12
3   NaN     NaN       14
5   NaN     7222.0    18

after this, I don't now how to add these values to even number indexes to my df. In addition to this, I am not sure if my approach is correct. I would appreciate if anyone could advise/help.

Upvotes: 6

Views: 6722

Answers (3)

smruthika mulakala
smruthika mulakala

Reputation: 21

To fetch even rows: df[df.index.map(lambda x: x%2==0)]

To fetch odd rows: df[df.index.map(lambda x: x%2==1)]

Upvotes: 2

jpp
jpp

Reputation: 164753

One solution is to ffill, then groupby with custom functions. Note the solution is not dependent explicitly on the dataframe index, and therefore will also work for the more general case where you have arbitrary NaN values which can be inferred from a previous row.

# forward fill to remove null values
df = df.ffill(downcast='infer')

# convert to str so you can use str.join later
grp_cols = ['Net', 'Quantity']
df[grp_cols] = df[grp_cols].astype(str)

# apply groupby with custom functions
res = df.groupby('Name').agg({'Net': lambda x: ','.join(np.unique(x)),
                              'Quantity': lambda x: ','.join(x)}).reset_index()

print(res)

#    Name        Net Quantity
# 0  Auto       1010    10,12
# 1  Indl  6223,7222    16,18
# 2  Rtal       4145    18,14

Upvotes: 1

cs95
cs95

Reputation: 402813

If you're okay with floats in 'Net', you can use groupby and agg:

df.groupby(df.index // 2).agg(lambda x: x.dropna().astype(str).str.cat(sep=','))

   Name            Net Quantity
0  Auto         1010.0    10,12
1  Rtal         4145.0    18,14
2  Indl  6223.0,7222.0    16,18

To handle floats, the solution becomes a little more complicated; you can use ffill and attempt to downcast if possible.

df.groupby(df.index // 2).agg(
    lambda x: ','.join(x.ffill(downcast='infer').astype(str).unique()))

   Name        Net Quantity
0  Auto       1010    10,12
1  Rtal       4145    18,14
2  Indl  6223,7222    16,18

Upvotes: 3

Related Questions