[FIXED] How tranpose the data of more than 1 cell to only 1 cell on google sheets?

Issue

I have the following column :

A1
Miu Kei Yuk
3354
of
2018
8 April 2022

and need to separate it into 3 columns : 3354 of 2018 Miu Kei Yuk 8 April 2022

So the first and the fifth row are correct, but the row number 2, 3 and 4 needs to be in only 1 cell.

I tried transpose(A1:A5) but iu results in :

Miu Kei Yuk domingo, março 07, 1909 of  2018    8 April 2022

Is it possible to do it ?

Solution

try:

={A2&" "&A3&" "&A4, A1, A5}

or:

={A2&" "&A3&" "&A4\ A1\ A5}

Answered By - player0

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)

Read More

[FIXED] How do I make RoR select option name and id concatenations of other fields?

Issue

I”m using Rails 4.2.3. I have a relation that returns certain columns …

MyObject.joins(:distance_unit)
    .where(["user_id = ?", user.id])
    .select("distance, distance_units.id, distance_units.abbrev")

This data is meant to be used in a select menu …

<%= select_tag "distance", options_from_collection_for_select(@distance_options, "distance", "distance") %>

However, what I want is for the id to be a concatenation of the “distance” and “distance_units.id” fields and the option name to be a concatenation of the “distance” and “distance_unit.abbrev” fields. How do I do that in Rails?

Solution

• You can do it with options_for_select, like this:

  <%= select_tag "distance", options_for_select(@distance_options.collect{|obj| ["#{obj.distance}_#{obj.id}", "#{obj.distance}_#{obj.abbrev}"]}) %>
  

Answered By - Devd

Answer Checked By - Mildred Charles (PHPFixing Admin)

Read More

[FIXED] How To Concatenate multiple non blank cells contents into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?

Issue

I'm facing this problem:

In Column C I need to:

• concatenate each vertical non-blank cells groups from Column A (ignoring the blank cells groups in between) AND,
• only concatenate them once (no duplicate smaller groups in-between) AND,
• skip "mono-cell" instances.

Problem Illustration:

Text Table for easy copying:

Column A	Column B	Column C
AA	1	AABBCC
BB	1
CC	1
	0
	0
DD	1	DDEEFF
EE	1
FF	1
	0
GG	1	GGHH
HH	1
	0
II	1	IIJJKKLLMM
JJ	1
KK	1
LL	1
MM	1
	0
NN	1
	0
	0
OO	1	OOPPQQ
PP	1
QQ	1

So far I found this convoluted solution:

In Column A I have "vertical groups" of cells with content separated by vertical intermediary blank cells.

In Column B I have 0s for corresponding Column A blank cells and 1s for corresponding Column A non blank cells using this pull-down formula:

=if(A2<>"",1,0)

In Column C I have the following "2 Steps" 2nd pull-down Formula:

=IFERROR(IFS(AND(B1<>1,product(B2:B14)=1),concatenate(A2:A14),AND(B1<>1,product(B2:B13)=1),concatenate(A2:A13),AND(B1<>1,product(B2:B12)=1),concatenate(A2:A12),AND(B1<>1,product(B2:B11)=1),concatenate(A2:A11),AND(B1<>1,product(B2:B10)=1),concatenate(A2:A10),AND(B1<>1,product(B2:B9)=1),concatenate(A2:A9),AND(B1<>1,product(B2:B8)=1),concatenate(A2:A8),AND(B1<>1,product(B2:B7)=1),concatenate(A2:A7),AND(B1<>1,product(B2:B6)=1),concatenate(A2:A6),AND(B1<>1,product(B2:B5)=1),concatenate(A2:A5),AND(B1<>1,product(B2:B4)=1),concatenate(A2:A4),AND(B1<>1,product(B2:B3)=1),concatenate(A2:A3),AND(B1<>1,product(C2)=1),""),"")

It works but I'm forced to skip a row to first input the cells content starting in cells A2/B2, and it uses 2 steps as 2nd drawback in Column C.

Would anyone offer a simpler and direct solution? Your help is much appreciated.

Solution

try:

=INDEX(LAMBDA(z, IFNA(VLOOKUP(z, LAMBDA(x, {INDEX(SPLIT(x, " "),,1), 
 SUBSTITUTE(IF(INDEX(SPLIT(x, " "),,2)<>"", x, ), " ", )})
 (FLATTEN(SPLIT(QUERY(IF(z="", "​", z),,9^9), "​"))), 2, )))
 (SUBSTITUTE(A2:INDEX(A:A, MAX((A:A<>"")*ROW(A:A))), " ", CHAR(9))))

Answered By - player0

Answer Checked By - Timothy Miller (PHPFixing Admin)

Read More

[FIXED] How To Concatenate 1 to 13/any non blank cells groups into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?

Issue

My new problem is identical to my previous question asked and kindly answered by @player0 and @TheMaster here:

How To Concatenate multiple non blank cells contents into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?

It is identical in all but the size/length of the non blank cells groups to output. Previously I asked to omit single cells groups as they wouldn't have any subordinate cells content to concatenate with. But that was a mistake as they'd still require laborious manual extraction one-by-one if omitted.

So the new problem is as follow:

In Column C I need to:

