OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Interchange Start and Date and other Values with Earlier Row if Dates greater than 8 in pandas dataframe

  • Thread starter Thread starter Divyank
  • Start date Start date
D

Divyank

Guest
Data:

Code:
# Sample data
data = {
    'Start Date': ['2022-10-18', '2022-10-25', '2023-04-17'],
    'End Date': ['2022-10-20', '2023-04-06', '2023-07-04'],
    'Close1': [17486.95, 17656.35, 17706.85],
    'Close2': [17563.95, 17599.15, 19389.00],
    'NF_BEES1': [0.58, 0.19, 0.12],
    'NF_BEES2': [0.63, 0.75, 0.73],
    'Difference': [77.00, -57.20, 1682.15],
    'Days Difference': [2, 163, 78]
}

# Create DataFrame
df = pd.DataFrame(data)
df

# Convert date columns to datetime
df['Start Date'] = pd.to_datetime(df['Start Date'])
df['End Date'] = pd.to_datetime(df['End Date'])

Output:

Code:
    Start Date  End Date    Close1  Close2  NF_BEES1    NF_BEES2    Difference  Days Difference
0   2022-10-18  2022-10-20  17486.95    17563.95    0.58    0.63    77.00   2
1   2022-10-25  2023-04-06  17656.35    17599.15    0.19    0.75    -57.20  163
2   2023-04-17  2023-07-04  17706.85    19389.00    0.12    0.73    1682.15 78

If Days Difference column > 8 , then create new row in df & End Date of Earlier row should be Start Date in new row & Start Date of current row (Days Difference >8) should be End Date.

My Code (Not working as Expected):

Code:
# Iterate through the DataFrame
for idx, row in df.iterrows():
    rows.append(row)
    if row['Days Difference'] > 8:
        # Create a new row
        new_row = row.copy()
        new_row['Start Date'] = rows[-2]['End Date']
        new_row['Close1'] = rows[-2]['Close2']
        new_row['NF_BEES1'] = rows[-2]['NF_BEES2']
        new_row['End Date'] = row['Start Date']
        new_row['Close2'] = row['Close1']
        new_row['NF_BEES2'] = row['NF_BEES1']
        new_row['Difference'] = (new_row['Close2'] - new_row['Close1'])
        new_row['Days Difference'] = (new_row['End Date'] - new_row['Start Date']).days
        print(new_row)
        print(type(new_row))

# Create a new DataFrame with the split rows
new_df = pd.DataFrame(rows)
new_df

My Output:

Code:
Start Date  End Date    Close1  Close2  NF_BEES1    NF_BEES2    Difference  Days Difference
0   2022-10-18  2022-10-20  17486.95    17563.95    0.58    0.63    77.00   2
1   2022-10-25  2023-04-06  17656.35    17599.15    0.19    0.75    -57.20  163
2   2023-04-17  2023-07-04  17706.85    19389.00    0.12    0.73    1682.15 78

Expected Output:

Code:
Start Date  End Date    Close1  Close2  NF_BEES1    NF_BEES2    Difference  Days Difference
18  2022-10-18  2022-10-20  17486.95    17563.95    0.58    0.63    77.00   2
19  2022-10-20  2022-10-25  17563.95    17656.35    0.63    0.19    93  5
20  2023-04-06  2023-04-17  17599.15    17706.85    0.75    0.12    107 11
<p>Data:</p>
<pre><code># Sample data
data = {
'Start Date': ['2022-10-18', '2022-10-25', '2023-04-17'],
'End Date': ['2022-10-20', '2023-04-06', '2023-07-04'],
'Close1': [17486.95, 17656.35, 17706.85],
'Close2': [17563.95, 17599.15, 19389.00],
'NF_BEES1': [0.58, 0.19, 0.12],
'NF_BEES2': [0.63, 0.75, 0.73],
'Difference': [77.00, -57.20, 1682.15],
'Days Difference': [2, 163, 78]
}

# Create DataFrame
df = pd.DataFrame(data)
df

# Convert date columns to datetime
df['Start Date'] = pd.to_datetime(df['Start Date'])
df['End Date'] = pd.to_datetime(df['End Date'])
</code></pre>
<p>Output:</p>
<pre><code> Start Date End Date Close1 Close2 NF_BEES1 NF_BEES2 Difference Days Difference
0 2022-10-18 2022-10-20 17486.95 17563.95 0.58 0.63 77.00 2
1 2022-10-25 2023-04-06 17656.35 17599.15 0.19 0.75 -57.20 163
2 2023-04-17 2023-07-04 17706.85 19389.00 0.12 0.73 1682.15 78
</code></pre>
<p>If <code>Days Difference</code> column > 8 , then create new row in df & <code>End Date</code> of Earlier row should be <code>Start Date</code> in new row & <code>Start Date</code> of current row (Days Difference >8) should be <code>End Date</code>.</p>
<p>My Code (Not working as Expected):</p>
<pre><code># Iterate through the DataFrame
for idx, row in df.iterrows():
rows.append(row)
if row['Days Difference'] > 8:
# Create a new row
new_row = row.copy()
new_row['Start Date'] = rows[-2]['End Date']
new_row['Close1'] = rows[-2]['Close2']
new_row['NF_BEES1'] = rows[-2]['NF_BEES2']
new_row['End Date'] = row['Start Date']
new_row['Close2'] = row['Close1']
new_row['NF_BEES2'] = row['NF_BEES1']
new_row['Difference'] = (new_row['Close2'] - new_row['Close1'])
new_row['Days Difference'] = (new_row['End Date'] - new_row['Start Date']).days
print(new_row)
print(type(new_row))

# Create a new DataFrame with the split rows
new_df = pd.DataFrame(rows)
new_df
</code></pre>
<p>My Output:</p>
<pre><code>
Start Date End Date Close1 Close2 NF_BEES1 NF_BEES2 Difference Days Difference
0 2022-10-18 2022-10-20 17486.95 17563.95 0.58 0.63 77.00 2
1 2022-10-25 2023-04-06 17656.35 17599.15 0.19 0.75 -57.20 163
2 2023-04-17 2023-07-04 17706.85 19389.00 0.12 0.73 1682.15 78
</code></pre>
<p>Expected Output:</p>
<pre><code>Start Date End Date Close1 Close2 NF_BEES1 NF_BEES2 Difference Days Difference
18 2022-10-18 2022-10-20 17486.95 17563.95 0.58 0.63 77.00 2
19 2022-10-20 2022-10-25 17563.95 17656.35 0.63 0.19 93 5
20 2023-04-06 2023-04-17 17599.15 17706.85 0.75 0.12 107 11
</code></pre>
 

Latest posts

M
Replies
0
Views
1
mckapusta
M
Top