Crate new row depends on 2 columns

Question

After Year row need a new row as Year period if column 1 is year and column3< 2010 then columns values for year period is Below 2010 same as other rows

  Column1  Column2 ColumnX  Column3
0    Year        1       A     2009
1    Date        1       A       12
2    Year        2       A     2021
3    Year        3       A     2011

       Column1  Column2 ColumnX             Column3
0         Year        1       A                2009
1  Year period        1       A           Below2010
2         Date        1       A                  12
3         Year        2       A                2021
4  Year period        2       A           Above2020
5         Year        3       A                2011
6  Year period        3       A  Range in 2010/2020

So what ? Explanations are quite unclear. What have you tried so far ? Post some code. — 0x0fba, Nov 24 '22 at 08:01
def get_period(row): if row["Column1"] == "Year": row["Column1"] = "Year period" if row["Column3"] < 2010: row["Column3"] = "Below2010" elif row["Column3"] > 2020: row["Column3"] = "Above2020" else: row["Column3"] = "Range in 2010/2020" return row s = df.apply(get_period, axis=1).dropna() for i in s.index: upper = df.loc[:i, :] lower = df.loc[i+1:, :] df = pd.concat([upper, s.loc[i:i], lower], axis=0) df = df.reset_index(drop=True) df — Mordor1110, Nov 24 '22 at 08:39

jezrael · Accepted Answer · 2022-11-24T09:28:21.043

0

Filter rows first in boolean indexing for Year columns, replace Column3 in numpy.select and add substring to Column1, last join with original by concat and sort indices by DataFrame.sort_index:

#necessary default RangeIndex
df = df.reset_index(drop=True)

df2 = df[df['Column1'].eq('Year')].copy()
df2['Column3'] = pd.to_numeric(df2['Column3'], errors='coerce')

df1 = (df2.assign(Column3 = lambda x: np.select([x['Column3']<2010, x['Column3']>2020], 
                                                  ['Below2010','Above2020'], 
                                                  default='Range in 2010/2020'),
                    Column1 =  lambda x: x['Column1'] + ' period'))

df = pd.concat([df, df1]).sort_index(kind='mergesort', ignore_index=True)
print (df)
       Column1  Column2 ColumnX             Column3
0         Year        1       A                2009
1  Year period        1       A           Below2010
2         Date        1       A                  12
3         Year        2       A                2021
4  Year period        2       A           Above2020
5         Year        3       A                2011
6  Year period        3       A  Range in 2010/2020

edited Nov 24 '22 at 09:28

answered Nov 24 '22 at 08:05

jezrael

822,522
95
1,334
1,252

Error : < not supported between instances of str and int – Eswar Nov 24 '22 at 08:47
@Eswar - add `df['Column3'] = pd.to_numeric(df['Column3'], errors='coerce')`, answer was eited. – jezrael Nov 24 '22 at 08:48
Please help me with some corrections, – Eswar Nov 24 '22 at 08:56
What if have column3 values as Strings and empty values – Eswar Nov 24 '22 at 08:56
@Eswar - If strings how possible compare years? Need numeric – jezrael Nov 24 '22 at 08:57
@Eswar - Need `df['Column3'] = pd.to_numeric(df['Column3'], errors='coerce')` for numeric from strings and empty values – jezrael Nov 24 '22 at 08:58
Somehows other Column3 values are gone missing – Eswar Nov 24 '22 at 09:09
@Eswar - numeric data are converted wrong to NaNs ? – jezrael Nov 24 '22 at 09:13
Yeah some strings and some numerical values became empty cells – Eswar Nov 24 '22 at 09:18
@Eswar - can you add some example of wrong converting numeric values? – jezrael Nov 24 '22 at 09:18
Data not found, 128, 107.6, world-wide – Eswar Nov 24 '22 at 09:20
@Eswar - I need data like list `print (df['Column3'].tolist())` – jezrael Nov 24 '22 at 09:20
Except years , everything converted into Nan’s – Eswar Nov 24 '22 at 09:23
@Eswar - I convert to numeric only filtered rows, answer was [edited](https://stackoverflow.com/posts/74557470/revisions) – jezrael Nov 24 '22 at 09:28
Can you help me with @. https://stackoverflow.com/questions/74568501/convert-dataframe-into-json-file – Eswar Nov 25 '22 at 06:57

Crate new row depends on 2 columns

1 Answers1