• concatenate each vertical non-blank cells groups from Column A (ignoring the blank cells groups in between) AND,
• only concatenate them once (no duplicate smaller groups in-between) AND,
• Any or from 1 to 13 at least groups sizes (that's the only difference needed).

Single Cell Groups remaining issue:

I've looked at new Functions REDUCE and LAMBDA but still need to work on grasping them. @TheMaster thanks for your suggestion, I've tested it but it's not returning any value for the single cell groups (please see the screenshot row 20, E20 doesn't return A20 value. What would be the fix you had in mind (I'm not sure what's the controlling element for the number of row/cells to modify) Thanks again!

Your Formula Tested:

@player0, thanks too for the reply and narrowing down of the formula, though the 2nd shared screenshot appears to be same as 1st one, can't see the change). I reproduced a simplified version of my dataset with the issue in next screenshots and below Text Table:

Your Formula In New Test:

My Regex Formula (To Output Only the Cells With 1s Word followed by 2 whitespaces):

=Arrayformula(if(regexmatch(A1:A,"^(\w+)(\s\s)"),A1:A,""))

My Regex Formula Reversed (To Output the other Cells):

=Arrayformula(if(regexmatch(A1:A,"^(\w+)(\s\s)")=FALSE,A1:A,""))

How To Concatenate multiple non blank cells contents into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?

Text Table Simplified Dataset:

Column A	Column B	Column C	Column D	Column E
A			A
ONE 1, 2, 3, 4, 5, 6, 7, 8,		ONE 1, 2, 3, 4, 5, 6, 7, 8,
&1, 2, 3, 4, 5, 6			&1, 2, 3, 4, 5, 6
TWO 1, 2, 3, 4, 5, 6, 7, 8,		TWO 1, 2, 3, 4, 5, 6, 7, 8,
&1, 2, 3, 4, 5			&1, 2, 3, 4, 5
THREE 1, 2, 3, 4, 5, 6, 7,		THREE 1, 2, 3, 4, 5, 6, 7,
&1, 2, 3, 4, 5, 6, 7,			&1, 2, 3, 4, 5, 6, 7, "	&1, 2, 3, 4, 5, 6, 7,&&1, 2, 3, 4, 5, 6, 7, 8&&&1, 2, 3"
&&1, 2, 3, 4, 5, 6, 7, 8			&&1, 2, 3, 4, 5, 6, 7, 8
&&&1, 2, 3			&&&1, 2, 3
FOUR 1, 2, 3, 4, 5, 6, 7,		FOUR 1, 2, 3, 4, 5, 6, 7,
One, Two, Three, For, Five			One, Two, Three, For, Five
FIVE 1, 2, 3, 4, 5, 6, 7,		FIVE 1, 2, 3, 4, 5, 6, 7,
&1, 2, 3, 4, 5,			&1, 2, 3, 4, 5, "	&1, 2, 3, 4, 5,&&1, 2, 3, 4, 5, 6, 7,&&&1, 2, 3, 4, 5, 6,&&&&1, 2, 3"
&&1, 2, 3, 4, 5, 6, 7,			&&1, 2, 3, 4, 5, 6, 7,
&&&1, 2, 3, 4, 5, 6,			&&&1, 2, 3, 4, 5, 6,
&&&&1, 2, 3			&&&&1, 2, 3

Solution

try:

=INDEX(LAMBDA(z, IFNA(VLOOKUP(z, LAMBDA(x, {INDEX(SPLIT(x, " "),,1), 
 SUBSTITUTE(x, " ", )})
 (FLATTEN(SPLIT(QUERY(IF(z="", "​", z),,9^9), "​"))), 2, )))
 (SUBSTITUTE(A2:INDEX(A:A, MAX((A:A<>"")*ROW(A:A))), " ", CHAR(9))))

Answered By - player0

Answer Checked By - Cary Denson (PHPFixing Admin)

Read More

[FIXED] How to fix an a concatenate formula conditional to a non blank cell

Issue

I'm trying to concatenate cells from columns C, D & E as long as Cell A is not blank (has a value)

Cell A is a formula based on listing weekdays:

=IF(WORKDAY(A$28-1;ROW(21:21))>A$29;"";WORKDAY(A$28-1;ROW(21:21)))

My concatenate formula is as follows:

=IF(ISBLANK(A24);"";CONCATENATE(E24;CHAR(10);D24;CHAR(10);C24))

The formula is still concatenating even if Cell A is blank

snapshot of excel

Solution

Blank

Blank (Empty Cell) means 'nothing' inside the cell, no formula no value i.e. a cell containing an Empty String ("") is not blank. As soon as you have put a formula or any value into a cell, it isn't blank anymore.

You should use this formula:

=IF(A24="";"";CONCATENATE(E24;CHAR(10);D24;CHAR(10);C24))

where A24="" includes blank cells, so you don't have to use ISBLANK, too.

Answered By - VBasic2008

Answer Checked By - Marie Seifert (PHPFixing Admin)

Read More

[FIXED] How can I use multiple substring functions in an update statement?

Issue

I have a table with a column called candidate_name in which the names are formatted as lastname, firstname. I'd like to update the column so that the names are in firstname lastname format.

I wrote this query to see if the string functions were working correctly:

SELECT 
    SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1) AS last,
    SUBSTRING(candidate_name, LOCATE(',', candidate_name) + 2, LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1) AS first
FROM 
    colorado_project.candidates
WHERE 
    candidate_name = 'AHREND, JARED';

This returns the strings into new columns as expected:

But I don't know how to use these in an update statement. I've tried many statements similar to this:

UPDATE candidates
SET candidate_name = SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1),
                     SUBSTRING(candidate_name, LOCATE(',', candidate_name) + 2,
        LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1)
WHERE candidate_name = 'AHREND, JARRED';

Pretty sure I've gotten every error known to mankind at this point, but the main error I get is

'1292 Truncated incorrect DOUBLE value 'AHREND'.

I've searched around and it seems like this error is often associated with comparing a string value to a number value, but I don't understand by the substring functions would be returning a number value now, when they were returning strings before? Or maybe that's not whats going on here. Any help would be appreciated. Thanks!

Solution

You must concatenate the 2 parts of the name:

UPDATE candidates
SET candidate_name = CONCAT( 
  SUBSTRING(candidate_name, LOCATE(',', candidate_name) + 2, LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1),
  ' ',
  SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1)
)
WHERE candidate_name = 'AHREND, JARED';

See the demo.

or, simpler with SUBSTRING_INDEX():

UPDATE candidates
SET candidate_name = CONCAT( 
  TRIM(SUBSTRING_INDEX(candidate_name, ',', -1)),
  ' ',
  SUBSTRING_INDEX(candidate_name, ',', 1)
)
WHERE candidate_name = 'AHREND, JARED';

See the demo.

Answered By - forpas

Answer Checked By - Senaida (PHPFixing Volunteer)

Read More

[FIXED] How can I create a new column and concatenate some values using php Excel?

Issue

I want to read an excel file and add a new column to the left of the first column on each sheet using PHP. (The excel file can have as many as 10 sheets)

2. concatenate "AFS-" to the data in each row of the first column and store the new string in the new column to the left.

Eg. sheet 1

Solution

To insert a single new column before column A:

$objPHPExcel->getActiveSheet()->insertNewColumnBefore('A', 1);

Then you can either insert the new values for cells in column 'A' as a formula:

for($row = 2; $row = 20; $row++) {
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A'.$row, '="AFS-" && B'.$row);
}

or as an absolute value

for($row = 2; $row = 20; $row++) {
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A'.$row, 'AFS-' . $objPHPExcel->getActiveSheet()->getCell("B".$row)->getValue());
}

To do this for all sheets, just iterate over the sheets

EDIT

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    ...
}

and then reference $worksheet instead of $objPHPExcel->getActiveSheet()

Answered By - Mark Baker

Answer Checked By - Candace Johnson (PHPFixing Volunteer)

Read More

[FIXED] How to combine two columns into one and ignore null values - Oracle SQL developer

Issue

I'm using Oracle SQL Developer and i have the following select:

SELECT a1.numero_brevetto,
         a1.titolo,
         a2.descrizione,
         a1.data_scadenza_brevetto,
         listagg(a3.organizzazione, ', ') within group (order by a3.organizzazione) organizzazione_1,
         listagg(a4.ragione_sociale, ', ' ) within group (order by a4.ragione_sociale) organizzazione_2
    FROM b_brevetto a1
         FULL OUTER JOIN b_tipo_brevetto a2
            ON a1.tipo_brevetto = a2.tipo_brevetto
         LEFT JOIN b_brevetto_titolari a3
            ON a1.codice_brevetto = a3.codice_brevetto
         LEFT JOIN b_ditta a4
            on a3.codice_ditta=a4.codice_ditta
   WHERE a3.corrente = 1 AND a1.numero_brevetto = 'BREVETTO10000'
GROUP by a1.numero_brevetto,
         a1.titolo,
         a2.descrizione,
         a1.data_scadenza_brevetto          
ORDER BY a1.numero_brevetto

The output is:

NUMERO_BREVETTO        TITOLO                        DESCRIZIONE                   DATA_SCAD       ORGANIZZAZIONE_1                       ORGANIZZAZIONE_2                                                                                                                                                                                         
---------------------- ----------------------------- ----------------------------- --------------- -------------------------------------- --------------------------------------------
BREVETTO10000          asdsad                        Brevetto italiano             18-FEB-39       (null)                                 BAYER S.P.A.

Starting from the previous select, I want to concatenate the values of the column Organizzazione_1 with the values of Organizzazione_2. i want to obtain the following output:

NUMERO_BREVETTO        TITOLO                        DESCRIZIONE                   DATA_SCAD       ORGANIZZAZIONE_1_ORGANIZZAZIONE_2                                                                                                                                                                                                               
---------------------- ----------------------------- ----------------------------- --------------- -------------------------------------- --------------------------------------------
BREVETTO10000          asdsad                        Brevetto italiano             18-FEB-39       BAYER S.P.A.

I tried with the CONCAT function:

SELECT a1.numero_brevetto,
         a1.titolo,
         a2.descrizione,
         a1.data_scadenza_brevetto,
         concat(concat(listagg(a3.organizzazione, ', ') within group (order by a3.organizzazione), ', '),
         listagg(a4.ragione_sociale, ', ' ) within group (order by a4.ragione_sociale)) titolari
    FROM b_brevetto a1
         FULL OUTER JOIN b_tipo_brevetto a2
            ON a1.tipo_brevetto = a2.tipo_brevetto
         LEFT JOIN b_brevetto_titolari a3
            ON a1.codice_brevetto = a3.codice_brevetto
         LEFT JOIN b_ditta a4
            on a3.codice_ditta=a4.codice_ditta
   WHERE a3.corrente = 1 AND a1.numero_brevetto = 'BREVETTO10000'
GROUP by a1.numero_brevetto,
         a1.titolo,
         a2.descrizione,
         a1.data_scadenza_brevetto          
ORDER BY a1.numero_brevetto

but i have this output:

NUMERO_BREVETTO        TITOLO                        DESCRIZIONE                   DATA_SCAD       ORGANIZZAZIONE_1_ORGANIZZAZIONE_2                                                                                                                                                                                                               
---------------------- ----------------------------- ----------------------------- --------------- -------------------------------------- --------------------------------------------
BREVETTO10000          asdsad                        Brevetto italiano             18-FEB-39       , BAYER S.P.A.

I wuold like to find a way to remove the extra comma near "BAYER S.P.A." when the column ORGANIZZAZIONE_1 has NULL value (how in this case). Moreover i wuold also remove the extra comma if column_ORGANIZZAZIONE_1 has had NOT NULL values and column ORGANIZZAZIONE_" all NULL VALUE, for example:

NUMERO_BREVETTO        TITOLO                        DESCRIZIONE                   DATA_SCAD       ORGANIZZAZIONE_1                       ORGANIZZAZIONE_2                                                                                                                                                                                         
---------------------- ----------------------------- ----------------------------- --------------- -------------------------------------- --------------------------------------------
BREVETTO20000          asdsad                        Brevetto italiano             18-FEB-59       PFIZER S.P.A.                         (null)

I want to have:

NUMERO_BREVETTO        TITOLO                        DESCRIZIONE                   DATA_SCAD       ORGANIZZAZIONE_1_ORGANIZZAZIONE_2                                                                                                                                                                                                               
---------------------- ----------------------------- ----------------------------- --------------- -------------------------------------- --------------------------------------------
BREVETTO10000          asdsad                        Brevetto italiano             18-FEB-39       PFIZER S.P.A.

Finally i want to mantain the comma in the case both column have values:

NUMERO_BREVETTO        TITOLO                        DESCRIZIONE                   DATA_SCAD       ORGANIZZAZIONE_1                       ORGANIZZAZIONE_2                                                                                                                                                                                         
---------------------- ----------------------------- ----------------------------- --------------- -------------------------------------- --------------------------------------------
BREVETTO10000          asdsad                        Brevetto italiano             18-FEB-39       PFIZER S.P.A.                          BAYER S.P.A., TEVA S.P.A.

I want to have:

NUMERO_BREVETTO        TITOLO                        DESCRIZIONE                   DATA_SCAD       ORGANIZZAZIONE_1_ORGANIZZAZIONE_2                                                                                                                                                                                                               
---------------------- ----------------------------- ----------------------------- --------------- -------------------------------------- --------------------------------------------
BREVETTO10000          asdsad                        Brevetto italiano             18-FEB-39       PFIZER S.P.A., BAYER S.P.A., TEVA S.P.A.

Pls help me P.S: Sorry for my english

Solution

There is multiple ways to do this:

case when listagg(a3.organizzazione, ', ') within group (order by a3.organizzazione) is not null 
     then  listagg(a3.organizzazione, ', ') within group (order by a3.organizzazione) || ',' 
end
||
listagg(a4.ragione_sociale, ', ' ) within group (order by a4.ragione_sociale)

or simplest way is to use the trim as follows:

trim(both ',' from
listagg(a3.organizzazione, ', ') within group (order by a3.organizzazione)
|| ','
|| listagg(a4.ragione_sociale, ', ' ) within group (order by a4.ragione_sociale)
)

Answered By - Popeye

Answer Checked By - Willingham (PHPFixing Volunteer)

Read More

[FIXED] How to print a loop's result horizontally with a string in python?

Issue

I want to print a loop's result horizontally with a string and a variable.

My code

total = 0
for i in range(2, 6):
    total += i
    print(i, end=' ', 'sum = {}'.format(total))

The output I want:

2 3 4 5 sum=14

Solution

You need to put the print of the sum outside the loop

total = 0
for i in range(2, 6):
    total += i
    print(i, end=' ')
print('sum = {}'.format(total))

Output

2 3 4 5 sum = 14

Answered By - Leo Arad

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

[FIXED] how to concatenate multiple rows into one

Issue

Using SQL 2008 R2 I have two tables and want to create a table like:

output

Input tables input table 1

inout table 2

I have tried using stuff function as

SELECT o.DEPT_ID,o.CLIENT_ID,
       code,
       (STUFF((SELECT CAST(', ' + CODE AS VARCHAR(MAX)) 
         FROM ORDERS
         WHERE (o.FUNDER_ID = f.FUNDER_ID) 
         FOR XML PATH ('')), 1, 2, '')) AS funder_code
FROM FUNDERS f
join ORDERS o on o.FUNDER_ID=f.FUNDER_ID 
where o.DEPT_ID=111 and CLIENT_ID='B001'

and I'm not getting the output.

Solution

First of all, your desired output appears to have the 'name' column from your second input table as a comma-separated list, but your code implies that you want the 'code' column concatenated instead. This solution concatenates the 'name' column.

Second, looking at your input tables, you can't directly use join ORDERS o on o.FUNDER_ID=f.FUNDER_ID because 'B0000000019' does not equal 'F19'. However, once you manipulate those columns so they could be joined, try this:

SELECT DISTINCT o.dept_id, o.client_id
    ,(STUFF((SELECT distinct CAST(', ' + name AS VARCHAR(MAX)) 
         FROM FUNDERS f
         JOIN ORDERS o2 ON o2.funder_id = f.funder_id
         FOR XML PATH ('')), 1, 2, '')) AS funder_code
FROM ORDERS o

Answered By - cpalmer

Answer Checked By - Mildred Charles (PHPFixing Admin)

Read More

[FIXED] How to combine several images to one image in a Grid structure in Python?

Issue

I have several images (PNG format) that I want to combine them into one image file in a grid structure (in such a way that I can set the No. of images shown in every row). Also, I want to add small empty space between images.

For example, assume that there are 7 images. And I want to set the No. of images shown in every row as 3. The general structure of the combined image will be:

Please let me know if you know a good way to do that (preferably using PIL/Pillow or matplotlib libraries). Thanks.

Solution

You can pass to the combine_images function number of expected columns, space between images in pixels and the list of images:

from PIL import Image


def combine_images(columns, space, images):
    rows = len(images) // columns
    if len(images) % columns:
        rows += 1
    width_max = max([Image.open(image).width for image in images])
    height_max = max([Image.open(image).height for image in images])
    background_width = width_max*columns + (space*columns)-space
    background_height = height_max*rows + (space*rows)-space
    background = Image.new('RGBA', (background_width, background_height), (255, 255, 255, 255))
    x = 0
    y = 0
    for i, image in enumerate(images):
        img = Image.open(image)
        x_offset = int((width_max-img.width)/2)
        y_offset = int((height_max-img.height)/2)
        background.paste(img, (x+x_offset, y+y_offset))
        x += width_max + space
        if (i+1) % columns == 0:
            y += height_max + space
            x = 0
    background.save('image.png')


combine_images(columns=3, space=20, images=['apple_PNG12507.png', 'banana_PNG838.png', 'blackberry_PNG45.png', 'cherry_PNG635.png', 'pear_PNG3466.png', 'plum_PNG8670.png', 'strawberry_PNG2595.png'])

Result for 7 images and 3 columns:

Result for 6 images and 2 columns:

Answered By - Alderven

Answer Checked By - Robin (PHPFixing Admin)

Read More

[FIXED] How to replicate DataFrame.append of pandas.Series by using pandas.concat?

Issue

I have a code in which I create a pivot table, I apply a function to each column, and I append the result as a row to the dataframe. The code behaves like this:

>>> import pandas as pd
>>> df = pd.DataFrame({
... 'id':['A','B','C','A','B','C'],
... 'par':['x','x','x','y','y','y'],
... 'val':[1,2,3,4,5,6]})
>>> pv = df.pivot_table(index="id", columns='par', values="val", aggfunc="sum")
>>> avg = pv.mean().rename('MyFunc')
>>> pv.append(avg)
par       x    y
id
A       1.0  4.0
B       2.0  5.0
C       3.0  6.0
MyFunc  2.0  5.0

Which is the output I expect. However, documentation of pandas.DataFrame.append says:

Deprecated since version 1.4.0: Use concat() instead. For further details see Deprecated DataFrame.append and Series.append

When I try to replicate this with pandas.concat I get a different output

>>> pd.concat([pv, avg])
     x    y    0
A  1.0  4.0  NaN
B  2.0  5.0  NaN
C  3.0  6.0  NaN
x  NaN  NaN  2.0
y  NaN  NaN  5.0
>>> pd.concat([pv, avg], axis=1)
     x    y  MyFunc
A  1.0  4.0     NaN
B  2.0  5.0     NaN
C  3.0  6.0     NaN
x  NaN  NaN     2.0
y  NaN  NaN     5.0

Is it possible to achieve the same result of append by using concat?

Solution

If append Series is possible use DataFrame.loc:

pv.loc['MyFunc'] = pv.mean()
print (pv)
par       x    y
id              
A       1.0  4.0
B       2.0  5.0
C       3.0  6.0
MyFunc  2.0  5.0

Or concat with convert Series to one row DataFrame by Series.to_frame and transpose:

print (avg.to_frame().T)
par       x    y
MyFunc  2.0  5.0

df = pd.concat([pv, avg.to_frame().T])
print (df)
par       x    y
A       1.0  4.0
B       2.0  5.0
C       3.0  6.0
MyFunc  2.0  5.0

Answered By - jezrael

Answer Checked By - Senaida (PHPFixing Volunteer)

Read More

[FIXED] Why does when I append the textbox value to the new message it gives me incorrect display?

Issue

I wish to append the message of the sender and the message of the receiver. However when I used

Me.MainDisplay.Text = Me.MainDisplay.Text & MessageToDisplay & Environment.NewLine 

The messages do not append instead it displays incorrectly. For example I send

1
2
3

it will display

1
1
2
1
2
3

I intend my program to display what I have sent and add to the list the next message that I will be sending or the message that the receiver will reply. Please help.

For your further reference you may refer to the below codes:

For Each filename In SortedFilenames
    If filename.Folder = "User" Then
        RecipientFolder = "C:\Users\bele\Desktop\" + CurrentUser + "'sInbox\" + CurrentRecipient + "'sFolder\"
    ElseIf filename.Folder = "Recipient" Then
        RecipientFolder = "C:\Users\bele\Desktop\" + CurrentRecipient + "'sInbox\" + CurrentUser + "'sFolder\"
    End If

    FileContents = My.Computer.FileSystem.ReadAllText(RecipientFolder + filename.Id.ToString("MMddyyyyhhmmss") + ".txt")

    If MessageToDisplay = "" Then
        If filename.Folder = "User" Then
            MessageToDisplay = CurrentRecipient + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
        ElseIf filename.Folder = "Recipient" Then
            MessageToDisplay = CurrentUser + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
        End If

    Else
        If filename.Folder = "User" Then
            MessageToDisplay = MessageToDisplay + Environment.NewLine + CurrentRecipient + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
        ElseIf filename.Folder = "Recipient" Then
            MessageToDisplay = MessageToDisplay + Environment.NewLine + CurrentUser + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
        End If

    End If
    Me.MainDisplay.Text = Me.MainDisplay.Text + MessageToDisplay & Environment.NewLine

    My.Computer.FileSystem.MoveFile(RecipientFolder + filename.Id.ToString("MMddyyyyhhmmss") + ".txt",
                                    "C:\Users\bele\Desktop\" + User + "'sInbox\" + User + "'sReadMessage\" & filename.Id.ToString("MMddyyyyhhmmss") + ".txt")
Next

Please help

Solution

Change:

If MessageToDisplay = "" Then
    If filename.Folder = "User" Then
        MessageToDisplay = CurrentRecipient + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
    ElseIf filename.Folder = "Recipient" Then
        MessageToDisplay = CurrentUser + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
    End If

Else
    If filename.Folder = "User" Then
        MessageToDisplay = MessageToDisplay + Environment.NewLine + CurrentRecipient + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
    ElseIf filename.Folder = "Recipient" Then
        MessageToDisplay = MessageToDisplay + Environment.NewLine + CurrentUser + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
    End If

End If
Me.MainDisplay.Text = Me.MainDisplay.Text + MessageToDisplay & Environment.NewLine

To:

If filename.Folder = "User" Then
    MessageToDisplay = CurrentRecipient + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
ElseIf filename.Folder = "Recipient" Then
    MessageToDisplay = CurrentUser + ": " + FileContents + Environment.NewLine + filename.Id.ToString("MM/dd/yyyy hh:mm:ss")
End If

Me.MainDisplay.Text = Me.MainDisplay.Text + MessageToDisplay & Environment.NewLine

Answered By - Idle_Mind

Answer Checked By - Gilberto Lyons (PHPFixing Admin)

Read More

[FIXED] How do I concatenate multiple C++ strings on one line?

Issue

C# has a syntax feature where you can concatenate many data types together on 1 line.

string s = new String();
s += "Hello world, " + myInt + niceToSeeYouString;
s += someChar1 + interestingDecimal + someChar2;

What would be the equivalent in C++? As far as I can see, you'd have to do it all on separate lines as it doesn't support multiple strings/variables with the + operator. This is OK, but doesn't look as neat.

string s;
s += "Hello world, " + "nice to see you, " + "or not.";

The above code produces an error.

Solution

#include <sstream>
#include <string>

std::stringstream ss;
ss << "Hello, world, " << myInt << niceToSeeYouString;
std::string s = ss.str();

Take a look at this Guru Of The Week article from Herb Sutter: The String Formatters of Manor Farm

Answered By - Paolo Tedesco

Answer Checked By - David Goodson (PHPFixing Volunteer)

Read More

[FIXED] How Can I concatenate a parameter string in javascript function on string in appended html?

Issue

This is my code for html

 <div id="content"></div> 

Then I append an inpunt to #content:

 $( document ).ready(function() {
  // Handler for .ready() called.
       var parameter = "<p>Hola</p>";
       $("#content").append('<div><p>click the button</p>'+
                     '<input type="submit" name="submit_answers" value="Submit" onclick="getValue();" >'+  
                     '<input type="submit" name="submit_answers" value="Submit" onclick="'+getValue2(parameter)+'" >'+                        
                     '</div>');
});

function getValue2(parameter){
    alert(parameter);
}

function getValue(){
    alert("Hola");
}

The first input works very well, but the second input dosen´t work after document is ready. What´s the better way to declare a function in this case?

Solution

You could do this:

onclick="getValue2("' + parameter + '")"

But something like this would be better:

var $div = $('<div><p>click the botton</p></div>');
var $button = $('<input type="submit" name="submit_answers" value="Submit">')
    .data('parameter', parameter)
    .click(function () {
        getValue2($(this).data('parameter'));
    }).appendTo($div);

$("#content").append($div);

Answered By - Jason P

Answer Checked By - Gilberto Lyons (PHPFixing Admin)

Read More

[FIXED] How do I append lists in Prolog?

Issue

How do I append lists in Prolog? I've searched on the Internet and I found this (from http://www.csupomona.edu/~jrfisher/www/prolog_tutorial/2_7.html)

append([X|Y],Z,[X|W]) :- append(Y,Z,W).  
append([],X,X).

So it gets the Z by removing the elements of [X|Y] in [X|W]. But how do I append two lists together?

Example,

appendlist([1,2],[3,4,5],X).

The result will be X = [1,2,3,4,5].

Also I don't know what happening in the recursion. (I traced it but didn't understand)

EDIT: What I want to know is how it should be coded to function like the predefined append() in Prolog.

Solution

The code as you've posted it is (almost) OK. The order of clauses just needs to be swapped (in order to make this predicate definition productive, when used in a generative fashion):

append( [], X, X).                                   % (* your 2nd line *)
append( [X | Y], Z, [X | W]) :- append( Y, Z, W).    % (* your first line *) 

This defines a relationship between the three arguments, let's say A, B and C.

Your first line says, " C is the result of appending A and B if A and C are non-empty lists, they both have the same head (i.e. first element), and the tail of C is the result of appending the tail of A with the same 2nd argument, B".

  a        a
  ----------
  b        b
  c        c
  .    d   d
       e   e
       .   .

Or from left to right:

         a | b c .
           |     d e .
         a | b c d e .

append(         [], 
                 Z,
                 Z ).       
append( [X | Y   ],
                 Z,
        [X |         W ] ) :- append(
             Y,  Z,  W).

Think about it, it makes perfect sense. What it does is, we want to define the append/3 relationship, and we know what we want it to be, so we just write down some obvious facts about it that we want it to fulfill, the laws that it must follow if you will.

So assuming we have this code already defined for us, what laws must it follow? Obviously, appending a tail of some list with another list gives us a tail of result of appending the full list with that 2nd list.

This defines how we "slide along" the first list. But what if there's nowhere more to slide? What if we've reached the end of that list? Then we've arrived at the empty list, and appending an empty list with another list gives us that list as the result. Obviously. And that's what that 2nd line in your code is telling us, it says, "appending an empty list with another list produces that list as the result".

Amazingly, having written down these two laws that append/3 must follow, is the same as writing down the definition itself.

addition: this explains it from a declarative point of view; do check out an answer by m09 which shows it more from the operational point of view.

Answered By - Will Ness

Answer Checked By - Mary Flores (PHPFixing Volunteer)

Read More

[FIXED] When concatenating DataFrame and Series, the series is inserted in "vertical"

Issue

I'm iterating over a DataFrame with DataFrame.iterrows():

for index, row_to_append in source_dataframe.iterrows():

Then I want to add some of the rows to some other DataFrames. For the appending I'm using:

pd.concat([destination_dataframe, row_to_append], axis=0, join='outer', ignore_index=True)

But the concatenation works in the wrong way, in fact given the destination_dataframe as:

   0         1         2         3  ...         5         6         7          class
0  0.470588  0.896774  0.408163  0.239130  ...  0.104294  0.253629  0.183333    yes
1  0.000000  0.600000  0.163265  0.304348  ...  0.509202  0.943638  0.200000    yes
2  0.176471  0.219355  0.265306  0.271739  ...  0.261759  0.072588  0.083333    yes
3  0.117647  0.987097  0.469388  0.413043  ...  0.251534  0.034159  0.533333    yes
4  0.058824  0.264516  0.428571  0.239130  ...  0.171779  0.116567  0.166667     no
5  0.058824  0.290323  0.428571  0.173913  ...  0.202454  0.038002  0.000000     no
6  0.294118  0.464516  0.510204  0.239130  ...  0.151329  0.052519  0.150000     no

And the row_to_add:

(0, 0.411765) (1, 0.36129) (2, 0.489796) (3, 0.23913) (4, 0.169471) (5, 0.241309) (6, 0.173356) (7, 0.183333) ('class', 'yes')

The output of the concatenation is:

       0         1         2  ...         6         7           class
0      0.470588  0.896774  0.408163  ...  0.253629  0.183333    yes
1           0.0  0.600000  0.163265  ...  0.943638  0.200000    yes
2      0.176471  0.219355  0.265306  ...  0.072588  0.083333    yes
3      0.117647  0.987097  0.469388  ...  0.034159  0.533333    yes
4      0.058824  0.264516  0.428571  ...  0.116567  0.166667     no
5      0.058824  0.290323  0.428571  ...  0.038002  0.000000     no
6      0.294118  0.464516  0.510204  ...  0.052519  0.150000     no
0      0.411765       NaN       NaN  ...       NaN       NaN    NaN
1       0.36129       NaN       NaN  ...       NaN       NaN    NaN
2      0.489796       NaN       NaN  ...       NaN       NaN    NaN
3       0.23913       NaN       NaN  ...       NaN       NaN    NaN
4      0.169471       NaN       NaN  ...       NaN       NaN    NaN
5      0.241309       NaN       NaN  ...       NaN       NaN    NaN
6      0.173356       NaN       NaN  ...       NaN       NaN    NaN
7      0.183333       NaN       NaN  ...       NaN       NaN    NaN
class       yes       NaN       NaN  ...       NaN       NaN    NaN

Where the row gets added "vertically" and not horizontally.

While I want:

       0         1         2  ...         6         7           class
0      0.470588  0.896774  0.408163  ...  0.253629  0.183333    yes
1           0.0  0.600000  0.163265  ...  0.943638  0.200000    yes
2      0.176471  0.219355  0.265306  ...  0.072588  0.083333    yes
3      0.117647  0.987097  0.469388  ...  0.034159  0.533333    yes
4      0.058824  0.264516  0.428571  ...  0.116567  0.166667     no
5      0.058824  0.290323  0.428571  ...  0.038002  0.000000     no
6      0.294118  0.464516  0.510204  ...  0.052519  0.150000     no
7      0.411765  0.36129   0.489796  ...  0.173356  0.183333    yes <-------

I tried changing the axis, join and ignore_index parameters but still didn't get the result.

Solution

You can convert the row_to_append to dataframe

row_to_append  = pd.Series(row_to_append).to_frame().T

out = pd.concat([destination_dataframe, row_to_append], axis=0, join='outer', ignore_index=True)

Answered By - BENY

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] How to improve my append and read excel For loop in python

Issue

Hope you can help me.

I have a folder where there are several .xlsx files with similar structure (NOTE that some of the files might be bigger than 50MB). I want to combine them all together and (eventually) send them to a database. But before that, I need to improve the performance of this block of code because sometimes it takes a lot of time to process all those files.

The code in question is this:

df_list = []    
for file in location:
    df_list.append(pd.read_excel(file, header=0, engine='openpyxl'))

df_concat = pd.concat(df_list)

Any suggestions?

Somewhere I read that converting Excel files to CSV might improve the performance, but should I do that before appending the files or after everything is concatenated? And considering df_list is a list, can I do that conversion?

Solution

I've found a solution with xlsx2csv

xlsx_path = './data/Extract/'
csv_path = './data/csv/'
list_of_xlsx = glob.glob(xlsx_path+'*.xlsx')


for xlsx in list_of_xlsx:
    # Extract File Name on group 2 "(.+)"
    filename = re.search(r'(.+[\\|\/])(.+)(\.(xlsx))', xlsx).group(2)
    # Setup the call for subprocess.call()
    call = ["python", "./xlsx2csv.py", xlsx, csv_path+filename+'.csv']
    try:
        subprocess.call(call) # On Windows use shell=True
    except:
        print('Failed with {}'.format(filepath)

outputcsv = './data/bigcsv.csv' #specify filepath+filename of output csv

listofdataframes = []
for file in glob.glob(csv_path+'*.csv'):
    df = pd.read_csv(file)
    if df.shape[1] == 24: # make sure 24 columns
        listofdataframes.append(df)
    else:
        print('{}  has {} columns - skipping'.format(file,df.shape[1]))

bigdataframe = pd.concat(listofdataframes).reset_index(drop=True)
bigdataframe.to_csv(outputcsv,index=False)

I tried to make this work for me but had no success. Maybe you might be able to have it working for you? Or does anyone have any ideas?

Answered By - newUser

Answer Checked By - Katrina (PHPFixing Volunteer)

Read More

[FIXED] How to convert DataFrame.append() to pandas.concat()?

Issue

In pandas 1.4.0: append() was deprecated, and the docs say to use concat() instead.

FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.

Codeblock in question:

def generate_features(data, num_samples, mask):
    """
    The main function for generating features to train or evaluate on.
    Returns a pd.DataFrame()
    """
    logger.debug("Generating features, number of samples", num_samples)
    features = pd.DataFrame()

    for count in range(num_samples):
        row, col = get_pixel_within_mask(data, mask)
        input_vars = get_pixel_data(data, row, col)
        features = features.append(input_vars)
        print_progress(count, num_samples)

    return features

These are the two options I've tried, but did not work:

features = pd.concat([features],[input_vars])

and

pd.concat([features],[input_vars])

This is the line that is deprecated and throwing the error:

features = features.append(input_vars)

Solution

You can store the DataFrames generated in the loop in a list and concatenate them with features once you finish the loop.

In other words, replace the loop:

for count in range(num_samples):
    # .... code to produce `input_vars`
    features = features.append(input_vars)        # remove this `DataFrame.append`

with the one below:

tmp = [features]                                  # initialize list
for count in range(num_samples):
    # .... code to produce `input_vars`
    tmp.append(input_vars)                        # append to the list, (not DF)
features = pd.concat(tmp)                         # concatenate after loop

You can certainly concatenate in the loop but it's more efficient to do it only once.

Answered By - enke

Answer Checked By - Katrina (PHPFixing Volunteer)

Read More

[FIXED] How to append to a list in Terraform?

Issue

I have some code in the general form:

variable "foo" {
  type = "list"
  default = [ 1,2,3 ]
}

resource "bar_type" "bar" {
  bar_field = "${var.foo}"
}

I want to append an addition value to bar_field without modifying foo. How can I do this? I don't see any sort of contacting or appending functions in their docs.

This is 0.11.x Terraform

Solution

You can use the concat function for this. Expanding upon the example in your question:

variable "foo" {
  type = "list"
  default = [ 1,2,3 ]
}

# assume a value of 4 of type number is the additional value to be appended
resource "bar_type" "bar" {
  bar_field = "${concat(var.foo, [4])}"
}

which appends to the value assigned to bar_field while ensuring var.foo remains unchanged.

Answered By - Matt Schuchard

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)

Read More